Oracle 12c provides the DBMS_SPM package for managing plan evolution. This article demonstrates the creation of SQL Plan using the DBMS_SPM package.
- Logon to you database server as the oracle software owner and connect to SQLPLUS as sysdba.
[root@linux2 ~]# su – oracle
Last login: Tue Dec 27 07:53:43 EST 2016 on pts/2
Enter database to use:
1 – ORCL
2 – CDB1
Option >
1
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 28 11:06:31 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
- Create test table for SPM test and populate with values.
create table test_spm(val_1 number, val_2 char(1));
insert into test_spm
select level, ‘a’ from dual connect by level <=100;
SQL> create table test_spm(val_1 number, val_2 char(1));
Table created.
SQL> insert into test_spm
2 select level, ‘a’ from dual connect by level <=100;
100 rows created.
SQL> commit;
Commit complete.
- Get the SQL PLAN name by setting the AUTOTRACE to TRACE and executing query against test table. SQL PLAN NAME in this example is SQL_PLAN_891rxd509mzp3eb1890ae.
SET AUTOTRACE TRACE
select * from test_spm;
SET AUTOTRACE TRACE
select * from test_spm;SQL> SQL>
100 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1145642998
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 100 | 500 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_SPM | 100 | 500 | 2 (0)| 00:00:01 |
——————————————————————————
Note
—–
– dynamic statistics used: dynamic sampling (level=AUTO)
– SQL plan baseline “SQL_PLAN_891rxd509mzp3eb1890ae” used for this statement
Statistics
———————————————————-
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
2816 bytes sent via SQL*Net to client
617 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
SQL>
- Set autotrace off.
set autotrace off;
SQL> set autotrace off;
- Get the SQL_HANDLE from PLAN_NAME out of the dba_sql_plan_baselines table.
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE plan_name = ‘SQL_PLAN_891rxd509mzp3eb1890ae’;
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE plan_name = ‘SQL_PLAN_891rxd509mzp3eb1890ae’;SQL> SQL> 2 3
SQL_HANDLE PLAN_NAME ENA ACC
——————– —————————— — —
SQL_8486fd694099fea3 SQL_PLAN_891rxd509mzp3eb1890ae YES YES
SQL>
- Create an evolve task using the function CREATE_EVOLVE_TASK function with SQL_HANDLE from above.
SET SERVEROUTPUT ON
DECLARE
l_return VARCHAR2(32767);
BEGIN
l_return := DBMS_SPM.create_evolve_task(sql_handle => ‘SQL_8486fd694099fea3’);
DBMS_OUTPUT.put_line(‘Task Name: ‘ || l_return);
END;
/
SQL> SET SERVEROUTPUT ON
DECLARE
l_return VARCHAR2(32767);
BEGIN
l_return := DBMS_SPM.create_evolve_task(sql_handle => ‘SQL_8486fd694099fea3’);
DBMS_OUTPUT.put_line(‘Task Name: ‘ || l_return);
END;
/SQL> 2 3 4 5 6 7
Task Name: TASK_1049
PL/SQL procedure successfully completed.
SQL>
- Execute the evolve task using the DBMS_SPM.execute_evolve_task function and the TASK_NAME from above.
SET SERVEROUTPUT ON
DECLARE
l_return VARCHAR2(32767);
BEGIN
l_return := DBMS_SPM.execute_evolve_task(task_name => ‘TASK_1049’);
DBMS_OUTPUT.put_line(‘Execution Name: ‘ || l_return);
END;
/
SQL> SET SERVEROUTPUT ON
DECLARE
l_return VARCHAR2(32767);
BEGIN
l_return := DBMS_SPM.execute_evolve_task(task_name => ‘TASK_1049’);
DBMS_OUTPUT.put_line(‘Execution Name: ‘ || l_return);
END;
/SQL> 2 3 4 5 6 7
Execution Name: EXEC_1082
PL/SQL procedure successfully completed.
SQL>
- Report on the task outcome using the DBMS_SPM.report_evolve_task function and the TASK_NAME and EXECUTION_NAME from above.
SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100
SELECT DBMS_SPM.report_evolve_task(task_name => ‘TASK_1049’, execution_name => ‘EXEC_1082’) AS output
FROM dual;
SQL>
SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100
SELECT DBMS_SPM.report_evolve_task(task_name => ‘TASK_1049’, execution_name => ‘EXEC_1082’) AS output
FROM dual;SQL> SQL> SQL> 2
OUTPUT
—————————————————————————————————-
GENERAL INFORMATION SECTION
———————————————————————————————
Task Information:
———————————————
Task Name : TASK_1049
Task Owner : SYS
Execution Name : EXEC_1082
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 12/28/2016 15:07:14
Finished : 12/28/2016 15:07:14
Last Updated : 12/28/2016 15:07:14
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
———————————————————————————————
SUMMARY SECTION
———————————————————————————————
Number of plans processed : 0
Number of findings : 0
Number of recommendations : 0
Number of errors : 0
———————————————————————————————
SQL>
- Implement the recommendations in the task with the DBMS_SPM.implement_evolve_task function and the TASK_NAME from above.
SET SERVEROUTPUT ON
DECLARE
l_return NUMBER;
BEGIN
l_return := DBMS_SPM.implement_evolve_task(task_name => ‘TASK_1049’);
DBMS_OUTPUT.put_line(‘Plans Accepted: ‘ || l_return);
END;
/
SQL>
SET SERVEROUTPUT ON
DECLARE
l_return NUMBER;
BEGIN
l_return := DBMS_SPM.implement_evolve_task(task_name => ‘TASK_1049’);
DBMS_OUTPUT.put_line(‘Plans Accepted: ‘ || l_return);
END;
/SQL> SQL> 2 3 4 5 6 7
Plans Accepted: 0
PL/SQL procedure successfully completed.
SQL>
- Verify the change by querying the DBA_SQL_PLAN_BASELINES table witht eh SQL_HANDLE from above.
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_handle = ‘SQL_8486fd694099fea3’;
SQL> SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_handle = ‘SQL_8486fd694099fea3’;
2 3
SQL_HANDLE PLAN_NAME ENA ACC
——————– —————————— — —
SQL_8486fd694099fea3 SQL_PLAN_891rxd509mzp3eb1890ae YES YES
SQL>
- This complets use of DBMS_SPM for creating SQL plan.
Larry Catt
OCP