Jump to content
Esu X

Oracle statistics gathering - stale_percent and concurrent testing

Recommended Posts


I want to change "stale_percent" to 3 and "concurrent" to true, but I need to test it first. I need to improve statistics gathering on very large fast changing tables.

What is the best way to check before and after it will be set to check if this make any difference?

I read that I need to check parameter JOB_QUEUE_PROCESSES and parallel_adaptive_multi_user before I will set "concurrent".

Could you advise what values are save when setting it?

Thank you in advance.

Share this post

Link to post
Share on other sites

Hi Esu, and welcome to the forum!

Except for very volatile data, a single dbms_stats is enough.

Remember, the ONLY reason to re-analyze stats is to change execution plans. A risky thing to do in a production database.

Please read carefully:


If it ain’t broke, don’t fix it!

Hope this helps . . . 

Share this post

Link to post
Share on other sites

Sorry, but I didn't quite get that...

I thought that having statistics the newest and latest is the best option. Re-analyzing them is not a good idea ?

We have large fast changing tables, we export and import statistics when a new table is created every month and it's improve performance a little and we have statistics up to date which we are using, but recently there was problem with optimizer which couldn't take new imported statistics, so we had problems and we needed to reload old ones before importing. Now, we want to improve this, so it won't happen and maybe test new things which could improve something... 

Could you please advise. Thank you.

Share this post

Link to post
Share on other sites

Hi Esu, Re-analyzing frequently is only right for people with tables where the distribution of column values changes.

-Are you using histograms?  Please read:  http://www.dba-oracle.com/art_orafaq_cbo_stats.htm

- Do you require the testing of changes before they go into production?

- Have you proved that re-analyzing changes plans?

- Have you looked at using stored outlines to keep good plans?  And you can do the same thing with SQL profiles.    If you use plan stability you can freeze most SQL plans, so that you can compare them with new plans before making changes in production.  Please read:  


Just remember, the only reason to re-analyze stats is to change SQL plans, s risky thing to do in prod!

What was the exact problem with importing stats?

I assume that the tables are partitioned because you said that they are very large.  Have you looked at incremental stats?



Are you using global or local partitioned indexes?

Please advise, thanks!

Share this post

Link to post
Share on other sites


You repeated your whole question???

What was your issuer with my previous answer?

What part of an existing plan is no longer performant?

More important, how does importing older stats, change the plan?  Please be specific.

 It sounds like you have a volatile table so you do want to re-sample stats.

Instead of importing stats, why not re-sample them in parallel?




Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now