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> 

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> 

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;
/

explain plan for <your select statement here>;

select * from table(dbms_xplan.display);

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> 

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 ;