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-----
From: PK J [mailto:pkj_01_at_yahoo.com]
Sent: Monday, July 10, 2000 10:46 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: queries too slow
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
Received on Tue Jul 11 2000 - 09:40:33 CDT
