The non-accepted plans can be verified by executing evolve_sql_plan_baseline. Another way of evolving a sql plan baseline is to use SQL Tuning advisor.
suppose we have two plans in sql plan baseline with one as accepted and other as non-accepted.
rajesh@ORA11G> select sql_handle,plan_name,enabled,accepted
2 from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_f0d7fcf6a2836d58 SQL_PLAN_g1pzwyuj86vas72c6cc5d YES NO
SYS_SQL_f0d7fcf6a2836d58 SQL_PLAN_g1pzwyuj86vas8f13956a YES YES
2 rows selected.
Elapsed: 00:00:00.00
rajesh@ORA11G>
The corresponding explain plan for these two handles are
rajesh@ORA11G> select t.*
2 from dba_sql_plan_baselines b,
3 table( dbms_xplan.display_sql_plan_baseline(b.sql_handle,b.plan_name) ) t
4 /
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 | | 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 | 71410 | 81 (2)| | |
| 8 | TABLE ACCESS FULL | PRODUCTS | 71406 | 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)
--------------------------------------------------------------------------------
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 | | 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 | 71410 | 1046K| 81 (2)| 00:00:01 |
| 6 | TABLE ACCESS FULL | PRODUCTS | 71406 | 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)
78 rows selected.
Elapsed: 00:00:00.18
rajesh@ORA11G>
rajesh@ORA11G> column sql_text format a40;
rajesh@ORA11G> l
1 select sql_id,sql_text
2 from v$sql
3* where sql_id = '8g13kqrksqsgj'
rajesh@ORA11G> /
SQL_ID SQL_TEXT
------------- ----------------------------------------
8g13kqrksqsgj select p.prod_name, p.prod_category,
c.cust_fname, c.cust_email,
s.amount_sold, s.quantity_sold from pro
ducts p, sales s, customers c w
here p.prod_id = s.prod_id and c.cust_id
= s.cust_id and c.cust_lname is not nul
l and c.cust_effective_from between to_d
ate(:dt1,'DD-MON-YYYY') and to_date(:dt
2,'DD-MON-YYYY')+1
1 row selected.
Elapsed: 00:00:00.00
rajesh@ORA11G>
Invoking the sql tuning advisor for this sqlid '8g13kqrksqsgj '
rajesh@ORA11G> variable taskid varchar2(100);
rajesh@ORA11G> variable sqlid varchar2(20);
rajesh@ORA11G> variable x clob;
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> exec :sqlid :='8g13kqrksqsgj';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@ORA11G>
rajesh@ORA11G> exec :taskid := dbms_sqltune.create_tuning_task(sql_id=>:sqlid);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.25
rajesh@ORA11G> exec dbms_sqltune.execute_tuning_task(task_name=>:taskid);
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.52
rajesh@ORA11G> select dbms_sqltune.report_tuning_task(task_name=>:taskid) from dual ;
DBMS_SQLTUNE.REPORT_TUNING_TASK(TASK_NAME=>:TASKID)
-----------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_2472
Tuning Task Owner : RAJESH
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/23/2013 13:52:26
Completed at : 03/23/2013 13:52:34
-------------------------------------------------------------------------------
Schema Name: RAJESH
SQL ID : 8g13kqrksqsgj
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
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 88.55%)
------------------------------------------
- Consider accepting the recommended SQL profile. A SQL plan baseline
corresponding to the plan with the SQL profile will also be created.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_2472',
task_owner => 'RAJESH', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time(us): 3340976 7056 99.78 %
CPU Time(us): 46800 7800 83.33 %
User I/O Time(us): 923408 0 100 %
Buffer Gets: 2221 254 88.56 %
Physical Read Requests: 87 0 100 %
Physical Write Requests: 0 0
Physical Read Bytes: 15302656 0 100 %
Physical Write Bytes: 0 0
Rows Processed: 0 0
Fetches: 0 0
Executions: 1 1
Notes
-----
1. The SQL profile plan was first executed to warm the buffer cache.
2. Statistics for the SQL profile plan were averaged over next 9 executions.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 54.6%)
-----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index RAJESH.IDX$$_09A80001 on RAJESH.SALES("CUST_ID");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 868086297
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 131 | 861 (1)| 00:00:11 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 1 | 131 | 861 (1)| 00:00:11 |
|* 3 | HASH JOIN | | 1 | 92 | 622 (1)| 00:00:08 |
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 5 | 385 | 540 (1)| 00:00:07 |
| 5 | TABLE ACCESS FULL| SALES | 71410 | 1046K| 81 (2)| 00:00:01 |
| 6 | TABLE ACCESS FULL | PRODUCTS | 71406 | 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)
2- Using SQL Profile
--------------------
Plan hash value: 1885014552
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 (3)| | |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 86 (3)| | |
|* 4 | HASH JOIN | | 1 | 85 (3)| | |
| 5 | PARTITION RANGE ITERATOR | | 5 | 3 (0)| KEY | KEY |
| 6 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 5 | 3 (0)| | |
|* 7 | INDEX RANGE SCAN | CUST_IDX_02 | 3 | 2 (0)| KEY | KEY |
| 8 | TABLE ACCESS FULL | SALES | 71410 | 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")
3- Using New Indices
--------------------
Plan hash value: 2777968508
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 (0)| | |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 39 (0)| | |
| 4 | NESTED LOOPS | | 1 | 38 (0)| | |
| 5 | PARTITION RANGE ITERATOR | | 5 | 3 (0)| KEY | KEY |
| 6 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 5 | 3 (0)| | |
|* 7 | INDEX RANGE SCAN | CUST_IDX_02 | 3 | 2 (0)| KEY | KEY |
| 8 | TABLE ACCESS BY INDEX ROWID | SALES | 1 | 31 (0)| | |
|* 9 | INDEX RANGE SCAN | IDX$$_09A80001 | 71 | 1 (0)| | |
|* 10 | INDEX UNIQUE SCAN | PROD_PK | 1 | 0 (0)| | |
| 11 | 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)
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("C"."CUST_ID"="S"."CUST_ID")
10 - access("P"."PROD_ID"="S"."PROD_ID")
-------------------------------------------------------------------------------
rajesh@ORA11G>
What we see here is that SQL Tuning Advisor found a tuned plan. When we accepted the recommended SQL profile, the SQL Tuning Advisor created a SQL profile and also added the plans to baselines, thus evolving the SQL plan baseline to two plans.
rajesh@ORA11G> select sql_handle,plan_name,enabled,accepted
2 from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_f0d7fcf6a2836d58 SQL_PLAN_g1pzwyuj86vas72c6cc5d YES NO
SYS_SQL_f0d7fcf6a2836d58 SQL_PLAN_g1pzwyuj86vas8f13956a YES YES
2 rows selected.
Elapsed: 00:00:00.00
rajesh@ORA11G>
rajesh@ORA11G> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_2472',task_owner => 'RAJESH', replace => TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.58
rajesh@ORA11G> select sql_handle,plan_name,enabled,accepted
2 from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_f0d7fcf6a2836d58 SQL_PLAN_g1pzwyuj86vas72c6cc5d YES NO
SYS_SQL_f0d7fcf6a2836d58 SQL_PLAN_g1pzwyuj86vas8f13956a YES YES
SYS_SQL_f0d7fcf6a2836d58 SQL_PLAN_g1pzwyuj86vasbcd49e51 YES YES
3 rows selected.
Elapsed: 00:00:00.00
rajesh@ORA11G>
rajesh@ORA11G> select t.*
2 from dba_sql_plan_baselines b,
3 table( dbms_xplan.display_sql_plan_baseline(b.sql_handle,b.plan_name) ) t
4 /
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 | | 1 | 324 (2)| | |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 1 | 324 (2)| | |
|* 3 | HASH JOIN | | 1 | 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 | 1 | 2 (0)| KEY | KEY |
| 7 | TABLE ACCESS FULL | SALES | 71410 | 81 (2)| | |
| 8 | TABLE ACCESS FULL | PRODUCTS | 71406 | 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 profile "SYS_SQLPROF_013d96606d4f0000" used for this statement
--------------------------------------------------------------------------------
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 | | 1 | 131 | 861 (1)| 00:00:11 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 1 | 131 | 861 (1)| 00:00:11 |
|* 3 | HASH JOIN | | 1 | 92 | 622 (1)| 00:00:08 |
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 1 | 77 | 540 (1)| 00:00:07 |
| 5 | TABLE ACCESS FULL| SALES | 71410 | 1046K| 81 (2)| 00:00:01 |
| 6 | TABLE ACCESS FULL | PRODUCTS | 71406 | 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 profile "SYS_SQLPROF_013d96606d4f0000" used for this statement
--------------------------------------------------------------------------------
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_g1pzwyuj86vasbcd49e51 Plan id: 3168050769
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-SQLTUNE
--------------------------------------------------------------------------------
Plan hash value: 1885014552
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 (3)| | |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 86 (3)| | |
|* 4 | HASH JOIN | | 1 | 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 | 1 | 2 (0)| KEY | KEY |
| 8 | TABLE ACCESS FULL | SALES | 71410 | 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")
Note
-----
- SQL profile "SYS_SQLPROF_013d96606d4f0000" used for this statement
132 rows selected.
Elapsed: 00:00:00.34
rajesh@ORA11G>
Thus, you can evolve a SQL plan baseline either by executing the evolve_sql_plan_baseline function or by using the SQL Tuning Advisor. New and provably better plans will be added by either of these methods to the SQL plan baseline.
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: 1885014552
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 (3)| | |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 86 (3)| | |
|* 4 | HASH JOIN | | 1 | 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 | 1 | 2 (0)| KEY | KEY |
| 8 | TABLE ACCESS FULL | SALES | 71410 | 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")
Note
-----
- SQL profile "SYS_SQLPROF_013d96606d4f0000" used for this statement
- SQL plan baseline "SQL_PLAN_g1pzwyuj86vasbcd49e51" used for this statement
rajesh@ORA11G> set autotrace off;
rajesh@ORA11G>
No comments:
Post a Comment