> I personally prefer a system which has a 99% throughput factor
> than a 99% db buffer cache hit ratio.
Hmmm... I know how to calculate the latter but how do you calculate the
former? :)
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. Better "un-tune" that SQL so you
can brag about achieving a high cache hit ratio. ;-)
Steve Orr
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Gaja
Krishna Vaidyanatha
Sent: Tuesday, July 11, 2000 12:20 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: queries too slow
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:
> > > Hi All,
> > >
> > > I have some queries which take too long to
> > execute,
> > > perform too
> > > many disk reads and affects very small no of rows.
> > I
> > > have analyzed
> > > the table,indexes and there are no chained rows
> > for
> > > this table.
> > > Explain plan says that the cost of the queries are
> > 3/2
> > > etc and I
> > > don't know if they could be optimized any more.
> > Please
> > > could
> > > anyone tell what could be wrong?
> > >
> > > The output of the TKPROF is following:(sorry for
> > the
> > > bad formatting)
> > >
> > > TIA,
> > >
> > > Prakash
> > >
> > >
> > > 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 157 475.75 508.74 1441954
> > > 1453090 793
> > > 157
> > > Fetch 0 0.00 0.00 0
> >
> > > 0 0 0
> > > ------- ------ -------- ---------- ----------
> > > ---------- ---------- ----------
> > > total 158 475.75 508.74 1441954
> > > 1453090 793
> > > 157
> > >
> > > 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 (UNIQUE SCAN) OF
> >
> > > 'EC_PRDITM_SKU_UK'
> > > (UNIQUE)
> > >
> > >
> >
>
> **
> > > **
> > >
> > >
> > > SELECT PRDITMID
> > > FROM
> > > EC_PRDITM WHERE SKU = :b1 AND DBSTS = 'A'
> > >
> > >
> > > call count cpu elapsed disk
> >
> > > query current
> > > rows
> > > ------- ------ -------- ---------- ----------
> > > ---------- ---------- ----------
> > > Parse 1 0.00 0.00 0
> >
> > > 0 0 0
> > > Execute 157 0.01 0.01 0
> >
> > > 0 0 157
> > > Fetch 157 373.98 393.26 1441790
> > > 1453035 471
> > > 157
> > > ------- ------ -------- ---------- ----------
> > > ---------- ---------- ----------
> > > total 315 373.99 393.27 1441790
> > > 1453035 471
> > > 314
> > >
> > > Misses in library cache during parse: 1
> > > Optimizer goal: CHOOSE
> > > Parsing user id: 31 (SDOSTLO20) (recursive
> > depth:
> > > 1)
> > >
> > > Rows Execution Plan
> > > -------
> > >
> > ---------------------------------------------------
> > > 0 SELECT STATEMENT GOAL: CHOOSE
> > > 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX
> > > ROWID)
> > > OF 'EC_PRDITM'
> > > 0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> >
> > > 'EC_PRDITM_SKU_UK'
> > > (UNIQUE)
> > >
> > >
> >
>
> **
> > > **
> > >
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Send instant messages & get email alerts with
> > Yahoo! Messenger.
> > > http://im.yahoo.com/
> > > --
> > > Author: PK J
> > > INET: pkj_01_at_yahoo.com
> > >
> > > Fat City Network Services -- (858) 538-5051
> > FAX: (858) 538-5051
> > > San Diego, California -- Public Internet
> > access / Mailing Lists
> > >
> >
>
> > > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > > (or the name of mailing list you want to be
> > removed from). You may
> > > also send the HELP command for other information
> > (like subscribing).
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Send instant messages & get email alerts with Yahoo!
> > Messenger.
> > http://im.yahoo.com/
> > --
> > Author: Steve Boyd
> > INET: pimaco_oracle_at_yahoo.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> __________________________________________________
> Do You Yahoo!?
> Get Yahoo! Mail - Free email you can access from anywhere!
> http://mail.yahoo.com/
> --
> Author: PK J
> INET: pkj_01_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail
> message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).
> You may
> also send the HELP command for other information (like
> subscribing).
> --
> Author: Jack Silvey
> INET: JSilvey_at_XOL.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail
> message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).
> You may
> also send the HELP command for other information (like
subscribing).
Gaja Krishna Vaidyanatha
Director, I-O Management Products
Quest Software Inc.
(972)-304-1170
gajav_at_yahoo.com
"Opinions and views expressed are my own and not of Quest"
Do You Yahoo!?
Get Yahoo! Mail – Free email you can access from anywhere!
http://mail.yahoo.com/
--
Author: Gaja Krishna Vaidyanatha
INET: gajav_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
Received on Tue Jul 11 2000 - 14:49:09 CDT
