Re: top down approach to reducing io from awr report
Date: Mon, 22 Aug 2011 05:38:06 -0700 (PDT)
Message-ID: <75ed14ae-b458-4071-8edf-b82d54f57c1b_at_br5g2000vbb.googlegroups.com>
On Aug 22, 6:40 am, dba cjb <chris.br..._at_providentinsurance.co.uk> wrote:
> 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
Are you able to view the source code for that PL/SQL function? If so, you could then see the SQL statements that are executed, find the SQL_ID for those SQL statements by searching V$SQL, and then check the AWR data. It could very well be the case that there is a SQL statement that by itself consumes very few resources, but when it is executed 1,000,000 times in the PL/SQL code, the total executions for the SQL statement become a significant factor.
You could monitor the changes in values of V$SQLSTATS to hopefully pick out the SQL statements with a large change in the value of DISK_READS, there is an example in the book that I co-authored (the script is in the source code download for the book): http://books.google.com/books?id=tUNtyMCwDWQC&pg=PA216#v=onepage&q&f=false
Another idea is to monitor the various wait events. If you have a copy of Microsoft Excel, you can try a project that I have been putting together that monitors the Oracle time model statistics, OS Statistics, wait events, and more (part 5 of the series adds DBMS_XPLAN, querying V$SQLSTATS, and enabling a 10046 trace): http://hoopercharles.wordpress.com/2011/08/18/oracle-database-time-model-viewer-in-excel-6/
Ideally, what you would want to do is identify a single session that
executes the PL/SQL function and enable a 10046 trace at level 8 (wait
events or level 12 (wait events with bind variables). After enabling
the trace (wait until the application connects before enabling), start
the trace and then perform the action that executes the PL/SQL
function. When finished, exit the application to make certain that
the execution plan is written to the trace file. There are a variety
of resources to help read the 10046 trace file, including TKPROF.
Part 1 of a 3 part series that I wrote:
http://hoopercharles.wordpress.com/2009/12/01/10046-extended-sql-trace-interpretation/
You do not necessarily want to look at the explain plans for the SQL statement, however the row source operation execution plans, as output by TKPROF (and found in the 10046 STAT lines), are a bit more helpful because they show the actual execution plan and work performed, but it is also helpful to compare that output to the explain plan for a SQL statement to see if the optimizer's estimates are reasonably accurate.
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Mon Aug 22 2011 - 07:38:06 CDT