Queries 3

 Queries 3

Automatic SQL Tuning - SQL Profiles. [ID 271196.1]

Document 262687.1 How to use the Sql Tuning AdvisorDocument 457531.1 How To Move SQL Profiles From One Database To Another DatabaseDocument 1253696.1 How To Use SQL Profiles for Queries Using Different Literals

References

NOTE:262687.1 - Using the DBMS_SQLTUNE package to Run the Sql Tuning AdvisorNOTE:457531.1 - How To Move SQL Profiles From One Database To Another DatabaseNOTE:1253696.1 - How To Use SQL Profiles for Queries Using Different Literals=================

Automatic SQL Tuning -

---

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later

Information in this document applies to any platform.

Purpose

SQL Profiles was a feature introduced in 10g and managed through the DBMS_SQLTUNE package or through Oracle Enterprise Manager as part of the Automatic SQL Tuning process.This FAQ answers basic questions on using SQL Profiles and provides an example using DBMS_SQLTUNE package.

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Questions and Answers

What is Automatic SQL Tuning?

The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information,leading to poor execution plans. Traditionally, users have had to correct this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for a fix.

Automatic SQL Tuning deals with this problem with its SQL Profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. The query optimizer under normal mode makes

estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates.

During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the OPTIMIZER_MODE initialization parameter setting from ALL_ROWS to FIRST_ROWS for that SQL statement.

The output of this type of analysis is a recommendation to accept the SQL Profile.

An SQL Profile, once accepted, is stored persistently in the data dictionary. An SQL Profile is specific to a particular query. If accepted, the optimizer under normal mode uses the information in the SQL Profile in conjunction with regular database statistics when generating an execution plan.

The availability of the additional information makes it possible to produce well-tuned plans for corresponding SQL statement without requiring any change to the application code.

What is a SQL Profile ?

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans.

The SQL profile does not contain information about individual execution plans. Rather, the optimizer has the following sources of information when choosing plans:

• The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on
• The supplemental statistics in the SQL profile

It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile. The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. However, over a long period of time, its content can become outdated and would have to be regenerated. This can be done by running Automatic SQL Tuning again on the same statement to regenerate the SQL Profile.

How can the scope of the SQL Profile be controlled ?

The scope of a SQL Profile can be controlled by the CATEGORY profile attribute. This attribute determines which user sessions can apply the profile. You can view the CATEGORY attribute for a SQL Profile in CATEGORY column of the DBA_SQL_PROFILES view.

select category,name from dba_sql_profiles;

By default, all profiles are created in the DEFAULT category. This means that all user sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.

By altering the category of a SQL profile, you can determine which sessions are affected by the creation of a profile. For example, by setting the category of a SQL Profile to DEV, only those users sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile. All other sessions do not have access to the SQL Profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a SQL Profile in a restricted environment before making it available to other user sessions.

To what statements can a SQL Profile be applied?

SELECT statements

UPDATE statements

INSERT statements (only with a SELECT clause)

DELETE statements

CREATE TABLE statements (only with the AS SELECT clause)

MERGE statements (the update or insert operations)

How can SQL Profiles be managed ?

SQL Profiles can be handled by Oracle Enterprise Manager as part of the Automatic SQL Tuning process or manually by using DBMS_SQLTUNE package.

Using Enterprise Manager

1. On the Performance page, click Top Activity.
   The Top Activity page appears.
2. Under Top SQL, click the SQL ID link of the SQL statement that is using a SQL profile.
   The SQL Details page appears.
3. Click the Plan Control tab.
   A list of SQL profiles is displayed under SQL Profiles and Outlines.
4. Select the SQL profile you want to manage.
   Do one of the following:
   • To enable a SQL profile that is disabled, click Disable/Enable.
   • To disable a SQL profile that is enabled, click Disable/Enable.
   • To remove a SQL profile, click Delete.
5. A confirmation page appears.
   Click Yes to continue, or No to cancel the action.

Using DBMS_SQLTUNE package.

To use the SQL Profiles APIs, you need the CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, and ALTER ANY SQL_PROFILE system privileges.

Accepting a SQL Profile

Use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database.

DECLARE

my_sqlprofile_name VARCHAR2(30);

BEGIN

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

task_name => 'my_sql_tuning_task',

name => 'my_sql_profile');

END;

my_sql_tuning_task is the name of the SQL tuning task.

You can view information about a SQL Profile in the DBA_SQL_PROFILES view.

Altering a SQL Profile

Alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an existing SQL Profile with the ALTER_SQL_PROFILE procedure.

BEGINDBMS_SQLTUNE.ALTER_SQL_PROFILE(name => 'my_sql_profile',attribute_name => 'STATUS',value => 'DISABLED');END;/

In this example, my_sql_profile is the name of the SQL Profile that you want to alter.

The status attribute is changed to disabled which means the SQL Profile will not be used during SQL compilation.

Dropping a SQL Profile

A SQL Profile can be dropped using the DROP_SQL_PROFILE procedure.

beginDBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');end;/

===Example===

SESSION 1 -- SCOTT

Create table, populate, create index and gather statistics

Execute query with no_index hint

Full Table Scan used

SQL> create table test (n number );

Table created.

SQL> declare

beginfor i in 1 .. 10000 loopinsert into test values(i);commit;end loop;end;/

PL/SQL procedure successfully completed.

SQL> create index test_idx on test(n);

Index created.

SQL> exec dbms_stats.gather_table_stats('','TEST');

PL/SQL procedure successfully completed.

set autotrace on

select /*+ no_index(test test_idx) */ * from test where n=1;

Plan hash value: 217508114--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4 | 4 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 4 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("N"=1)

SESSION 2 -- SYS

Create and execute tuning task and run report tuning task.

Accept recommended SQL Profile

declaremy_task_name VARCHAR2(30);my_sqltext CLOB;beginmy_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,user_name => 'SCOTT',scope => 'COMPREHENSIVE',time_limit => 60,task_name => 'my_sql_tuning_task_2',description => 'Task to tune a query on a specified table');end;/

PL/SQL procedure successfully completed.

beginDBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');end;

/

PL/SQL procedure successfully completed.

set long 10000set longchunksize 1000set linesize 100set heading offSELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;set heading on

Output:

GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name : my_sql_tuning_task_2Tuning Task Owner : SYSWorkload Type : Single SQL StatementScope : COMPREHENSIVETime Limit(seconds): 60Completion Status : COMPLETEDStarted at : 09/24/2012 12:36:44Completed at : 09/24/2012 12:36:49-------------------------------------------------------------------------------Schema Name: SCOTTSQL ID : d4wgpc5g0s0vuSQL Text : select /*+ no_index(test test_idx) */ * from test where n=1-------------------------------------------------------------------------------FINDINGS SECTION (1 finding)-------------------------------------------------------------------------------1- SQL Profile Finding (see explain plans section below)--------------------------------------------------------1- SQL Profile Finding (see explain plans section below)--------------------------------------------------------A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 90.95%)------------------------------------------ -Consider accepting the recommended SQL profile.execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_2', 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): .001004 .000331 67.03 % CPU Time (s): .001 0 100 % User I/O Time (s): 0 0 Buffer Gets: 22 2 90.9 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 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.-------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------1- Original With Adjusted Cost------------------------------Plan hash value: 217508114--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4 | 4 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 4 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("N"=1)
2- Using SQL Profile--------------------Plan hash value: 1416057887-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 4 | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - access("N"=1)-------------------------------------------------------------------------------

DECLAREmy_sqlprofile_name VARCHAR2(30);beginmy_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name => 'my_sql_tuning_task_2',name => 'my_sql_profile');end;/

PL/SQL procedure successfully completed.

SESSION 1 -- SCOTT

Run query again

Even with no_index hint , index is used

Note: In Explain Plan we see "SQL profile "my_sql_profile" used for this statement

SQL> set autotrace on

SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

Execution Plan-------------------------------------------------------------------------Plan hash value: 1416057887-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 4 | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - access("N"=1)---------------Note------ SQL profile "my_sql_profile" used for this statement

===How do I produce a report of every tuning set?===

SELECT 'SELECT d.id , d.owner , d.description , d.created , d.last_modified , d.statement_count, ss.*FROM TABLE(DBMS_SQLTUNE.select_sqlset ('''||name||''')) ss, dba_sqlset d WHERE d.name='''||name||''';'FROM dba_sqlset d ORDER BY d.last_modified DESC

=============

Using the DBMS_SQLTUNE package to Run the Sql Tuning Advisor [ID 262687.1]

---

Purpose

This article provides an introduction to the use of the SQL TUNING ADVISOR (STA) via the DBMS_SQLTUNE package.

Scope

DBAs and Support Analysts

Details

The SQL Tuning Advisor automates the entire SQL tuning process replacing manual SQL tuning. It analyzes candidate SQL statements, and executes a complete analysis of the statements including:

• Determining stale or missing statistics
• Determining better execution plans
• Detecting better access paths and objects required to satisfy them (indexes, materialized views)
• Restructuring SQL

While the primary interface for the SQL Tuning Advisor is the Oracle Enterprise Manager Database Control, the advisor can also be controlled with procedures in the DBMS_SQLTUNE package. Output can be queried via the various advisory views in SQL*Plus.

To use these procedures the user must have been granted the DBA role and the ADVISOR privilege (If using the SQL Tuning Advisor in Oracle Enterprise Manager, the user must have been granted the select_catalog_role role).

Running SQL Tuning Advisor using the DBMS_SQLTUNE package is a two-step process:

1. Create a SQL tuning task
2. Execute a SQL tuning task

Note: You can run SQL Tuning Advisor from the command line using $ORACLE_HOME/rdbms/admin/sqltrpt.sql

Example:

This example is based on the "SH" account executing the various tasks. To allow the "SH" user to both create task and execute it user "SH" needs to be granted proper access:

CONNECT / AS SYSDBA GRANT ADVISOR TO SH; GRANT SELECT_CATALOG_ROLE TO SH; GRANT EXECUTE ON DBMS_SQLTUNE TO SH;

The example presented makes use of a table called SALES, residing in the

SH schema. The table Is not analyzed.

• Create a SQL tuning task
  You can create tuning tasks from the following:
  • SQL statement selected by SQL identifier from the cursor cache
  • SQL Tuning Set containing multiple statements
  • Text of a single SQL statement
  • SQL statement selected by SQL identifier from the Automatic Workload Repository.
    
    
  You can create the task using the following:
  
  
  DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB; BEGIN my_sqltext := 'SELECT * ' || 'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 '; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext, user_name => 'SH', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'TEST_sql_tuning_task', description => 'Task to tune a query on a specified PRODUCT'); END;
  
  
  Parameter explanation:
  • User_name: User under which the CREATE_TUNING_TASK function analyzes the SQL statement.
  • Scope: COMPREHENSIVE. This means that the advisor also performs SQL Profiling analysis
  • Time_limit: Time in seconds that the function can run.
    
    
  The CREATE_TUNING_TASK function simply creates the task and returns either the task name that you have provided or generates a unique task name. This task name is the identifier by which you execute it (or perform other procedures upon it). To view the task names associated with a specific owner, you can run the following:
  select task_name from dba_advisor_log where owner='SH';
• Execute the tuning task.:
  Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');
• Check the status of the task using following query:
  select status from dba_advisor_log where task_name='TEST_sql_tuning_task';
• View the Recommendations
  set long 65536 set longchunksize 65536 set linesize 100 select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task') from dual;
  The output of above will be like this:
  DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
• --------------------------------------------------------------------------------
• GENERAL INFORMATION SECTION -------------------------------------------------------------------------------
• Tuning Task Name : TEST_sql_tuning_task Scope : COMPREHENSIVE
• Time Limit(seconds): 60
• Completion Status : COMPLETED
• Started at : 02/04/2004 23:17:49
• Completed at : 02/04/2004 23:18:19 -
• ------------------------------------------------------------------------------
• SQL ID : 9bxw71yp99fr6 DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
• --------------------------------------------------------------------------------
• SQL Text: SELECT * FROM sales WHERE prod_id = 10 AND cust_id = 100
• -------------------------------------------------------------------------------
• FINDINGS SECTION (5 findings)
• -------------------------------------------------------------------------------
• 1- Statistics Finding
• ---------------------
• Index "SH"."SALES_PROMO_BIX" was not analyzed.
• Recommendation DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
• --------------------------------------------------------------------------------
• Consider collecting optimizer statistics for this index. execute dbms_stats.gather_index_stats(ownname => 'SH', indname => 'SALES_PROMO_BIX', estimate_percent =>
  
  
  SQL tuning information views, such as DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS, and DBA_SQLTUNE_PLANS views can also be queried to get this information.
  
  
  Note: it is possible for the SQL Tuning Advisor to return no recommendations for a particular SQL statement e.g. in cases where the plan is already optimal or the Automatic Tuning Optimization mode cannot find a better plan.

=================================

||How to Move SQL Profiles from One Database to Another (Including to Higher Versions) [ID 457531.1

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]

Information in this document applies to any platform.

Checked for relevance on 09-SEP-2010

Goal

The purpose of this document is to assist in moving an SQL Profile implemented on one database to another database.

Note that you can transport a SQL profile to any Oracle database created in the same release or later. The reverse may not be true as profiles are not guaranteed to be backwardly compatible.

See:

Oracle® Database Performance Tuning Guide

11g Release 2 (11.2)

E16638-07

Chapter 17 Automatic SQL Tuning

Section 17.5.5 Transporting a SQL Profile

==Solution==

What is a SQL Profile?

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans..

Managing SQL Profiles

For information on SQL Profiles see:

Document 271196.1 Automatic SQL Tuning - SQL Profiles

===Steps to Create and Transfer Profile from One Database to Another===

The following example illustrates the process of moving a SQL Profile from 10.2.0.4.0 to 11.2.0.3.0

1. Create SQL Profile in SCOTT schema

The SQL Profile is created based on the tuning task created and the recommendations given by the tuning task:

DECLARE my_task_name VARCHAR2(30);my_sqltext CLOB;my_sqlprofile_name VARCHAR2(30);BEGIN my_sqltext := 'select /*+ no_index(emp pk_emp) */ * from emp where empno=7839';my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext, user_name => 'SCOTT', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'my_sql_tuning_task', description => 'Demo Task to tune a query'); DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task'); my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'my_sql_tuning_task', name => 'my_sql_profile');END;
/ PL/SQL procedure successfully completed.set lines 130 set autotrace on select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------7839 KING PRESIDENT 17-NOV-81 5000 10Execution Plan ----------------------------------------------------------Plan hash value: 4066871323--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("EMPNO"=7839)Note----- -SQL profile "my_sql_profile" used for this statementNote: Even though no_index hint included, the plan uses an index as determined by the SQL profile. The Note section provides plan information that indicates that "my_sql_profile" is used.

===2. Creating a staging table to store the SQL Profiles===

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT'); PL/SQL procedure successfully completed.

• table_name => name of the table to store the SQL Profiles.
• schema_name => name of the schema where the table is to be created.

3. Pack the SQL Profiles into the Staging Table

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile');PL/SQL procedure successfully completed.

• staging_table_name => name of the table to store the SQL Profiles.
• profile_name => name of the SQL Profile to be packed.

Note: The table_name and schema_name are case-sensitive.

SQL> desc STAGE Name Null?Type----------------------------------------- -------- ----------------------------PROFILE_NAME VARCHAR2(30) CATEGORY VARCHAR2(30) SIGNATURE NUMBER SQL_TEXT CLOB DESCRIPTION VARCHAR2(500) TYPE VARCHAR2(9) STATUS VARCHAR2(8) BOOLEAN_FLAGS NUMBER ATTRIBUTES SQLPROF_ATTR VERSION NUMBER SPARE1 CLOB SPARE2 BLOB

===4. Export the Staging Table to the Target Database===

Using Datapump or Export/Import transfer the table to the target database where you would like to create the same profile.

4a. Export from Source Database

my_linux_1:~> exp scott/tiger tables=STAGE Export: Release 10.2.0.4.0 - Production on Sun Feb 12 17:43:21 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table STAGE 1 rows exported Export terminated successfully without warnings.

====4b. Import into Target Database====

my_linux_1:~> imp scott/tiger tables=STAGE Import: Release 11.2.0.3.0 - Production on Mon Feb 13 14:49:12 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses WE8MSWIN1252 character set (possible charset conversion) . importing SCOTT's objects into SCOTT . importing SCOTT's objects into SCOTT . . importing table "STAGE" 1 rows imported Import terminated successfully with warnings.

===5. Unpack the SQL Profiles===

5a. Test before unpacking

SQL> set lines 130 SQL> set autotrace onSQL> select /*+ no_index(emp pk_emp) */ * from emp where empno=7839;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------7839 KING PRESIDENT 17-NOV-81 5000 10Execution Plan----------------------------------------------------------Plan hash value: 2872589290--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("EMPNO"=7839)Note: The NO_INDEX hint has been honoured and a FULL table scan has been done on EMP

====5b. Unpack Staging Table====

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE'); PL/SQL procedure successfully completed.

===6. Check the SQL Profile is enabled in Target Database===

set lines 130 set autotrace onselect /*+ no_index(emp pk_emp) */ * from emp where empno=7839;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------7839 KING PRESIDENT 17-NOV-81 5000 10Execution Plan----------------------------------------------------------Plan hash value: 4066871323--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("EMPNO"=7839)Note----- -SQL profile "my_sql_profile" used for this statement=====================================================================================================

How To Use SQL Profiles for Queries Using Different Literals [ID 1253696.1]

---

---

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.1.0 - Release: 10.2 to 11.2

Information in this document applies to any platform.

Goal

1. To create SQL Profiles for the queries using literals where in the different literal values are passed for every execution.

2. To make the SQL Profile used for SQL with the changing literal values in every execution.

Default behaviour: By default, if SQL Profile is created for the sql with literals, then the profile would be used only for the SQL with whatever literals used while running the tuning advisor. For the same sql with only change in literals, the profile would not be used.

Solution

Showing default behaviour of SQL Profile.

Example:

SQL> create table test (n number );

Table created.

declarebeginfor i in 1 .. 10000loopinsert into test values(i);commit;end loop;end;

/

PL/SQL procedure successfully completed.

create index test_idx on test(n);Index created.

analyze table test estimate statistics (OR use dbms_stats)Table analyzed.

select /*+ no_index(test test_idx) */ * from test where n=1Execution Plan

---

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=13)1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=6 Card=1 Bytes=13)

Let us just use SQL Tuning Advisor for the following sql to get recommendations out of it.

select /*+ no_index(test test_idx) */ * from test where n=1;

SQL> DECLARE2 my_task_name VARCHAR2(30);3 my_sqltext CLOB;4 BEGIN5 my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(7 sql_text=> my_sqltext,8 user_name => 'SCOTT',9 scope => 'COMPREHENSIVE',10 time_limit => 60,11 task_name => 'my_sql_tuning_task_2',12 description => 'Task to tune a query on a specified table');13 END;14

PL/SQL procedure successfully completed.

SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');PL/SQL procedure successfully completed.

SQL> set long 2000SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;

FINDINGS SECTION (1 finding)

1- SQL Profile Finding (see explain plans section below)DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 84.03%)

---

- Consider accepting the recommended SQL profile.

execute dbms_sqltune.accept_sql_profile(task_name =>my_sql_tuning_task_2', replace => TRUE);

Now the SQL is created for the above sql.

SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE);

PL/SQL procedure successfully completed.

If we execute the sql it uses the SQL Profile only for particular literals which are passed during the creation.

SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

N1Execution PlanPlan hash value: 2882402178

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |

Predicate Information (identified by operation id):1 - access("N"=1)Note- SQL profile "SYS_SQLPROF_014af9c017890000" used for this statementBut if we change the literal to another, the the profile would not be used and optimizer uses different plan.

SQL> select /*+ no_index(test test_idx) */ * from test where n=2;N2

Execution PlanPlan hash value: 1357081020

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

|| 0 | SELECT STATEMENT | | 1 | 3 | 6 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST | 1 | 3 | 6 (0)| 00:00:01 |Predicate Information (identified by operation id):1 - filter("N"=2) => SQL profile not used with different literal

To maintain plan stability and make use of the SQL profile to be used for whatever literals passed, there is one option called FORCE_MATCH from 10.2.

If TRUE this causes SQL profiles to target all SQL statements which have the same text after normalizing all literal values into bind variables. (Note that if a combination of literal values and bind values is used in a SQL statement, no bind transformation occurs.) This is analogous to the matching algorithm used by the FORCE option of the cursor_sharing parameter.

Now the SQL profile is recreated with this option and after that it is used for whatever literals. It internally replaces the literals to binds.

SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE, force_match=>true);PL/SQL procedure successfully completed.

Now even if the literals are changed, the SQL profile gets used.

SQL> select /*+ no_index(test test_idx) */ * from test where n=10;N10Execution PlanPlan hash value: 2882402178

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |

Predicate Information (identified by operation id):1 - access("N"=10)

Note- SQL profile "SYS_SQLPROF_014af9c167e84001" used for this statement

=> SQL profile getting used with different literal using force_match

This FORCE_MATCH option is very good feature which we can use it for SQLs using literals so that the same execution plan is getting used.