Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Competition for OraPerf

Competition for OraPerf

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 3 Nov 2006 11:19:46 -0000
Message-ID: <bY-dnRlFbLJUuNbYRVnyhA@bt.com>

I see the Burleson promotion engine has gone into action again - with assistance from Texas Memory Systems.

See http://www.statspackanalyzer.com

There's even a sample of a report - which happens to have a link to a Burleson article on average about every 3rd line of text

Here's a couple of details from the report:


    Parses: 63/s
    Hard parses: 0/s

    You are performing more than 63 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)


Interesting - so if that's what "Parses" are, what are "Hard Parses" ? Parses that were for really difficult SQL statements perhaps ? And if a "parse" executes your SQL, what does an "execute" do ?
(Actually, DDL can be executed on a parse call, and statements
can be optimized on the execute call - but that's just one of those little details).

For the less well informed, "Parses" which are not "Hard Parses" are re-using shared cursors which have not been explicitly held open for re-execution by some layer of the client software. And if you have your session_cached_cursors set, then a parse IS nearly as efficient as re-using a held cursor.

If you want to ANALYZE parses, you have to look at three or four statistics at once - then check the activity and impact of the relevant latches.


    Top Timed Events:

    db file sequential read     22%
        Moving your indexes to solid state disks can reduce the
        amount of time spent waiting for this event.

    CPU time    21%
        Solid state disks help to increase the CPU time by reducing
        IO related wait events. If this is the main wait event, tuning
        SQL statements and/or increasing server CPU resources
        will provide the greatest performance improvement.

------------------------------------------------------------------------

I wonder what the other 60% was ? Neither of these figures seems too extreme - after all you've got to spend your time somewhere. But Solid State Disk for both as a 'solution' ? Is it a coincidence that the other name on the website is Texas Memory ?

Of course, single block reads from tables are also registered as db file sequential reads, and funnily enough indexes are more likely to be buffered in the database cache because they are a lot smaller than tables and have a different pattern of use. So why is the advice 'move the indexes' and not 'move the tables' ?
(Maybe because moving the tables is clearly no feasible in most
cases, but moving the indexes sounds as if it might be affordable ?) Possibly, to ANALYZE the statspack report this bit of code should have jumped to the Segment Statistics at this point to see if any indexes really needed to be on SSD.

By the way - Solid state disks help to INCREASE CPU time, but if this is the main wait (!!!) event then tuning the SQL will provide the greatest improvement. (Or adding more CPU !!!)

So if we combine the two pieces of advice:

    Add Solid State Disk until CPU time is the highest "wait" event,     then tune the SQL:.

And let's not forget that if you add CPU, you are probably going to use the same amount of CPU, but spread over more CPUs, so that "wait" time won't drop. In fact, with more CPUs, you are likely to get more competition for the Bus, which means more CPU stall time, which could mean spending much more CPU time to do the same amount of work.


Physical writes: 1,947/s

You are performing more than 1,947 disk writes per second. Check to ensure that you are using direct I/O (non buffer) on your database and perform an IO balance and an IO timing analysis to determine if a disk or array is undergoing IO related stress, and verify your DBWR optimization. Check you average disk read speed later in this report and ensure that it is under 7 milliseconds. If not, consider SSD for faster physical reads.


Changing the I/O patterns or configuration doesn't change the number of writes you do. And we didn't see (apparently) any indication that we might have a write TIMING problem in the sample Top Timed Events. And why is this ANALYZER telling us to go and analyze the rest of the statspack file - shouldn't it be cross-referencing related bits of information for us.

At least it does tell us to look at disk speeds. But why is it talking about read speeds when the statistic is about writes ? Oh, look - it's that bit about solid state disc again.


Competition for OraPerf ? Maybe not; still, as the home page says:

    This website is not a substitute for your favorite Oracle consultant     or even to replace the well-liked OraPerf.com website. .

Now, Oraperf isn't a miracle worker - but it's been around so many years you would have thought that the next person who seriously wanted to try something similar would have produced something a little better.


-- 
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
Received on Fri Nov 03 2006 - 05:19:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US