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
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> 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]
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
Best regards,
Alex Gorbachev

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