Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Very high buffer gets
9.2.0.6 (on HP-UX)
From: Alex Gorbachev [mailto:gorbyx_at_gmail.com]
Sent: Monday, February 13, 2006 4:07 PM
To: Nahata, Naveen (US - Glen Mills)
Cc: oracle-l_at_freelists.org
Subject: Re: Very high buffer gets
If I recall correctly this is a bug in AQ handling. I think you should be able to find it easilly on Metalink. I think Oracle attributes LIO that is caused by QMN during queue maintenance (delayed purging) instead of attributing those LIOs to the statements causing it. What is your version?
2006/2/13, Nahata, Naveen (US - Glen Mills) <nnahata_at_deloitte.com>:
Hi All,
After tracing the QMON process which was doing lots of buffer gets, I
found this culprit. None of the three tables involved in the query have
more than 2 blocks. I know the nested loop is iterating over the blocks
but that should still not account for more than 200 blocks / execution.
Why do I see such high values for buffer gets?
select t.schema , t.name, t.flags, q.name from system.aq$_queue_tables t, sys.aq$_queue_table_affinities aft, system.aq$_queues q where aft.table_objno = t.objno and aft.owner_instance = :1 and q.table_objno = t.objno and q.usage = 0 and bitand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.name in ('DEF$_AQCALL' , 'DEF$_AQERROR') and t.schema = 'SYSTEM')for update of t.name, aft.table_objno skip locked call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message.
----------
Parse 84 0.00 0.03 0 0 0 0 Execute 84 0.03 0.03 0 0 0 0 Fetch 84 11.87 47.87 266 536288 45281 4627 ------- ------ -------- ---------- ---------- ---------- ----------
----------
total 252 11.90 47.94 266 536288 45281 4627 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 60 FOR UPDATE 62 HASH JOIN 59 NESTED LOOPS 59 TABLE ACCESS FULL AQ$_QUEUE_TABLES 59 TABLE ACCESS BY INDEX ROWID AQ$_QUEUE_TABLE_AFFINITIES 59 INDEX UNIQUE SCAN AQ$_QTABLE_AFFINITIES_PK (object id 2400) 64 TABLE ACCESS FULL AQ$_QUEUES Naveen This message (including any attachments) contains confidential
Any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. [v.E.1]
-- http://www.freelists.org/webpage/oracle-l
-- Best regards, Alex Gorbachev -- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 13 2006 - 16:29:17 CST
![]() |
![]() |