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@xxxxxxxxxxxx>: > > 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 > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > 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 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. > > > Any disclosure, copying, or distribution of this message, or the taking of > any action based on it, is strictly prohibited. [v.E.1] > -- > //www.freelists.org/webpage/oracle-l > > > -- Best regards, Alex Gorbachev