 |
|
Oracle Tips by Burleson |
3rd Party Profiler: Database
Call Statistics (part II)
Notice the breakdown of time spent on
parsing and executing as well as the number of times these events
happened for this statement. The total number of rows in the Fetch
line is 6. On average, it took over 1,000 executions to get one
row. One piece of information to make note of is the Action Count
column which indicates that this statement was executed almost 7,000
times and was parsed the same number of times as it was executed.
For some reason, it appears this statement is being parsed once for
every time it executes which causes additional SQL*Net traffic. It
is also responsible for unnecessary CPU utilization. Review Figure
6.9 and notice that CPU time is the number two component of this
response time. Significantly reducing parse activity would reduce
the two biggest components of this job. Reducing parsing activity
to one parse to many executes is the goal. If this process were
changed to eliminate all unnecessary parses, there is the very real
potential of cutting the run time of this job by 50% or more.
If excessive parsing is a problem, the
following script, parse_v_execute.sql, can be used for ongoing
monitoring:
* parse_v_execute.sql
--
*************************************************
-- Copyright © 2003 by Rampant TechPress
-- This script is free for non-commercial
purposes
-- with no warranties. Use at your own
risk.
--
-- To license this script for a commercial
purpose,
-- contact info@rampant.cc
--
*************************************************
/* parse_v_execute.sql */
column sql_text format a50
column parse_calls format 9,999,999
column executions format 9,999,999
column ratio format 999.99
heading "Parse-Execute|Ratio"
column loaded_versions format 999,999
heading "Loaded|Versions"
set pages 1000
select
sql_text,
parse_calls,
executions,
parse_calls/executions "Ratio",
loaded_versions
from
v$sql
where:
See Code Depot
The above book excerpt is from:
Oracle Wait Event Tuning
High Performance with Wait
Event Iinterface Analysis
ISBN 0-9745993-7-9
Stephen Andert
http://www.rampant-books.com/book_2004_2_wait_tuning.htm |
