And after all that, what is your recommendation to tune the query?
I do agree with you. I have seen plenty of queries that were doing massive index scans, and were
returning most of the rows. They had a great hit-rate, but would have run much faster if they
would have done a full table scan.
Anyway, I'm sure the problem with the query is something like it is using the wrong index, or
there isn't a good index to use, or maybe even that it should be doing a full table scan.
Experiment, and find what works the best.
- Gaja Krishna Vaidyanatha <gajav_at_yahoo.com> wrote:
> 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)
> >
> >
> ****************************************************************************
> > ****
> >
> > --- Steve Boyd <pimaco_oracle_at_yahoo.com> wrote:
> > > You may not have chained rows on your table, but is
> > > your 'EC_PRDITM_SKU_UK' index stagnated? Do
> > > the following commands:
> > > ANALYZE INDEX EC_PRDITM_SKU_UK VALIDATE STRUCTURE;
> > > SELECT * FROM INDEX_STATS;
> > >
> > > If the DEL_LF_ROWS value is high, then you probably
> > > need to rebuild your index. Of course, I am
> > > assuming that the EC_PRDITM_SKU_UK index is on the
> > > SKU column. If not, then try creating an index
> > > on that column.
> > >
> > > --- PK J <pkj_01_at_yahoo.com> wrote:
Received on Tue Jul 11 2000 - 15:01:55 CDT
