Re: Data Dictionary Hit Ratio - myth or fact?

I can attest that Jonathan is less than 100% accurate.

He once could not remember my NAME! Oh the shame and humiliation!! I even think he called me BOB once!! ACKKKK!!!

:-)

Still with respect to things Oracle, I still rather think he stands as one of the great ones.

And he's a darned nice guy too.  

Robert G. Freeman
Author:
Oracle Database 11g New Features (Oracle Press) Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press) Oracle9i RMAN Backup and Recovery (Oracle Press) Oracle9i New Feature
Blog: http://robertgfreeman.blogspot.com (Oracle Press)

• Original Message ---- From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> To: oracle_l <oracle-l_at_freelists.org> Sent: Sunday, March 23, 2008 3:34:57 AM Subject: Re: Data Dictionary Hit Ratio - myth or fact?

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_at_xxxxxxxxx> To: "Jared Still" <jkstill_at_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.

--
http://www.freelists.org/webpage/oracle-l





--
http://www.freelists.org/webpage/oracle-l