Re: concurrent statistics gathering

On Tue, 2024-03-19 at 08:42 +0100, Wilhelm Klotz wrote:
> Hi,
>  
> anyone using concurrent statistics gathering, as described in
> https://blogs.oracle.com/optimizer/post/concurrent-statistics-gathering
> ?  Any tricks / hints and tips?
>  
> We are on Oracle 19.20, with large OLTP databases (many db objects,
> partitioned tables) on Solaris and Linux. Statistics gathering is not
> really „concurrent“, as after some time there is no really concurrent
> processing anymore. Support is not really helpful until now.
>  
> Thanks and Regards
> willyk

Well, that depends on what else is going on on the system. Gathering table statistics requires a shared lock on the table. It also requires exclusive locks on the rows containing statistics.That lock may not be available if there is an exclusive lock on the table or if another statistics modifying operation is ongoing. If the system is idle, statistics collection will inevitably run in parallel, if concurrent gathering was specified. Such is, at least, my own experience. You will have as many processes as you specified. You can check what are they waiting for in V$SESSION_WAIT, in my opinion the single most important virtual table for fixing performance problems. You can also trace DBMS_STATS. There is a good article showing how to do that here:
https://fatdba.com/2022/07/10/how-to-trace-dbms_stats-and-see-what-is-under-the-hood-for-a-slow-sluggish-stats-collection/ That should tell you what is going on. Also, presenting the trace will help you with TR you will have to open to get the possible bug addressed. Regards

-- 
Mladen Gogala
Database SME
https://dbwhisperer.wordpress.com


--
http://www.freelists.org/webpage/oracle-l