Hi Prakash,
I had reformatted your outputs (on notepad), even this time I did the
same
thing. There is no value of rows column in the execution plan :~((
Anyway..... Another thing you could try is create two more indexes.
- Composite index having keys DBSTS,SKU, say IND_DBSTS_SKU).
- Single column index on DBSTS, say IND_DBSTS.
Use various combination of these indexes as hint in following order:
- /* INDEX (ECPRDITM, EC_PRDITM_SKU_UK) INDEX (ECPRDITM, IND_DBSTS)*/
- /* INDEX (ECPRDITM, IND_DBSTS_SKU) */
Also try to change the where clause as
WHERE DBSTS = 'A' AND SKU = :b7
I had faced similar situation, and solved the things using diff index
hints. Hope this helps.....
Rajesh
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Steve Boyd
Sent: Monday, July 10, 2000 10:17 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: queries too slow
Just for fun, try running the update with the following hint:
UPDATE /*+ FULL(ECPRDITM) */ EC_PRDITM SET DSCR=:b1 || ', ' || :b2
,PRDITMNAME=:b1 || ', ' || :b2
|| '; ' || :b5 || ', ' || :b6
WHERE
SKU = :b7 AND DBSTS = 'A'
See if this runs faster, and what tkprof says then.
- PK J <pkj_01_at_yahoo.com> wrote:
> 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)
> > >
>
=== message truncated ===
Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
http://mail.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
Received on Tue Jul 11 2000 - 00:14:40 CDT
