We had just ended the 7th conference of All India Oracle Users Group (AIOUG) North India Chapter, There were great topics given by great speakers like Exadata & Smart Scan from Oracle ACE Aman Sharma, 12C EM Cloud control from deepak. It was a great event with great audience showing up on the day, this event happens last saturday on every month in North India. I also got a chance as a Speaker to present on Oracle Histograms. The event this time took place in Mohali, India. I would like to thank everyone who came to the event and shared their Oracle Knowledge through this event. More information about AIOUG NIC can be found on the facebook page https://www.facebook.com/AIOUG.NIC .
Tuesday, 30 September 2014
Wednesday, 10 September 2014
Oracle Management Server (OMS) startup in 12C
As 12C OEM Cloud control uses weblogic so the following has to be started whenever you want OMS to be up and running, sometimes we get error that AdminServer Could Not Be Started, so in that case we have to make sure EMGC_ADMINSERVER is up and running, this error comes when the lock files are not cleared properly, so we have to shutdown oms and clean up the files and then restart them all over again and then we will get to see the following up and running. The lock files to be cleared are in the following location
[oracle@node4 ~]$ cd /u01/app/oracle/product/gc_inst/user_projects/domains/GCDomain/servers
[oracle@node4 servers]$ ls -ltr
total 16
drwxr-x--- 10 oracle oinstall 4096 Sep 9 20:09 EMGC_ADMINSERVER
drwxr-x--- 3 oracle oinstall 4096 Sep 9 20:23 domain_bak
drwxr-x--- 10 oracle oinstall 4096 Sep 9 20:31 EMGC_OMS1
-rw-r----- 1 oracle oinstall 14 Sep 10 16:33 AdminServerTag
[oracle@node4 bin]$ ps -ef | grep EMGC_ADMINSERVER
oracle 11989 11944 5 16:33 ? 00:02:48 /u01/app/oracle/product/middleware/jdk16/jdk/bin/java -server -Xms256m -Xmx512m -XX:MaxPermSize=512m -Dweblogic.Name=EMGC_ADMINSERVER -Djava.security.policy=/u01/app/oracle/product/middleware/wlserver_10.3/server/lib/weblogic.policy -Dweblogic.ProductionModeEnabled=true -Dweblogic.system.BootIdentityFile=/u01/app/oracle/product/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/security/boot.properties -Dweblogic.nodemanager.ServiceEnabled=true -Djava.security.egd=file:///dev/./urandom -Dweblogic.debug.DebugWebAppSecurity=true -Dweblogic.SSL.LoginTimeoutMillis=300000 -Djps.auth.debug=true -Djps.authz=ACC -Djps.combiner.optimize.lazyeval=true -Djps.combiner.optimize=true -Djps.policystore.hybrid.mode=false -Djps.subject.cache.key=5 -Djps.subject.cache.ttl=600000 -Doracle.apm.home=/u01/app/oracle/product/middleware/oms/apm/ -DAPM_HELP_FILENAME=oesohwconfig.xml -da -Dplatform.home=/u01/app/oracle/product/middleware/wlserver_10.3 -Dwls.home=/u01/app/oracle/product/middleware/wlserver_10.3/server -Dweblogic.home=/u01/app/oracle/product/middleware/wlserver_10.3/server -Dcommon.components.home=/u01/app/oracle/product/middleware/oracle_common -Djrf.version=11.1.1 -Dorg.apache.commons.logging.Log=org.apache.commons.logging.impl.Jdk14Logger -Ddomain.home=/u01/app/oracle/product/gc_inst/user_projects/domains/GCDomain -Djrockit.optfile=/u01/app/oracle/product/middleware/oracle_common/modules/oracle.jrf_11.1.1/jrocket_optfile.txt -Doracle.server.config.dir=/u01/app/oracle/product/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/servers/EMGC_ADMINSERVER -Doracle.domain.config.dir=/u01/app/oracle/product/gc_inst/user_projects/domains/GCDomain/config/fmwconfig -Digf.arisidbeans.carmlloc=/u01/app/oracle/product/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/carml -Digf.arisidstack.home=/u01/app/oracle/product/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/arisidprovider -Doracle.security.jps.config=/u01/app/oracle/product/gc_inst/user_projects/domains/GCDomain/config/fmwconfig/jps-config.xml -Doracle.deployed.app.dir=/u01/app/oracle/product/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/tmp/_WL_user -Doracle.deployed.app.ext=/- -Dweblogic.alternateTypesDirectory=/u01/app/oracle/product/middleware/oracle_common/modules/oracle.ossoiap_11.1.1,/u01/app/oracle/product/middleware/oracle_common/modules/oracle.oamprovider_11.1.1,/u01/app/oracle/product/middleware/oracle_common/modules/oracle.jps_11.1.1,/u01/app/oracle/product/middleware/oms/sysman/jlib -Djava.protocol.handler.pkgs=oracle.mds.net.protocol -Dweblogic.jdbc.remoteEnabled=false -Doracle.apm.home=/u01/app/oracle/product/middleware/oms/apm/ -DAPM_HELP_FILENAME=oesohwconfig.xml -Dweblogic.management.discover=true -Dwlw.iterativeDev=false -Dwlw.testConsole=false -Dwlw.logErrorsToConsole=false -Dweblogic.ext.dirs=/u01/app/oracle/product/middleware/patch_wls1036/profiles/default/sysext_manifest_classpath weblogic.Server
How to start the Oracle Management Server in 12C with weblogic
I had just Started using Release 4 of Oracle Enterprise Manager Cloud Control 12c
Starting up my web tier and 12C Oracle Management Server for cloud control
[oracle@node4 ~]$ cd $OMS_HOME
[oracle@node4 oms]$ pwd
/u01/app/oracle/product/middleware/oms
[oracle@node4 oms]$ cd bin
[oracle@node4 bin]$ ./emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down
[oracle@node4 bin]$ ./emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
Click the below picture to get the closer look
---------------------------------------------------------------------------
To shutdown the OMS do the following :--
[oracle@node4 ~]$ cd /u01/app/oracle/product/middleware/oms
[oracle@node4 oms]$ cd bin
[oracle@node4 bin]$ ./emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down
12C Data guard Broker ORA-01017: invalid username/password; logon denied
## While doing switchover and failover with 12C Broker i kept on investigating for a long time why invalid username password error is coming even though the password and password file is same for primary and standby, i later realized that the password was not complex and that was the reason i was getting this error. Let us see now for a switchover and failover example.
DGMGRL> switchover to prd ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prd" on database "prd"
Connecting to instance "prd"...
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
connect to instance "prd" of database "prd"
DGMGRL> switchover to prd ;
not logged on
DGMGRL> connect sys@drs
Password:
ORA-01017: invalid username/password; logon denied
## Now i made sure my password is complex
DGMGRL> connect sys@drs
Password:
Connected as SYSDBA.
DGMGRL> switchover to prd ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prd" on database "prd"
Connecting to instance "prd"...
Connected as SYSDBA.
New primary database "prd" is opening...
Operation requires startup of instance "drs" on database "drs"
Starting instance "drs"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prd"
DGMGRL> show configuration
Configuration - myconfig1
Protection Mode: MaxPerformance
Databases:
prd - Primary database
drs - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to prd ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prd" on database "prd"
Connecting to instance "prd"...
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
connect to instance "prd" of database "prd"
DGMGRL> switchover to prd ;
not logged on
DGMGRL> connect sys@drs
Password:
ORA-01017: invalid username/password; logon denied
## Now i made sure my password is complex
DGMGRL> connect sys@drs
Password:
Connected as SYSDBA.
DGMGRL> switchover to prd ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "prd" on database "prd"
Connecting to instance "prd"...
Connected as SYSDBA.
New primary database "prd" is opening...
Operation requires startup of instance "drs" on database "drs"
Starting instance "drs"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prd"
DGMGRL> show configuration
Configuration - myconfig1
Protection Mode: MaxPerformance
Databases:
prd - Primary database
drs - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
RMAN-05609: Must specify a username for target connection when using active duplicate
While i was setting up my 12C Standby database i faced an error which says RMAN-05609: Must specify a username for target connection when using active duplicate , because we had to put a username instead of using OS authentication, so here is how we solved it.
[oracle@node4 ~]$ rman target/ auxiliary sys@drs
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Sep 8 14:59:08 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRD (DBID=1921407359)
auxiliary database Password:
connected to auxiliary database: PRD (not mounted)
RMAN> duplicate target database to drs from active database;
Starting Duplicate Db at 08-SEP-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/08/2014 14:59:27
RMAN-05501: aborting duplication of target database
RMAN-05609: Must specify a username for target connection when using active duplicate
RMAN> exit
Recovery Manager complete.
## Now we specify a username and the error does not come
[oracle@node4 ~]$ rman target sys@prd auxiliary sys@drs
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Sep 8 14:59:44 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: PRD (DBID=1921407359)
auxiliary database Password:
connected to auxiliary database: PRD (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 08-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwprd' auxiliary format
'/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwdrs' ;
}
executing Memory Script
Starting backup at 08-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
Finished backup at 08-SEP-14
contents of Memory Script:
{
restore clone from service 'prd' standby controlfile;
}
executing Memory Script
Starting restore at 08-SEP-14
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prd
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/drs/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/drs/control02.ctl
Finished restore at 08-SEP-14
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/drs/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/drs/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/drs/example01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/drs/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/drs/undotbs01.dbf";
set newname for datafile 6 to
"/u01/app/oracle/oradata/drs/users01.dbf";
restore
from service 'prd' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/drs/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 08-SEP-14
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prd
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/drs/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prd
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/drs/example01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prd
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/drs/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prd
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/drs/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prd
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/drs/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-SEP-14
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=857746892 file name=/u01/app/oracle/oradata/drs/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=857746892 file name=/u01/app/oracle/oradata/drs/example01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=857746892 file name=/u01/app/oracle/oradata/drs/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=857746892 file name=/u01/app/oracle/oradata/drs/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=857746892 file name=/u01/app/oracle/oradata/drs/users01.dbf
Finished Duplicate Db at 08-SEP-14
RMAN> exit
Recovery Manager complete.
[oracle@node4 ~]$ rman target/ auxiliary sys@drs
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Sep 8 14:59:08 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRD (DBID=1921407359)
auxiliary database Password:
connected to auxiliary database: PRD (not mounted)
RMAN> duplicate target database to drs from active database;
Starting Duplicate Db at 08-SEP-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/08/2014 14:59:27
RMAN-05501: aborting duplication of target database
RMAN-05609: Must specify a username for target connection when using active duplicate
RMAN> exit
Recovery Manager complete.
## Now we specify a username and the error does not come
[oracle@node4 ~]$ rman target sys@prd auxiliary sys@drs
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Sep 8 14:59:44 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: PRD (DBID=1921407359)
auxiliary database Password:
connected to auxiliary database: PRD (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 08-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwprd' auxiliary format
'/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwdrs' ;
}
executing Memory Script
Starting backup at 08-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
Finished backup at 08-SEP-14
contents of Memory Script:
{
restore clone from service 'prd' standby controlfile;
}
executing Memory Script
Starting restore at 08-SEP-14
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prd
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/drs/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/drs/control02.ctl
Finished restore at 08-SEP-14
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/drs/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/drs/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/drs/example01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/drs/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/drs/undotbs01.dbf";
set newname for datafile 6 to
"/u01/app/oracle/oradata/drs/users01.dbf";
restore
from service 'prd' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/drs/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 08-SEP-14
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prd
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/drs/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prd
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/drs/example01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prd
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/drs/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prd
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/drs/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prd
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/drs/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-SEP-14
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=857746892 file name=/u01/app/oracle/oradata/drs/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=857746892 file name=/u01/app/oracle/oradata/drs/example01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=857746892 file name=/u01/app/oracle/oradata/drs/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=857746892 file name=/u01/app/oracle/oradata/drs/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=857746892 file name=/u01/app/oracle/oradata/drs/users01.dbf
Finished Duplicate Db at 08-SEP-14
RMAN> exit
Recovery Manager complete.
Tuesday, 2 September 2014
12C SQL PLAN MANAGEMENT (SPM)
To download the pdf of this article click http://goo.gl/HyMdaS
September 2014
Author :- KARAN DODWAL
Oracle Certified Master DBA
Senior Oracle Architect
Oracle Solaris Administrator
Oracle PLSQL Developer Expert
Oracle Database Instructor
12C SQL PLAN MANAGEMENT (SPM)
SQL Plan baselines were introduced in 11G, its a remedy to stop the database to perform badly in case the optimizer fails to select a cost effective plan in case there are changes made in the database by any means, be it major changes or minor changes. So SQL Plan Baselines have that proactive approach to stop the bad performance in future by reproducing alternate plans and storing them in a repository, later verifying their performance, and just in case they dont behave well and they dont give guarantee of good performance oracle simply ignores them and puts them in history but at the initial stage if they do behave very well oracle accepts them inside the SQL Plan Baseline and keep them marked as accepted, subsequent execution plans are kept in history till they are verified and confirm they dont degrade performance. In other words the mission of SQL plan baselines is to preserve the performance of oracle database when repeating SQL statements tend to execute and affect the performance of the database regardless of changes in the database. So it is a conservative and a proactive approach.
So in simple words SPM does the following :-
1) It captures the plans at the initial stage and moves them into the baseline where plans are marked as accepted, (DBA can also manually load the plans in baseline)
2) New and alternate plans are moved to history (non accepted), only the plans in baseline are used which are enabled and accepted
3) In evolution phase plans in history are compared to plans in baseline, if the plans are proven to perform well they are pushed to baseline if they give guarantee they will not degrade performance.
## New in 12C Evolution is automatically done as an Auto Task SYS_AUTO_SPM_EVOLVE_TASK.
So lets get into SQL PLAN MANAGEMENT (SPM) more deep now, Oracle captures the execution plans of the statements if the DBA enables the behaviour with optimizer_capture_sql_plan_baselines parameter which by default is FALSE. And once oracle captures the initial plan it stores them in the baseline so that in future if any changes do happen, the same accepted plan will be used to avoid performance degradation which is mainly due to Expensive/Alternate/New plans. Because the truth is that some plan changes and they cause performance degradation. A very important thing to know is that SQL plan baselines reproduces an execution plan whereas SQL Profiles will only correct optimizer cost estimates and that is one big reason why SQL Plan Baslines were introduced in 11G and as we know SQL Profiles were introduced in 10G. Remember that along with statement logs of the SQL statements, SQL Profiles are also stored in the SQL MANAGEMENT BASE which is a repository of SQL Plan Baselines (SPM). Because some statements are tuned with SQL Tuning Advisor and once the recommendation is implementation oracle can put the plan used with sql profiles in the sql plan baselines and this interaction requires tuning pack to be available and enabled because SPM itself does not require tuning pack, it is free remember, the interaction of sql tuning advisor at nightly window task (Auto Task of SQL TUNING ADVISOR) with SPM requires the tuning pack. Not clear about the interaction part ? well let me explain this, when sql tuning advisor is run and it finds a good tuned plan and when we implement the recommendation lets say of accepting an sql profile of it, then oracle can easily put a brand new plan into the baseline directly and moreover it can even move the plan from plan history (non accepted plan) into the plan baseline where the plan is marked as accepted. So can we say that sql tuning advisor does evolve as well ? well yes it does.
Before SPM we had SQL Profiles. Before Profiles we had Stored Outlines (SO). And before SO we had CBO Hints. With SPM we can have stable plans and we can have more than one per SQL. In oracle database 12C, new enhancement is that when a new plan is put into the SQL management Base (SMB) which is the repository of SQL PLAN BASLINES, plan rows are persisted in SMB which did not use to happen in 11G. In 11g oracle used to compile the statement everytime when we used to display plans by using display_sql_plan_baseline function of DBMS_SPM package to show the execution plan. Moreover automatic evolving of SQL plan baseline is also available in Oracle 12C which is done with help of SPM evolve advisor and internally the task name is SYS_AUTO_SPM_EVOLVE_TASK. SPM evolve advisor basically evolves plans that are in history and that may or may not be accepted. So offcourse a non accepted plans are compared to plans in the baselines, if they prove to perform well and they give guarantee that they will not cause performance regession when changes occur they are marked as accepted. In Oracle 12C new functions are available which are create_evolve_Task and execute_evolve_task to allow DBA's to fetch a report and implement the results anytime anywhere which means accept non accepted plans which are better after evolution phase and if they give guarantee not to regress the performance of database in case of changes of any kind happen in the database. We will see later in this white paper with some examples of what changes are introduced in 12C. But now let us continue with essentials of SQL PLAN MANAGEMENT (SPM).
Note :- As we know SMB the repository of SQL PLAN MANAGEMENT is in sysaux tablespace, We need to make sure that sysaux tablespace is always online in order for us to use sql plan management objects otherwise performance can be compromised and can degrade the performance of oracle database to a considerable amount.
_______________________
In order to use SQL Plan Management you will need the EXECUTE privilege on the DBMS_SPM package and to evolve a plan in an existing SQL plan baseline you will need the ADMINISTER SQL MANAGEMENT OBJECT privilege.
A very important point to note is that if any user is granted the ADMINISTER SQL MANAGEMENT OBJECT privilege he or she is able to execute any subprogram in the DBMS_SPM package.
______________________
SQL PLAN BASELINES are controlled by two intialization parameters which are :
SQL> SHOW PARAMETER SQL_PLAN
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
_______________________
SO let us examine pratically how SQL PLAN BASELINES works in Oracle.
There is a select query below and DBA's wants to load it into the baseline as the plan is good.
SQL> select sql_id from v$sql where sql_text like 'select * from emp where empn%';
SQL_ID
-------------
2cv6qqj01b9wu
We can load plans into the baselines from shared sql area (cursor cache), SQL tuning set (STS), Staging table and Stored outline.
Let us now see how to load the plans from all the 4 methods.
Method 1 (Cursor Cache)
SQL> declare
a pls_integer;
begin
a:= dbms_spm.load_plans_from_cursor_cache(sql_id=>'2cv6qqj01b9wu');
end;
/
PL/SQL procedure successfully completed.
SQL> select signature,SQL_HANDLE,enabled,accepted,cpu_time from dba_sql_plan_baselines where sql_text like 'select * from emp where empno%';
SIGNATURE SQL_HANDLE ENA ACC CPU_TIME
---------- ------------------------------ --- --- ----------
8.1104E+18 SYS_SQL_708dd8dbda3c5c92 YES YES 7998
So it is confirmed that the plan is accepted and enabled to be used by sql plan baseline. Let us see a report output from SQL PLAN BASELINE with the help of DISPLAY_SQL_PLAN_BASELINE function of DBMS_SPM package.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_708dd8dbda3c5c92
SQL text: select * from emp where empno=7788
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Plan name: SQL_PLAN_713fsvgd3sr4k695cc014 Plan id: 1767686164
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | PK_EMP |
----------------------------------------------
20 rows selected.
Method 2 SQL tuning set (STS)
Load plans from sql tuning set into the baseline.
VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
sqlset_name => 'STS_SALES1', -
basic_filter => 'sql_text like ''SELECT CUSTOMER_ID, ACCOUNT_NUMBER%''' );
Method 3 (Staging table)
Once stage table is created and packed and after it is transported and imported with data pump into target database, execute the following. In this example we are making this plan as fixed for some business reasons of a production database.
DECLARE
my_plans NUMBER;
BEGIN
my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE (
table_name => 'stage1'
, fixed => 'yes'
);
END;
/
Method 4 (Stored outline)
SQL> GRANT CREATE ANY OUTLINE TO SCOTT;
Grant succeeded.
SQL> select sql_id,hash_value, child_number from v$sql where sql_text like 'select ename from emp%';
SQL_ID HASH_VALUE CHILD_NUMBER
------------- ---------- ------------
3frqmfujukdts 2745775928 0
[oracle@node4 ~]$ sqlplus scott
SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 1 00:25:09 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Last Successful login time: Sun Aug 31 2014 16:51:24 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> ALTER SESSION SET create_stored_outlines=TRUE;
Session altered.
SQL> BEGIN
DBMS_OUTLN.create_outline(
hash_value => 2745775928,
child_number => 0,
category => 'DEV');
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT name, category,owner FROM dba_outlines;
NAME CATEGORY OWNER
---------------------------------------- -------------------- --------------------
SYS_OUTLINE_14090100280772402 DEV SCOTT
SQL> variable v clob
SQL> execute :v := dbms_spm.migrate_stored_outline(attribute_name=>'outline_name',attribute_value=>'SYS_OUTLINE_14090100280772402', fixed=>'NO');
PL/SQL procedure successfully completed.
The column name origin will display STORED-OUTLINE if it has been migrated from stored outline, so following query is useful for that purpose. Let us see by running the command,
SQL> select sql_handle,sql_text,origin,plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT ORIGIN PLAN_NAME ENA ACC
-------------------- ------------------------------ -------------- -------------------------------------------------- --- ---
SQL_cf69d4c0f3821825 select ename from emp STORED-OUTLINE SYS_OUTLINE_14090100280772402 YES YES
___________________________________________
In the following example we will evolve the sql plan baseline, note here we are evolving plans with respect to a particular SQL Handle and all plans corresponding to a sql statement or a SQL Handle will be evolved.
SQL> declare
c clob;
begin
c := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_708dd8dbda3c5c92');
dbms_output.put_line(c);
end;
/
PL/SQL procedure successfully completed.
__________________________________________________
In below example after checking the values we configure setting for SQL MANAGEMENT BASE (SMB). The quota available in SYSAUX tablespace and retention period in the sysaux tablespace. Be default SMB can take 10% of SYSUAX tablespace and it can be increased till 50% and retention period is 53 weeks by default, almost 1 year which can also be increased to 523 weeks (maximum) and 5 weeks minimum.
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
SQL> BEGIN
DBMS_SPM.CONFIGURE('space_budget_percent',30);
END;
/
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_SPM.CONFIGURE( 'plan_retention_weeks',105);
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 30
PLAN_RETENTION_WEEKS 105
_____________________________________________________
The following example shows how to unaccept a plan in Oracle 11G. We will also see in this white paper later how syntax differs from 11G R1 to 11G R2.
SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',format=>'basic'));
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
attribute_name=>'ENABLED',
attribute_value=>'NO');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
In below example we make a plan as fixed which means this plan will always be used and that is why it is called as FIXED. Remember that there can be 2 fixed plans also for a statement and in that case the best cheapest plan will be chosen by the optimizer. So we can mark more than 1 plan as fixed for a statement, lets see how we can change FIXED attribute for a plan.
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
attribute_name=>'FIXED',
attribute_value=>'YES');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
## Attribute of a plan can be enabled, fixed, autopurged,
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_708dd8dbda3c5c92
SQL text: select * from emp where empno=7788
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_713fsvgd3sr4k695cc014 Plan id: 1767686164
Enabled: NO Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | PK_EMP |
----------------------------------------------
20 rows selected.
The Oracle database according to plan retention settings purges plans that have not been used and offcourse which are longer than the plan retention period, and how does oracle determines about it ?? The answer is identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. So autopurge setting works this way, if you plan not to purge that plan, just configure autopurge to NO simply. let us see how we can do it.
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
attribute_name=>'AUTOPURGE',
attribute_value=>'NO');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
SQL> select AUTOPURGE,OPTIMIZER_COST from dba_sql_plan_baselines where sql_handle='SYS_SQL_708dd8dbda3c5c92' ;
AUT OPTIMIZER_COST
--- --------------
NO 1
The following example shows to set time_limit values, this means time limit set for every plan verification, Time limit is in number of minutes. This applies only if verify = 'YES'and the parameter VERIFY specifies whether to execute the plan and compare the performance before changing non-accepted plans into accepted plans, if the non-accepted plan shows performance improvement, it will be changed to an accepted plan. When set to 'YES', the non-accepted plan(s) will be accepted if verified to give better performance, When set to 'NO', the non-accepted plan(s) will be changed to accepted plan(s).
SQL> declare
c clob;
begin
c := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',time_limit=>20);
dbms_output.put_line(c);
end;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
------
Inputs:
-------
SQL_HANDLE = SYS_SQL_708dd8dbda3c5c92
PLAN_NAME =
TIME_LIMIT = 20
VERIFY = YES
COMMIT =
YES
---------------------------------------------------------------------------
----
Report
Summary
------------------------------------------------------------------------
-------
There were no SQL plan baselines that required processing.
PL/SQL procedure successfully completed.
_______________________________________________________________________________________
SQL> declare
c clob;
begin
c := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',verify=>'NO');
dbms_output.put_line(c);
end;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
------
Inputs:
-------
SQL_HANDLE = SYS_SQL_708dd8dbda3c5c92
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = NO
COMMIT =
YES
---------------------------------------------------------------------------
----
Report
Summary
------------------------------------------------------------------------
-------
There were no SQL plan baselines that required processing.
PL/SQL procedure successfully completed.
_______________________________________
SQL> select plan_name,enabled,accepted,fixed from dba_sql_plan_baselines where sql_handle='SYS_SQL_708dd8dbda3c5c92';
PLAN_NAME ENA ACC FIX
------------------------------ --- --- ---
SQL_PLAN_713fsvgd3sr4k695cc014 NO YES NO
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
plan_name=>'SQL_PLAN_713fsvgd3sr4k695cc014',
attribute_name=>'FIXED',
attribute_value=>'YES');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
_______________________________________________
Let us take an example of some changes introduced in Oracle 12C when it comes to SQL PLAN MANAGEMENT (SPM).
Automatic Evolution Task := This task will run automatically and it will accept the plans automatically if they are proven to perform well in the evolution phase, so default behaviour is accepting if proven to perform well and we can enable or disable this setting by setting up the parameter for Auto Evolution Task as follows :=
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => 'ACCEPT_PLANS'
, value => 'true'
);
END;
/
Lets check and confirm :-
SQL> COL PARAMETER_NAME FORMAT a25
SQL> COL VALUE FORMAT a10
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE" FROM DBA_ADVISOR_PARAMETERS WHERE ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND
( (PARAMETER_NAME = 'ACCEPT_PLANS') OR (PARAMETER_NAME = 'TIME_LIMIT') ) );
PARAMETER_NAME VALUE
------------------------- ----------
TIME_LIMIT 3600
ACCEPT_PLANS TRUE
We can also set LOCAL_TIME_LIMIT parameter, For example lets say we want to limit the task time to 30 minutes. By default it is 1 hour.
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => 'LOCAL_TIME_LIMIT'
, value => 1800
);
END;
/
If you do not have the automatic evolve task enabled, you are most welcome to run it manually
Let us see how we do it manually
SQL> VARIABLE v1 NUMBER
SQL> VARIABLE task_name VARCHAR2(50)
SQL> VARIABLE exe_name VARCHAR2(50)
SQL> VARIABLE evol_out CLOB
SQL> EXECUTE :task_name := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_cf69d4c0f3821825',plan_name => 'SYS_OUTLINE_14090100280772402');
PL/SQL procedure successfully completed.
SQL> SELECT :task_name FROM DUAL;
:TASK_NAME
--------------------------------------------------------------------------------------------------------------------------------
TASK_44
## Execute the evolve task
SQL> EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:task_name);
PL/SQL procedure successfully completed.
SQL> SELECT :exe_name FROM DUAL;
:EXE_NAME
--------------------------------------------------------------------------------------------------------------------------------
EXEC_27
SQL> EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:task_name,execution_name=>:exe_name );
PL/SQL procedure successfully completed.
## Following code shows how to report for evolve task
SQL> set long 10000
SQL> SELECT :evol_out FROM DUAL;
:EVOL_OUT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
--------------------------------------------------------------------------------
-------------
Task Information:
---------------------------------------------
Task Name : TASK_44
Task Owner : SYS
Execution Name : EXEC_27
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
:EVOL_OUT
--------------------------------------------------------------------------------
Status : COMPLETED
Started : 09/01/2014 00:38:47
Finished : 09/01/2014 00:38:47
Last Updated : 09/01/2014 00:38:47
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
--------------------------------------------------------------------------------
.........................
.
.........................................
## Implement the evolve task
SQL> EXECUTE :v1 := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:task_name,execution_name=>:exe_name );
PL/SQL procedure successfully completed.
## To drop the baseline we execute the following code
EXEC :v1 := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_cf69d4c0f3821825');
_______________________________________________
In 11G R1 and R2 there is a slight difference in making changes to an accepted plan, In 11gR2, the sql plans are accepted and unaccepted using a different procedure which is used in 11gR1.
Also, once plans are accepted in 11gR2, they do not become UNACCEPTED. To avoid using that plan, the attribute ENABLED must be set to 'NO'.
In 11gR1
DECLARE
report varchar2(1000);
BEGIN
report := dbms_spm.ALTER_SQL_PLAN_BASELINE
('SYS_SQL_7de69bb90f3e54d2',
'SQL_PLAN_7vtnvr47mwp6k54bc8843',
ATTRIBUTE_NAME => 'ACCEPTED',
ATTRIBUTE_VALUE => 'YES');
END;
/
You will get below error if you use this procedurein 11G R2.
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2469
ORA-06512: at line 1
_______________________
In Oracle 11gR2
To ACCEPT the baseline use DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE as follows:
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report varchar2(1000);
BEGIN
report := dbms_spm.evolve_sql_plan_baseline
('SYS_SQL_7de69bb90f3e54d2',
'SQL_PLAN_7vtnvr47mwp6k54bc8843',
VERIFY=>'NO' ,
COMMIT=>'YES');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
The parameter VERIFY specifies whether to execute the plan and compare the performance before changing non-accepted plans into accepted plans
If the non-accepted plan shows performance improvement, it will be changed to an accepted plan. When set to 'YES', the non-accepted plan(s) will be accepted if verified to give better performance When set to 'NO', the non-accepted plan(s) will be changed to accepted plan(s).
The parameter COMMIT specifies whether to update the ACCEPTED status of non-accepted plans from 'NO' to 'YES'
When set to 'YES', baseline will be updated to ACCEPTED status and a report will be generated
When set to 'NO', a report will be generated without actually changing the status of the baseline(s) to ACCEPTED.
_______________________
To UNACCEPT the baseline set ENABLED attribute to NO using
DBMS_SPM.ALTER_SQL_PLAN_BASELINE:
DECLARE
report varchar2(1000);
BEGIN
report := dbms_spm.ALTER_SQL_PLAN_BASELINE
('SYS_SQL_7de69bb90f3e54d2',
'SQL_PLAN_7vtnvr47mwp6k54bc8843',
ATTRIBUTE_NAME => 'ENABLED',
ATTRIBUTE_VALUE => 'NO');
END;
/
Prior to 11gR2, the plan could be "UNACCEPTED" by executing ALTER_SQL_PLAN with ATRIBUTE_NAME => 'ACCEPTED' and ATTRIBUTE_VALUE=> 'NO'.
The status of ACCEPTED in DBA_SQL_PLAN_BASELINES would be updated to 'NO'.
FROM 11gR2, to change a plan to be "unaccepted", you need to execute ALTER_SQL_PLAN with ATRIBUTE_NAME => 'ENABLED' and ATTRIBUTE_VALUE=> 'NO'.
________________________
Sometimes because of some problems faced we need to enable tracing and see what's going in the background to see what went wrong, lets see how we can enable tracing for SPM
Normally to trace all sessions of SPM we can do the following
SQL>exec dbms_spm.configure('spm_tracing',1);
To disable we can do the following
SQL> exec dbms_spm.configure('spm_tracing',0);
To check and verify we can do the following
SQL> SELECT parameter_name, parameter_value FROM sys.smb$config WHERE parameter_name='SPM_TRACING';
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPM_TRACING 0
Lets take an example :=
SQL> exec dbms_spm.configure('spm_tracing',1);
PL/SQL procedure successfully completed.
SQL> DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'try',
sqlset_owner => 'TEST',
commit_rows => 5);
END;
/
PL/SQL procedure successfully completed.
This gives trace similar to:
.../diag/rdbms/demo/demo/trace/demo_ora_15811.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /.../orcl/oracle
System name: Linux
Node name: tlax66
Release: 2.6.9-78.0.0.0.1.ELsmp
Version: #1 SMP Thu Aug 29 18:15:25 EDT 2014
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 10311, image: oracle@orcl (TNS V1-V3)
_______________________
There have been some bugs and knows issues when using SPM in different versions, Some have been solved with some workarounds and some by uprading or patching. Please refer to below notes
Known Issues
Document 1187767.1 Baseline Not Used Under Plsql When OFE Is Changed
Document 1204524.1 Dbms_spm.Load_plans_from_sqlset Getting Xml Parsing Error
Document 12732879.8 Bug 12732879 - Execution Plan of Query with non-peeked binds is not reproducible
Document 11719151.8 Bug 11719151 - SQL Plan Management capture causes slowness
Document 9910484.8 Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX
Document 14009271.8 Bug 14009271 - Cannot reproduce execution plan imported from SQL Tuning Sets / Outlines
_____________________________
References
http://docs.oracle.com/database/121/TGSQL/toc.htm
http://docs.oracle.com/database/121/ADMIN/toc.htm
September 2014
Author :- KARAN DODWAL
Oracle Certified Master DBA
Senior Oracle Architect
Oracle Solaris Administrator
Oracle PLSQL Developer Expert
Oracle Database Instructor
12C SQL PLAN MANAGEMENT (SPM)
SQL Plan baselines were introduced in 11G, its a remedy to stop the database to perform badly in case the optimizer fails to select a cost effective plan in case there are changes made in the database by any means, be it major changes or minor changes. So SQL Plan Baselines have that proactive approach to stop the bad performance in future by reproducing alternate plans and storing them in a repository, later verifying their performance, and just in case they dont behave well and they dont give guarantee of good performance oracle simply ignores them and puts them in history but at the initial stage if they do behave very well oracle accepts them inside the SQL Plan Baseline and keep them marked as accepted, subsequent execution plans are kept in history till they are verified and confirm they dont degrade performance. In other words the mission of SQL plan baselines is to preserve the performance of oracle database when repeating SQL statements tend to execute and affect the performance of the database regardless of changes in the database. So it is a conservative and a proactive approach.
So in simple words SPM does the following :-
1) It captures the plans at the initial stage and moves them into the baseline where plans are marked as accepted, (DBA can also manually load the plans in baseline)
2) New and alternate plans are moved to history (non accepted), only the plans in baseline are used which are enabled and accepted
3) In evolution phase plans in history are compared to plans in baseline, if the plans are proven to perform well they are pushed to baseline if they give guarantee they will not degrade performance.
## New in 12C Evolution is automatically done as an Auto Task SYS_AUTO_SPM_EVOLVE_TASK.
So lets get into SQL PLAN MANAGEMENT (SPM) more deep now, Oracle captures the execution plans of the statements if the DBA enables the behaviour with optimizer_capture_sql_plan_baselines parameter which by default is FALSE. And once oracle captures the initial plan it stores them in the baseline so that in future if any changes do happen, the same accepted plan will be used to avoid performance degradation which is mainly due to Expensive/Alternate/New plans. Because the truth is that some plan changes and they cause performance degradation. A very important thing to know is that SQL plan baselines reproduces an execution plan whereas SQL Profiles will only correct optimizer cost estimates and that is one big reason why SQL Plan Baslines were introduced in 11G and as we know SQL Profiles were introduced in 10G. Remember that along with statement logs of the SQL statements, SQL Profiles are also stored in the SQL MANAGEMENT BASE which is a repository of SQL Plan Baselines (SPM). Because some statements are tuned with SQL Tuning Advisor and once the recommendation is implementation oracle can put the plan used with sql profiles in the sql plan baselines and this interaction requires tuning pack to be available and enabled because SPM itself does not require tuning pack, it is free remember, the interaction of sql tuning advisor at nightly window task (Auto Task of SQL TUNING ADVISOR) with SPM requires the tuning pack. Not clear about the interaction part ? well let me explain this, when sql tuning advisor is run and it finds a good tuned plan and when we implement the recommendation lets say of accepting an sql profile of it, then oracle can easily put a brand new plan into the baseline directly and moreover it can even move the plan from plan history (non accepted plan) into the plan baseline where the plan is marked as accepted. So can we say that sql tuning advisor does evolve as well ? well yes it does.
Before SPM we had SQL Profiles. Before Profiles we had Stored Outlines (SO). And before SO we had CBO Hints. With SPM we can have stable plans and we can have more than one per SQL. In oracle database 12C, new enhancement is that when a new plan is put into the SQL management Base (SMB) which is the repository of SQL PLAN BASLINES, plan rows are persisted in SMB which did not use to happen in 11G. In 11g oracle used to compile the statement everytime when we used to display plans by using display_sql_plan_baseline function of DBMS_SPM package to show the execution plan. Moreover automatic evolving of SQL plan baseline is also available in Oracle 12C which is done with help of SPM evolve advisor and internally the task name is SYS_AUTO_SPM_EVOLVE_TASK. SPM evolve advisor basically evolves plans that are in history and that may or may not be accepted. So offcourse a non accepted plans are compared to plans in the baselines, if they prove to perform well and they give guarantee that they will not cause performance regession when changes occur they are marked as accepted. In Oracle 12C new functions are available which are create_evolve_Task and execute_evolve_task to allow DBA's to fetch a report and implement the results anytime anywhere which means accept non accepted plans which are better after evolution phase and if they give guarantee not to regress the performance of database in case of changes of any kind happen in the database. We will see later in this white paper with some examples of what changes are introduced in 12C. But now let us continue with essentials of SQL PLAN MANAGEMENT (SPM).
Note :- As we know SMB the repository of SQL PLAN MANAGEMENT is in sysaux tablespace, We need to make sure that sysaux tablespace is always online in order for us to use sql plan management objects otherwise performance can be compromised and can degrade the performance of oracle database to a considerable amount.
_______________________
In order to use SQL Plan Management you will need the EXECUTE privilege on the DBMS_SPM package and to evolve a plan in an existing SQL plan baseline you will need the ADMINISTER SQL MANAGEMENT OBJECT privilege.
A very important point to note is that if any user is granted the ADMINISTER SQL MANAGEMENT OBJECT privilege he or she is able to execute any subprogram in the DBMS_SPM package.
______________________
SQL PLAN BASELINES are controlled by two intialization parameters which are :
SQL> SHOW PARAMETER SQL_PLAN
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
_______________________
SO let us examine pratically how SQL PLAN BASELINES works in Oracle.
There is a select query below and DBA's wants to load it into the baseline as the plan is good.
SQL> select sql_id from v$sql where sql_text like 'select * from emp where empn%';
SQL_ID
-------------
2cv6qqj01b9wu
We can load plans into the baselines from shared sql area (cursor cache), SQL tuning set (STS), Staging table and Stored outline.
Let us now see how to load the plans from all the 4 methods.
Method 1 (Cursor Cache)
SQL> declare
a pls_integer;
begin
a:= dbms_spm.load_plans_from_cursor_cache(sql_id=>'2cv6qqj01b9wu');
end;
/
PL/SQL procedure successfully completed.
SQL> select signature,SQL_HANDLE,enabled,accepted,cpu_time from dba_sql_plan_baselines where sql_text like 'select * from emp where empno%';
SIGNATURE SQL_HANDLE ENA ACC CPU_TIME
---------- ------------------------------ --- --- ----------
8.1104E+18 SYS_SQL_708dd8dbda3c5c92 YES YES 7998
So it is confirmed that the plan is accepted and enabled to be used by sql plan baseline. Let us see a report output from SQL PLAN BASELINE with the help of DISPLAY_SQL_PLAN_BASELINE function of DBMS_SPM package.
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_708dd8dbda3c5c92
SQL text: select * from emp where empno=7788
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Plan name: SQL_PLAN_713fsvgd3sr4k695cc014 Plan id: 1767686164
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | PK_EMP |
----------------------------------------------
20 rows selected.
Method 2 SQL tuning set (STS)
Load plans from sql tuning set into the baseline.
VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
sqlset_name => 'STS_SALES1', -
basic_filter => 'sql_text like ''SELECT CUSTOMER_ID, ACCOUNT_NUMBER%''' );
Method 3 (Staging table)
Once stage table is created and packed and after it is transported and imported with data pump into target database, execute the following. In this example we are making this plan as fixed for some business reasons of a production database.
DECLARE
my_plans NUMBER;
BEGIN
my_plans := DBMS_SPM.UNPACK_STGTAB_BASELINE (
table_name => 'stage1'
, fixed => 'yes'
);
END;
/
Method 4 (Stored outline)
SQL> GRANT CREATE ANY OUTLINE TO SCOTT;
Grant succeeded.
SQL> select sql_id,hash_value, child_number from v$sql where sql_text like 'select ename from emp%';
SQL_ID HASH_VALUE CHILD_NUMBER
------------- ---------- ------------
3frqmfujukdts 2745775928 0
[oracle@node4 ~]$ sqlplus scott
SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 1 00:25:09 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Last Successful login time: Sun Aug 31 2014 16:51:24 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> ALTER SESSION SET create_stored_outlines=TRUE;
Session altered.
SQL> BEGIN
DBMS_OUTLN.create_outline(
hash_value => 2745775928,
child_number => 0,
category => 'DEV');
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT name, category,owner FROM dba_outlines;
NAME CATEGORY OWNER
---------------------------------------- -------------------- --------------------
SYS_OUTLINE_14090100280772402 DEV SCOTT
SQL> variable v clob
SQL> execute :v := dbms_spm.migrate_stored_outline(attribute_name=>'outline_name',attribute_value=>'SYS_OUTLINE_14090100280772402', fixed=>'NO');
PL/SQL procedure successfully completed.
The column name origin will display STORED-OUTLINE if it has been migrated from stored outline, so following query is useful for that purpose. Let us see by running the command,
SQL> select sql_handle,sql_text,origin,plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT ORIGIN PLAN_NAME ENA ACC
-------------------- ------------------------------ -------------- -------------------------------------------------- --- ---
SQL_cf69d4c0f3821825 select ename from emp STORED-OUTLINE SYS_OUTLINE_14090100280772402 YES YES
___________________________________________
In the following example we will evolve the sql plan baseline, note here we are evolving plans with respect to a particular SQL Handle and all plans corresponding to a sql statement or a SQL Handle will be evolved.
SQL> declare
c clob;
begin
c := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_708dd8dbda3c5c92');
dbms_output.put_line(c);
end;
/
PL/SQL procedure successfully completed.
__________________________________________________
In below example after checking the values we configure setting for SQL MANAGEMENT BASE (SMB). The quota available in SYSAUX tablespace and retention period in the sysaux tablespace. Be default SMB can take 10% of SYSUAX tablespace and it can be increased till 50% and retention period is 53 weeks by default, almost 1 year which can also be increased to 523 weeks (maximum) and 5 weeks minimum.
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
SQL> BEGIN
DBMS_SPM.CONFIGURE('space_budget_percent',30);
END;
/
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_SPM.CONFIGURE( 'plan_retention_weeks',105);
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_SQL_MANAGEMENT_CONFIG;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 30
PLAN_RETENTION_WEEKS 105
_____________________________________________________
The following example shows how to unaccept a plan in Oracle 11G. We will also see in this white paper later how syntax differs from 11G R1 to 11G R2.
SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',format=>'basic'));
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
attribute_name=>'ENABLED',
attribute_value=>'NO');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
In below example we make a plan as fixed which means this plan will always be used and that is why it is called as FIXED. Remember that there can be 2 fixed plans also for a statement and in that case the best cheapest plan will be chosen by the optimizer. So we can mark more than 1 plan as fixed for a statement, lets see how we can change FIXED attribute for a plan.
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
attribute_name=>'FIXED',
attribute_value=>'YES');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
## Attribute of a plan can be enabled, fixed, autopurged,
SQL> SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_708dd8dbda3c5c92
SQL text: select * from emp where empno=7788
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_713fsvgd3sr4k695cc014 Plan id: 1767686164
Enabled: NO Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | PK_EMP |
----------------------------------------------
20 rows selected.
The Oracle database according to plan retention settings purges plans that have not been used and offcourse which are longer than the plan retention period, and how does oracle determines about it ?? The answer is identified by the LAST_EXECUTED timestamp stored in the SMB for that plan. So autopurge setting works this way, if you plan not to purge that plan, just configure autopurge to NO simply. let us see how we can do it.
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
attribute_name=>'AUTOPURGE',
attribute_value=>'NO');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
SQL> select AUTOPURGE,OPTIMIZER_COST from dba_sql_plan_baselines where sql_handle='SYS_SQL_708dd8dbda3c5c92' ;
AUT OPTIMIZER_COST
--- --------------
NO 1
The following example shows to set time_limit values, this means time limit set for every plan verification, Time limit is in number of minutes. This applies only if verify = 'YES'and the parameter VERIFY specifies whether to execute the plan and compare the performance before changing non-accepted plans into accepted plans, if the non-accepted plan shows performance improvement, it will be changed to an accepted plan. When set to 'YES', the non-accepted plan(s) will be accepted if verified to give better performance, When set to 'NO', the non-accepted plan(s) will be changed to accepted plan(s).
SQL> declare
c clob;
begin
c := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',time_limit=>20);
dbms_output.put_line(c);
end;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
------
Inputs:
-------
SQL_HANDLE = SYS_SQL_708dd8dbda3c5c92
PLAN_NAME =
TIME_LIMIT = 20
VERIFY = YES
COMMIT =
YES
---------------------------------------------------------------------------
----
Report
Summary
------------------------------------------------------------------------
-------
There were no SQL plan baselines that required processing.
PL/SQL procedure successfully completed.
_______________________________________________________________________________________
SQL> declare
c clob;
begin
c := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_708dd8dbda3c5c92',verify=>'NO');
dbms_output.put_line(c);
end;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
------
Inputs:
-------
SQL_HANDLE = SYS_SQL_708dd8dbda3c5c92
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = NO
COMMIT =
YES
---------------------------------------------------------------------------
----
Report
Summary
------------------------------------------------------------------------
-------
There were no SQL plan baselines that required processing.
PL/SQL procedure successfully completed.
_______________________________________
SQL> select plan_name,enabled,accepted,fixed from dba_sql_plan_baselines where sql_handle='SYS_SQL_708dd8dbda3c5c92';
PLAN_NAME ENA ACC FIX
------------------------------ --- --- ---
SQL_PLAN_713fsvgd3sr4k695cc014 NO YES NO
SQL> DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_708dd8dbda3c5c92',
plan_name=>'SQL_PLAN_713fsvgd3sr4k695cc014',
attribute_name=>'FIXED',
attribute_value=>'YES');
DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
/
Plans altered: 1
PL/SQL procedure successfully completed.
_______________________________________________
Let us take an example of some changes introduced in Oracle 12C when it comes to SQL PLAN MANAGEMENT (SPM).
Automatic Evolution Task := This task will run automatically and it will accept the plans automatically if they are proven to perform well in the evolution phase, so default behaviour is accepting if proven to perform well and we can enable or disable this setting by setting up the parameter for Auto Evolution Task as follows :=
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => 'ACCEPT_PLANS'
, value => 'true'
);
END;
/
Lets check and confirm :-
SQL> COL PARAMETER_NAME FORMAT a25
SQL> COL VALUE FORMAT a10
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE" FROM DBA_ADVISOR_PARAMETERS WHERE ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND
( (PARAMETER_NAME = 'ACCEPT_PLANS') OR (PARAMETER_NAME = 'TIME_LIMIT') ) );
PARAMETER_NAME VALUE
------------------------- ----------
TIME_LIMIT 3600
ACCEPT_PLANS TRUE
We can also set LOCAL_TIME_LIMIT parameter, For example lets say we want to limit the task time to 30 minutes. By default it is 1 hour.
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
, parameter => 'LOCAL_TIME_LIMIT'
, value => 1800
);
END;
/
If you do not have the automatic evolve task enabled, you are most welcome to run it manually
Let us see how we do it manually
SQL> VARIABLE v1 NUMBER
SQL> VARIABLE task_name VARCHAR2(50)
SQL> VARIABLE exe_name VARCHAR2(50)
SQL> VARIABLE evol_out CLOB
SQL> EXECUTE :task_name := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_cf69d4c0f3821825',plan_name => 'SYS_OUTLINE_14090100280772402');
PL/SQL procedure successfully completed.
SQL> SELECT :task_name FROM DUAL;
:TASK_NAME
--------------------------------------------------------------------------------------------------------------------------------
TASK_44
## Execute the evolve task
SQL> EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:task_name);
PL/SQL procedure successfully completed.
SQL> SELECT :exe_name FROM DUAL;
:EXE_NAME
--------------------------------------------------------------------------------------------------------------------------------
EXEC_27
SQL> EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:task_name,execution_name=>:exe_name );
PL/SQL procedure successfully completed.
## Following code shows how to report for evolve task
SQL> set long 10000
SQL> SELECT :evol_out FROM DUAL;
:EVOL_OUT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
--------------------------------------------------------------------------------
-------------
Task Information:
---------------------------------------------
Task Name : TASK_44
Task Owner : SYS
Execution Name : EXEC_27
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
:EVOL_OUT
--------------------------------------------------------------------------------
Status : COMPLETED
Started : 09/01/2014 00:38:47
Finished : 09/01/2014 00:38:47
Last Updated : 09/01/2014 00:38:47
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
--------------------------------------------------------------------------------
.........................
.
.........................................
## Implement the evolve task
SQL> EXECUTE :v1 := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:task_name,execution_name=>:exe_name );
PL/SQL procedure successfully completed.
## To drop the baseline we execute the following code
EXEC :v1 := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_cf69d4c0f3821825');
_______________________________________________
In 11G R1 and R2 there is a slight difference in making changes to an accepted plan, In 11gR2, the sql plans are accepted and unaccepted using a different procedure which is used in 11gR1.
Also, once plans are accepted in 11gR2, they do not become UNACCEPTED. To avoid using that plan, the attribute ENABLED must be set to 'NO'.
In 11gR1
DECLARE
report varchar2(1000);
BEGIN
report := dbms_spm.ALTER_SQL_PLAN_BASELINE
('SYS_SQL_7de69bb90f3e54d2',
'SQL_PLAN_7vtnvr47mwp6k54bc8843',
ATTRIBUTE_NAME => 'ACCEPTED',
ATTRIBUTE_VALUE => 'YES');
END;
/
You will get below error if you use this procedurein 11G R2.
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2469
ORA-06512: at line 1
_______________________
In Oracle 11gR2
To ACCEPT the baseline use DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE as follows:
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report varchar2(1000);
BEGIN
report := dbms_spm.evolve_sql_plan_baseline
('SYS_SQL_7de69bb90f3e54d2',
'SQL_PLAN_7vtnvr47mwp6k54bc8843',
VERIFY=>'NO' ,
COMMIT=>'YES');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
The parameter VERIFY specifies whether to execute the plan and compare the performance before changing non-accepted plans into accepted plans
If the non-accepted plan shows performance improvement, it will be changed to an accepted plan. When set to 'YES', the non-accepted plan(s) will be accepted if verified to give better performance When set to 'NO', the non-accepted plan(s) will be changed to accepted plan(s).
The parameter COMMIT specifies whether to update the ACCEPTED status of non-accepted plans from 'NO' to 'YES'
When set to 'YES', baseline will be updated to ACCEPTED status and a report will be generated
When set to 'NO', a report will be generated without actually changing the status of the baseline(s) to ACCEPTED.
_______________________
To UNACCEPT the baseline set ENABLED attribute to NO using
DBMS_SPM.ALTER_SQL_PLAN_BASELINE:
DECLARE
report varchar2(1000);
BEGIN
report := dbms_spm.ALTER_SQL_PLAN_BASELINE
('SYS_SQL_7de69bb90f3e54d2',
'SQL_PLAN_7vtnvr47mwp6k54bc8843',
ATTRIBUTE_NAME => 'ENABLED',
ATTRIBUTE_VALUE => 'NO');
END;
/
Prior to 11gR2, the plan could be "UNACCEPTED" by executing ALTER_SQL_PLAN with ATRIBUTE_NAME => 'ACCEPTED' and ATTRIBUTE_VALUE=> 'NO'.
The status of ACCEPTED in DBA_SQL_PLAN_BASELINES would be updated to 'NO'.
FROM 11gR2, to change a plan to be "unaccepted", you need to execute ALTER_SQL_PLAN with ATRIBUTE_NAME => 'ENABLED' and ATTRIBUTE_VALUE=> 'NO'.
________________________
Sometimes because of some problems faced we need to enable tracing and see what's going in the background to see what went wrong, lets see how we can enable tracing for SPM
Normally to trace all sessions of SPM we can do the following
SQL>exec dbms_spm.configure('spm_tracing',1);
To disable we can do the following
SQL> exec dbms_spm.configure('spm_tracing',0);
To check and verify we can do the following
SQL> SELECT parameter_name, parameter_value FROM sys.smb$config WHERE parameter_name='SPM_TRACING';
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPM_TRACING 0
Lets take an example :=
SQL> exec dbms_spm.configure('spm_tracing',1);
PL/SQL procedure successfully completed.
SQL> DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'try',
sqlset_owner => 'TEST',
commit_rows => 5);
END;
/
PL/SQL procedure successfully completed.
This gives trace similar to:
.../diag/rdbms/demo/demo/trace/demo_ora_15811.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /.../orcl/oracle
System name: Linux
Node name: tlax66
Release: 2.6.9-78.0.0.0.1.ELsmp
Version: #1 SMP Thu Aug 29 18:15:25 EDT 2014
Machine: i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 10311, image: oracle@orcl (TNS V1-V3)
_______________________
There have been some bugs and knows issues when using SPM in different versions, Some have been solved with some workarounds and some by uprading or patching. Please refer to below notes
Known Issues
Document 1187767.1 Baseline Not Used Under Plsql When OFE Is Changed
Document 1204524.1 Dbms_spm.Load_plans_from_sqlset Getting Xml Parsing Error
Document 12732879.8 Bug 12732879 - Execution Plan of Query with non-peeked binds is not reproducible
Document 11719151.8 Bug 11719151 - SQL Plan Management capture causes slowness
Document 9910484.8 Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX
Document 14009271.8 Bug 14009271 - Cannot reproduce execution plan imported from SQL Tuning Sets / Outlines
_____________________________
References
http://docs.oracle.com/database/121/TGSQL/toc.htm
http://docs.oracle.com/database/121/ADMIN/toc.htm
12C AUTOMATIC SQL TUNING
To download this white paper click http://goo.gl/gg23o3
12C AUTOMATIC SQL TUNING
September 2014
Author :- KARAN DODWAL
Oracle Certified Master DBA
Senior Oracle Architect
Oracle Solaris Administrator
Oracle PLSQL Developer Expert
Oracle Database Instructor
Automatic SQL Tuning
Automatic SQL Tuning was a new feature introduced in 10G and later in 11g some changes were made as well which we will discuss in this white paper, With 10G version Automatic sql tuning had the idea to run sql tuning advisor on high load sql statements so with the oracle 10g version Oracle introduced sql tuning advisor and with that the tuning experts used to tune the sql statements by manually running sql tuning advisor on the high load sql statements identified by ADDM. ADDM automatically identifies the high load statements by looking into the system (performance statistics in AWR retention in sysaux tablespace). The problem is that users must manually look into into ADDM reports and find the high load statements and run sql tuning advisor on them. 11G and 12C takes this 1 step ahead, these versions run the sql tuning advisor every night automatically on high load SQL statements based on the AWR top SQL identification and view the recommendation reports or even accept the recommendations automatically in the case of accepting sql profiles which must be enabled with accept_sql_profiles to true of dbms_sqltune tuning advisor package. We will discuss in a while what sql profile is and what is the use of it.
The nightly automatic sql tuning task has a tuning process which identifies high load sql statements and runs sql tuning advisor on them , and all recommendations are reported except the acceptance of sql profiles automatically which has to be enabled, otherwise everything is just recommended not implemented. In this discussion of ours we have used 2 major oracle words here which is sql tuning advisor and sql profiles. Let us understand what they are, sql access advisor will be discussed in my next article, so lets proceed with sql tuning advisor and sql profiles for now.
SQL Tuning Advisor
SQL Tuning Advisor is an advisor available in tuning pack and it takes the input the sql statements and performs analysis on them , it does 4 types of analysis on the sql statements and then give its recommendations for the DBA to implement if benefit percentage is good enough based on their requirements and the service level agreements(SLA). This advisor also runs at night during maitenance window and time limit for every statement is 1200 seconds (20 minutes) when this task runs. The 4 analysis done by sql tuning advisor are following :-
Statistics Analsysis := To see if tables involved in the statement have stale statistics or they do not have statistics at all. So if stats are not there or they are now stale oracle updates this information to gather_stats_job.
Access path analysis := How we can create indexes and better tune the statements. But remember only creation of indexes is recommended not the impact of it, that is why sometimes another advisor sql access advisor is recommended to be run by sql tuning advisor, sql access advisor is an expert which recommends on indexes like what kind of indexes should be created, or if we can change an existing type of index like for example changing a b tree index to a bitmap index if there is a low cardinality column. So sometimes this is the reason why sql tuning advisor recommends us to run sql access advisor because sql access advisor is an expert of indexes, materialized views and partitioning.
Sql Structure analysis := To check how we write our queries so it can recommend to change the code lets say for example use union all instead of union because union all avoids sorting or even it can recommend us to use exists operatior instead of IN.
SQL Profiling :- SQL Profiles were introduced in 10G and it continues to be available in 11g and 12C as well as it has lot of advantages. From 11g onwards SQL PLAN MANAGEMENT has taken over and it controls the whole usage of plans now and it takes cares of the sql profiles as well. SQL Profiles had a great impact on the working of oracle database in that you never had to hard code the select statement in order for you to kick in a particular access path. Hard coding was done in previous releases with hints through stored outlines. Optimizer has poor estimates sometimes in absence of accurate statistics thats when sql profile helps with information to the optimizer like database configuration, bind variable values, optimizer statistics, data set, etc. In short SQL profiles are a guide to the optimizer to make a better plan. Remember you dont create them on your own, You are guided to accept the sql profile by oracle with the help of sql tuning advisor which will then be used by oracle database automatically when the statement is parsed if the sql profile category is available.
A very critical thing to know is that SQL Profiles does not have capability to reproduce an execution plan whereas SQL Plan Baselines will have that capability. So SQL Profiles will only correct optimizer cost estimates and an SQL Profiles are only recommended once you run the SQL Tuning Advisor in which you want to tune a bad query so this means its a reactive approach in contrast to SQL PLan baseline's Proactive approach. We will be discussing more deep about sql profiles in another article but let us focus more on sql tuning advisor now.
If you have licensed your tuning pack you have SQL Tuning Advisor which can recommend to accept an sql profile for the following types of statements:
# DML statements (SELECT, INSERT with a SELECT clause, UPDATE, and DELETE)
# CREATE TABLE statements (only with the AS SELECT clause)
# MERGE statements (the update or insert operations)
SQL Tuning advisor can run in limited scope and comprehensive scope, in limited scope it does all analysis except sql profiling, in comprehensive scope it does all analysis including sql profiling so if we have enough time for analysis we should go for comprehensive scope.
When the sql tuning advisor is run in auto task in maitenance window its main focus is to pick top high load sql statements based on the top 4 different time periods :- the past week, any day of past week, any hour of past week, or a single response time. It checks the high load statements based on both cpu time and i/o time together. Both the cpu time and I/O time has to be better, if only 1 of them is better oracle ignores that plan for the sql profile, When checking whether or not to create an sql profile for the statement it checks if the statment can be improved 3 times better than the original execution, so if the statement is getting better 3 times than the original by looking at the benefit factor of cpu time and i/o time together, it recommends for the acceptance of the sql profile, Lets take an example := if a statement is found with a better plan which uses parallelism which takes less i/o time but more cpu consumption in this case it will be rejected because cpu time is worse and is not better than the original plan.
Auto task has the following parameters which can be configured :-
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK','LOCAL_TIME_LIMIT', 1200);
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK','ACCEPT_SQL_PROFILES', 'true');
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK','MAX_SQL_PROFILES_PER_EXEC', 50);
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK','MAX_AUTO_SQL_PROFILES', 10002);
END;
/
The LOCAL_TIME_LIMIT means maximum time devoted for a single statement (1200 seconds) 20 minutes is the default.
The ACCEPT_SQL_PROFILES means accepting sql profile automatically or not, by default it is FALSE.
The MAX_SQL_PROFILES_PER_EXEC means maximum sql profiles acceptance limit for a single task. 20 is the default value.
The MAX_AUTO_SQL_PROFILES means how many profiles getting accepted overall in the oracle database at any point in time. 10000 is the default value.
________________________________
To use the APIs, the user needs at least the ADVISOR privilege.
To view the Automatic SQL Tuning Report for the nightly task, you need the ADVISOR privilege and SELECT privileges on the DBA_ADVISOR views
## Running the sql tuning advisor for a single SQL Statement
SQL> select sql_id from v$sql where sql_text like 'select * from t1%';
SQL_ID
-------------
27uhu2q2xuu7r
Lets create a tuning task with sql tuning advisor with comprehensive scope which means oracle will also analyze whether statement can benefit from sql profile as well. Had it been normal scope it would have only analyzed statistics, access path and sql structure and not sql profiling.
Let us see the execution plan of a select statement with a full table scan.
SQL> select * from T1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1159K| 107M| 4471 (1)| 00:00:54 |
| 1 | TABLE ACCESS FULL| T1 | 1159K| 107M| 4471 (1)| 00:00:54 |
--------------------------------------------------------------------------
SQL> set serveroutput on
declare
a varchar2(200);
begin
a := dbms_sqltune.create_tuning_Task(
sql_id=>'27uhu2q2xuu7r',
scope=>'comprehensive',
task_name=>'task3',time_limit=>60);
dbms_output.put_line('task name := '||a);
end;
/
SQL>
task name := task3
PL/SQL procedure successfully completed.
In order to see exactly what went in the background we can enable tracing
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
EXEC dbms_sqltune.execute_tuning_task('task3');
SELECT SOFAR, TOTALWORK FROM V$ADVISOR_PROGRESS WHERE USER_NAME = 'HR' AND TASK_NAME = 'TASK3';
SET linesize 180
SET longchunksize 180
SET pagesize 900
SET long 1000000
SELECT dbms_sqltune.report_tuning_task('task3') FROM dual;
## Accepting an sql profile
EXEC dbms_sqltune.accept_sql_profile(:task_name);
_______________________________________
## Running sql tuning advisor with sql tuning sets
## Advisor and Administer sql tuning set priviges are very critical to give to the user to run the below code.
SQL> set serveroutput on
declare
a varchar2(200);
begin
a := dbms_sqltune.create_tuning_Task(
sqlset_name=>'STS1',
scope=>'comprehensive',
task_name=>'task6',time_limit=>60);
dbms_output.put_line('task name := '||a);
end;
/
SQL>
task name := task6
PL/SQL procedure successfully completed.
In order to see exactly what went in the background we can enable tracing
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
EXEC dbms_sqltune.execute_tuning_task('task6');
SQL> select OPNAME,ADVISOR_NAME,SOFAR,TOTALWORK from V$ADVISOR_PROGRESS ;
OPNAME ADVISOR_NAME SOFAR TOTALWORK
---------- -------------------- ---------- ----------
Advisor SQL Tuning Advisor 16 16
SET linesize 180
SET longchunksize 180
SET pagesize 900
SET long 1000000
SELECT dbms_sqltune.report_tuning_task('task6') FROM dual;
## Let us see some recommendations made by oracle for some of the sections
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."CLU$" and its indices are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'CLU$', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 19%)
---------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'task6', object_id
=> 10, task_owner => 'SYS', 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 (s): .0462 .049297 -6.7 %
CPU Time (s): .044793 .043793 2.23 %
User I/O Time (s): .003361 .001033 69.26 %
Buffer Gets: 19189 15542 19 %
Physical Read Requests: 229 229 0 %
Physical Write Requests: 0 0
Physical Read Bytes: 1875968 1875968 0 %
Physical Write Bytes: 0 0
Rows Processed: 3699 3699
Fetches: 3699 3699
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
3- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 3607810482 2014-08-31/01:23:10 0.450 STS
Information
-----------
- Because no execution history for the Original Plan was found, the SQL
Tuning Advisor could not determine if any of these execution plans are
superior to it. However, if you know that one alternative plan is better
than the Original Plan, you can create a SQL plan baseline for it. This
will instruct the Oracle optimizer to pick it over any other choices in
the future.
execute dbms_sqltune.create_sql_plan_baseline(task_name => 'task6',
object_id => 10, owner_name => 'SYS', plan_hash_value =>
xxxxxxxx);
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 1 of the execution plan.
The optimizer cannot merge a view that contains a set operator.
## With above recommendations it is clear that statistics are stale for CLU$ table and also an sql profile is recommended to accept and also the plan to be pushed in sql plan baseline, what is sql profile is dicussed in this white paper and what is sql plan baseline is discussed in another white paper of mine which you can find on my blog.
___________________________________________________________________________
To view the Automatic SQL Tuning Report for the nightly task, you need the ADVISOR privilege and SELECT privileges on the DBA_ADVISOR views
Lets run the REPORT_AUTO_TUNING_TASK function in the DBMS_SQLTUNE package, the following code will display a report for all SQL Statements that were analayzed in recent execution and also the recommendations will be shown that were not implemented including several sections with details, like for example GENERAL INFORMATION SECTION which displays general information like time of the task exection and also the STATISTICS ANALYSIS SECTION and so on..
variable my_rept CLOB;
BEGIN
:my_rept :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(
begin_exec => NULL,
end_exec => NULL,
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL',
object_id => NULL,
result_limit => NULL);
END;
/
print :my_rept
___________________________________________________________________________
To enable the automated task, execute the following PL/SQL block:
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE (
client_name => 'sql tuning advisor'
, operation => NULL
, window_name => NULL
);
END;
/
To disable the automated task, execute the following PL/SQL block:
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE (
client_name => 'sql tuning advisor'
, operation => NULL
, window_name => NULL
);
END;
/
______________________________
Query the data dictionary to confirm the change.
For example, query DBA_AUTOTASK_CLIENT as follows :
COL CLIENT_NAME FORMAT a20
SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME = 'sql tuning advisor';
CLIENT_NAME STATUS
-------------------- --------
sql tuning advisor ENABLED
___________________________________________________
12C AUTOMATIC SQL TUNING
September 2014
Author :- KARAN DODWAL
Oracle Certified Master DBA
Senior Oracle Architect
Oracle Solaris Administrator
Oracle PLSQL Developer Expert
Oracle Database Instructor
Automatic SQL Tuning
Automatic SQL Tuning was a new feature introduced in 10G and later in 11g some changes were made as well which we will discuss in this white paper, With 10G version Automatic sql tuning had the idea to run sql tuning advisor on high load sql statements so with the oracle 10g version Oracle introduced sql tuning advisor and with that the tuning experts used to tune the sql statements by manually running sql tuning advisor on the high load sql statements identified by ADDM. ADDM automatically identifies the high load statements by looking into the system (performance statistics in AWR retention in sysaux tablespace). The problem is that users must manually look into into ADDM reports and find the high load statements and run sql tuning advisor on them. 11G and 12C takes this 1 step ahead, these versions run the sql tuning advisor every night automatically on high load SQL statements based on the AWR top SQL identification and view the recommendation reports or even accept the recommendations automatically in the case of accepting sql profiles which must be enabled with accept_sql_profiles to true of dbms_sqltune tuning advisor package. We will discuss in a while what sql profile is and what is the use of it.
The nightly automatic sql tuning task has a tuning process which identifies high load sql statements and runs sql tuning advisor on them , and all recommendations are reported except the acceptance of sql profiles automatically which has to be enabled, otherwise everything is just recommended not implemented. In this discussion of ours we have used 2 major oracle words here which is sql tuning advisor and sql profiles. Let us understand what they are, sql access advisor will be discussed in my next article, so lets proceed with sql tuning advisor and sql profiles for now.
SQL Tuning Advisor
SQL Tuning Advisor is an advisor available in tuning pack and it takes the input the sql statements and performs analysis on them , it does 4 types of analysis on the sql statements and then give its recommendations for the DBA to implement if benefit percentage is good enough based on their requirements and the service level agreements(SLA). This advisor also runs at night during maitenance window and time limit for every statement is 1200 seconds (20 minutes) when this task runs. The 4 analysis done by sql tuning advisor are following :-
Statistics Analsysis := To see if tables involved in the statement have stale statistics or they do not have statistics at all. So if stats are not there or they are now stale oracle updates this information to gather_stats_job.
Access path analysis := How we can create indexes and better tune the statements. But remember only creation of indexes is recommended not the impact of it, that is why sometimes another advisor sql access advisor is recommended to be run by sql tuning advisor, sql access advisor is an expert which recommends on indexes like what kind of indexes should be created, or if we can change an existing type of index like for example changing a b tree index to a bitmap index if there is a low cardinality column. So sometimes this is the reason why sql tuning advisor recommends us to run sql access advisor because sql access advisor is an expert of indexes, materialized views and partitioning.
Sql Structure analysis := To check how we write our queries so it can recommend to change the code lets say for example use union all instead of union because union all avoids sorting or even it can recommend us to use exists operatior instead of IN.
SQL Profiling :- SQL Profiles were introduced in 10G and it continues to be available in 11g and 12C as well as it has lot of advantages. From 11g onwards SQL PLAN MANAGEMENT has taken over and it controls the whole usage of plans now and it takes cares of the sql profiles as well. SQL Profiles had a great impact on the working of oracle database in that you never had to hard code the select statement in order for you to kick in a particular access path. Hard coding was done in previous releases with hints through stored outlines. Optimizer has poor estimates sometimes in absence of accurate statistics thats when sql profile helps with information to the optimizer like database configuration, bind variable values, optimizer statistics, data set, etc. In short SQL profiles are a guide to the optimizer to make a better plan. Remember you dont create them on your own, You are guided to accept the sql profile by oracle with the help of sql tuning advisor which will then be used by oracle database automatically when the statement is parsed if the sql profile category is available.
A very critical thing to know is that SQL Profiles does not have capability to reproduce an execution plan whereas SQL Plan Baselines will have that capability. So SQL Profiles will only correct optimizer cost estimates and an SQL Profiles are only recommended once you run the SQL Tuning Advisor in which you want to tune a bad query so this means its a reactive approach in contrast to SQL PLan baseline's Proactive approach. We will be discussing more deep about sql profiles in another article but let us focus more on sql tuning advisor now.
If you have licensed your tuning pack you have SQL Tuning Advisor which can recommend to accept an sql profile for the following types of statements:
# DML statements (SELECT, INSERT with a SELECT clause, UPDATE, and DELETE)
# CREATE TABLE statements (only with the AS SELECT clause)
# MERGE statements (the update or insert operations)
SQL Tuning advisor can run in limited scope and comprehensive scope, in limited scope it does all analysis except sql profiling, in comprehensive scope it does all analysis including sql profiling so if we have enough time for analysis we should go for comprehensive scope.
When the sql tuning advisor is run in auto task in maitenance window its main focus is to pick top high load sql statements based on the top 4 different time periods :- the past week, any day of past week, any hour of past week, or a single response time. It checks the high load statements based on both cpu time and i/o time together. Both the cpu time and I/O time has to be better, if only 1 of them is better oracle ignores that plan for the sql profile, When checking whether or not to create an sql profile for the statement it checks if the statment can be improved 3 times better than the original execution, so if the statement is getting better 3 times than the original by looking at the benefit factor of cpu time and i/o time together, it recommends for the acceptance of the sql profile, Lets take an example := if a statement is found with a better plan which uses parallelism which takes less i/o time but more cpu consumption in this case it will be rejected because cpu time is worse and is not better than the original plan.
Auto task has the following parameters which can be configured :-
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK','LOCAL_TIME_LIMIT', 1200);
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK','ACCEPT_SQL_PROFILES', 'true');
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK','MAX_SQL_PROFILES_PER_EXEC', 50);
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK','MAX_AUTO_SQL_PROFILES', 10002);
END;
/
The LOCAL_TIME_LIMIT means maximum time devoted for a single statement (1200 seconds) 20 minutes is the default.
The ACCEPT_SQL_PROFILES means accepting sql profile automatically or not, by default it is FALSE.
The MAX_SQL_PROFILES_PER_EXEC means maximum sql profiles acceptance limit for a single task. 20 is the default value.
The MAX_AUTO_SQL_PROFILES means how many profiles getting accepted overall in the oracle database at any point in time. 10000 is the default value.
________________________________
To use the APIs, the user needs at least the ADVISOR privilege.
To view the Automatic SQL Tuning Report for the nightly task, you need the ADVISOR privilege and SELECT privileges on the DBA_ADVISOR views
## Running the sql tuning advisor for a single SQL Statement
SQL> select sql_id from v$sql where sql_text like 'select * from t1%';
SQL_ID
-------------
27uhu2q2xuu7r
Lets create a tuning task with sql tuning advisor with comprehensive scope which means oracle will also analyze whether statement can benefit from sql profile as well. Had it been normal scope it would have only analyzed statistics, access path and sql structure and not sql profiling.
Let us see the execution plan of a select statement with a full table scan.
SQL> select * from T1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1159K| 107M| 4471 (1)| 00:00:54 |
| 1 | TABLE ACCESS FULL| T1 | 1159K| 107M| 4471 (1)| 00:00:54 |
--------------------------------------------------------------------------
SQL> set serveroutput on
declare
a varchar2(200);
begin
a := dbms_sqltune.create_tuning_Task(
sql_id=>'27uhu2q2xuu7r',
scope=>'comprehensive',
task_name=>'task3',time_limit=>60);
dbms_output.put_line('task name := '||a);
end;
/
SQL>
task name := task3
PL/SQL procedure successfully completed.
In order to see exactly what went in the background we can enable tracing
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
EXEC dbms_sqltune.execute_tuning_task('task3');
SELECT SOFAR, TOTALWORK FROM V$ADVISOR_PROGRESS WHERE USER_NAME = 'HR' AND TASK_NAME = 'TASK3';
SET linesize 180
SET longchunksize 180
SET pagesize 900
SET long 1000000
SELECT dbms_sqltune.report_tuning_task('task3') FROM dual;
## Accepting an sql profile
EXEC dbms_sqltune.accept_sql_profile(:task_name);
_______________________________________
## Running sql tuning advisor with sql tuning sets
## Advisor and Administer sql tuning set priviges are very critical to give to the user to run the below code.
SQL> set serveroutput on
declare
a varchar2(200);
begin
a := dbms_sqltune.create_tuning_Task(
sqlset_name=>'STS1',
scope=>'comprehensive',
task_name=>'task6',time_limit=>60);
dbms_output.put_line('task name := '||a);
end;
/
SQL>
task name := task6
PL/SQL procedure successfully completed.
In order to see exactly what went in the background we can enable tracing
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
EXEC dbms_sqltune.execute_tuning_task('task6');
SQL> select OPNAME,ADVISOR_NAME,SOFAR,TOTALWORK from V$ADVISOR_PROGRESS ;
OPNAME ADVISOR_NAME SOFAR TOTALWORK
---------- -------------------- ---------- ----------
Advisor SQL Tuning Advisor 16 16
SET linesize 180
SET longchunksize 180
SET pagesize 900
SET long 1000000
SELECT dbms_sqltune.report_tuning_task('task6') FROM dual;
## Let us see some recommendations made by oracle for some of the sections
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."CLU$" and its indices are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'CLU$', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 19%)
---------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'task6', object_id
=> 10, task_owner => 'SYS', 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 (s): .0462 .049297 -6.7 %
CPU Time (s): .044793 .043793 2.23 %
User I/O Time (s): .003361 .001033 69.26 %
Buffer Gets: 19189 15542 19 %
Physical Read Requests: 229 229 0 %
Physical Write Requests: 0 0
Physical Read Bytes: 1875968 1875968 0 %
Physical Write Bytes: 0 0
Rows Processed: 3699 3699
Fetches: 3699 3699
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
3- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 3607810482 2014-08-31/01:23:10 0.450 STS
Information
-----------
- Because no execution history for the Original Plan was found, the SQL
Tuning Advisor could not determine if any of these execution plans are
superior to it. However, if you know that one alternative plan is better
than the Original Plan, you can create a SQL plan baseline for it. This
will instruct the Oracle optimizer to pick it over any other choices in
the future.
execute dbms_sqltune.create_sql_plan_baseline(task_name => 'task6',
object_id => 10, owner_name => 'SYS', plan_hash_value =>
xxxxxxxx);
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 1 of the execution plan.
The optimizer cannot merge a view that contains a set operator.
## With above recommendations it is clear that statistics are stale for CLU$ table and also an sql profile is recommended to accept and also the plan to be pushed in sql plan baseline, what is sql profile is dicussed in this white paper and what is sql plan baseline is discussed in another white paper of mine which you can find on my blog.
___________________________________________________________________________
To view the Automatic SQL Tuning Report for the nightly task, you need the ADVISOR privilege and SELECT privileges on the DBA_ADVISOR views
Lets run the REPORT_AUTO_TUNING_TASK function in the DBMS_SQLTUNE package, the following code will display a report for all SQL Statements that were analayzed in recent execution and also the recommendations will be shown that were not implemented including several sections with details, like for example GENERAL INFORMATION SECTION which displays general information like time of the task exection and also the STATISTICS ANALYSIS SECTION and so on..
variable my_rept CLOB;
BEGIN
:my_rept :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(
begin_exec => NULL,
end_exec => NULL,
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL',
object_id => NULL,
result_limit => NULL);
END;
/
print :my_rept
___________________________________________________________________________
To enable the automated task, execute the following PL/SQL block:
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE (
client_name => 'sql tuning advisor'
, operation => NULL
, window_name => NULL
);
END;
/
To disable the automated task, execute the following PL/SQL block:
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE (
client_name => 'sql tuning advisor'
, operation => NULL
, window_name => NULL
);
END;
/
______________________________
Query the data dictionary to confirm the change.
For example, query DBA_AUTOTASK_CLIENT as follows :
COL CLIENT_NAME FORMAT a20
SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME = 'sql tuning advisor';
CLIENT_NAME STATUS
-------------------- --------
sql tuning advisor ENABLED
___________________________________________________
12C SQL TUNING SETS
To download this white paper click http://goo.gl/bLeaxi
12C SQL TUNING SETS
August 2014
Author :- KARAN DODWAL
Oracle Certified Master DBA
Senior Oracle Architect
Oracle Solaris Administrator
Oracle PLSQL Developer Expert
Oracle Database Instructor
__________________
SQL Tuning Set (STS) is like a container which can hold several SQL statements with execution statistics and lot other information inside it, Just like other database objects STS is also a database object, it is basically a database object that hold multiple sql statements and their respective statistics and some other information relative to a sql statement like buffer gets, disk reads, CPU time and so on... more importantly associated execution plans are also stored in sql tuning sets. It is different from other objects in that it is used for tuning and testing purposes like for example bulk loading sql queries in an STS with their plans, compilation environment, execution statistics and other information relative to a SQL Statement and give it to the SQL Tuning Advisor for recommendations if we have the tuning pack available and enabled. Not only sql tuning advisor but we can also use STS for sql access advisor, sql performance analyzer (part of Real Application Testing (RAT) License) and for transportation purposes.
With the command line we can use the DBMS_SQLTUNE package to manage SQL tuning sets. Though we can also use EM DB Console at anytime.
How to create an SQL Tuning Set
SQL> BEGIN
DBMS_SQLTUNE.create_sqlset (
sqlset_name => 'STS_KD',
description => 'An SQL tuning set for some testing');
END;
/
PL/SQL procedure successfully completed.
SQL> DECLARE
v_cur DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN v_cur FOR
SELECT VALUE(my)
FROM TABLE(
DBMS_SQLTUNE.select_cursor_cache(
basic_filter => 'parsing_schema_name = ''SCOTT''',
attribute_list => 'ALL')) my;
DBMS_SQLTUNE.load_sqlset(sqlset_name => 'STS_KD', populate_cursor => v_cur);
END;
/
PL/SQL procedure successfully completed.
Privileges needed to create and manage sql tuning sets are ADMINISTER SQL TUNING SET and ADMINISTER ANY SQL TUNING SET
_______________________________________
Lets see some description of sql tuning sets from data dictionary
SQL Tuning Set Subprograms
A subprogram in plsql is either a function or a procedure and in context of the package dbms_sqltune, there are few subprograms put inside this package and the code for them is inside their package body.
Subprograms and their Descriptions
ADD_SQLSET_REFERENCE Function := Adds a new reference to an existing SQL tuning set to indicate its use by a client
CAPTURE_CURSOR_CACHE_SQLSET Procedure := Over a specified time interval incrementally captures a workload from the cursor cache into a SQL tuning set
CREATE_SQLSET Procedure and Function := Creates a SQL tuning set object in the database
CREATE_STGTAB_SQLSET Procedure := Creates a staging table through which SQL Tuning Sets are imported and exported
DELETE_SQLSET Procedure := Deletes a set of SQL statements from a SQL tuning set
DROP_SQLSET Procedure := Drops a SQL tuning set if it is not active
LOAD_SQLSET Procedure := Populates the SQL tuning set with a set of selected SQL
PACK_STGTAB_SQLSET Procedure := Copies tuning sets out of the SYS schema into the staging table
REMOVE_SQLSET_REFERENCE Procedure := Deactivates a SQL tuning set to indicate it is no longer used by the client
SELECT_CURSOR_CACHE Function := Collects SQL statements from the cursor cache
SELECT_SQL_TRACE Function := Reads the content of one or more trace files and returns the SQL statements it finds in the format of sqlset_row
SELECT_SQLSET Function := Collects SQL statements from an existing SQL tuning set
SELECT_WORKLOAD_REPOSITORY Functions := Collects SQL statements from the workload repository
UNPACK_STGTAB_SQLSET Procedure := Copies one or more SQL tuning sets from the staging table
UPDATE_SQLSET Procedures := Updates whether selected string fields for a SQL statement in a SQL tuning set or the set numerical attributes of a SQL in a SQL tuning set
_______________________________________
How to run SQL Tuning advisor with SQL Tuning Sets
SQL> set serveroutput on
declare
a varchar2(200);
begin
a := dbms_sqltune.create_tuning_Task(
sqlset_name=>'STS_KD',
scope=>'comprehensive',
task_name=>'task6',time_limit=>60);
dbms_output.put_line('task name := '||a);
end;
/
SQL>
task name := task6
PL/SQL procedure successfully completed.
In order to see exactly what went in the background we can enable tracing as well like for example
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
EXEC dbms_sqltune.execute_tuning_task('task6');
SQL> select OPNAME,ADVISOR_NAME,SOFAR,TOTALWORK from V$ADVISOR_PROGRESS ;
OPNAME ADVISOR_NAME SOFAR TOTALWORK
---------- -------------------- ---------- ----------
Advisor SQL Tuning Advisor 16 16
## to display the report we will use the following
SET linesize 200
SET longchunksize 200
SET pagesize 1000
SET long 1000000
SELECT dbms_sqltune.report_tuning_task('task6') FROM dual;
### There were lot of statements in the sql tuning set so i have trimmed some of them and i have deliberately queried the sys schema object WRH$_ACTIVE_SESSION_HISTORY table which is used in active session history to see if oracle recommends us to improve it further and displayed Statistics Finding section to show that how good sql tuning advisor as it recommends us the solution in order to improve the performance of the statements.
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."WRH$_ACTIVE_SESSION_HISTORY" and its
indices are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'WRH$_ACTIVE_SESSION_HISTORY', estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."WRM$_SNAPSHOT" and its indices are
stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'WRM$_SNAPSHOT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
_______________________________________
Transferring SQL Tuning Sets
First let us create a staging table
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'TAB_STS',
schema_name => 'SYSTEM',
tablespace_name => 'USERS');
END;
/
PL/SQL procedure successfully completed.
Now lets pack the staging table with the data of sql tuning set using the PACK_STGTAB_SQLSET procedure
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'STS_KD',
sqlset_owner => 'SYS',
staging_table_name => 'TAB_STS',
staging_schema_owner => 'SYSTEM');
END;
/
PL/SQL procedure successfully completed.
Now we will transport the table with the help of datapump to the destination database.
## Lets export the TAB_STS table at source database
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.125 MB
.....................................
..........................................
Dump file set for SYSTEM.SYS_EXPORT_TABLE_02 is:
/u01/app/dirs/d1/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_02" successfully completed at Sun Aug 31 01:30:12 2014 elapsed 0 00:00:35
## Now lets import the TAB_STS table at destination with datapump
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=d1
...........
.............
........Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."TAB_STS" 2.328 MB 4727 rows
...........................
......................................
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Aug 31 01:33:01 2014 elapsed 0 00:00:41
## Once we import then we can unpack the sql tuning set with the following code
BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => '%',
sqlset_owner => 'SYS',
replace => TRUE,
staging_table_name => 'TAB_STS',
staging_schema_owner => 'SYSTEM');
END;
/
PL/SQL procedure successfully completed.
_______________________________________
When we are upgrading the database from 10g to 11g we usually populate the 10g sql tuning sets with sql statements and then we load the same to 11g sql plan baseline to preserve the performance of the oracle database , how we do it let us see
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'STS_KD');
END;
/
PL/SQL procedure successfully completed.
_______________________________________
To display the contents of an STS:
SQL>COLUMN SQL_TEXT FORMAT a30
SQL>COLUMN SCH FORMAT a3
SQL>COLUMN ELAPSED FORMAT 999999999
SQL> SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT,
ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'STS_KD' ) );
SQL_ID SCH SQL_TEXT ELAPSED BUFFER_GETS
------------- --- ------------------------------ ---------- -----------
14pxsqq5qw6kb SCO select * from sales where obje 1048699 315093
TT ct_id=1000
2xtm4g9sfzyw0 SCO select * from sales where obje 241277 44436
TT ct_id<1000
4d45hfmk7khhp SCO select * from sales where obje 47424 2120
TT ct_id>1000
946ddvxj22pnd SCO SELECT /* OPT_DYN_SAMP */ /*+ 4498 74
TT ALL_ROWS IGNORE_WHERE_CLAUSE N
SQL_ID SCH SQL_TEXT ELAPSED BUFFER_GETS
------------- --- ------------------------------ ---------- -----------
O_PARALLEL(SAMPLESUB
agn7ru0spffyj SCO SELECT /* OPT_DYN_SAMP */ /*+ 4983 74
TT ALL_ROWS IGNORE_WHERE_CLAUSE N
O_PARALLEL(SAMPLESUB
cdtjaangp5x17 SCO create table sales as select 2180424 5025
TT * from sys.dba_objects
cx0ttf4y4ahxn SCO SELECT /* OPT_DYN_SAMP */ /*+ 4472526 4067
TT ALL_ROWS IGNORE_WHERE_CLAUSE N
SQL_ID SCH SQL_TEXT ELAPSED BUFFER_GETS
------------- --- ------------------------------ ---------- -----------
O_PARALLEL(SAMPLESUB
d6qnn0vpu8xmx SCO SELECT /* OPT_DYN_SAMP */ /*+ 8149 122
TT ALL_ROWS IGNORE_WHERE_CLAUSE N
O_PARALLEL(SAMPLESUB
fcaxkvxh5h4g6 SCO insert into sales select * fro 50204574 370599
TT m sales
gwkz0aduy1w41 SCO select * from sales 14774553 65032
TT
SQL_ID SCH SQL_TEXT ELAPSED BUFFER_GETS
------------- --- ------------------------------ ---------- -----------
10 rows selected.
_______________________________________
Modifying a SQL Tuning Set
Use the basic_filter predicate to filter the SQL from the STS defined on attributes of the SQLSET_ROW. The following example deletes all statements in the STS with fetch counts over 100:
BEGIN
DBMS_SQLTUNE.DELETE_SQLSET (
sqlset_name => 'STS_KD'
, basic_filter => 'fetches > 100'
);
END;
/
PL/SQL procedure successfully completed.
Set attribute values for SQL statements.
The following example sets the priority of statement 7nr1kb47kw04j to 1:
BEGIN
DBMS_SQLTUNE.UPDATE_SQLSET (
sqlset_name => 'STS_KD'
, sql_id => '7nr1kb47kw04j'
, attribute_name => 'PRIORITY'
, attribute_value => 1
);
END;
/
PL/SQL procedure successfully completed.
_______________________________________
In the case of multitenant architecture Oracle 12C we can transport SQL Tuning Sets from a Non-CDB to a CDB
Whenever we are shipping sql tuning sets from a non-CDB to a CDB database, we must remap the con_dbid of each SQL statement in the STS to a con_dbid within the destination CDB. The steps to do it are as follows:
Firstly pack the STS into a staging table using DBMS_SQLTUNE.PACK_STGTAB_SQLSET, Secondly remap each con_dbid in the staging table using DBMS_SQLTUNE.REMAP_STGTAB_SQLSET, Thirdly export the STS, Fourthly unpack the STS in the destination CDB.
Lets see what is the PL/SQL code and how we can do it, in this example we re remapping con_dbid 1031 to 1032:
BEGIN
DBMS_SQLTUNE.REMAP_STGTAB_SQLSET (
staging_table_name => 'sts_kd1'
, staging_schema_owner => 'STAGE_USER'
, old_con_dbid => 1031
, new_con_dbid => 1032
);
END;
/
_______________________________________
In the past versions before 12C there have been some bugs, problems so let us take a brief look at them and see what solution did we implement to solve those bugs and problems.
SQL> BEGIN
DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'STS_KD');
END;
/
ORA-13757: "SQL Tuning Set" "STS_KD" is active.
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 2912
ORA-06512: at "SYS.DBMS_SQLTUNE", line 482 ORA-06512: at line 1
#### This is an nnpublished bug 5742773 - SQLTUNE TASKS ARE NOT DROPPED DUE TO PHANTOM REFERENCES.
Make sure that you delete all the Advisor Tasks :
Unpublished bug 5742773 - (SQLTUNE TASKS ARE NOT DROPPED DUE TO PHANTOM REFERENCES)
The above bug is fixed in 11g.
We can also do a workaround of it by doing the following
SQL> delete from wri$_sqlset_references where sqlset_id in (select id from wri$_sqlset_definitions where name ='<name of the SQL Tuning Set>');
SQL>commit;
SQL> BEGIN
DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'STS_KD');
END;
/
## The best practise is to upgrade your database to atleast 11.2.0.2 or higher.
Another problem faced by some DBA's for the below code
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK was run with execution_type set to 'COMPARE PERFORMANCE'.
SQL> declare
ename varchar2(512);
BEGIN
ename := dbms_sqlpa.execute_analysis_task(
task_name => '10gr2_11gr2_spa',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'compare_102_112_io',
execution_params =>
dbms_advisor.arglist('COMPARISON_METRIC','USER_IO_TIME','EXECUTION_NAME1','10gr2_STS_trial','EXECUTION_NAME2','11gr2_STS_trial'),
execution_desc => 'comp_user_io_time');
END;
/
ERROR at line 1:
ORA-13793: invalid comparison metric
ORA-06512: at "SYS.PRVT_ADVISOR", line 2758
ORA-06512: at "SYS.DBMS_ADVISOR", line 241
ORA-06512: at "SYS.DBMS_SQLPA", line 342
ORA-06512: at "SYS.DBMS_SQLPA", line 359
ORA-06512: at line 37
Comparison_metric being compared is: USER_IO_TIME, PARSE_TIME or IO_INTERCONNECT_BYTES SQL Tuning Set was previously converted for the analysis task using 'CONVERT SQLSET'
Following error occurs:
11.2.0.4 and later:
ORA-13793: invalid comparison metric
11.2.0.3 and earlier:
ORA-904: "USER_IO_TIME": invalid identifier
Oracle says this is expected behavior.
At the time of DBMS_SQLPA.EXECUTE_ANALYSIS_TASK being executed, the information for the metrics above are not yet available in the SQL Tuning Set (ie DBA_SQLSET_STATEMENTS). An unpublished internal bug has been raised with development who are working on enhancing the statistics for the SQL Tuning Sets: Bug 13946904 MISSING METRICS STATS FOR PARSE AND USER_IO_TIME FOR CONVERTED STS TRIAL
Until then you can workaround the issue by comparing a "TEST EXECUTE" trial with a second "TEST EXECUTE" trial in order to get reliable values for these statistics.
________________________
# Below are the dictionary views relative to sql tuning set
SQL> desc dba_sqlset
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
OWNER VARCHAR2(30)
DESCRIPTION VARCHAR2(256)
CREATED DATE
LAST_MODIFIED DATE
STATEMENT_COUNT NUMBER
SQL> desc dba_sqlset_statements
Name Null? Type
----------------------------------------- -------- ----------------------------
SQLSET_NAME NOT NULL VARCHAR2(30)
SQLSET_OWNER VARCHAR2(30)
SQLSET_ID NOT NULL NUMBER
SQL_ID NOT NULL VARCHAR2(13)
FORCE_MATCHING_SIGNATURE NOT NULL NUMBER
SQL_TEXT CLOB
PARSING_SCHEMA_NAME VARCHAR2(30)
PARSING_SCHEMA_ID NUMBER
PLAN_HASH_VALUE NOT NULL NUMBER
BIND_DATA RAW(2000)
BINDS_CAPTURED CHAR(1)
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
ROWS_PROCESSED NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(2000)
PRIORITY NUMBER
COMMAND_TYPE NUMBER
FIRST_LOAD_TIME VARCHAR2(19)
STAT_PERIOD NUMBER
ACTIVE_STAT_PERIOD NUMBER
OTHER CLOB
PLAN_TIMESTAMP DATE
SQL_SEQ NOT NULL NUMBER
__________________________________
## References
http://docs.oracle.com/database/121/LNPLS/toc.htm
http://docs.oracle.com/database/121/ADMIN/toc.htm
http://docs.oracle.com/database/121/TGSQL/toc.htm
12C SQL TUNING SETS
August 2014
Author :- KARAN DODWAL
Oracle Certified Master DBA
Senior Oracle Architect
Oracle Solaris Administrator
Oracle PLSQL Developer Expert
Oracle Database Instructor
__________________
SQL Tuning Set (STS) is like a container which can hold several SQL statements with execution statistics and lot other information inside it, Just like other database objects STS is also a database object, it is basically a database object that hold multiple sql statements and their respective statistics and some other information relative to a sql statement like buffer gets, disk reads, CPU time and so on... more importantly associated execution plans are also stored in sql tuning sets. It is different from other objects in that it is used for tuning and testing purposes like for example bulk loading sql queries in an STS with their plans, compilation environment, execution statistics and other information relative to a SQL Statement and give it to the SQL Tuning Advisor for recommendations if we have the tuning pack available and enabled. Not only sql tuning advisor but we can also use STS for sql access advisor, sql performance analyzer (part of Real Application Testing (RAT) License) and for transportation purposes.
With the command line we can use the DBMS_SQLTUNE package to manage SQL tuning sets. Though we can also use EM DB Console at anytime.
How to create an SQL Tuning Set
SQL> BEGIN
DBMS_SQLTUNE.create_sqlset (
sqlset_name => 'STS_KD',
description => 'An SQL tuning set for some testing');
END;
/
PL/SQL procedure successfully completed.
SQL> DECLARE
v_cur DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN v_cur FOR
SELECT VALUE(my)
FROM TABLE(
DBMS_SQLTUNE.select_cursor_cache(
basic_filter => 'parsing_schema_name = ''SCOTT''',
attribute_list => 'ALL')) my;
DBMS_SQLTUNE.load_sqlset(sqlset_name => 'STS_KD', populate_cursor => v_cur);
END;
/
PL/SQL procedure successfully completed.
Privileges needed to create and manage sql tuning sets are ADMINISTER SQL TUNING SET and ADMINISTER ANY SQL TUNING SET
_______________________________________
Lets see some description of sql tuning sets from data dictionary
SQL Tuning Set Subprograms
A subprogram in plsql is either a function or a procedure and in context of the package dbms_sqltune, there are few subprograms put inside this package and the code for them is inside their package body.
Subprograms and their Descriptions
ADD_SQLSET_REFERENCE Function := Adds a new reference to an existing SQL tuning set to indicate its use by a client
CAPTURE_CURSOR_CACHE_SQLSET Procedure := Over a specified time interval incrementally captures a workload from the cursor cache into a SQL tuning set
CREATE_SQLSET Procedure and Function := Creates a SQL tuning set object in the database
CREATE_STGTAB_SQLSET Procedure := Creates a staging table through which SQL Tuning Sets are imported and exported
DELETE_SQLSET Procedure := Deletes a set of SQL statements from a SQL tuning set
DROP_SQLSET Procedure := Drops a SQL tuning set if it is not active
LOAD_SQLSET Procedure := Populates the SQL tuning set with a set of selected SQL
PACK_STGTAB_SQLSET Procedure := Copies tuning sets out of the SYS schema into the staging table
REMOVE_SQLSET_REFERENCE Procedure := Deactivates a SQL tuning set to indicate it is no longer used by the client
SELECT_CURSOR_CACHE Function := Collects SQL statements from the cursor cache
SELECT_SQL_TRACE Function := Reads the content of one or more trace files and returns the SQL statements it finds in the format of sqlset_row
SELECT_SQLSET Function := Collects SQL statements from an existing SQL tuning set
SELECT_WORKLOAD_REPOSITORY Functions := Collects SQL statements from the workload repository
UNPACK_STGTAB_SQLSET Procedure := Copies one or more SQL tuning sets from the staging table
UPDATE_SQLSET Procedures := Updates whether selected string fields for a SQL statement in a SQL tuning set or the set numerical attributes of a SQL in a SQL tuning set
_______________________________________
How to run SQL Tuning advisor with SQL Tuning Sets
SQL> set serveroutput on
declare
a varchar2(200);
begin
a := dbms_sqltune.create_tuning_Task(
sqlset_name=>'STS_KD',
scope=>'comprehensive',
task_name=>'task6',time_limit=>60);
dbms_output.put_line('task name := '||a);
end;
/
SQL>
task name := task6
PL/SQL procedure successfully completed.
In order to see exactly what went in the background we can enable tracing as well like for example
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
EXEC dbms_sqltune.execute_tuning_task('task6');
SQL> select OPNAME,ADVISOR_NAME,SOFAR,TOTALWORK from V$ADVISOR_PROGRESS ;
OPNAME ADVISOR_NAME SOFAR TOTALWORK
---------- -------------------- ---------- ----------
Advisor SQL Tuning Advisor 16 16
## to display the report we will use the following
SET linesize 200
SET longchunksize 200
SET pagesize 1000
SET long 1000000
SELECT dbms_sqltune.report_tuning_task('task6') FROM dual;
### There were lot of statements in the sql tuning set so i have trimmed some of them and i have deliberately queried the sys schema object WRH$_ACTIVE_SESSION_HISTORY table which is used in active session history to see if oracle recommends us to improve it further and displayed Statistics Finding section to show that how good sql tuning advisor as it recommends us the solution in order to improve the performance of the statements.
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."WRH$_ACTIVE_SESSION_HISTORY" and its
indices are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'WRH$_ACTIVE_SESSION_HISTORY', estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."WRM$_SNAPSHOT" and its indices are
stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'WRM$_SNAPSHOT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
_______________________________________
Transferring SQL Tuning Sets
First let us create a staging table
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'TAB_STS',
schema_name => 'SYSTEM',
tablespace_name => 'USERS');
END;
/
PL/SQL procedure successfully completed.
Now lets pack the staging table with the data of sql tuning set using the PACK_STGTAB_SQLSET procedure
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'STS_KD',
sqlset_owner => 'SYS',
staging_table_name => 'TAB_STS',
staging_schema_owner => 'SYSTEM');
END;
/
PL/SQL procedure successfully completed.
Now we will transport the table with the help of datapump to the destination database.
## Lets export the TAB_STS table at source database
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.125 MB
.....................................
..........................................
Dump file set for SYSTEM.SYS_EXPORT_TABLE_02 is:
/u01/app/dirs/d1/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_02" successfully completed at Sun Aug 31 01:30:12 2014 elapsed 0 00:00:35
## Now lets import the TAB_STS table at destination with datapump
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=d1
...........
.............
........Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."TAB_STS" 2.328 MB 4727 rows
...........................
......................................
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Aug 31 01:33:01 2014 elapsed 0 00:00:41
## Once we import then we can unpack the sql tuning set with the following code
BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => '%',
sqlset_owner => 'SYS',
replace => TRUE,
staging_table_name => 'TAB_STS',
staging_schema_owner => 'SYSTEM');
END;
/
PL/SQL procedure successfully completed.
_______________________________________
When we are upgrading the database from 10g to 11g we usually populate the 10g sql tuning sets with sql statements and then we load the same to 11g sql plan baseline to preserve the performance of the oracle database , how we do it let us see
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'STS_KD');
END;
/
PL/SQL procedure successfully completed.
_______________________________________
To display the contents of an STS:
SQL>COLUMN SQL_TEXT FORMAT a30
SQL>COLUMN SCH FORMAT a3
SQL>COLUMN ELAPSED FORMAT 999999999
SQL> SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT,
ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'STS_KD' ) );
SQL_ID SCH SQL_TEXT ELAPSED BUFFER_GETS
------------- --- ------------------------------ ---------- -----------
14pxsqq5qw6kb SCO select * from sales where obje 1048699 315093
TT ct_id=1000
2xtm4g9sfzyw0 SCO select * from sales where obje 241277 44436
TT ct_id<1000
4d45hfmk7khhp SCO select * from sales where obje 47424 2120
TT ct_id>1000
946ddvxj22pnd SCO SELECT /* OPT_DYN_SAMP */ /*+ 4498 74
TT ALL_ROWS IGNORE_WHERE_CLAUSE N
SQL_ID SCH SQL_TEXT ELAPSED BUFFER_GETS
------------- --- ------------------------------ ---------- -----------
O_PARALLEL(SAMPLESUB
agn7ru0spffyj SCO SELECT /* OPT_DYN_SAMP */ /*+ 4983 74
TT ALL_ROWS IGNORE_WHERE_CLAUSE N
O_PARALLEL(SAMPLESUB
cdtjaangp5x17 SCO create table sales as select 2180424 5025
TT * from sys.dba_objects
cx0ttf4y4ahxn SCO SELECT /* OPT_DYN_SAMP */ /*+ 4472526 4067
TT ALL_ROWS IGNORE_WHERE_CLAUSE N
SQL_ID SCH SQL_TEXT ELAPSED BUFFER_GETS
------------- --- ------------------------------ ---------- -----------
O_PARALLEL(SAMPLESUB
d6qnn0vpu8xmx SCO SELECT /* OPT_DYN_SAMP */ /*+ 8149 122
TT ALL_ROWS IGNORE_WHERE_CLAUSE N
O_PARALLEL(SAMPLESUB
fcaxkvxh5h4g6 SCO insert into sales select * fro 50204574 370599
TT m sales
gwkz0aduy1w41 SCO select * from sales 14774553 65032
TT
SQL_ID SCH SQL_TEXT ELAPSED BUFFER_GETS
------------- --- ------------------------------ ---------- -----------
10 rows selected.
_______________________________________
Modifying a SQL Tuning Set
Use the basic_filter predicate to filter the SQL from the STS defined on attributes of the SQLSET_ROW. The following example deletes all statements in the STS with fetch counts over 100:
BEGIN
DBMS_SQLTUNE.DELETE_SQLSET (
sqlset_name => 'STS_KD'
, basic_filter => 'fetches > 100'
);
END;
/
PL/SQL procedure successfully completed.
Set attribute values for SQL statements.
The following example sets the priority of statement 7nr1kb47kw04j to 1:
BEGIN
DBMS_SQLTUNE.UPDATE_SQLSET (
sqlset_name => 'STS_KD'
, sql_id => '7nr1kb47kw04j'
, attribute_name => 'PRIORITY'
, attribute_value => 1
);
END;
/
PL/SQL procedure successfully completed.
_______________________________________
In the case of multitenant architecture Oracle 12C we can transport SQL Tuning Sets from a Non-CDB to a CDB
Whenever we are shipping sql tuning sets from a non-CDB to a CDB database, we must remap the con_dbid of each SQL statement in the STS to a con_dbid within the destination CDB. The steps to do it are as follows:
Firstly pack the STS into a staging table using DBMS_SQLTUNE.PACK_STGTAB_SQLSET, Secondly remap each con_dbid in the staging table using DBMS_SQLTUNE.REMAP_STGTAB_SQLSET, Thirdly export the STS, Fourthly unpack the STS in the destination CDB.
Lets see what is the PL/SQL code and how we can do it, in this example we re remapping con_dbid 1031 to 1032:
BEGIN
DBMS_SQLTUNE.REMAP_STGTAB_SQLSET (
staging_table_name => 'sts_kd1'
, staging_schema_owner => 'STAGE_USER'
, old_con_dbid => 1031
, new_con_dbid => 1032
);
END;
/
_______________________________________
In the past versions before 12C there have been some bugs, problems so let us take a brief look at them and see what solution did we implement to solve those bugs and problems.
SQL> BEGIN
DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'STS_KD');
END;
/
ORA-13757: "SQL Tuning Set" "STS_KD" is active.
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 2912
ORA-06512: at "SYS.DBMS_SQLTUNE", line 482 ORA-06512: at line 1
#### This is an nnpublished bug 5742773 - SQLTUNE TASKS ARE NOT DROPPED DUE TO PHANTOM REFERENCES.
Make sure that you delete all the Advisor Tasks :
Unpublished bug 5742773 - (SQLTUNE TASKS ARE NOT DROPPED DUE TO PHANTOM REFERENCES)
The above bug is fixed in 11g.
We can also do a workaround of it by doing the following
SQL> delete from wri$_sqlset_references where sqlset_id in (select id from wri$_sqlset_definitions where name ='<name of the SQL Tuning Set>');
SQL>commit;
SQL> BEGIN
DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'STS_KD');
END;
/
## The best practise is to upgrade your database to atleast 11.2.0.2 or higher.
Another problem faced by some DBA's for the below code
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK was run with execution_type set to 'COMPARE PERFORMANCE'.
SQL> declare
ename varchar2(512);
BEGIN
ename := dbms_sqlpa.execute_analysis_task(
task_name => '10gr2_11gr2_spa',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'compare_102_112_io',
execution_params =>
dbms_advisor.arglist('COMPARISON_METRIC','USER_IO_TIME','EXECUTION_NAME1','10gr2_STS_trial','EXECUTION_NAME2','11gr2_STS_trial'),
execution_desc => 'comp_user_io_time');
END;
/
ERROR at line 1:
ORA-13793: invalid comparison metric
ORA-06512: at "SYS.PRVT_ADVISOR", line 2758
ORA-06512: at "SYS.DBMS_ADVISOR", line 241
ORA-06512: at "SYS.DBMS_SQLPA", line 342
ORA-06512: at "SYS.DBMS_SQLPA", line 359
ORA-06512: at line 37
Comparison_metric being compared is: USER_IO_TIME, PARSE_TIME or IO_INTERCONNECT_BYTES SQL Tuning Set was previously converted for the analysis task using 'CONVERT SQLSET'
Following error occurs:
11.2.0.4 and later:
ORA-13793: invalid comparison metric
11.2.0.3 and earlier:
ORA-904: "USER_IO_TIME": invalid identifier
Oracle says this is expected behavior.
At the time of DBMS_SQLPA.EXECUTE_ANALYSIS_TASK being executed, the information for the metrics above are not yet available in the SQL Tuning Set (ie DBA_SQLSET_STATEMENTS). An unpublished internal bug has been raised with development who are working on enhancing the statistics for the SQL Tuning Sets: Bug 13946904 MISSING METRICS STATS FOR PARSE AND USER_IO_TIME FOR CONVERTED STS TRIAL
Until then you can workaround the issue by comparing a "TEST EXECUTE" trial with a second "TEST EXECUTE" trial in order to get reliable values for these statistics.
________________________
# Below are the dictionary views relative to sql tuning set
SQL> desc dba_sqlset
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
OWNER VARCHAR2(30)
DESCRIPTION VARCHAR2(256)
CREATED DATE
LAST_MODIFIED DATE
STATEMENT_COUNT NUMBER
SQL> desc dba_sqlset_statements
Name Null? Type
----------------------------------------- -------- ----------------------------
SQLSET_NAME NOT NULL VARCHAR2(30)
SQLSET_OWNER VARCHAR2(30)
SQLSET_ID NOT NULL NUMBER
SQL_ID NOT NULL VARCHAR2(13)
FORCE_MATCHING_SIGNATURE NOT NULL NUMBER
SQL_TEXT CLOB
PARSING_SCHEMA_NAME VARCHAR2(30)
PARSING_SCHEMA_ID NUMBER
PLAN_HASH_VALUE NOT NULL NUMBER
BIND_DATA RAW(2000)
BINDS_CAPTURED CHAR(1)
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
ROWS_PROCESSED NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(2000)
PRIORITY NUMBER
COMMAND_TYPE NUMBER
FIRST_LOAD_TIME VARCHAR2(19)
STAT_PERIOD NUMBER
ACTIVE_STAT_PERIOD NUMBER
OTHER CLOB
PLAN_TIMESTAMP DATE
SQL_SEQ NOT NULL NUMBER
__________________________________
## References
http://docs.oracle.com/database/121/LNPLS/toc.htm
http://docs.oracle.com/database/121/ADMIN/toc.htm
http://docs.oracle.com/database/121/TGSQL/toc.htm
Subscribe to:
Posts (Atom)