SQL Plan Management (SPM) Evolve Advisor in Oracle 12c analyzes SQL plans which were not used during executions to determine if they would run better than accepted plans. If the plan is determined to run better than the accepted plan it will be accepted, more than one plan can be accepted for give SQL statement. This evaluation is executed during maintenance window when automatic SQL tuning is set to COMPREHENSIVE mode. The evaluation can be run manually with DBMS_SPM package, SPM Evolve Advisor or the Automatic SQL Tuning advisor. This can be set in EM or through DBMS_AUTO_TASK_ADMIN package. This procedure show the setup of SPM.
- 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>
- Execute the following block:
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE ( client_name => ‘sql tuning advisor’,
operation => NULL,
window_name => NULL );
END;
/
SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE ( client_name => ‘sql tuning advisor’,
operation => NULL,
window_name => NULL );
END;
/
2 3 4 5 6
PL/SQL procedure successfully completed.
SQL>
- Query dictionary to confirm:
SELECT client_name, status
FROM dba_autotask_client
WHERE client_name = ‘sql tuning advisor’;
CLIENT_NAME STATUS
——————– ——–
sql tuning advisor ENABLED
- Disable with DBMS_AUTO_TASK_ADMIN.DISABLE procedure.
- This completes overview of Adaptive SQL PLAN Management.
Larry Catt
OCP