Tuesday 30 September 2014

All India Oracle Users Group (AIOUG) North India Chapter 7th Conference

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 .

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

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.

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




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 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