| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: slow query
Hi Prakash,
One important thing missing in your execution plan is,
the numeric values under ROWS heading. This is one of the
most imp. info.
To get this you have to logout from the session from which you are executing the sqls, then run tkprof on the trace file. If sqls are running from application, then run tkprof after sometime (app. 30 min), this will help you getting Rows value for execution plans.
Once you get this value, we can further analyze the execution plans, to find the solution.
Another thing that comes in mind is what is avg. row length of the table?
Also you can try rebuilding the index, but this can be told confidently only after getting Rows statistics of execution plan.
HTH,
Rajesh
-----Original Message-----
Sent: Friday, July 07, 2000 6:06 AM
To: Multiple recipients of list ORACLE-L
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
------- ------ -------- ---------- ---------- ---------- ----------
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
------- ------ -------- ---------- ---------- ---------- ----------
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)
-- 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 ListsReceived on Sun Jul 09 2000 - 00:45:36 CDT
--------------------------------------------------------------------
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). -- Author: Jack Silvey INET: JSilvey_at_XOL.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
 |
 |