RE: queries too slow

From: Jack Silvey <JSilvey_at_XOL.com>
Date: Fri, 7 Jul 2000 18:18:12 -0500
Message-Id: <10551.111487@fatcity.com>

Prakash,

Disk reads:

Take a look at the number of rows read from disk. 1.4 million? Yikes! Check your buffer cache hit ratio. I bet it is way too low. I think that if you add some db_block_buffers and restart the db you will see significant improvements.

Jack
ocp x 2
Senior @ xol.com

-----Original Message-----

From: PK J [mailto:pkj_01_at_yahoo.com]
Sent: Friday, July 07, 2000 6:06 AM
To: Multiple recipients of list ORACLE-L Subject: queries too slow

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

Received on Fri Jul 07 2000 - 18:18:12 CDT