Re: Competition for OraPerf

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: 6 Nov 2006 10:24:03 -0800
Message-ID: <1162837443.023701.272140@h48g2000cwc.googlegroups.com>

Richard Foote wrote:
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:bY-dnRlFbLJUuNbYRVnyhA_at_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.
> >
> > =========================================

>

> Hi Jonathan

>

> All depends on who the next person might be ...

>

> This tool reminds me of those awful remakes of Hollywood classics where the
> originals are sooo much better and you wonder why they bothered.

>

> There are sooo many errors, inconsistencies, ambiguities and down right
> misleading advice in the sample report that it's hard to know where to
> begin. Some of the things that instantly spring to mind however include:

>
>

> As you say, only 43% of the wait events are accounted for and that the
> report appears to simply just ignore more than ½ of all database waits. It's
> actually quite "normal" for CPU and db file sequential reads to be among the
> top timed events although it's a little unusual for them to account for just
> 43% of activity. There are likely to be a least 3 or 4 other significant
> events the report just ignores which could be of more interest than the two
> listed.

>
>

> With CPU contributing just 21% of time, one of the key recommendations is to
> increase the speed or number of CPUs !!

>
>

> There's at least a dozen or more recommendations for the use of Solid State
> Disks (SSD) with little justification. For example it recommends SSD for
> caching frequently accessed small tables/indexes and then immediately
> recommends storing them of SSD !! As you mention, it recommends storing just
> indexes on SSD to reduce db file sequential reads waits when indexes are
> likely to contributed only a tiny proportion of these waits as indexes are
> more likely to be cached than their tables and are only accessed a small
> percentage of times in comparison to table blocks for any significant range
> scan. Also, db file sequential read waits are only 2ms on average, and yet
> SSD are still recommended. Indeed even if SSD were to be used, the
> recommendation to move to SSD again would still be prevalent in the report
> !!

>
>

> Indeed it recommends SSD to tune the small table scans without considering
> that the large block size in combination with a large
> db_file_multiblock_read_count (64) in combination with missing indexes in
> combination with missing system statistics could all be the culprit/cure.

>
>

> In the load Profile part of the report, it recommends dealing with physical
> writes by moving to SSD to increase the physical read performance. What the
> ?

>
>

> It totally confuses and misinterprets the meaning of parses and hard parses,
> and provides inappropriate advice as a result.

>
>

> It recommends a large block size for indexes even though the block size is
> already at 16K and there's no indication that index performance is an issue
> and no mention of the disadvantages of this approach.

>
>

> In the Instance activity stats part of the report, it recommends dealing
> with the so-called high physical I/O by increasing the buffer cache or
> moving to SSD (of course), not one mention of perhaps tuning the code that
> is driving the high PIO demand.

>
>

> It totally confuses the distinction between a migrated and a chain row as it
> doesn't always cause double I/Os in both cases, aren't not necessary both
> fixed by reorganising tables, are neither necessarily best fixed with
> dbms_redefinition, may not need pctfree to be readjusted and it's migrated
> rows that might be fixed and prevented with a better pctfree. Oh, and
> pctfree may actually need to be reduced during the reorg.

>
>

> Tablespace I/O stats appear to miss a few rather important tablespaces,
> namely SYSTEM, UNDO, TEMP. Perhaps their performance is not so important or
> perhaps they're already on SSD !!

>
>

> A little odd that tablespace I/O stats is concerned about the speed of
> tablespace TABLE_C at 11.4 ms average but not TABLE_B which is only a little
> faster at 9.6 ms (somewhat slower than the 7ms recommended at the start of
> the report) or TABLE_G at 24.8 ms or TABLE_H at 31.5 ms. Especially
> considering existing hardware is capable of supplying 50% of I/O at 1 ms as
> per TABLE_A (perhaps it's already on SSD :) Also, low usage doesn't
> necessarily mean low importance.

>
>

> Latch activity lists Gets Requests statistics for the redo copy latch
> despite it being a Nowait Request latch (no wonder it's 0 !!). It also
> complains about cache buffer related latch performance but in the next
> section recommends doubling the size of the buffer cache from 10G to 20G,
> potentially putting more stress on these latches. Also, decreasing the
> compactness of tables by reorganising them with a higher pctfree is a rather
> expensive method to fix a potentially minor problem (0.2 percent latch
> misses) and could introduce much much bigger ones. Perhaps reducing LIO
> might be a slightly more effective strategy !! It also recommends reducing
> redo allocation contention by increasing the redo log size although this may
> also have the opposite effect.

>
>

> In the shared pool advisory section, the report recommends doubling the
> shared_pool and in the next section warns that the shared pool is already
> "unusually large" !!

>
>

> I could go on and on but life is too short. This is nothing more than a
> rather silly and embarrassingly blatant SSD marketing exercise. At the end
> of the day, what does the report actually achieve, what are the performance
> issues that have been resolved, what were the database problems that needed
> addressing ?

>

Good point.

This thread continues on with many people "wondering about" the performance characteristics of solid state disk and getting confused with flash memory and usb devices.

Flash memory and usb drives aren't intended to be used as ssd. Flash memory is not terrible at read performance but doesn't work well for write performance. In addition flash memory gets destroyed eventually with enough writes. Not exactly a stable situation for redo logs or any other oracle files.

Read ssd can/would use a high speed data transfer technology ( scsi interface or sata etc ) and contains high quality memory chips ( not flash ).

Wonder no more.

>

> I think it's all best summed up with the following quote from the disclaimer
> on the home page "Even a perfect interpretation of your Statspack could lead
> to inaccurate recommendations that do not improve Oracle database
> performance". In other words, if we happened to guess a real problem, we may
> not be able to guess our way to a real solution !!
>

That sounds like a chorus line by a song performed by the Cherryh Sisters. Of course Cary Millsap's believes in predicting improvements in oracle performance but that involves real work and improving problems that yield a tangible benefit to the customer. Received on Mon Nov 06 2006 - 12:24:03 CST