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)