Home » RDBMS Server » Performance Tuning » Explain the explain plan
| Explain the explain plan [message #65819] |
Tue, 04 January 2005 20:17  |
Fahami
Messages: 22
Registered: December 2004
|
Junior Member |
|
|
Hi everybody,
I did explain plan for this sql
explain plan for SELECT ROUND(AVG(SAMPLE_RESULT.RESULT),2) avg_result,
AVG(DECODE(SAMPLE.QUALITYSTATUS, 'P', 1,'F', 5)) qltystatus,
TO_CHAR("SAMPLE"."SAMPLEDT",'dd-Mon-yyyy') sampledt
FROM "SAMPLE","SAMPLE_RESULT","ACTIVITY"
WHERE "SAMPLE"."SAMPLEID" = "SAMPLE_RESULT"."SAMPLEID"
AND "SAMPLE_RESULT"."TESTINSTANCE" = fn_max_instance (sample.sampleid, sample_result.testid, sample_result.propertyid)
AND "SAMPLE"."ACTIVITYID" = "ACTIVITY"."ACTIVITYID"
AND ACTIVITY.LOCATIONID = 'S5003C'
AND SAMPLE.EVENTID <> 'AD-HOC'
AND "SAMPLE"."SAMPLESTATUS" = 'L'
AND TO_DATE(TO_CHAR(SAMPLE.SAMPLEDT,'dd-mon-yyyy')) BETWEEN '01-Jan-2003' AND '07-Jan-2003'
AND SAMPLE_RESULT.PROPERTYID = 'GCV'
GROUP BY TO_CHAR("SAMPLE"."SAMPLEDT",'dd-Mon-yyyy')
and I got this result:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 11 |
| 1 | SORT GROUP BY | | 1 | 78 | 11 |
|* 2 | FILTER | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | SAMPLE_RESULT | 20469 | 619K| 2 |
| 4 | NESTED LOOPS | | 1 | 78 | 9 |
| 5 | NESTED LOOPS | | 2 | 94 | 5 |
| 6 | TABLE ACCESS BY INDEX ROWID| ACTIVITY | 1 | 15 | 3 |
|* 7 | INDEX RANGE SCAN | IDX_ACTIVITY_LOCATION | 1 | | 1 |
|* 8 | TABLE ACCESS BY INDEX ROWID| SAMPLE | 2 | 64 | 2 |
|* 9 | INDEX RANGE SCAN | KSAMPLE_ACTIVITYID | 366 | | 1 |
|* 10 | INDEX RANGE SCAN | SAMPLE_RESULT_IDX_003 | 1 | | 1 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('01-Jan-2003')<=TO_DATE('07-Jan-2003'))
3 - filter("SAMPLE_RESULT"."TESTINSTANCE"="LVL_GPP"."FN_MAX_INSTANCE"("SAMPLE"."SAMPL
EID","SAMPLE_RESULT"."TESTID","SAMPLE_RESULT"."PROPERTYID"))
7 - access("ACTIVITY"."LOCATIONID"='S5003C')
8 - filter("SAMPLE"."EVENTID"<>'AD-HOC' AND TO_DATE(TO_CHAR("SAMPLE"."SAMPLEDT",'dd-m
on-yyyy'))>='01-Jan-2003' AND TO_DATE(TO_CHAR("SAMPLE"."SAMPLEDT",'dd-mon-
yyyy'))<='07-Jan-2003')
9 - access("SAMPLE"."ACTIVITYID"="ACTIVITY"."ACTIVITYID" AND "SAMPLE"."SAMPLESTATUS"=
'L')
10 - access("SAMPLE"."SAMPLEID"="SAMPLE_RESULT"."SAMPLEID" AND "SAMPLE_RESULT"."PROPER
TYID"='GCV')
filter("SAMPLE_RESULT"."PROPERTYID"='GCV')
Note: cpu costing is off
34 rows selected.
********************************
So what suppose I do since the query takes more than half an hour to complete.
Thanks & Regards.
|
|
|
|
| Re: Explain the explain plan [message #65826 is a reply to message #65819] |
Wed, 05 January 2005 04:06  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Two things jump out at me here.
First, if at all possible, take the logic from your fn_max_instance function and embed it within your SQL statement. Context switching between PL/SQL and SQL can be costly.
Second, it looks like your sample.sampledt column has a datatype of DATE, correct? Then this predicateAND TO_DATE(TO_CHAR(SAMPLE.SAMPLEDT,'dd-mon-yyyy')) BETWEEN '01-Jan-2003' AND '07-Jan-2003' AND sample.sampledt BETWEEN TO_DATE('01-Jan-2003','DD-Mon-YYYY') AND TO_DATE('07-Jan-2003','DD-Mon-YYYY')
Hope this helps.
|
|
|
|
| Re: Explain the explain plan [message #65828 is a reply to message #65826] |
Wed, 05 January 2005 17:18 |
Fahami
Messages: 22
Registered: December 2004
|
Junior Member |
|
|
Hi,
This is my fn_max_instance
CREATE OR REPLACE FUNCTION fn_max_instance
(v_sampleid sample_result.sampleid%TYPE,
v_testid sample_result.testid%TYPE,
v_propertyid sample_result.propertyid%TYPE)
RETURN PLS_INTEGER
IS
v_instance sample_result.testinstance%TYPE;
BEGIN
SELECT MAX(testinstance) INTO v_instance
FROM sample_result
WHERE sampleid = v_sampleid
AND testid = v_testid
AND propertyid = v_propertyid;
RETURN v_instance;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 1;
END fn_max_instance;
Yes, you're are correct, sampledt is DATE datatype. i should realize that from beginning.
Thanks & Regards.
|
|
|
|
| Re: Explain the explain plan [message #65831 is a reply to message #65828] |
Thu, 06 January 2005 04:10 |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Warning....untested code ahead....but does the following SQL yield the same output as yours?
----------------------------------------------------------------------
SELECT ROUND(AVG(mx.result),2) avg_result
, AVG(DECODE(mx.qualitystatus
, 'P', 1
, 'F', 5)) qltystatus
, TO_CHAR(mx.sampledt
, 'DD-Mon-YYYY') sampledt
FROM (SELECT sr.result
, s.qualitystatus
, s.sampledt
, sr.testinstance
, MAX(sr.testinstance)
OVER (PARTITION BY sr.sampleid
, sr.testid
, sr.propertyid) max_testinstance
FROM sample s
, sample_result sr
, activity a
WHERE s.sampleid = sr.sampleid
AND s.activityid = a.activityid
AND a.locationid = 'S5003C'
AND s.eventid <> 'AD-HOC'
AND s.samplestatus = 'L'
AND s.sampledt BETWEEN TO_DATE('01-Jan-2003','DD-Mon-YYYY')
AND TO_DATE('07-Jan-2003','DD-Mon-YYYY')
AND sr.propertyid = 'GCV') mx
WHERE mx.testinstance = mx.max_testinstance
GROUP BY TO_CHAR(mx.sampledt,'DD-Mon-YYYY')
/
----------------------------------------------------------------------
|
|
|
|
Goto Forum:
Current Time: Thu Jan 09 06:22:48 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
