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

On Mon, 22 Aug 2011 03:40:23 -0700, dba cjb wrote:
---------------------------------------------------------------------------------------------
The best way would be to collect the execution trace for the processes
using that particular PL/SQL and analyze it with tkprof or orasrp.
Profiling the PL/SQL code with the DBMS_PROFILER also wouldn't be a bad
idea.



--
http://mgogala.byethost5.com

dba cjb:

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

....

Start by purchasing and reading Cary Millsaps "Optimizing Oracle
Performance" book.

On Mon, 22 Aug 2011 07:38:57 -0700, John Hurley wrote:


Are you getting a commission from Cary?



--
http://mgogala.byethost5.com

On Aug 22, 8:36*am, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
lol, a fair observation, but they have been appropriate IMO. People
need to be told they have to follow some methodology, it isn't obvious
that AWR by itself isn't enough, though it often can at least point at
the problem. Sometimes it points at the victims of the problems
rather than the actual problems.

jg
--
@home.com is bogus.
Never underestimate corporate idiocy.
http://www.itp.net/10261-oracle-take...ound-with-sap-

On Mon, 22 Aug 2011 11:02:29 -0700, joel garry wrote:

I agree, it was just a tongue in cheek remark. I can wholeheartedly
recommend reading Cary Millsap's book myself, only John did that twice,
in a very short period of time.



--
http://mgogala.byethost5.com

Mladen:

# I agree, it was just a tongue in cheek remark.

....

No harm no foul! Looking forward to Cary's presentation at OOW 2011
myself.

# I can wholeheartedly recommend reading Cary Millsap's book myself,
only John did that twice, > in a very short period of time.

....

I hope to do it again a 3rd and 4th time if relevant! Wishing Cary
would do his own book on attacking stuff via the most recent tools
that Oracle has available ... not that there is anything incorrect
about the Method R approach!

On Aug 22, 8:50*pm, John Hurley <hurleyjo... (AT) yahoo (DOT) com> wrote:
thanks to all for your input
/ the next step in my education begins!


Chris B