Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Competition for OraPerf
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.htmlReceived on Fri Nov 03 2006 - 05:19:46 CST