RE: Very high buffer gets

From: Nahata, Naveen (US - Glen Mills) <nnahata_at_deloitte.com>
Date: Mon, 13 Feb 2006 17:29:17 -0500
Message-Id: <7498054865834541A868EA11FDD4D078013CE6AE@usndc0410.us.deloitte.com>

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
	------- ------  -------- ---------- ---------- ----------
---------- 


	----------

	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

Received on Mon Feb 13 2006 - 16:29:17 CST