Home » RDBMS Server » Performance Tuning » Execution Plan (merged)
Execution Plan (merged) [message #386971] Tue, 17 February 2009 07:34 Go to next message
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 #387097 is a reply to message #386971] Tue, 17 February 2009 22:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Make sure your function is defined as DETERMINISTIC

Ross Leishman
Re: Execution Plan (merged) [message #387100 is a reply to message #387097] Tue, 17 February 2009 22:34 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Ross,

What you mean by deterministic? Could you pls give me more details!

Thanks in advance.
Brian.
Re: Execution Plan (merged) [message #387101 is a reply to message #386971] Tue, 17 February 2009 22:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

Too bad GOOGLE is broken for you
Results 1 - 10 of about 162,000 for oracle deterministic function.


http://asktom.oracle.com/tkyte/article1/index.html

[Updated on: Tue, 17 February 2009 22:38]

Report message to a moderator

Re: Execution Plan (merged) [message #387109 is a reply to message #386971] Tue, 17 February 2009 23:39 Go to previous messageGo to next message
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 messageGo to next message
cookiemonster
Messages: 12422
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 messageGo to next message
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 messageGo to next message
cookiemonster
Messages: 12422
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 messageGo to next message
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 messageGo to next message
cookiemonster
Messages: 12422
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 messageGo to next message
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 messageGo to next message
cookiemonster
Messages: 12422
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 messageGo to next message
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 messageGo to next message
cookiemonster
Messages: 12422
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 messageGo to next message
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 messageGo to next message
cookiemonster
Messages: 12422
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 messageGo to next message
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 messageGo to next message
cookiemonster
Messages: 12422
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 messageGo to next message
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 messageGo to next message
cookiemonster
Messages: 12422
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 #387736 is a reply to message #387618] Fri, 20 February 2009 07:42 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Metalink note ID - 304192.1

Brian
Re: Execution Plan (merged) [message #387839 is a reply to message #386971] Fri, 20 February 2009 15:40 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I stand corrected it seems. Though it does appear to be a dodgy work-around for something oracle didn't think anyone would want.

Interestingly I can't find the actual metalink note although I can find several references to it.

Have you read this:
http://learndiscoverer.blogspot.com/2008/11/metalink-note-304192.html


It details a slightly better way of doing what you're doing.

That said, it probably won't solve your index issue, which is down to what I said before.
Re: Execution Plan (merged) [message #387852 is a reply to message #387839] Fri, 20 February 2009 21:13 Go to previous message
rleishman
Messages: 3724
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



Previous Topic: doubt in Performace tuning
Next Topic: Index usage
Goto Forum:
  


Current Time: Thu Dec 08 18:37:03 CST 2016

Total time taken to generate the page: 0.14321 seconds