Re: DBMS_STATS long library cache lock - instance crash with ora-4031
- From: "Rich Jesse" <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 25 Oct 2010 13:15:25 -0500 (CDT)
Hey Greg,
> partition level (granulity => partition) .
> When using dbms_stats.gather_object_stats , a library cache lock is held
> for 10 minutes , but sometimes
> there is almost instant execution.
> The table got 19 000 partitions and no global stats (there was
> dbms_stats.delete_table_stats(... cascade_partion=> false used) ),
> but I'm not sure if thats relevant only to no global stats objects. .
> As a consequence there is ora-4031 reported mostly on ges resource and
> instance is evicted .
> Oracle suggested applaing patch on 'increasing ges resource usage' :)
> but Im sure this is not the root of the problem .
> How can I diagnose further that issue ?
> Any ideas ?
I had a situation where DBMS_STATS was causing severe library cache
contention that would appear to hang the instance for a few minutes. In my
case, the contention was due to DBMS_STATS invalidating 10s of thousands of
unshared cursors (that don't use bind variables) on a few of the tables it
would hit.
Here's a little more of my story (and the solution I used) from July 2010:
//www.freelists.org/post/oracle-l/DBMS-STATS-lib-cahce-latching-from-invalidations,1
HTH! GL!
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
