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
Received on Mon Jul 10 2000 - 08:53:02 CDT
