top down approach to reducing io from awr report

From: dba cjb <chris.brown_at_providentinsurance.co.uk>
Date: Mon, 22 Aug 2011 03:40:23 -0700 (PDT)
Message-ID: <7f5f3dee-5f2a-4e4c-9cb8-5ce81dbce84c_at_s7g2000yqd.googlegroups.com>

oracle 10.2.0.4 enterprise on windows 2003

I am looking at awr reports for most disk intensive sql / the trouble i am finding that pl/sql code accounts for most of the work

eg

Physical Reads	Execu tions	Reads per Exec	%Total	CPU Time (s)	Elapsed
Time (s)	SQL Id	SQL Module	SQL Text
53,165,663	0		                               12.63
3649.28	                4614.59



---------------------------------------------------------------------------------------------------

sqlid is 12y4 2rjqn8bdh	SQL*Plus code is 	DECLARE vFunc

VARCHAR2(30) :...

---

Is there any way I can dig deeper to find the sql that is doing most of the work
/ the sql_id for the pl/sql doesn't give me anything to look at ...or am I restricted at what I can drill down on via sql_id

My general aim is to reduce i/o overhead where possible & I want to attack the 'biggest' culprits
by looking at explain plans.

I would welcome any ideas on drilling further om pl/sql code or comments on alternative approaches?

regards
Chris B Received on Mon Aug 22 2011 - 05:40:23 CDT