RE: Very high buffer gets

From: Joseph Amalraj <joseph_at_amalrajinc.com>
Date: Tue, 14 Feb 2006 08:33:43 -0800 (PST)
Message-ID: <20060214163343.5208.qmail@web405.biz.mail.mud.yahoo.com>

Can you tell, what is the blocks
  from dba_segments
  for these segments.    

  Thanks

"Nahata, Naveen (US - Glen Mills)" <nnahata_at_deloitte.com> wrote:

      I did a full compute, see below:    

    1 select blocks, num_rows, sample_size, to_char(last_analyzed, 'DD-MON-YYYY HH24:MI:SS')   2 from all_tables
  3* where table_name in ('AQ$_QUEUE_TABLES', 'AQ$_QUEUE_TABLE_AFFINITIES', 'AQ$_QUEUES') SQL> set lines 100
SQL> /           BLOCKS NUM_ROWS SAMPLE_SIZE TO_CHAR(LAST_ANALYZED,'DD-
---------- ---------- ----------- --------------------------

         1         61          61 06-FEB-2006 15:50:09
         2        136         136 06-FEB-2006 15:51:14
         1         61          61 06-FEB-2006 15:50:48
   

  I figured out with the help of John Clarke, that there are too many CR blocks being read, but I still fail to understand why 645 blocks / row?    

  Am going to do a 10201 trace alongwith 10046 trace to figure out what's going on.    

  Naveen

  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

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

Received on Tue Feb 14 2006 - 10:33:43 CST