queries too slow

From: PK J <pkj_01_at_yahoo.com>
Date: Fri, 7 Jul 2000 02:56:22 -0700 (PDT)
Message-Id: <10551.111417@fatcity.com>

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)

Received on Fri Jul 07 2000 - 04:56:22 CDT