Step by step test Sql Plan Management

–Test Version: 11.1.0.6
–Plan history is maintained only for repeatable SQL statements.A SQL statement is recognized as repeatable when it is parsed or executed again after it has been logged.

–How to turn on Automatic Plan Capture
Alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE

–Manual Plan Loading
–Loading Plans from SQL Tuning Sets
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => ‘tset1′);
END;
/
–you can load plans from AWR to Sql tuning Set, then load it to SPM

–Manual load from cursor cache
–run a test sql
T@ORCL11G>select count(*) from t;

COUNT(*)
———-
1101840

SYS@orcl11g>select sid,serial# from v$session where username=’T';

SID    SERIAL#
———- ———-
87          7

SYS@orcl11g>select sql_id from v$session where sid=87;

SQL_ID
————-
cyzznbykb509s

–manual load the sql to base line from cursor cache
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => ‘cyzznbykb509s’);
END;
/

–get sql_handle
SYS@orcl11g>select sql_handle,sql_text from DBA_SQL_PLAN_BASELINES;

SQL_HANDLE                     SQL_TEXT
—————————— ————————————
SYS_SQL_793213869456f9be       select count(*) from t

–Evolving Plans
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => ‘SYS_SQL_793213869456f9be’);
DBMS_OUTPUT.PUT_LINE(report);
END;
/
———————————————————
Evolve SQL Plan Baseline Report
———————————————————

Inputs:
——-
SQL_HANDLE =
SYS_SQL_793213869456f9be
PLAN_NAME  =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY     = YES
COMMIT     =
YES

———————————————————
Report
Summary
———————————————————
There were no SQL plan baselines that
required processing.

PL/SQL procedure successfully completed.

–Displaying SQL Plan Baselines
select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>’SYS_SQL_793213869456f9be’,
format=>’basic’));

PLAN_TABLE_OUTPUT
———————————————————————
SQL handle: SYS_SQL_793213869456f9be
SQL text: select count(*) from t
———————————————————————
Plan name: SYS_SQL_PLAN_9456f9be3fdbb376
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
———————————————————————
Plan hash value: 2966233522
———————————–
| Id  | Operation          | Name |
———————————–
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
———————————–

20 rows selected.

–modify table and add index
T@ORCL11G>alter table t modify object_name not null;
Table altered.

T@ORCL11G>create index idx_obj_name on t(object_name);
Index created.

T@ORCL11G>select count(*) from t;
COUNT(*)
———-
1101840

SYS@orcl11g>select sql_id from v$session where sid=87;
SQL_ID
————-
cyzznbykb509s

–load the sql plan from cursor cache again
SYS@orcl11g>DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => ‘cyzznbykb509s’);
END;
/
–When you manually load plans into a SQL plan baseline, these loaded plans are added as accepted plans.

–Evolving the sql plan again
SYS@orcl11g>DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => ‘SYS_SQL_793213869456f9be’);
DBMS_OUTPUT.PUT_LINE(report);
END;
/
————————————————————————-
Evolve SQL Plan Baseline Report
————————————————————————-
Inputs:
——-
SQL_HANDLE =
SYS_SQL_793213869456f9be
PLAN_NAME  =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY     = YES
COMMIT     = YES

Plan:
SYS_SQL_PLAN_9456f9be3e1ca9ac
———————————–
Plan was verified: Time used 53.006 seconds.
Passed
performance criterion: Compound improvement ratio >= 2.96.
Plan was changed to an accepted plan.

Baseline Plan      Test Plan     Improv. Ratio
————-      ———     ————-
Execution Status:        COMPLETE       COMPLETE
Rows Processed:                 1              1
Elapsed Time(ms):           30428           5475                            5.56
CPU Time(ms):                 290            130              2.23
Buffer Gets:                16228           5477                            2.96
Disk Reads:                 16217           5463              2.97
Direct Writes:                  0              0

Fetches:                      238                101              2.36
Executions:                     1                            1

————————————————————————-
Report Summary
————————————————————————-
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
PL/SQL procedure successfully completed.

–The new plan was accepted
–check SQL Plan Baselines again
SYS@orcl11g>select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>’SYS_SQL_793213869456f9be’,
format=>’basic’));
PLAN_TABLE_OUTPUT
———————————————————————–
SQL handle: SYS_SQL_793213869456f9be
SQL text: select count(*) from t
———————————————————————–
Plan name: SYS_SQL_PLAN_9456f9be3e1ca9ac
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE  –Don’t understand why it is auto!?
———————————————————————–
Plan hash value: 1628650996
———————————————-
| Id  | Operation             | Name         |
———————————————-
|   0 | SELECT STATEMENT      |              |
|   1 |  SORT AGGREGATE       |              |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJ_NAME | –the new plan is added
———————————————-

———————————————————————–
Plan name: SYS_SQL_PLAN_9456f9be3fdbb376
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
———————————————————————–
Plan hash value: 2966233522
———————————–
| Id  | Operation          | Name |
———————————–
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
———————————–

35 rows selected.

–let’s fix the new plan SYS_SQL_PLAN_9456f9be3e1ca9ac
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle  => NULL,
plan_name   => ‘SYS_SQL_PLAN_9456f9be3e1ca9ac’,
attribute_name => ‘fixed’,
attribute_value => ‘YES’);
END;
/

–let’s check the plan again
SYS@orcl11g>select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>’SYS_SQL_793213869456f9be’,
format=>’basic’));
PLAN_TABLE_OUTPUT
———————————————————————-
SQL handle: SYS_SQL_793213869456f9be
SQL text: select count(*) from t
———————————————————————-
Plan name: SYS_SQL_PLAN_9456f9be3e1ca9ac
Enabled: YES     Fixed: YES Accepted: YES     Origin: AUTO-CAPTURE
———————————————————————-
Plan hash value: 1628650996  –It is now fixed “Fixed: YES”

———————————————-
| Id  | Operation             | Name         |
———————————————-
|   0 | SELECT STATEMENT      |              |
|   1 |  SORT AGGREGATE       |              |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJ_NAME |
———————————————-

———————————————————————-
Plan name: SYS_SQL_PLAN_9456f9be3fdbb376
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
———————————————————————-

Plan hash value: 2966233522

———————————–
| Id  | Operation          | Name |
———————————–
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
———————————–

35 rows selected.

–A fixed plan takes precedence over a non-fixed plan.Oracle will use the fixed plan whenever possible
–A SQL plan baseline is fixed if it contains at least one enabled plan whose FIXED attribute is set to YES.

–clean this sql baseline
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle => ‘SYS_SQL_793213869456f9be’);
END;
/

T@ORCL11G>select sql_handle,sql_text from DBA_SQL_PLAN_BASELINES;
no rows selected

–Other related configurations
–Disk Space Usage in SYSAUX
–
–To change the percentage limit, use the CONFIGURE procedure of the DBMS_SPM package:
BEGIN
DBMS_SPM.CONFIGURE(
’space_budget_percent’,30);  –default 10% SYSAUX, range 1% to 50%
END;
/

–Purging Policy
BEGIN
DBMS_SPM.CONFIGURE(
‘plan_retention_weeks’,105);  –default 53 weeks, range 5 to 523
END;
/

–get the configuration from view dba_sql_management_config
select parameter_name, parameter_value from dba_sql_management_config;
PARAMETER_NAME                 PARAMETER_VALUE
—————————— —————
SPACE_BUDGET_PERCENT                        30
PLAN_RETENTION_WEEKS                       105

–
–Importing and Exporting SQL Plan Baselines
–
1.On the original system, create a staging table using the CREATE_STGTAB_BASELINE procedure:
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => ’stage1′);
END;
/

2.pack the needed baselines
DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => ’stage1′,
enabled => ‘yes’,
creator => ‘dba1′);
END;
/

3.Export the staging table stage1 into a flat file using the export command or Oracle Data Pump.
4.Transfer the flat file to the target system.
5.Import the staging table stage1 from the flat file using the import command or Oracle Data Pump.
6.Unpack the SQL plan baselines from the staging table into the SQL management base on the target system using the UNPACK_STGTAB_BASELINE function:
DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE(
table_name => ’stage1′,
fixed => ‘yes’);
END;
/