Esu X
asked on
Oracle statistics gathering - stale_percent and concurrent testing
Hello...
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_us
Could you advise what values are save when setting it?
Thank you in advance.
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_us
Could you advise what values are save when setting it?
Thank you in advance.
you might need to lock statistics and not gather new stats ...
might also be a viable solution !
might also be a viable solution !
ASKER
@ slightwv (䄆 Netminder)
We have large fast changing tables, when we export and import statistics when a new table is created every month 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 and we had problems and we needed to reload old one before importing. Now, we want to improve this, so it won't happen and maybe test new things which could improve some things... that's why I asked this question. Statistics gathering is working, but maybe it could work better.
@ Geert G
It's a good idea to lock statistics and not gather them at all ? I thought that having them the newest and latest is the best option.
We have large fast changing tables, when we export and import statistics when a new table is created every month 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 and we had problems and we needed to reload old one before importing. Now, we want to improve this, so it won't happen and maybe test new things which could improve some things... that's why I asked this question. Statistics gathering is working, but maybe it could work better.
@ Geert G
It's a good idea to lock statistics and not gather them at all ? I thought that having them the newest and latest is the best option.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
I read your previous question on this subject and I still don't understand what problem you are trying to solve.
"improving statistics gathering" isn't quantifiable therefore you cannot measure it.
I'm guessing the default maintenance windows aren't working correctly which is why you are looking to mess with them.
What problems have you observed that has caused you to look into this? It needs to be measureable and reproducible.