| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> 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
Thanks for sharing the write up. I can only wonder how confused a person who is unfamilar with Oracle would be after reading portions of the report.
Some of the suggestions offered by the sample report would have been better if they were lifted directly from the Oracle documentation. Just a couple examples:
"You have excessive buffer busy waits with 3.1 per transaction. Buffer busy waits are most commonly caused by segment header contention and can be remedied by increasing the value of the tables & index freelists or freelist_groups parameters, tuning your database writer (DBWR process, or by using Automatic Segment Storage Management (ASSM) in the tablespace definition. Using super-fast SSD will also reduce buffer busy waits because transactions are completed many times faster."
Without examining V$WAITSTAT, you could very well be throwing hardware/disk storage at a problem that is caused by contention for the same block that is already in memory - this hardware/disk storage improvement may adversely affect the performance of a critical application process, if it removes the bottleneck that prevented a less critical application process from consuming a greater percentage of system resources (Cary Millsap discusses this in his tuning book, and it seems as though it was also mentioned in Tales of the Oak Table).
Trying to learn how to best utilize Oracle by reading online articles, print magazines, and some books is a bit like taking a stroll through a mine field at night.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Fri Nov 03 2006 - 07:52:31 CST
 |
 |