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
No comments:
Post a Comment