| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: queries too slow
Steve,
Comments included.
Gaja.
> I have seen instances where tuning the SQL actually lowers the
> cache hit
> ratio because the queries are no longer wasting time
> repeatedly hitting on
> data that wasn't needed in the first place.
BINGO...exactly my point!
> Better "un-tune" that SQL so you can brag about achieving a
> high cache hit ratio. ;-)
Sound like a "Job Security" issue....;-)
>
>
> Steve Orr
>
>
> -----Original Message-----
> Krishna Vaidyanatha
> Sent: Tuesday, July 11, 2000 12:20 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Friends,
>
> Here we go again, the false correlation between db cache hit
> ratio and its effect on performance. We just got done with a
> similar thread a couple of weeks ago. Even without seeing
> anything in Prakash's environment, I would probably guess that
> the current size of the database buffer cache may be OK. A
> 79%
> cache hit ratio is by no means "WAY too low". WAY too low
> compared to what - 99%? Is there any guarantee that at 99%
> cache hit ratio that the queries will be screaming? I
> strongly
> doubt it.
>
> There are way too many folks who pin all of their hopes in
> performance increase by throwing memory at the Oracle SGA,
> specically at the db buffer cache.
>
> Myth : Quite possibly one of the biggest performance myths out
> there is, "If I do not run my Oracle database with a cache hit
> ratio of 90% or above, I have bad performance".
>
> Performance needs to be measured by "system throughput", how
> much work has the system done for you. We have even dealt
> with
> systems with 65-70% cache hit ratios where the performance of
> the transactions and the queries was steller. The incremental
> increase in cache hit ratio that one achieves by configuring
> the
> db buffer cache with more memory, does not do much to system
> performance.
>
> Hence the memory should be probably utilized for something
> else
> (e.g. sort_area_size, hash_area_size etc.). Like anything in
> life, the law of diminishing returns will come to play sooner
> or
> later. Further, the reference to a 99% cache hit ratio and
> the
> assumption that performance is great as a result, it not
> correct. Not by a long shot.
>
> While a reasonable cache hit ratio is required to avoid an
> inordinate amount of physical I/O, the bigger issue that needs
> to be tackled is "What is causing such a high amount of
> physical
> I/O?". 9.9 out of 10 times, it is an application problem. May
> be the application needs to perform a "full table scan"
> instead
> of an "index scan" or vice versa. May be the index used is
> not
> optimal. It does not have all the "required columns". A bad
> cache hit ratio is a "symptom", while a bad query is "the
> disease".
>
> Another point to keep in mind is that on systems that are
> configured with mass storage devices (which have
> controller/disk
> level caches), a physical I/O from Oracle's perspective still
> does not mean "physical I/O from disk". It could mean
> "logical
> I/O from the storage array's cache". There is more to
> performance than the cache hit ratio of your db buffer cache.
>
> Fact: Within reason and hardware limitation if the db buffer
> cache is configured optimally, the cache hit ratio is only a
> very small indicator/symptom of the bigger problem/disease.
>
> Remember the 80-20 rule. 80% or more of our problems with
> Oracle is with bad SQL and no matter how much memory you throw
> at Oracle, if the disease is not cured, the symptoms will
> still
> exist. A 99% cache hit ratio may not mean much when measuring
> system performance and system throughput. I personally prefer
> a
> system which has a 99% throughput factor than a 99% db buffer
> cache hit ratio.
>
> Don't get me wrong I am all for "logical I/O" instead of
> "physical I/O" and I am fully aware of the 3 orders of
> magnitude
> in performance difference between the two. I am all for
> giving
> Oracle "enough" memory. But only upto a certain point. I'd
> rather spend my time fixing, re-writing and tuning my queries.
> The performance difference that I get in tuning a query is
> many
> times higher than 3 orders of magnitude. I believe in curing
> diseases not symptoms. Let's go tune those awful queries.
>
> Best Regards,
>
> Gaja.
>
> --- Jack Silvey <JSilvey_at_XOL.com> wrote:
> > Prakash,
> >
> > 79% is WAY too low. For an OLTP system (small number of rows
> > returned) you
> > should see in the >90% range, and want to see >95% range.
> >99%
> > is ideal. Get
> > after that DBA! It will help greatly across the board.
> >
> > Now, about the number of blocks and rows visited for the
> > number of rows
> > returned:
> >
> > The question we must ask ourselves is why does oracle think
> it
> > necessary to
> > run through 1.4 million rows to get to the 156 it returns?
> > Especially with a
> > unique index?
> >
> > Gut instinct tells me that the optimizer is making incorrect
> > choices based
> > on the prescense of the :b7 variable. While using bind
> > variables will reduce
> > parsing, it can also cause incorrect optimizer choices.
> >
> > I would bet that the optimizer is choosing all the rows
> where
> > DBSTS = 'A'
> > and then sorting through those rows to find out how many
> have
> > the sku=:b7
> > clause.
> >
> > How many rows in the table have DBSTS = 'A'? If it is 1.4
> > million, we have
> > our answer.
> >
> > Jack
> >
> >
> >
> > -----Original Message-----
> > Sent: Monday, July 10, 2000 10:46 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Thanks to all who replied.
> > Steve/Stephane, I tried rebuilding indexes and
> > analyzing schema after that, that didn't work. The new
> > execution plan uses 'index range scan' instead of
> > previous 'index unique scan'.
> > Rajesh, the avg row len is 147 bytes and the total no
> > of blocks used for this table is 9255. Also, the
> > 'rows' information in the tkprof is there, but is
> > slightly difficult to see because of bad formatting.
> > Jack, the buffer cache hit ratio is 79%, i think it's
> > much less than it should be. I'll ask our dba(i'm a
> > developer) to increase db_block_buffers, but it will
> > still try to read SO much information from data block
> > buffers, which is too much for a small no of rows
> > (157)??
> >
> > Prakash
> >
> > The new output from tkprof is following:
> >
> >
> >
> >
> > UPDATE EC_PRDITM SET DSCR=:b1 || ', ' || :b2
> > ,PRDITMNAME=:b1 || ', ' || :b2
> > || '; ' || :b5 || ', ' || :b6
> > WHERE
> > SKU = :b7 AND DBSTS = 'A'
> >
> >
> > call count cpu elapsed disk
> > query current rows
> > ------- ------ -------- ---------- ----------
> > ---------- ---------- ----------
> > Parse 1 0.00 0.00 0
> > 0 0 0
> > Execute 156 546.82 1330.06 1437402
> > 1443835 787 156
> > Fetch 0 0.00 0.00 0
> > 0 0 0
> > ------- ------ -------- ---------- ----------
> > ---------- ---------- ----------
> > total 157 546.82 1330.06 1437402
> > 1443835 787 156
> >
> > Misses in library cache during parse: 1
> > Misses in library cache during execute: 1
> > Optimizer goal: CHOOSE
> > Parsing user id: 31 (SDOSTLO20) (recursive depth:
> > 1)
> >
> > Rows Execution Plan
> > -------
> > ---------------------------------------------------
> > 0 UPDATE STATEMENT GOAL: CHOOSE
> > 0 UPDATE OF 'EC_PRDITM'
> > 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> > 'EC_PRDITM_SKU_UK'
> > (UNIQUE)
> >
> >
>
 |
 |