Execution plan changes in oracle for various reason like stats gathering, database upgrades or new index creations, However most new plans are obviously improvement but some might lead to worst performance. Starting with Oracle 11g a new feature called sql plan management (SPM) allows user to maintain stable and Optimal performance for a SQL statement.
SPM incorporates the positive attributes of plan stability and plan adaptability.
A managed SQL statement is one for which SPM has been enabled. SPM can be configured to work automatically or it can be manually controlled. SPM helps prevent performance regressions by enabling the detection of plan changes for managed SQL statements. For this purpose, SPM maintains, on disk, a plan history consisting of different execution plans generated for each managed SQL statement. An enhanced version of the Oracle optimizer, called SPM aware optimizer, accesses, uses, and manages this information which is stored in a repository called the SQL Management Base (SMB).
The plan history enables the SPM aware optimizer to determine whether the best-cost plan it has produced using the cost-based method is a brand new plan or not. A brand new plan represents a plan change that has potential to cause performance regression. For this reason, the SPM aware optimizer does not choose a brand new best-cost plan. Instead, it chooses from a set of accepted plans. An accepted plan is one that has been either verified to not cause performance regression or designated to have good performance. A set of accepted plans is called a SQL plan baseline, which represents a subset of the plan history.
A brand new plan is added to the plan history as a non-accepted plan. Later, an SPM utility verifies its performance, and keeps it as a non-accepted plan if it will cause a performance regression, or changes it to an accepted plan if it will provide a performance improvement. The plan performance verification process ensures both plan stability and plan adaptability.
The figure below shows the SMB containing plan history for three sql statements. Each plan history contains some accepted plan and some non-accepted plans.
1) using SQL Tuning Set,
2) from the Cursor Cache,
3) Exporting from one database and import into another database, and
4) Automatically for every statement
First we will see how to create sql plan baselines. sql plan baselines can be created automatically for all sql statements by setting the parameter Optimizer_capture_sql_plan_baselines=True (by default false) The first plan captured for any statement is automatically accepted and becomes part of the SQL plan baseline, so enable this parameter only when you are sure that the default plans are performing well.
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.25
rajesh@ORA11G>
rajesh@ORA11G> alter table products
2 add constraint prod_pk
3 primary key(prod_id);
Table altered.
Elapsed: 00:00:01.49
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:09.69
rajesh@ORA11G>
rajesh@ORA11G> alter table customers
2 add constraint cust_pk
3 primary key(cust_id);
Table altered.
Elapsed: 00:00:00.35
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:03.90
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.22
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.32
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> variable dt1 varchar2(20);
rajesh@ORA11G> variable dt2 varchar2(20);
rajesh@ORA11G>
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>
rajesh@ORA11G> show parameter sql_plan_baseline
NAME TYPE VALUE
------------------------- ----------- -------------------------
optimizer_capture_sql_pla boolean FALSE
optimizer_use_sql_plan_ba boolean TRUE
rajesh@ORA11G> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.
Elapsed: 00:00:00.01
rajesh@ORA11G> @d:\query1.sql;
rajesh@ORA11G> @d:\query1.sql;
rajesh@ORA11G> alter session set optimizer_capture_sql_plan_baselines=false;
Session altered.
Elapsed: 00:00:00.00
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 null
and c.cust_effective_from
between to_date(:dt1,'DD-MON-YYYY')
and to_date(:dt2,'DD-MON-YYYY')+1"
PLAN_NAME : "SQL_PLAN_g1pzwyuj86vas8f13956a"
CREATOR : "RAJESH"
ORIGIN : "AUTO-CAPTURE"
PARSING_SCHEMA_NAME : "RAJESH"
DESCRIPTION : ""
VERSION : "11.2.0.1.0"
CREATED : "04-JAN-13 06.02.49.000000 PM"
LAST_MODIFIED : "04-JAN-13 06.02.49.000000 PM"
LAST_EXECUTED : "04-JAN-13 06.02.49.000000 PM"
LAST_VERIFIED : ""
ENABLED : "YES"
ACCEPTED : "YES"
FIXED : "NO"
AUTOPURGE : "YES"
OPTIMIZER_COST : "856"
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"
-----------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.18
rajesh@ORA11G>
This shows a plan being captured automatically when the same statement is executed twice.
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.08
Execution Plan
----------------------------------------------------------
Plan hash value: 868086297
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 7467 | 856 (1)| 00:00:11 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 57 | 7467 | 856 (1)| 00:00:11 |
|* 3 | HASH JOIN | | 57 | 5244 | 619 (1)| 00:00:08 |
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 77 | 538 (1)| 00:00:07 |
| 5 | TABLE ACCESS FULL| SALES | 71149 | 1042K| 80 (2)| 00:00:01 |
| 6 | TABLE ACCESS FULL | PRODUCTS | 71145 | 2709K| 236 (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 "SQL_PLAN_g1pzwyuj86vas8f13956a" used for this statement
rajesh@ORA11G> set autotrace off;
The "Note" section at the bottom tells you that the optimizer is using the SQL Plan baseline.
Now we create a new index in our "Customers" table, present in our schema.
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>
The two SPM parameter remains default in our schema.
rajesh@ORA11G> show parameter sql_plan_baseline
NAME TYPE VALUE
------------------------- ----------- -------------------------
optimizer_capture_sql_pla boolean FALSE
optimizer_use_sql_plan_ba boolean TRUE
rajesh@ORA11G>
Let's re-execute the same sql statement with the same binded inputs ( but now we have a new object "cust_idx_02" present in this schema )
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.12
Execution Plan
----------------------------------------------------------
Plan hash value: 868086297
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 7467 | 856 (1)| 00:00:11 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 57 | 7467 | 856 (1)| 00:00:11 |
|* 3 | HASH JOIN | | 57 | 5244 | 619 (1)| 00:00:08 |
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 77 | 538 (1)| 00:00:07 |
| 5 | TABLE ACCESS FULL| SALES | 71149 | 1042K| 80 (2)| 00:00:01 |
| 6 | TABLE ACCESS FULL | PRODUCTS | 71145 | 2709K| 236 (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 "SQL_PLAN_g1pzwyuj86vas8f13956a" used for this statement
Statistics
----------------------------------------------------------
18 recursive calls
34 db block gets
2223 consistent gets
63 physical reads
4076 redo size
616 bytes sent via SQL*Net to client
404 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;
This shows that the Optimizer used an accepeted plan in the sql plan baseline in favour of a new plan. In face, we can also check that the optimizer inserted the new plan into the statement's plan history.
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 : "04-JAN-13 06.04.16.000000 PM"
LAST_MODIFIED : "04-JAN-13 06.04.16.000000 PM"
LAST_EXECUTED : ""
LAST_VERIFIED : ""
ENABLED : "YES"
ACCEPTED : "NO"
FIXED : "NO"
AUTOPURGE : "YES"
OPTIMIZER_COST : "386"
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 : "SQL_PLAN_g1pzwyuj86vas8f13956a"
CREATOR : "RAJESH"
ORIGIN : "AUTO-CAPTURE"
PARSING_SCHEMA_NAME : "RAJESH"
DESCRIPTION : ""
VERSION : "11.2.0.1.0"
CREATED : "04-JAN-13 06.02.49.000000 PM"
LAST_MODIFIED : "04-JAN-13 06.02.49.000000 PM"
LAST_EXECUTED : "04-JAN-13 06.02.49.000000 PM"
LAST_VERIFIED : ""
ENABLED : "YES"
ACCEPTED : "YES"
FIXED : "NO"
AUTOPURGE : "YES"
OPTIMIZER_COST : "856"
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"
---------------------------------------------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.18
rajesh@ORA11G>
rajesh@ORA11G>
The 'NO' value for the accepted column implies that the new plan is in the plan history but is not available for use until it is verified to be a good plan. The optimizer will continue to use an accepted plan until new plans are verified and added to the SQL plan baseline. To view the explain plan for each plans available in the plan history we can invoke the new "display_sql_plan_baseline" method added to "dbms_xplan" API.
rajesh@ORA11G> select t2.*
2 from dba_sql_plan_baselines b,
3 table( dbms_xplan.display_sql_plan_baseline(b.sql_handle,b.plan_name)) t2;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
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 null 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 Plan id: 1925631069
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 1085928825
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 321 (2)| | |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 57 | 321 (2)| | |
|* 3 | HASH JOIN | | 57 | 84 (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 | 1 | 2 (0)| KEY | KEY |
| 7 | TABLE ACCESS FULL | SALES | 71149 | 80 (2)| | |
| 8 | TABLE ACCESS FULL | PRODUCTS | 71145 | 236 (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)
--------------------------------------------------------------------------------
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 null and c.cust_effective_from between
to_date(:dt1,'DD-MON-YYYY') and to_date(:dt2,'DD-MON-YYYY')+1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g1pzwyuj86vas8f13956a Plan id: 2400425322
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 868086297
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 7467 | 856 (1)| 00:00:11 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 57 | 7467 | 856 (1)| 00:00:11 |
|* 3 | HASH JOIN | | 57 | 5244 | 619 (1)| 00:00:08 |
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 77 | 538 (1)| 00:00:07 |
| 5 | TABLE ACCESS FULL| SALES | 71149 | 1042K| 80 (2)| 00:00:01 |
| 6 | TABLE ACCESS FULL | PRODUCTS | 71145 | 2709K| 236 (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)
78 rows selected.
Elapsed: 00:00:00.41
rajesh@ORA11G>
rajesh@ORA11G>
When you create a SQL plan baseline for a SQL statement, the SPM aware optimizer thus guarantees that no new plans will be used other than the ones in the SQL plan baseline. This prevents unexpected plan changes that sometimes lead to performance regressions. Preventing new plans from being used is fine, but what if the new plans are in fact better?
Non-accepted plans can be verified by executing the evolve_sql_plan_baseline function. This function will execute the non-accepted plan and compare its performance to the best accepted plan. The execution is performed using the conditions (e.g., bind values, parameters, etc.) in effect at the time the non-accepted plan was added to the plan history. If the non-accepted plan's performance is better, the function will make it accepted, thus adding it to the SQL plan baseline.
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.01
rajesh@ORA11G> exec :plan_name := 'SQL_PLAN_g1pzwyuj86vas72c6cc5d';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA11G> exec :x := dbms_spm.evolve_sql_plan_baseline(:sql_handle,:plan_name);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.40
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 .39 seconds.
Plan passed performance criterion: 7.54 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): 22.299 7.728 2.89
CPU Time(ms): 17.333 6.933 2.5
Buffer Gets: 2213 293 7.55
Physical Read Requests: 2 0
Physical Write Requests: 0 0
Physical Read Bytes: 183864 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>
The plan verification report shows that the new plan's performance was better and so it was made accepted and became part of the SQL plan baseline. We can confirm it by looking in the dba_sql_plan_baselines view.
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 null
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 : "04-JAN-13 06.04.16.000000 PM"
LAST_MODIFIED : "04-JAN-13 06.05.45.000000 PM"
LAST_EXECUTED : ""
LAST_VERIFIED : "04-JAN-13 06.05.45.000000 PM"
ENABLED : "YES"
ACCEPTED : "YES"
FIXED : "NO"
AUTOPURGE : "YES"
OPTIMIZER_COST : "386"
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 null
and c.cust_effective_from
between to_date(:dt1,'DD-MON-YYYY')
and to_date(:dt2,'DD-MON-YYYY')+1"
PLAN_NAME : "SQL_PLAN_g1pzwyuj86vas8f13956a"
CREATOR : "RAJESH"
ORIGIN : "AUTO-CAPTURE"
PARSING_SCHEMA_NAME : "RAJESH"
DESCRIPTION : ""
VERSION : "11.2.0.1.0"
CREATED : "04-JAN-13 06.02.49.000000 PM"
LAST_MODIFIED : "04-JAN-13 06.02.49.000000 PM"
LAST_EXECUTED : "04-JAN-13 06.02.49.000000 PM"
LAST_VERIFIED : ""
ENABLED : "YES"
ACCEPTED : "YES"
FIXED : "NO"
AUTOPURGE : "YES"
OPTIMIZER_COST : "856"
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"
---------------------------------------------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.19
The SQL plan baseline now has two accepted plans. The explain plan now confirm this newly accepted plan will be used for further execution.
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.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1085928825
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 321 (2)| | |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 57 | 321 (2)| | |
|* 3 | HASH JOIN | | 57 | 84 (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 | 1 | 2 (0)| KEY | KEY |
| 7 | TABLE ACCESS FULL | SALES | 71149 | 80 (2)| | |
| 8 | TABLE ACCESS FULL | PRODUCTS | 71145 | 236 (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
rajesh@ORA11G> set autotrace off;
rajesh@ORA11G>
Thus, you can evolve a SQL plan baseline by executing the evolve_sql_plan_baseline function. New and provably better plans will be added to the SQL plan baseline.
No comments:
Post a Comment