SQL Plan Management 11g (SPM)
=============================
The dbms_spm package is the interface provided by oracle to perform controlled manipulation of plan history and sql plan base lines maintained for various SQL statements.
SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by recording and evaluating the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan
baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.
SQL Baselines -
The sql base line (base sqlplan) can be captured automatically by setting the parameter optimizer_capture_sql_plan_baselines to true which will caputre the baseline for all the repeated sql statements or we can load the sql plans manually when the parameter is set to false.
When a SQL statement is hard parsed, the cost based optimizer produces several execution
plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer
compares the plan it just produced with the plans in the SQL plan baseline.
If a matching plan is found that is flagged as accepted the plan is used. If the SQL plan baseline
doesn't contain an accepted plan matching the one it just created, the optimizer evaluates the
accepted plans in the SQL plan baseline and uses the one with the lowest cost.
If the execution plan originally produced by the optimizer has a lower cost than those present
in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used
until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, so they are considered non-reproducible, the optimizer will use the plan with the lowest cost.
Oracle call this a "conservative plan selection strategy", as the optimizer preferentially
uses a tried an tested execution plan, even if a new plan looks like it might perform better.
Only when the newer plan is proved to perform well will it be accepted for use.
The plans can be loaded manually using the load_plans_from_sqlset or load_plans_from_cursor_cache procedures of dbms_spm package.
Here we can see the example of creating a baseline using the manual method.
Example:-
SQL> create table emp (emp_no number, e_name varchar2(30), depart_id number);
Table created.
SQL> create table department (depart_id number primary key, department_name varchar2 (30))
Table created.
SQL> alter table emp add constraint emp_pk primary key (emp_no);
Table altered.
SQL> alter table emp add constraint dep_fk foreign key (depart_id) references department (depart_id);
Table altered.
Insert some values to deparment and emp tables:-
SQL> select * from department;
DEPART_ID DEPARTMENT_NAME
---------- ------------------------------
100 Software
101 Hardware
102 HR
103 Admin
104 Transport
105 Health
106 Electricity
107 Wealth
108 Miscellanious
9 rows selected.
SQL > begin
for i in 1000..300000 loop
insert into emp values (i,dbms_random.string('U',30),round(dbms_random.value(100,108)));
if i mod 10000 = 0 then
commit;
end if;
end loop;
end;
/
PL/SQL procedure successfully completed.
Now execute the below query:-
SQL> select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name
order by 1;
DEPART DEPART_NAME COUNT(*)
---------- ------------------------------ ----------
100 Software 18826
101 Hardware 37311
102 HR 37225
103 Admin 37542
104 Transport 37416
105 Health 37558
106 Electricity 37244
107 Wealth 37249
108 Miscellanous 18627
9 rows selected.
Lets look at the plan of the above query.
SQL> select sql_id, child_number, sql_text from V$sql where sql_text like '%b.department_name Depart_Name%';
SQL_ID CHILD_NUMBER
------------- ------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
bzd4sb9qvcmkf 0
select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name order by 1
SQL> select * from table (dbms_xplan.display_cursor('bzd4sb9qvcmkf',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bzd4sb9qvcmkf, child number 0
-------------------------------------
select a.depart_id depart, b.department_name Depart_Name, count(*) from
emp a , department b where a.depart_id = b.depart_id group by
a.depart_id,b.department_name order by 1
Plan hash value: 1683054800
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 528 (100)| |
| 1 | SORT GROUP BY | | 361K| 14M| 528 (3)| 00:00:07 |
|* 2 | HASH JOIN | | 361K| 14M| 518 (1)| 00:00:07 |
| 3 | TABLE ACCESS FULL| DEPARTMENT | 9 | 270 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 361K| 4585K| 514 (1)| 00:00:07 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPART_ID"="B"."DEPART_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
27 rows selected.
Lets load the above plan as the baseline for this query.
declare
kk PLS_INTEGER;
begin
kk := dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id => 'bzd4sb9qvcmkf');
end;
/
Let check the status of the baseline.
SQL> select sql_handle, sql_text,plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where sql_text like '%b.department_name Depart_Name%';
SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC FIX
------------------------------ -------------------------------------------------------------------------------- ------------------------------ --- --- ---
SYS_SQL_b5cbf74fe2ccb35f select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , SQL_PLAN_bbkzr9zjctcuze5d89d94 YES YES NO
department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
ame
order by 1
We see that this base sql shows as accepted plan and not the fixed one, which means when any other optimal plan is found for this statement then it will be added to the history .
Currently there is no index has been used in the depart_id column. Lets create a index and then
we will check the change.
SQL> create index dept_idx on emp(depart_id);
Index created.
Now we will execute the same query.
SQL> select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name
order by 1;
DEPART DEPART_NAME COUNT(*)
---------- ------------------------------ ----------
100 Software 18826
101 Hardware 37311
102 HR 37225
103 Admin 37542
104 Transport 37416
105 Health 37558
106 Electricity 37244
107 Wealth 37249
108 Miscellanous 18627
9 rows selected.
Now lets look the plan now.
SQL> select sql_id, child_number, sql_text from V$sql where sql_text like '%b.department_name Depart_Name%';
SQL_ID CHILD_NUMBER
------------- ------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
bzd4sb9qvcmkf 1
select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name order by 1
SQL> select * from table (dbms_xplan.display_cursor('bzd4sb9qvcmkf',1));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bzd4sb9qvcmkf, child number 1
-------------------------------------
select a.depart_id depart, b.department_name Depart_Name, count(*) from
emp a , department b where a.depart_id = b.depart_id group by
a.depart_id,b.department_name order by 1
Plan hash value: 1683054800
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | SORT GROUP BY | | 82 | 3526 | 6 (34)| 00:00:01 |
|* 2 | HASH JOIN | | 82 | 3526 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENT | 82 | 2460 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 82 | 1066 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPART_ID"="B"."DEPART_ID")
Note
-----
- SQL plan baseline SQL_PLAN_bbkzr9zjctcuze5d89d94 used for this statement
27 rows selected.
As per the above plan the sql base line SQL_PLAN_bbkzr9zjctcuze5d89d94 has been used. Its not using
the index which we have created.
SQL> select sql_handle, sql_text,plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where sql_text like '%b.department_name Depart_Name%';
SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC FIX
------------------------------ -------------------------------------------------------------------------------- ------------------------------ --- --- ---
SYS_SQL_b5cbf74fe2ccb35f select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , SQL_PLAN_bbkzr9zjctcuz635a35b2 YES NO NO
department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
ame
order by 1
SYS_SQL_b5cbf74fe2ccb35f select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , SQL_PLAN_bbkzr9zjctcuze5d89d94 YES YES NO
department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
ame
order by 1
From the base line view we could see that we have two plans but the new one is not selected, lets check the plan of both the sql handle.
SQL> select * from table (dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('SYS_SQL_b5cbf74fe2ccb35f'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_b5cbf74fe2ccb35f
SQL text: select a.depart_id depart, b.department_name Depart_Name, count(*) from
emp a , department b where a.depart_id = b.depart_id group by
a.depart_id,b.department_name order by 1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_bbkzr9zjctcuz635a35b2 Plan id: 1666856370
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 1965216693
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 3526 | 175 (2)| 00:00:03 |
| 1 | SORT GROUP BY | | 82 | 3526 | 175 (2)| 00:00:03 |
|* 2 | HASH JOIN | | 82 | 3526 | 174 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL | DEPARTMENT | 82 | 2460 | 2 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| DEPT_IDX | 82 | 1066 | 171 (1)| 00:00:03 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPART_ID"="B"."DEPART_ID")
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_bbkzr9zjctcuze5d89d94 Plan id: 3856178580
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 1683054800
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 3526 | 6 (34)| 00:00:01 |
| 1 | SORT GROUP BY | | 82 | 3526 | 6 (34)| 00:00:01 |
|* 2 | HASH JOIN | | 82 | 3526 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENT | 82 | 2460 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 82 | 1066 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPART_ID"="B"."DEPART_ID")
51 rows selected.
From the above we could see that the plan which uses the index is having less cpu cost
than the full table scan plan.
But still the optimizer uses the high cost plan since this is the plan which been
accepted.
Lets check the execution time.
SQL> set timing on;
SQL> select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name
order by 1;
DEPART DEPART_NAME COUNT(*)
---------- ------------------------------ ----------
100 Software 18826
101 Hardware 37311
102 HR 37225
103 Admin 37542
104 Transport 37416
105 Health 37558
106 Electricity 37244
107 Wealth 37249
108 Miscellanous 18627
9 rows selected.
Elapsed: 00:00:00.44
It is taking 44 milli seconds, Let have a look on the report which will provide us a clear picture on the
differernce between the new plan (which uses the index) and the base line plan (full table scan).
We can use the function evolve_sql_plan_baseline to generate this report.
Here we passing the below parameters:-
sql_handle
SQL statement identifier. Unless plan_name is specified, NULL means to consider all statements with non-accepted plans in their SQL plan baselines.
plan_name
Plan identifier. Default NULL means to consider all non accepted plans in the SQL plan baseline of either the identified SQL statement or all SQL statements if sql_handle is NULL.
verify
Specifies whether to execute the plans and compare the performance before changing non-accepted plans into accepted plans. A performance verification involves executing a non-accepted plan and a plan chosen from corresponding SQL plan baseline and comparing their performance statistics. If non-accepted plan shows performance improvement, it is changed to an accepted plan.
■ 'YES' (Default) - verifies that a non-accepted plan gives
better performance before changing it to an accepted plan
■ 'NO' - directs not to execute plans but only to change
non-accepted plans into accepted plans
commit
Specifies whether to update the ACCEPTED status of non-accepted plans from 'NO' to 'YES'.
■ 'YES' (Default) - perform updates of qualifying non-accepted plans and generate a report that shows the
updates and the result of performance verification when verify = 'YES'.
■ 'NO' - generate a report without any updates. Note that commit = 'NO' together with verify = 'NO' represents a no-op.
SQl> set long 10000;
SQL> select dbms_spm.EVOLVE_SQL_PLAN_BASELINE (
sql_handle => 'SYS_SQL_b5cbf74fe2ccb35f',
plan_name => 'SQL_PLAN_bbkzr9zjctcuz635a35b2',
verify => 'YES',
commit => 'NO')
from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_B5CBF74FE2CCB35F',PLAN_NA
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_b5cbf74fe2ccb35f
PLAN_NAME = SQL_PLAN_bbkzr9zjctcuz635a35b2
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = NO
Plan: SQL_PLAN_bbkzr9zjctcuz635a35b2
------------------------------------
Plan was verified: Time used 2.06 seconds.
Plan passed performance criterion: 2.93 times better than baseline plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 9 9
Elapsed Time(ms): 111.211 86.951 1.28
CPU Time(ms): 107.983 86.653 1.25
Buffer Gets: 1894 641 2.95
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 3510 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 0
###############################################################
From the above report, we came to know that the new plan which uses the index is much better than the baseline plan
Now we will accept the new plan.
SQL> select dbms_spm.EVOLVE_SQL_PLAN_BASELINE (
sql_handle => 'SYS_SQL_b5cbf74fe2ccb35f',
plan_name => 'SQL_PLAN_bbkzr9zjctcuz635a35b2',
verify => 'YES',
commit => 'YES')
from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_B5CBF74FE2CCB35F',PLAN_NA
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_b5cbf74fe2ccb35f
PLAN_NAME = SQL_PLAN_bbkzr9zjctcuz635a35b2
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_bbkzr9zjctcuz635a35b2
------------------------------------
Plan was verified: Time used 2.15 seconds.
Plan passed performance criterion: 2.93 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows Processed: 9 9
Elapsed Time(ms): 170.138 87.028 1.95
CPU Time(ms): 121.481 86.764 1.4
Buffer Gets: 1894 641 2.95
Physical Read Requests: 27 0
Physical Write Requests: 0 0
Physical Read Bytes: 2940928 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
####
Now we have accepted the plan.
SQL> select sql_handle, sql_text,plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where sql_text like '%b.department_name Depart_Name%';
SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC FIX
------------------------------ -------------------------------------------------------------------------------- ------------------------------ --- --- ---
SYS_SQL_b5cbf74fe2ccb35f select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , SQL_PLAN_bbkzr9zjctcuz635a35b2 YES YES NO
department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
ame
order by 1
SYS_SQL_b5cbf74fe2ccb35f select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , SQL_PLAN_bbkzr9zjctcuze5d89d94 YES YES NO
department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
ame
order by 1
Now the plan has been accepted. Let run the query now.
SQL> select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name order by 1;
DEPART DEPART_NAME COUNT(*)
---------- ------------------------------ ----------
100 Software 18826
101 Hardware 37311
102 HR 37225
103 Admin 37542
104 Transport 37416
105 Health 37558
106 Electricity 37244
107 Wealth 37249
108 Miscellanous 18627
9 rows selected.
Elapsed: 00:00:00.11
We could see the considerable performance improvement on the query.
SQL> select sql_id, child_number, sql_text from V$sql where sql_text like '%b.department_name Depart_Name%';
SQL_ID CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
bzd4sb9qvcmkf 0
select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a ,
department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
ame order by 1
SQL> select * from table (dbms_xplan.display_cursor('bzd4sb9qvcmkf',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bzd4sb9qvcmkf, child number 0
-------------------------------------
select a.depart_id depart, b.department_name Depart_Name, count(*) from
emp a , department b where a.depart_id = b.depart_id group by
a.depart_id,b.department_name order by 1
Plan hash value: 1965216693
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 184 (100)| |
| 1 | SORT GROUP BY | | 58 | 928 | 184 (6)| 00:00:03 |
|* 2 | HASH JOIN | | 298K| 4671K| 176 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL | DEPARTMENT | 9 | 108 | 3 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| DEPT_IDX | 298K| 1167K| 171 (1)| 00:00:03 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPART_ID"="B"."DEPART_ID")
Note
-----
- SQL plan baseline SQL_PLAN_bbkzr9zjctcuz635a35b2 used for this statement
27 rows selected.
Altering sql baselines:-
=======================
The funtion ALTER_SQL_PLAN_BASELINE is used to change the attribute of a single plan or all
plans associated with the sql statment.
Here lets make the plan fixed.
SQL> set serveroutput on;
SQL> declare
kk pls_integer;
begin
kk := dbms_spm.alter_sql_plan_baseline
(
sql_handle => 'SYS_SQL_b5cbf74fe2ccb35f',
plan_name => 'SQL_PLAN_bbkzr9zjctcuz635a35b2',
attribute_name => 'fixed',
attribute_value => 'YES'
);
dbms_output.put_line ('No of plans altered :' || kk);
end;
/
No of plans altered :1
PL/SQL procedure successfully completed.
SQL> select sql_handle, sql_text,plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where sql_text like '%b.department_name Depart_Name%';
SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC FIX
------------------------------ -------------------------------------------------------------------------------- ------------------------------ --- --- ---
SYS_SQL_b5cbf74fe2ccb35f select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , SQL_PLAN_bbkzr9zjctcuz635a35b2 YES YES YES
department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
ame
order by 1
SYS_SQL_b5cbf74fe2ccb35f select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , SQL_PLAN_bbkzr9zjctcuze5d89d94 YES YES NO
department b where a.depart_id = b.depart_id group by a.depart_id,b.department_n
ame
order by 1
We have made the plan which uses the index as the fixed plan, if we drop the index now then it will use
plan with full table scan.
SQL> drop index dept_idx;
Index dropped.
Now execute the same query.
SQL>select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name
order by 1;
DEPART DEPART_NAME COUNT(*)
---------- ------------------------------ ----------
100 Software 18826
101 Hardware 37311
102 HR 37225
103 Admin 37542
104 Transport 37416
105 Health 37558
106 Electricity 37244
107 Wealth 37249
108 Miscellanous 18627
9 rows selected.
Check for the sql_id and the child number in V$SQL.
select sql_id, child_number, sql_text from V$sql where sql_text like '%b.department_name Depart_Name%';
SQL_ID CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
bzd4sb9qvcmkf 1
select a.depart_id depart, b.department_name Depart_Name, count(*) from emp a , department b where a.depart_id = b.depart_id group by a.depart_id,b.department_name order by 1
View the Explain plan.
SQL>select * from table (dbms_xplan.display_cursor('bzd4sb9qvcmkf',1));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bzd4sb9qvcmkf, child number 1
-------------------------------------
select a.depart_id depart, b.department_name Depart_Name, count(*) from
emp a , department b where a.depart_id = b.depart_id group by
a.depart_id,b.department_name order by 1
Plan hash value: 1683054800
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 526 (100)|
| 1 | SORT GROUP BY | | 58 | 928 | 526 (3)| 00:00:07
|* 2 | HASH JOIN | | 298K| 4671K| 518 (1)| 00:00:07
| 3 | TABLE ACCESS FULL| DEPARTMENT | 9 | 108 | 3 (0)| 00:00:01
| 4 | TABLE ACCESS FULL| EMP | 298K| 1167K| 513 (1)| 00:00:07
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."DEPART_ID"="B"."DEPART_ID")
Note
-----
- SQL plan baseline SQL_PLAN_bbkzr9zjctcuze5d89d94 used for this statement
27 rows selected.
We can see that the sql plan baseline SQL_PLAN_bbkzr9zjctcuze5d89d94 has been used for this statement.
Creating Sql plan baseline using the group of sqls from the AWR.
-------------------------------------------------------------------------------
This can be achvied by creating SQL tuning sets (STS) and loading STS to the baseline.
1) Create STS
SQL> begin
dbms_sqltune.create_sqlset
(
sqlset_name => 'manzoor_schema_queries',
description => 'SQL set containing Manzoor schema queries'
);
end;
/
PL/SQL procedure successfully completed.
2) Fetch the SQL from the AWR and load it to the STS. In this example
we are fetching the queries which are executed by Manzoor schema and loading it
to the STS.
SQL> declare
cur dbms_sqltune.sqlset_cursor;
begin
open cur for select value(p) from table
(
dbms_sqltune.SELECT_WORKLOAD_REPOSITORY
(
begin_snap => 119,
end_snap => 167,
basic_filter => 'parsing_schema_name = ''MANZOOR''')
) p;
dbms_sqltune.load_sqlset
(
sqlset_name => 'manzoor_schema_queries',
populate_cursor => cur
);
end;
/
PL/SQL procedure successfully completed.
3) View the Contents of the STS.
select * from table (dbms_sqltune.select_sqlset('manzoor_schema_queries'));
or
SQL> select sql_id from table (dbms_sqltune.select_sqlset('manzoor_schema_queries'));
SQL_ID
-------------
2f8g6twkxb9kk
2t7vq1zz3vxs0
3s1hh8cvfan6w
4bwrxw78c3mkt
4skyphxhrysfd
5suzhbyd00h6y
5xkzm5zygmmy2
6nnntb2b3gr0j
7hys3h7ysgf9m
96gu8dckuy91w
99qa3zyarxvms
a03rbpj8vf87r
a7hv3uyyaqhpf
acstsbcy1t89n
acstsbcy1t89n
acstsbcy1t89n
arkb04w4cm40x
bbcmhs3ygxqph
bsvpwnht1m9bm
cpm405mcw3bkj
cy33kh23mvvqd
fbgavzya57gb1
fbgavzya57gb1
fg4skgcja2cyj
fxw7k56bs4xjq
g3f3cw3zy5aat
g59vz2u4cu404
4) Create baseline using the STS.
SQL> declare
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'manzoor_schema_queries');
end;
/
5) View the baseline.
SQL> select sql_handle, sql_text , enabled, accepted from dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT ENA ACC
---------------------------------------- -------------------------------------------------------------------------------- --- ---
SQL_24a5515c01211454 select count(*) from emp YES YES
SQL_1278d3e5ddb954cd SELECT PLAN_TABLE_OUTPUT FROM TABLE(CAST(DBMS_XPLAN.PREPARE_RECORDS(:B1 , :B2 ) YES YES
SQL_9be38e7cab117c72 SELECT EXTRACTVALUE(VALUE(D), '/row/@op'), EXTRACTVALUE(VALUE(D), '/row/@dis'), YES YES
SQL_4b1157e1544bc5a0 select sum(sal) from emp where empno between :a and :b YES YES
SQL_4b1157e1544bc5a0 select sum(sal) from emp where empno between :a and :b YES YES
SQL_ad5456be0f0aac5f update emp set ORA_ARCHIVE_STATE = 1 where join_date < to_date ('01-JAN-2011','D YES YES
SQL_9ed6c64b78007214 select table_name from user_tables YES YES
SQL_294c437e331fa51f SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1)) YES YES
SQL_b0605bfc6cefbcfb insert /* + append */ into emp select * from emp YES YES
SQL_2fabb0ed611c90e0 delete from emp YES YES
SQL_823bf3a9309ffdd0 select * from dba_tab_privs where grantee = 'AHAMED' YES YES
SQL_823bf3a9309ffdd0 select * from dba_tab_privs where grantee = 'AHAMED' YES YES
SQL_823bf3a9309ffdd0 select * from dba_tab_privs where grantee = 'AHAMED' YES YES
SQL_eaa2be30102d9d49 SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ extractvalue(xmlv YES YES
SQL_30a83b4102b41c40 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB YES YES
SQL_e838970c4838b3ba select a.ename, b.dept_name from emp a, dept b where a.deptno = b.dept_id and a. YES YES
SQL_bff897b9dbcabe27 SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P YES YES
SQL_9f1cc00482584a97 create table emp as YES YES
SQL_ee5069d793e00152 select * from table (dbms_xplan.display) YES YES
SQL> select owner,name, STATEMENT_COUNT from DBA_SQLSET;
OWNER NAME STATEMENT_COUNT
---------- ---------------------------------------- ---------------
SYS manzoor_schema_queries 24
SQL> select SQL_ID,SQL_TEXT from DBA_SQLSET_STATEMENTS;
SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------
g59vz2u4cu404 select count(*) from emp
g3f3cw3zy5aat SELECT PLAN_TABLE_OUTPUT FROM TABLE(CAST(DBMS_XPLAN.PREPARE_RECORDS(:B1 , :B2 )
fxw7k56bs4xjq BEGIN dbms_stats.gather_table_stats('MANZOOR','EMP'); END;
fg4skgcja2cyj SELECT EXTRACTVALUE(VALUE(D), '/row/@op'), EXTRACTVALUE(VALUE(D), '/row/@dis'),
fbgavzya57gb1 select sum(sal) from emp where empno between :a and :b
fbgavzya57gb1 select sum(sal) from emp where empno between :a and :b
cy33kh23mvvqd update emp set ORA_ARCHIVE_STATE = 1 where join_date < to_date ('01-JAN-2011','D
cpm405mcw3bkj select table_name from user_tables
bsvpwnht1m9bm SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))
bbcmhs3ygxqph insert /* + append */ into emp select * from emp
arkb04w4cm40x delete from emp
acstsbcy1t89n select * from dba_tab_privs where grantee = 'AHAMED'
acstsbcy1t89n select * from dba_tab_privs where grantee = 'AHAMED'
acstsbcy1t89n select * from dba_tab_privs where grantee = 'AHAMED'
a7hv3uyyaqhpf SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ extractvalue(xmlv
a03rbpj8vf87r SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB
99qa3zyarxvms insert into plan_table (statement_id, timestamp, operation, options,object_node,
96gu8dckuy91w select a.ename, b.dept_name from emp a, dept b where a.deptno = b.dept_id and a.
7hys3h7ysgf9m SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P
6nnntb2b3gr0j INSERT INTO EMP VALUES (:B1 , DBMS_RANDOM.STRING('U',30))
5xkzm5zygmmy2 create table emp as
5suzhbyd00h6y begin
for i in 1..50000 loop
insert into emp values (i, dbms_random.string('U',1
4skyphxhrysfd EXPLAIN PLAN SET STATEMENT_ID='PLUS1415267676' FOR select * from emp where emp_i
4bwrxw78c3mkt INSERT INTO EMP VALUES (:B1 , DBMS_RANDOM.STRING('U',10), 'SOFTWARE',5000,SYSDAT
3s1hh8cvfan6w SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))
2t7vq1zz3vxs0 begin
for i in 1..50000 loop
insert into emp values (i, dbms_random.stri
SQL Management Base
The SQL management base resides in the SYSAUX tablespace and stores SQL plan baselines, as well as statement logs,
plan histories and SQL profiles. Space usage is controlled by altering two name-value attributes using the CONFIGURE
procedure of the DBMS_SPM package.
space_budget_percent (default 10) : Maximum size as a percentage of SYSAUX space. Allowable values 1-50.
plan_retention_weeks (default 53) : Number of weeks unused plans are retained before being purged. Allowable values 5-523 weeks.
The current settings are visible using the DBA_SQL_MANAGEMENT_CONFIG view.
SELECT parameter_name, parameter_value
FROM dba_sql_management_config;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
2 rows selected.
SQL>
The following example shows both values being reset.
BEGIN
DBMS_SPM.configure('space_budget_percent', 11);
DBMS_SPM.configure('plan_retention_weeks', 54);
END;
/
SELECT parameter_name, parameter_value
FROM dba_sql_management_config;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 11
PLAN_RETENTION_WEEKS 54
2 rows selected.
SQL>
Others function/procedures in dbms_spm.
CREATE_STGTAB_BASELINE
Creates a staging table that used for transporting SQL plan
baselines from one system to another
DROP_SQL_PLAN_BASELINE
drops a single plan, or all plans associated with a SQL statement
LOAD_PLANS_FROM_SQLSET Function
Loads plans stored in a SQL tuning set (STS) into SQL plan
baselines
MIGRATE_STORED_OUTLINE Functions
Migrates existing stored outlines to SQL plan baselines
PACK_STGTAB_BASELINE Function
Packs (exports) SQL plan baselines from SQL management base
into a staging table
UNPACK_STGTAB_BASELINE Function
Unpacks (imports) SQL plan baselines from a staging table into
SQL management base
No comments:
Post a Comment