Home » RDBMS Server » Performance Tuning » Explain the explain plan
Explain the explain plan [message #65819] Tue, 04 January 2005 20:17 Go to next message
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 Go to previous messageGo to next message
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 predicate
AND TO_DATE(TO_CHAR(SAMPLE.SAMPLEDT,'dd-mon-yyyy')) BETWEEN '01-Jan-2003' AND '07-Jan-2003'
should instead read
AND sample.sampledt BETWEEN TO_DATE('01-Jan-2003','DD-Mon-YYYY') AND TO_DATE('07-Jan-2003','DD-Mon-YYYY')
You should only compare DATEs to DATEs, NUMBERs to NUMBERs and strings to strings.

Hope this helps.
Re: Explain the explain plan [message #65828 is a reply to message #65826] Wed, 05 January 2005 17:18 Go to previous messageGo to next message
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 Go to previous message
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')
/

----------------------------------------------------------------------
Previous Topic: how to make sql&pl/sql mor efficient
Next Topic: when database is full
Goto Forum:
  


Current Time: Thu Mar 28 14:06:17 CDT 2024