Gather Schema Statistics

[satish]

Hi erman,

We have a scheduled program which runs once every month.Can we run GSS program with 100 percent to this specific schema so that we can get more accurate plans.The schema is not big and can we do that with 100%

Thank you

[satish]

I will be more clear.

We have a custom program that runs once every month.Size of the custom schema is 2GB.
My concern is,can we run GSS program with estimate_percent of 100% to give good statistics to optimiser so that it can give us efficient plans.

Is there any issue if we run GSS program with estimate_percent of 100?

Thank you,

[ErmanArslansOracleBlog]

Hi Raj,

There is no optimized value for that estimate percent . It is the estimate sample percentage actually.

Oracle Support perfectly explains this ->

SQL performance should decide what needs to be done (e.g. if a SQL query completes in 1 second at 10% then what will the real gain be of using 40%). The E-Business suite document recommends a default of 10 but the more records in the table the higher the estimate percent should be .

Also, basically, when youı have a higher value for estimate percent, than your stats collection scheduled program will take longer time to complete.

[satish]

Thanks erman.

I agree that it will run long but there is no harm if we run at higher percent as it is only 2GB right?

[ErmanArslansOracleBlog]

high estimate percent means high processing and more accurate info for the optimize.

So in your example, you should decide that. It depends.

It depends on your processing power, the load that it brings and what you earn in terms of sql performance.