Home » RDBMS Server » Performance Tuning » Execution Plan (merged)
Execution Plan (merged) [message #386971] |
Tue, 17 February 2009 07:34 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
SQL> explain plan set statement_id = 'RBN' for
2 select as_of_date,group_1,level_1,level_2,level_3,level_4,level_5,
3 sum(case when priority_id =123 and amt_os > 10000000 then 2/3*(amt_os-10000000) else 0 end)/100000 amt_os
4 from hpdevteam.mirror_boi_ccis_mis_dtl a, owb_target.information_ver1 b
5 where a.priority_id = 123 and b.level_id= 131 and b.report_name='Priority Sector'
6 and as_of_date = to_date('31-Dec-2008','DD-Mon-YYYY')
7 group by as_of_date,group_1,level_1,level_2,level_3,level_4,level_5 ;
Explained.
SQL> select operation, options, object_owner, object_name from plan_table where statement_id = 'RBN' ;
OPERATION OPTIONS OBJECT_OWNER OBJECT_NAME
-------------------- --------------- -------------------- ------------------------------
SELECT STATEMENT
SORT GROUP BY
MERGE JOIN CARTESIAN
TABLE ACCESS FULL OWB_TARGET INFORMATION_VER1
BUFFER SORT
TABLE ACCESS BY INDEX ROWID HPDEVTEAM MIRROR_BOI_CCIS_MIS_DTL
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX RANGE SCAN HPDEVTEAM CCIS_AOD_ID
8 rows selected.
SQL>
In the above query as_of_date = to_date('31-Dec-2008','DD-Mon-YYYY'), I replace with as_of_date = to_date(OWB_TARGET.SETPARAM.GET_PARAM1(),'DD-Mon-YYYY'), Which also returns a value '31-Dec-2008'. But my execution plan changes and goes for a FTS.
SQL> delete from plan_table where statement_id = 'RBN';
8 rows deleted.
SQL> explain plan set statement_id = 'RBN' for
2 select as_of_date,group_1,level_1,level_2,level_3,level_4,level_5,
3 sum(case when priority_id =123 and amt_os > 10000000 then 2/3*(amt_os-10000000) else 0 end)/100000 amt_os
4 from hpdevteam.mirror_boi_ccis_mis_dtl a, owb_target.information_ver1 b
5 where a.priority_id = 123 and b.level_id= 131 and b.report_name='Priority Sector'
6 and as_of_date = to_date(OWB_TARGET.SETPARAM.GET_PARAM1(),'DD-Mon-YYYY')
7 group by as_of_date,group_1,level_1,level_2,level_3,level_4,level_5 ;
Explained.
SQL> select operation, options, object_owner, object_name from plan_table where statement_id = 'RBN' ;
OPERATION OPTIONS OBJECT_OWNER OBJECT_NAME
-------------------- --------------- -------------------- ------------------------------
SELECT STATEMENT
SORT GROUP BY
MERGE JOIN CARTESIAN
TABLE ACCESS FULL OWB_TARGET INFORMATION_VER1
BUFFER SORT
TABLE ACCESS FULL HPDEVTEAM MIRROR_BOI_CCIS_MIS_DTL
6 rows selected.
SQL>
My first execution plan is correct and it generates result fast. How can I get the same execution Plan ?
Brian
|
|
|
|
|
|
Re: Execution Plan (merged) [message #387109 is a reply to message #386971] |
Tue, 17 February 2009 23:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi,
Thanks for your inputs.
I have created the functions as DETERMINISTIC as below, do I need to create the function based index for as_of_date = to_date(OWB_TARGET.SETPARAM.GET_PARAM1(),'DD-Mon-YYYY') ? I'm not sure how to create index for above requirement !
Still the plan is going to FTS !
CREATE OR REPLACE PACKAGE SETPARAM
AS
param1 varchar2(25);
param2 varchar2(25);
param3 varchar2(25);
param4 varchar2(25);
FUNCTION SET_PARAM1(p1 IN VARCHAR2) RETURN NUMBER DETERMINISTIC;
FUNCTION SET_PARAM2(p2 IN VARCHAR2) RETURN NUMBER DETERMINISTIC;
FUNCTION SET_PARAM3(p3 IN VARCHAR2) RETURN NUMBER DETERMINISTIC;
FUNCTION SET_PARAM4(p4 IN VARCHAR2) RETURN NUMBER DETERMINISTIC;
FUNCTION GET_PARAM1 RETURN VARCHAR2 DETERMINISTIC;
FUNCTION GET_PARAM2 RETURN VARCHAR2 DETERMINISTIC;
FUNCTION GET_PARAM3 RETURN VARCHAR2 DETERMINISTIC;
FUNCTION GET_PARAM4 RETURN VARCHAR2 DETERMINISTIC;
END;
/
CREATE OR REPLACE PACKAGE BODY SETPARAM AS
FUNCTION SET_PARAM1(p1 IN VARCHAR2) RETURN NUMBER DETERMINISTIC IS
BEGIN
param1 := P1;
RETURN 1;
END;
FUNCTION SET_PARAM2(p2 IN VARCHAR2) RETURN NUMBER DETERMINISTIC IS
BEGIN
param2 := P2;
RETURN 1;
END;
FUNCTION SET_PARAM3(p3 IN VARCHAR2) RETURN NUMBER DETERMINISTIC IS
BEGIN
param3 := P3;
RETURN 1;
END;
FUNCTION SET_PARAM4(p4 IN VARCHAR2) RETURN NUMBER DETERMINISTIC IS
BEGIN
param4 := P4;
RETURN 1;
END;
FUNCTION GET_PARAM1 RETURN VARCHAR2 DETERMINISTIC AS
BEGIN
RETURN param1;
END;
FUNCTION GET_PARAM2 RETURN VARCHAR2 DETERMINISTIC AS
BEGIN
RETURN param2;
END;
FUNCTION GET_PARAM3 RETURN VARCHAR2 DETERMINISTIC AS
BEGIN
RETURN param3;
END;
FUNCTION GET_PARAM4 RETURN VARCHAR2 DETERMINISTIC AS
BEGIN
RETURN param4;
END;
END;
/
Brian
|
|
|
Re: Execution Plan (merged) [message #387187 is a reply to message #386971] |
Wed, 18 February 2009 03:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
For all that you've put the deterministic key-word against those functions the get functions are not deterministic.
Deterministic means the function will always return the same output return value for any given set of input argument values.
That's not true in your case.
|
|
|
Re: Execution Plan (merged) [message #387226 is a reply to message #386971] |
Wed, 18 February 2009 06:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi,
In my case, the FUNCTION GET_PARAM1 RETURN VARCHAR2 DETERMINISTIC AS does not have any input, it returns value stored in PARAM1. This value remains same unless PARAM1 changes.
I did not understnad why this not deterministic !
Brian.
|
|
|
Re: Execution Plan (merged) [message #387230 is a reply to message #386971] |
Wed, 18 February 2009 06:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Because the value of param1 can change.
A function that queries a database table can not be deterministic because someone can change the data in the table between function calls.
Likewise a function that returns a global variable can not be deterministic because someone can change the value of the variable between function calls.
Your set functions are deterministic - they always return 1.
That said I've no idea why they are functions at all, I would have thought they should be procedures.
Why don't you post proper explain plans for your queries (the ones you provided originally aren't much use).
Do the following in SQLplus:
explain plan for <your select statement here>;
select * from table(dbms_xplan.display);
and post the results here.
|
|
|
Re: Execution Plan (merged) [message #387235 is a reply to message #387230] |
Wed, 18 February 2009 06:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi,
Following is the execute plan.
SQL> ed
Wrote file afiedt.buf
1 explain plan for
2 select as_of_date,group_1,level_1,level_2,level_3,level_4,level_5,
3 sum(case when priority_id =123 and amt_os > 10000000 then 2/3*(amt_os-10000000) else 0 end)/100000 amt_os
4 from hpdevteam.mirror_boi_ccis_mis_dtl a, owb_target.information_ver1 b
5 where a.priority_id = 123 and b.level_id= 131 and b.report_name='Priority Sector'
6 and as_of_date = to_date('31-Dec-2008','DD-Mon-YYYY')
7* group by as_of_date,group_1,level_1,level_2,level_3,level_4,level_5
SQL> /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 464 | 1674 |
| 1 | SORT GROUP BY | | 1 | 464 | 1674 |
| 2 | MERGE JOIN CARTESIAN | | 706 | 319K| 1645 |
|* 3 | TABLE ACCESS FULL | INFORMATION_VER1 | 1 | 447 | 2 |
| 4 | BUFFER SORT | | 43052 | 714K| 1672 |
| 5 | TABLE ACCESS BY INDEX ROWID | MIRROR_BOI_CCIS_MIS_DTL | 43052 | 714K| 1645 |
| 6 | BITMAP CONVERSION TO ROWIDS| | | | |
|* 7 | BITMAP INDEX RANGE SCAN | CCIS_AOD_ID | | | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."LEVEL_ID"=131 AND "B"."REPORT_NAME"='Priority Sector')
7 - access("A"."AS_OF_DATE"=TO_DATE('2008-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"A"."PRIORITY_ID"=123)
filter("A"."PRIORITY_ID"=123 AND "A"."AS_OF_DATE"=TO_DATE('2008-12-31 00:00:00',
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
'yyyy-mm-dd hh24:mi:ss'))
Note: cpu costing is off
24 rows selected.
SQL> ed
Wrote file afiedt.buf
1 explain plan for
2 select as_of_date,group_1,level_1,level_2,level_3,level_4,level_5,
3 sum(case when priority_id =123 and amt_os > 10000000 then 2/3*(amt_os-10000000) else 0 end)/100000 amt_os
4 from hpdevteam.mirror_boi_ccis_mis_dtl a, owb_target.information_ver1 b
5 where a.priority_id = 123 and b.level_id= 131 and b.report_name='Priority Sector'
6 and as_of_date = to_date(OWB_TARGET.SETPARAM.GET_PARAM1(),'DD-Mon-YYYY')
7* group by as_of_date,group_1,level_1,level_2,level_3,level_4,level_5
SQL> /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 464 | 7903 |
| 1 | SORT GROUP BY | | 1 | 464 | 7903 |
| 2 | MERGE JOIN CARTESIAN| | 706 | 319K| 7874 |
|* 3 | TABLE ACCESS FULL | INFORMATION_VER1 | 1 | 447 | 2 |
| 4 | BUFFER SORT | | 43052 | 714K| 7901 |
|* 5 | TABLE ACCESS FULL | MIRROR_BOI_CCIS_MIS_DTL | 43052 | 714K| 7872 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."LEVEL_ID"=131 AND "B"."REPORT_NAME"='Priority Sector')
5 - filter("A"."PRIORITY_ID"=123 AND "A"."AS_OF_DATE"=TO_DATE("OWB_TARGET"."SET
PARAM"."GET_PARAM1"(),'DD-Mon-YYYY'))
Note: cpu costing is off
20 rows selected.
SQL>
Brian.
|
|
|
Re: Execution Plan (merged) [message #387245 is a reply to message #386971] |
Wed, 18 February 2009 07:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
How many rows are there in mirror_boi_ccis_mis_dtl?
How many of those rows have as_of_date = to_date('31-Dec-2008','DD-Mon-YYYY')?
How many have priority_id = 123 ?
How many satisfy both of those conditions?
|
|
|
Re: Execution Plan (merged) [message #387342 is a reply to message #387245] |
Wed, 18 February 2009 22:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Statistics are gathered
FTS(20 Mins) is costlier than Range Scan(60 Sec) for above said conditions.
1. How many rows are there in mirror_boi_ccis_mis_dtl? - 2841417
2. How many of those rows have as_of_date = to_date('31-Dec-2008','DD-Mon-YYYY')? - 2841417
3. How many have priority_id = 123 ? - 254
4. How many satisfy both of those conditions? - (2 & 3) 254
5. How many having level_id= 131 in owb_target.information_ver1 - 1
Brian
|
|
|
Re: Execution Plan (merged) [message #387407 is a reply to message #386971] |
Thu, 19 February 2009 02:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
All the records have the same as_of_date? really?
What column(s) is your index actually on?
minor note - those plans you've been posting are explain plans not execution plans. Execution plans are something different - helps not to confuse terms.
|
|
|
Re: Execution Plan (merged) [message #387412 is a reply to message #387407] |
Thu, 19 February 2009 02:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Yes, All records of same date.
Bitmap index ON AS_OF_DATE, PRIORITY_ID, FACILITYCD
Normal index on AS_OF_DATE, BRN_CD, BORR_CD, FACILITYCD, SEQUENCE_NO, STATUS_TAG
Brian
|
|
|
Re: Execution Plan (merged) [message #387428 is a reply to message #386971] |
Thu, 19 February 2009 03:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well that explains a lot.
Generally when choosing columns to index it helps to pick ones that'll help narrow down the number of rows oracle needs to look at.
Consequently indexing a column which has the same value for every row is a BAD idea.
I assume that originally the optimiser knew that the data you were looking for was contained in the bitmap index and just scanned that for all the entries that matched the priority_id.
Then when you replaced the hard-coded date with the function the optimiser was no longer sure if the data you were looking for was in the bitmap index so skipped using it.
It didn't use the normal index because that would be less efficient than a full table scan.
(I'm guessing slightly with the bitmap index cause I don't use them - can someone who does please confirm?)
The solution is simple, recreate the indexes without as_of_date.
|
|
|
Re: Execution Plan (merged) [message #387465 is a reply to message #386971] |
Thu, 19 February 2009 05:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi,
Quote: | Consequently indexing a column which has the same value for every row is a BAD idea.
|
I agree with you, But in my case there would be multiple dates going to be inserted. That is the reason I've created so.
In my case when the condition was as_of_date = to_date('31-Dec-2008','DD-Mon-YYYY') it used the index and the response time was around 40 Seconds(to fetch all records). But, When I changed the condition to as_of_date = to_date(OWB_TARGET.SETPARAM.GET_PARAM1(),'DD-Mon-YYYY')(this is requird for scripting within an application. Because I can not use bind variables there), it went for a FTS and the response time was around 20 minutes.
Brian.
|
|
|
Re: Execution Plan (merged) [message #387469 is a reply to message #386971] |
Thu, 19 February 2009 06:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
So are you saying that your test data is unrepresentative?
How many different dates would expect to have normally?
If your application forces you to use functions like that I can only strongly recommend you replace it with something designed this century.
If you start using functions like that generally you WILL end up with bug ridden, badly perfoming code that's almost impossible to debug and maintain.
|
|
|
Re: Execution Plan (merged) [message #387476 is a reply to message #387469] |
Thu, 19 February 2009 06:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi,
At present I'm testing for only one date.
At present I'm going to have around 3.000.000*12 records and will increment as and when month passes by.
I did not understand what you mean by I can only strongly recommend you replace it with something designed this century..
Brian.
|
|
|
Re: Execution Plan (merged) [message #387478 is a reply to message #386971] |
Thu, 19 February 2009 07:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You do realise that one date dictates a different index to lots of dates?
If your data only has one date you shouldn't index date.
If your data has lots of dates you should index date.
You've got indexes set up that'll speed up queries for data that has lots of different dates.
But that's not representative of the data you have in your table.
So it'll be less than optimal.
You can either:
a) get some test data which actually has a range of dates. (if a range of dates is normal)
b) remove the date from the indexes (if one date is normal).
As for
Quote: | I can only strongly recommend you replace it with something designed this century.
|
Most modern applications can cope with bind variables, what are you using?
|
|
|
Re: Execution Plan (merged) [message #387556 is a reply to message #387478] |
Thu, 19 February 2009 22:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Brayan
Messages: 315 Registered: June 2002
|
Senior Member |
|
|
Hi,
One of our Team uses Oracle Discoverer reports.
In which the custom folders can not have parameters. So I have used a workaround, to behave like a user defined parameters.
And that is the reason I'm using a package like SETPARAM as posted.
Brian.
|
|
|
Re: Execution Plan (merged) [message #387618 is a reply to message #386971] |
Fri, 20 February 2009 01:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
While I don't use Oracle Discoverer personally, I find it virtually impossible to believe an Oracle product is incapable of using bind variables.
I suggest you post a question on the subject in the discoverer forum and see if someone can sort you out.
|
|
|
|
|
Re: Execution Plan (merged) [message #387852 is a reply to message #387839] |
Fri, 20 February 2009 21:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Try rebuilding your index so that PRIORITY_ID is the first column.
Alternatively, try this:
select
as_of_date,group_1,level_1,level_2,level_3,level_4,level_5,
sum(case when priority_id =123 and amt_os > 10000000 then
(amt_os-10000000) else 0 end)/100000 amt_os
from hpdevteam.mirror_boi_ccis_mis_dtl a,
owb_target.information_ver1 b
where a.priority_id = 123 and b.level_id= 131
and b.report_name='Priority Sector'
and as_of_date IN
(select to_date(OWB_TARGET.SETPARAM.GET_PARAM1(),'DD-Mon-YYYY')
from dual)
group by as_of_date,group_1,level_1,level_2,level_3,level_4,level_5 ;
Ross Leishman
|
|
|
Goto Forum:
Current Time: Wed Sep 11 00:17:52 CDT 2024
|