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.

Report message to a moderator

- 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
Code: [Select all] [Show/ hide]
AND TO_DATE(TO_CHAR(SAMPLE.SAMPLEDT,'dd-mon-yyyy')) BETWEEN '01-Jan-2003' AND '07-Jan-2003'
should instead read
Code: [Select all] [Show/ hide]
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.

Report message to a moderator

- 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.

Report message to a moderator

- 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')
/
----------------------------------------------------------------------

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 02 19:37:46 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.