Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1)
Information in this document applies to any platform.
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.
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.
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.
SESSION 2 -- SYS
Create and execute tuning task and run report tuning task.
Accept recommended SQL Profile
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
====================================================
Note--> Information collected from oracle SR for knowledge purpose. it's not a commercial purpose.. All rights reserved to Oracle.
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and laterInformation 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.
This FAQ answers basic questions on using SQL Profiles and provides an example using DBMS_SQLTUNE package.
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.
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.
The following documentation provides more information about the SQL Tuning Advisor:
Oracle® Database 2 Day DBA
12c Release 1 (12.1)
E17643-13
10 Monitoring and Tuning the Database
About the SQL Tuning Advisor
12c Release 1 (12.1)
E17643-13
10 Monitoring and Tuning the Database
About the SQL Tuning Advisor
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 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)
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
- On the Performance page, click Top Activity.
The Top Activity page appears. - Under Top SQL, click the SQL ID link of the SQL statement that is using a SQL profile.
The SQL Details page appears. - Click the Plan Control tab.
A list of SQL profiles is displayed under SQL Profiles and Outlines. - 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.
- 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_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.
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'my_sql_profile',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
DBMS_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.
begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
end;
/
DBMS_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
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.
Table created.
SQL> declare
begin
for i in 1 .. 10000 loop
insert into test values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
begin
for i in 1 .. 10000 loop
insert into test values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> create index test_idx on test(n);
Index created.
Index created.
SQL> exec dbms_stats.gather_table_stats('','TEST');
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
set autotrace on
select /*+ no_index(test test_idx) */ * from test where n=1;
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
declare
my_task_name VARCHAR2(30);
my_sqltext CLOB;
begin
my_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.
my_task_name VARCHAR2(30);
my_sqltext CLOB;
begin
my_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.
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');
end;
/
PL/SQL procedure successfully completed.
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');
end;
/
PL/SQL procedure successfully completed.
set long 10000
set longchunksize 1000
set linesize 100
set heading off
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;
set heading on
set longchunksize 1000
set linesize 100
set heading off
SELECT 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_2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 09/24/2012 12:36:44
Completed at : 09/24/2012 12:36:49
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : d4wgpc5g0s0vu
SQL 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)
-------------------------------------------------------------------------------
DECLARE
my_sqlprofile_name VARCHAR2(30);
begin
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task_2',
name => 'my_sql_profile');
end;
/
PL/SQL procedure successfully completed.
my_sqlprofile_name VARCHAR2(30);
begin
my_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;
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
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
====================================================
Note--> Information collected from oracle SR for knowledge purpose. it's not a commercial purpose.. All rights reserved to Oracle.
Comments
Post a Comment
Oracle DBA Information