Re: high gets per execution for an update based on rowid

From: Denis <denis.sun_at_yahoo.com>
Date: Wed, 11 Apr 2012 05:04:28 -0700 (PDT)
Message-ID: <1334145868.82615.YahooMailNeo_at_web161803.mail.bf1.yahoo.com>

Suspect hit a bug, asked Oracle to confirm in the SR
 

==> seeing latch: row cache objects and enq: US - contention wait event during bad time for the problem sql when run at 2K/execution
 

select SAMPLE_TIME, sql_id, SQL_PLAN_HASH_VALUE, event from dba_hist_active_sess_history where sample_time between to_date('2012-04-10 13:00', 'YYYY-MM-DD HH24:MI') and to_date('2012-04-10 14:00', 'YYYY-MM-DD HH24:MI') and sql_id='9x1h5r7vgd662';
 

output excerpt:

10-APR-12 01.30.14.337 PM 9x1h5r7vgd662 2573686592 latch: row cache objects
10-APR-12 01.30.14.337 PM 9x1h5r7vgd662 2573686592 latch: row cache objects
10-APR-12 01.31.25.555 PM 9x1h5r7vgd662 2573686592 enq: US - contention
10-APR-12 01.32.57.095 PM 9x1h5r7vgd662 2573686592 enq: US - contention
10-APR-12 01.35.49.826 PM 9x1h5r7vgd662 2573686592 enq: US - contention
10-APR-12 01.47.51.979 PM 9x1h5r7vgd662 2573686592 latch: row cache objects
10-APR-12 01.48.02.227 PM 9x1h5r7vgd662 2573686592 latch: row cache objects ...

 
 

==> Bug 7291739
-- Symptoms
Waits for "latch: row cache objects"
Waits for "enq: US - contention"
-- Description
High 'latch: row cache objects' contention may be seen on dc_rollback_segments together with high 'enq: US - contention' waits when using system managed undo with an auto tuned undo retention period.
 

Not sure if the memory setting change especially from sga_target=0 to 12000M, which introduced ASMM could be the trigger of the bug.
 

Denis

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

Received on Wed Apr 11 2012 - 07:04:28 CDT