Re: queries too slow

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).
  

  ---