Re: Data Dictionary Hit Ratio - myth or fact?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle_l" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 23 Mar 2008 09:34:57 -0000
Andrew.
You are assuming that Lewis is 100% accurate in everything he says and
statspack analyzer isnt? Not necessarily a good assumption...
I know that I am not 100% accurate in everything I say - so I agree that
the first assertion is not a good assumption.
The second assertion is an excellent assumption. StatspackAnalyzer is
clearly not 100% accurate.
I have
never seen the
particular phrasing he quotes, so I suspect it is no longer used in any
case.
See http://www.statspackanalyzer.com/sample.asp
The original sample output has been replaced since I wrote my notes,
but, despite the fact that the new one shows a rate of zero per second
for "Hard parses", the explanation still says:
You are performing more than 1,098 SQL parses per second. A parse is the
process of executing
your SQL, checking for proper security authorization, checks for the
existence of tables, columns,
and other referenced objects, and generating an execution plan. Your high
parses suggest that your
system has many incoming unique SQL statements or that your SQL is not
reentrant (i.e. literal
values in the WHERE clause, not using bind variables). Confirm that the
1,098 parses per second
is reasonable and consider setting cursor_sharing=force if warranted.
Setting cursor_sharing=force
can cause dramatic performance improvements for systems with ad-hoc query
tools such as
Crystal Reports or Business Objects.
Would you care to post an example of the text you've seen when you've
sent in a Statspack or AWR report that has a high rate of parse calls ?
If you want other examples of accuracy, check
http://forums.oracle.com/forums/thread.jspa?threadID=631269&tstart=50
In that example, the "db file sequential read" accounts for 1.56%
of the elapsed time. The only suggestion made by StatspackAnalyzer
with regard to the Top 5 Timed events is:
The sequential read event occurs when Oracle reads single blocks of a table
or index. Look at the tablespace IO section of the report for tablespaces
with
less than 2 average blocks per read, high response time, and a large
percentage
of the total IO. Improving the response time of these tables with faster
storage
will help reduce this wait event and speed up the database. Moving the data
files
with the largest amount of time spend waiting on single-block reads to
faster storage
can significantly reduce the amount of time spent waiting on this event. By
reducing
the time spent waiting on this event, the database performance could
increase 2%.
Technically you can claim that every detail in that statement is close to 100%
accurate -
when viewed in isolation. But if it's supposed to be a helpful analysis of a
Statspack
report, it's far from accurate, particularly when you cross-check and discover
that the
system apparently waited on "db file sequential read " 133,334 times for an
elapsed time
of 210.76 seconds - which is an average of: 1.58 milliseconds: so most of those
I/Os are
coming out of a cache somewhere anyway and faster storage isn't going to help.
Statspack analyzer is currently very good at identifying the number of soft
v. hard parses, which is very important to know. I have used it several
times, and found it accurate.
Please feel free to send us an example that demonstrates this.
As you will see from the link to the forum, Don Burleson doesn't
seem to mind when people publish their StatspackAnalyzer outputs,
even though there is a copyright notice attached to the output.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "Andrew Kerber" <andrew.kerber@xxxxxxxxx>
To: "Jared Still" <jkstill@xxxxxxxxx>
Date: Wed, 19 Mar 2008 13:13:37 -0500
You are assuming that Lewis is 100% accurate in everything he says and
statspack analyzer isnt? Not necessarily a good assumption... At the very
least, that article is over a year old, and according to the statspack
analyzer site, its analysis is continually revised. I have never seen the
particular phrasing he quotes, so I suspect it is no longer used in any
case.
Statspack analyzer is currently very good at identifying the number of soft
v. hard parses, which is very important to know. I have used it several
times, and found it accurate.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
