Its an offical statement from Oracle that Stored oulines will be depreciated in future will replaced by sql plan managment.
Oracle database performance tuning guide, Chapter# 20
<quote>
Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.
If you have existing stored outlines, then consider migrating them to SQL plan baselines by following the steps in "Migrating Stored Outlines to SQL Plan Baselines". When the migration is complete, you should disable or remove the stored outlines
</quote>
When using stored outlines
1)You can have only one stored outlines per sql statement, this means only one execution plan which is fine in some cases but not in all cases. (eg, bind values)
2)Stored outlines, doesnot allow for evolution. that is even if better plans exists stored outlines will continue to be used. Which results in supressing the better plans derived by CBO untill stored outlines are dropped/disabled.
First some schema objects with relationships.
rajesh@ORA11G> create table products as
2 select rownum as prod_id,
3 object_name as prod_name,
4 object_type as prod_category,
5 object_id as prod_category_id,
6 data_object_id as prod_subcategory_id,
7 round(dbms_random.value(1,2000)) as prod_min_price,
8 round(dbms_random.value(1,4000)) as prod_max_price,
9 'Total' as prod_total,
10 created as effective_from,
11 add_months(created,3) as effective_to,
12 status as prod_status
13 from all_objects;
Table created.
Elapsed: 00:00:05.26
rajesh@ORA11G>
rajesh@ORA11G> alter table products
2 add constraint prod_pk
3 primary key(prod_id);
Table altered.
Elapsed: 00:00:02.98
rajesh@ORA11G>
rajesh@ORA11G> create table customers as
2 select rownum as cust_id,
3 object_name as cust_fname,
4 subobject_name as cust_lname,
5 decode(mod(rownum,2),0,'M','F') gender,
6 to_char(created,'YYYY') as cust_birth_year,
7 'Single' as cust_marital_status,
8 dbms_random.string('A',30) as cust_street_address,
9 dbms_random.value(1,10000) as cust_postal_code,
10 dbms_random.string('E',5) as cust_city,
11 dbms_random.string('C',5) as cust_state_province,
12 round(dbms_random.value(1,9999))||'-'||
13 round(dbms_random.value(1,999))||'-'||
14 round(dbms_random.value(1,999)) as cust_phone_no,
15 object_name||'@company.com' as cust_email,
16 'CustomerTotal' as cust_total,
17 created as cust_effective_from,
18 add_months(created,3) as cust_effective_to
19 from all_objects ;
Table created.
Elapsed: 00:00:10.70
rajesh@ORA11G>
rajesh@ORA11G> alter table customers
2 add constraint cust_pk
3 primary key(cust_id);
Table altered.
Elapsed: 00:00:00.33
rajesh@ORA11G>
rajesh@ORA11G> create table sales as
2 select mod(rownum,10000)+1 as prod_id,
3 mod(rownum,1000)+1 as cust_id,
4 to_date('01-Jan-1998','dd-mon-yyyy')
5 +mod(rownum,4000) as time_id,
6 mod(rownum,10) as quantity_sold,
7 round(dbms_random.value(1,5000)) as amount_sold
8 from all_objects ;
Table created.
Elapsed: 00:00:05.67
rajesh@ORA11G>
rajesh@ORA11G> alter table sales
2 add constraint fk_sales_prod
3 foreign key(prod_id)
4 references products;
Table altered.
Elapsed: 00:00:00.15
rajesh@ORA11G>
rajesh@ORA11G> alter table sales
2 add constraint fk_sales_cust
3 foreign key(cust_id)
4 references customers;
Table altered.
Elapsed: 00:00:00.09
rajesh@ORA11G>
rajesh@ORA11G> create index cust_idx_01
2 on customers(cust_effective_from,gender)
3 nologging;
Index created.
Elapsed: 00:00:00.12
rajesh@ORA11G>
rajesh@ORA11G> begin
2 dbms_stats.gather_table_stats(user,'products');
3 dbms_stats.gather_table_stats(user,'customers');
4 dbms_stats.gather_table_stats(user,'sales');
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.43
rajesh@ORA11G>
rajesh@ORA11G> variable dt1 varchar2(20);
rajesh@ORA11G> variable dt2 varchar2(20);
rajesh@ORA11G> exec :dt1 := '01-DEC-2012';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@ORA11G> exec :dt2 := '30-DEC-2012';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@ORA11G>
Then, create stored outlines and lets verify whether it gets picked by Optimizer.
rajesh@ORA11G> alter session set create_stored_outlines=APP_OUTLINES;
Session altered.
Elapsed: 00:00:00.00
rajesh@ORA11G> select p.prod_name, p.prod_category,
2 c.cust_fname, c.cust_email,
3 s.amount_sold, s.quantity_sold
4 from products p, sales s,
5 customers c
6 where p.prod_id = s.prod_id
7 and c.cust_id = s.cust_id
8 and c.cust_lname is not null
9 and c.cust_effective_from
10 between to_date(:dt1,'DD-MON-YYYY')
11 and to_date(:dt2,'DD-MON-YYYY')+1 ;
no rows selected
Elapsed: 00:00:00.02
rajesh@ORA11G> alter session set create_stored_outlines=false;
Session altered.
Elapsed: 00:00:00.00
rajesh@ORA11G>
rajesh@ORA11G> alter session set use_stored_outlines=APP_OUTLINES;
Session altered.
Elapsed: 00:00:00.00
rajesh@ORA11G> set autotrace traceonly explain;
rajesh@ORA11G> select p.prod_name, p.prod_category,
2 c.cust_fname, c.cust_email,
3 s.amount_sold, s.quantity_sold
4 from products p, sales s,
5 customers c
6 where p.prod_id = s.prod_id
7 and c.cust_id = s.cust_id
8 and c.cust_lname is not null
9 and c.cust_effective_from
10 between to_date(:dt1,'DD-MON-YYYY')
11 and to_date(:dt2,'DD-MON-YYYY')+1 ;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 868086297
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 93 | 12183 | 861 (1)| 00:00:11 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 93 | 12183 | 861 (1)| 00:00:11 |
|* 3 | HASH JOIN | | 93 | 8556 | 622 (1)| 00:00:08 |
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 77 | 540 (1)| 00:00:07 |
| 5 | TABLE ACCESS FULL| SALES | 71411 | 1046K| 81 (2)| 00:00:01 |
| 6 | TABLE ACCESS FULL | PRODUCTS | 71407 | 2719K| 238 (1)| 00:00:03 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:DT1,'DD-MON-YYYY')<=TO_DATE(:DT2,'DD-MON-YYYY')+1)
2 - access("P"."PROD_ID"="S"."PROD_ID")
3 - access("C"."CUST_ID"="S"."CUST_ID")
4 - filter("C"."CUST_LNAME" IS NOT NULL AND
"C"."CUST_EFFECTIVE_FROM">=TO_DATE(:DT1,'DD-MON-YYYY') AND
"C"."CUST_EFFECTIVE_FROM"<=TO_DATE(:DT2,'DD-MON-YYYY')+1)
Note
-----
- outline "SYS_OUTLINE_13032318100388102" used for this statement
rajesh@ORA11G> set autotrace off;
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> alter session set use_stored_outlines=false;
Session altered.
Elapsed: 00:00:00.00
rajesh@ORA11G>
Next, we will create a new index and compare its plan's produced with Outlines and CBO's.
rajesh@ORA11G> create index cust_idx_02
2 on customers(cust_effective_from,cust_lname)
3 global partition by range(cust_effective_from)
4 ( partition p1 values less than ( to_date('09/01/2012','mm/dd/yyyy') ),
5 partition p2 values less than ( to_date('10/01/2012','mm/dd/yyyy') ),
6 partition p3 values less than ( to_date('11/01/2012','mm/dd/yyyy') ),
7 partition p4 values less than ( to_date('12/01/2012','mm/dd/yyyy') ),
8 partition p5 values less than ( to_date('01/01/2013','mm/dd/yyyy') ),
9 partition p6 values less than ( to_date('02/01/2013','mm/dd/yyyy') ),
10 partition pmax values less than ( maxvalue ) )
11 /
Index created.
Elapsed: 00:00:00.14
rajesh@ORA11G> set autotrace traceonly explain;
rajesh@ORA11G> alter session set use_stored_outlines=APP_OUTLINES;
Session altered.
Elapsed: 00:00:00.00
rajesh@ORA11G> select p.prod_name, p.prod_category,
2 c.cust_fname, c.cust_email,
3 s.amount_sold, s.quantity_sold
4 from products p, sales s,
5 customers c
6 where p.prod_id = s.prod_id
7 and c.cust_id = s.cust_id
8 and c.cust_lname is not null
9 and c.cust_effective_from
10 between to_date(:dt1,'DD-MON-YYYY')
11 and to_date(:dt2,'DD-MON-YYYY')+1 ;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 868086297
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 93 | 12183 | 861 (1)| 00:00:11 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 93 | 12183 | 861 (1)| 00:00:11 |
|* 3 | HASH JOIN | | 93 | 8556 | 622 (1)| 00:00:08 |
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 77 | 540 (1)| 00:00:07 |
| 5 | TABLE ACCESS FULL| SALES | 71411 | 1046K| 81 (2)| 00:00:01 |
| 6 | TABLE ACCESS FULL | PRODUCTS | 71407 | 2719K| 238 (1)| 00:00:03 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:DT1,'DD-MON-YYYY')<=TO_DATE(:DT2,'DD-MON-YYYY')+1)
2 - access("P"."PROD_ID"="S"."PROD_ID")
3 - access("C"."CUST_ID"="S"."CUST_ID")
4 - filter("C"."CUST_LNAME" IS NOT NULL AND
"C"."CUST_EFFECTIVE_FROM">=TO_DATE(:DT1,'DD-MON-YYYY') AND
"C"."CUST_EFFECTIVE_FROM"<=TO_DATE(:DT2,'DD-MON-YYYY')+1)
Note
-----
- outline "SYS_OUTLINE_13032318100388102" used for this statement
rajesh@ORA11G> alter session set use_stored_outlines=false;
Session altered.
Elapsed: 00:00:00.00
rajesh@ORA11G> select p.prod_name, p.prod_category,
2 c.cust_fname, c.cust_email,
3 s.amount_sold, s.quantity_sold
4 from products p, sales s,
5 customers c
6 where p.prod_id = s.prod_id
7 and c.cust_id = s.cust_id
8 and c.cust_lname is not null
9 and c.cust_effective_from
10 between to_date(:dt1,'DD-MON-YYYY')
11 and to_date(:dt2,'DD-MON-YYYY')+1 ;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1885014552
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 93 | 178 (2)| | |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 93 | 178 (2)| | |
|* 4 | HASH JOIN | | 93 | 85 (3)| | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 3 (0)| KEY | KEY |
| 6 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 3 (0)| | |
|* 7 | INDEX RANGE SCAN | CUST_IDX_02 | 2 | 2 (0)| KEY | KEY |
| 8 | TABLE ACCESS FULL | SALES | 71411 | 81 (2)| | |
|* 9 | INDEX UNIQUE SCAN | PROD_PK | 1 | 0 (0)| | |
| 10 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | 1 | 1 (0)| | |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:DT1,'DD-MON-YYYY')<=TO_DATE(:DT2,'DD-MON-YYYY')+1)
4 - access("C"."CUST_ID"="S"."CUST_ID")
7 - access("C"."CUST_EFFECTIVE_FROM">=TO_DATE(:DT1,'DD-MON-YYYY') AND
"C"."CUST_EFFECTIVE_FROM"<=TO_DATE(:DT2,'DD-MON-YYYY')+1)
filter("C"."CUST_LNAME" IS NOT NULL)
9 - access("P"."PROD_ID"="S"."PROD_ID")
rajesh@ORA11G> set autotrace off;
rajesh@ORA11G>
You can see that CBO has produced a different plan which is far better than plans present in Stored outlines. From this we can say that Outlines doesn't allow you to evolve the plans (even if best plan exists)
rajesh@ORA11G> @printtbl "select * from user_outlines"
NAME : "SYS_OUTLINE_13032318100388102"
CATEGORY : "APP_OUTLINES"
USED : "USED"
TIMESTAMP : "23-mar-2013 18:10:03"
VERSION : "11.2.0.1.0"
SQL_TEXT : "select p.prod_name, p.prod_category,
c.cust_fname, c.cust_email,
s.amount_sold, s.quantity_sold
from products p, sales s,
customers c
where p.prod_id = s.prod_id
and c.cust_id = s.cust_id
and c.cust_lname is not n
ull
and c.cust_effective_from
between to_date(:dt1,'DD-MON-YYYY')
and to_date(:dt2,'DD-MON-YYYY')+1 "
SIGNATURE : "F27E24E394A37D65F0BD33F916F4398D"
COMPATIBLE : "COMPATIBLE"
ENABLED : "ENABLED"
FORMAT : "NORMAL"
MIGRATED : "NOT-MIGRATED"
---------------------------------------------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA11G>
rajesh@ORA11G> @printtbl "select * from dba_sql_plan_baselines"
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@ORA11G>
Now lets see what happen's when we convert stored outlines to sql plan baselines.
rajesh@ORA11G>
rajesh@ORA11G> declare
2 l_clob clob;
3 begin
4 l_clob := dbms_spm.migrate_stored_outline
5 (attribute_name=>'CATEGORY',
6 attribute_value=>'APP_OUTLINES');
7 end;
8 /
before migrate:
after migrate:
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
rajesh@ORA11G> @printtbl "select * from dba_sql_plan_baselines"
SIGNATURE : "17354617825314172248"
SQL_HANDLE : "SYS_SQL_f0d7fcf6a2836d58"
SQL_TEXT : "select p.prod_name, p.prod_category,
c.cust_fname, c.cust_email,
s.amount_sold, s.quantity_sold
from products p, sales s,
customers c
where p.prod_id = s.prod_id
and c.cust_id = s.cust_id
and c.cust_lname is not n
ull
and c.cust_effective_from
between to_date(:dt1,'DD-MON-YYYY')
and to_date(:dt2,'DD-MON-YYYY')+1 "
PLAN_NAME : "SYS_OUTLINE_13032318100388102"
CREATOR : "RAJESH"
ORIGIN : "STORED-OUTLINE"
PARSING_SCHEMA_NAME : "RAJESH"
DESCRIPTION : ""
VERSION : "11.2.0.1.0"
CREATED : "23-MAR-13 06.10.03.000000 PM"
LAST_MODIFIED : "23-MAR-13 06.13.30.000000 PM"
LAST_EXECUTED : ""
LAST_VERIFIED : ""
ENABLED : "YES"
ACCEPTED : "YES"
FIXED : "NO"
AUTOPURGE : "NO"
OPTIMIZER_COST : "860"
MODULE : "APP_OUTLINES"
ACTION : ""
EXECUTIONS : "0"
ELAPSED_TIME : "0"
CPU_TIME : "0"
BUFFER_GETS : "0"
DISK_READS : "0"
DIRECT_WRITES : "0"
ROWS_PROCESSED : "0"
FETCHES : "0"
END_OF_FETCH_COUNT : "0"
---------------------------------------------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> show parameter sql_plan_baselines
NAME TYPE VALUE
------------------------- ----------- -------------------------
optimizer_capture_sql_pla boolean FALSE
optimizer_use_sql_plan_ba boolean TRUE
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> set autotrace traceonly explain statistics;
rajesh@ORA11G> select p.prod_name, p.prod_category,
2 c.cust_fname, c.cust_email,
3 s.amount_sold, s.quantity_sold
4 from products p, sales s,
5 customers c
6 where p.prod_id = s.prod_id
7 and c.cust_id = s.cust_id
8 and c.cust_lname is not null
9 and c.cust_effective_from
10 between to_date(:dt1,'DD-MON-YYYY')
11 and to_date(:dt2,'DD-MON-YYYY')+1 ;
no rows selected
Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
Plan hash value: 868086297
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 93 | 12183 | 861 (1)| 00:00:11 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 93 | 12183 | 861 (1)| 00:00:11 |
|* 3 | HASH JOIN | | 93 | 8556 | 622 (1)| 00:00:08 |
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 77 | 540 (1)| 00:00:07 |
| 5 | TABLE ACCESS FULL| SALES | 71411 | 1046K| 81 (2)| 00:00:01 |
| 6 | TABLE ACCESS FULL | PRODUCTS | 71407 | 2719K| 238 (1)| 00:00:03 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:DT1,'DD-MON-YYYY')<=TO_DATE(:DT2,'DD-MON-YYYY')+1)
2 - access("P"."PROD_ID"="S"."PROD_ID")
3 - access("C"."CUST_ID"="S"."CUST_ID")
4 - filter("C"."CUST_LNAME" IS NOT NULL AND
"C"."CUST_EFFECTIVE_FROM">=TO_DATE(:DT1,'DD-MON-YYYY') AND
"C"."CUST_EFFECTIVE_FROM"<=TO_DATE(:DT2,'DD-MON-YYYY')+1)
Note
-----
- SQL plan baseline "SYS_OUTLINE_13032318100388102" used for this statement
Statistics
----------------------------------------------------------
41 recursive calls
46 db block gets
2244 consistent gets
2 physical reads
6564 redo size
616 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
rajesh@ORA11G> set autotrace off;
From this we can conclude that optimizer started to use Accepted plan's from baselines, but if you look into the plan history, we will be having a new un-accepted plan generated by CBO. This new plan will not be used unless its moved into plan baselines ( ie, marked "Accepted" )
rajesh@ORA11G>
rajesh@ORA11G> @printtbl "select * from dba_sql_plan_baselines"
SIGNATURE : "17354617825314172248"
SQL_HANDLE : "SYS_SQL_f0d7fcf6a2836d58"
SQL_TEXT : "select p.prod_name, p.prod_category,
c.cust_fname, c.cust_email,
s.amount_sold, s.quantity_sold
from products p, sales s,
customers c
where p.prod_id = s.prod_id
and c.cust_id = s.cust_id
and c.cust_lname is not n
ull
and c.cust_effective_from
between to_date(:dt1,'DD-MON-YYYY')
and to_date(:dt2,'DD-MON-YYYY')+1 "
PLAN_NAME : "SQL_PLAN_g1pzwyuj86vas72c6cc5d"
CREATOR : "RAJESH"
ORIGIN : "AUTO-CAPTURE"
PARSING_SCHEMA_NAME : "RAJESH"
DESCRIPTION : ""
VERSION : "11.2.0.1.0"
CREATED : "23-MAR-13 06.14.25.000000 PM"
LAST_MODIFIED : "23-MAR-13 06.14.25.000000 PM"
LAST_EXECUTED : ""
LAST_VERIFIED : ""
ENABLED : "YES"
ACCEPTED : "NO"
FIXED : "NO"
AUTOPURGE : "YES"
OPTIMIZER_COST : "361"
MODULE : "SQL*Plus"
ACTION : ""
EXECUTIONS : "0"
ELAPSED_TIME : "0"
CPU_TIME : "0"
BUFFER_GETS : "0"
DISK_READS : "0"
DIRECT_WRITES : "0"
ROWS_PROCESSED : "0"
FETCHES : "0"
END_OF_FETCH_COUNT : "0"
---------------------------------------------------
SIGNATURE : "17354617825314172248"
SQL_HANDLE : "SYS_SQL_f0d7fcf6a2836d58"
SQL_TEXT : "select p.prod_name, p.prod_category,
c.cust_fname, c.cust_email,
s.amount_sold, s.quantity_sold
from products p, sales s,
customers c
where p.prod_id = s.prod_id
and c.cust_id = s.cust_id
and c.cust_lname is not n
ull
and c.cust_effective_from
between to_date(:dt1,'DD-MON-YYYY')
and to_date(:dt2,'DD-MON-YYYY')+1 "
PLAN_NAME : "SYS_OUTLINE_13032318100388102"
CREATOR : "RAJESH"
ORIGIN : "STORED-OUTLINE"
PARSING_SCHEMA_NAME : "RAJESH"
DESCRIPTION : ""
VERSION : "11.2.0.1.0"
CREATED : "23-MAR-13 06.10.03.000000 PM"
LAST_MODIFIED : "23-MAR-13 06.13.30.000000 PM"
LAST_EXECUTED : "23-MAR-13 06.14.25.000000 PM"
LAST_VERIFIED : ""
ENABLED : "YES"
ACCEPTED : "YES"
FIXED : "NO"
AUTOPURGE : "NO"
OPTIMIZER_COST : "860"
MODULE : "APP_OUTLINES"
ACTION : ""
EXECUTIONS : "0"
ELAPSED_TIME : "0"
CPU_TIME : "0"
BUFFER_GETS : "0"
DISK_READS : "0"
DIRECT_WRITES : "0"
ROWS_PROCESSED : "0"
FETCHES : "0"
END_OF_FETCH_COUNT : "0"
---------------------------------------------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
rajesh@ORA11G>
rajesh@ORA11G> variable x clob;
rajesh@ORA11G> variable sql_handle varchar2(50);
rajesh@ORA11G> variable plan_name varchar2(50);
rajesh@ORA11G> exec :sql_handle := 'SYS_SQL_f0d7fcf6a2836d58';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@ORA11G> exec :plan_name := 'SQL_PLAN_g1pzwyuj86vas72c6cc5d';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@ORA11G> exec :x := dbms_spm.evolve_sql_plan_baseline(:sql_handle,:plan_name);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.36
rajesh@ORA11G> print x
X
----------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_f0d7fcf6a2836d58
PLAN_NAME = SQL_PLAN_g1pzwyuj86vas72c6cc5d
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_g1pzwyuj86vas72c6cc5d
------------------------------------
Plan was verified: Time used .359 seconds.
Plan passed performance criterion: 7.83 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 0 0
Elapsed Time(ms): 18.652 7.231 2.58
CPU Time(ms): 19.066 8.666 2.2
Buffer Gets: 2221 283 7.85
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 1820 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> set autotrace traceonly explain statistics;
rajesh@ORA11G> select p.prod_name, p.prod_category,
2 c.cust_fname, c.cust_email,
3 s.amount_sold, s.quantity_sold
4 from products p, sales s,
5 customers c
6 where p.prod_id = s.prod_id
7 and c.cust_id = s.cust_id
8 and c.cust_lname is not null
9 and c.cust_effective_from
10 between to_date(:dt1,'DD-MON-YYYY')
11 and to_date(:dt2,'DD-MON-YYYY')+1 ;
no rows selected
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1085928825
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 93 | 324 (2)| | |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 93 | 324 (2)| | |
|* 3 | HASH JOIN | | 93 | 85 (3)| | |
| 4 | PARTITION RANGE ITERATOR | | 1 | 3 (0)| KEY | KEY |
| 5 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 3 (0)| | |
|* 6 | INDEX RANGE SCAN | CUST_IDX_02 | 2 | 2 (0)| KEY | KEY |
| 7 | TABLE ACCESS FULL | SALES | 71411 | 81 (2)| | |
| 8 | TABLE ACCESS FULL | PRODUCTS | 71407 | 238 (1)| | |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:DT1,'DD-MON-YYYY')<=TO_DATE(:DT2,'DD-MON-YYYY')+1)
2 - access("P"."PROD_ID"="S"."PROD_ID")
3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("C"."CUST_EFFECTIVE_FROM">=TO_DATE(:DT1,'DD-MON-YYYY') AND
"C"."CUST_EFFECTIVE_FROM"<=TO_DATE(:DT2,'DD-MON-YYYY')+1)
filter("C"."CUST_LNAME" IS NOT NULL)
Note
-----
- SQL plan baseline "SQL_PLAN_g1pzwyuj86vas72c6cc5d" used for this statement
Statistics
----------------------------------------------------------
13 recursive calls
14 db block gets
291 consistent gets
0 physical reads
3064 redo size
616 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
rajesh@ORA11G> set autotrace off;
rajesh@ORA11G>
From now on, we can have sql plan baselines active, (which keeps watching for new plan) and disable stored outlines.
