Re: Competition for OraPerf

Jonathan Lewis wrote:
> 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

Lots of high end systems apparently that one of the Cherry Sisters works on.

63 parses a second is too much.

Maybe just ask the people to use the system less often? Received on Fri Nov 03 2006 - 09:55:54 CST