Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Very high buffer gets
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.namein
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
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-lReceived on Tue Feb 14 2006 - 10:33:43 CST