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 ABC, only those users sessions where the SQLTUNE_CATEGORY initialization parameter is set to ABC 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)
Automatic SQL Tuning - SQL Profiles
----------------------------------------
In this Demo, I have demonstrated 2 things.
1. How to create SQL PROFILE and accept that.
2. How to control any user from using newely accepted prolfile.
Section-1:
---------------
PART-1: In this Part i'll show you how to generate SQL PROFILE and how to accept that profile.
---------------------------------------------------------------------------------------------
From Scott
------------
1) create table test (n number );
2)
declare
begin
for i in 1 .. 10000 loop
insert into test values(i);
commit;
end loop;
end;
/
3) create index test_idx on test(n);
4) exec dbms_stats.gather_table_stats('','TEST');
5) Here you can see I am using no_index hint so optimzer will go for full table scan, which is but obvious.
SQL> set autotrace traceonly
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
FROM SYS USER
--------------------
6) Now, run SQLTUNING task to generate SQL PROFILE and accept the recommended 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;
/
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');
end;
/
7) Now, see the report for SQL PROFILE recommendation.
SQL> set long 1000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------------
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 : 10/26/2011 15:07:04
Completed at : 10/26/2011 15:07:08
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
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)
--------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------------
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);
9) Finally accept recommended profile.
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;
/
AGAIN FROM SCOTT USER
-------------------------------
10) This time if you will fire the same query again, optimizer will ignore no_index hint and will go for index scan because it will use SQL PROFILE.
SQL> set autotrace traceonly
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
PART-2:
In part-2 of Section-1, I'll Show you that if current situation is different and you have new statistics for your query and if we have better plan for the query then
optimzer will accept new plan with the help of new statistics and SQL PROFILE.
(NOTE- 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.)
1) In your table again inert lots of 1's so that record will be repeated and then see what happen. Will optimizer choose SQL PROFILE or not?
SQL> declare
begin
for i in 1 .. 1000000 loop
insert into test values(1);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('','TEST');
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
1010001 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1011K| 2964K| 450 (2)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| TEST | 1011K| 2964K| 450 (2)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=1)
Note
-----
- SQL profile "my_sql_profile" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
70296 consistent gets
2581 physical reads
0 redo size
17372507 bytes sent via SQL*Net to client
741186 bytes received via SQL*Net from client
67335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1010001 rows processed
SQL>
So finally you can see, optimizer is using SQL profile and new statistics and going for the full table scan, but it is not going for index scan (According to the SQL
PROFILE).
SECTION-2: HOW TO AVOID ANY USER TO USE SQL PROFILE
====================================================
There are 2 method to implement this.
Method-1:
---------
All the profile accepted, they all are accepted as "DEFAULT" CATEGORY. So if you want to avoid any user from using that newely accepted profile then just change their
session parameter "SQLTUNE_CATEGORY" and put any other category name.
Method-2:
---------
While accepting the sql profile change the category option to any thing else but not "DEFAULT".
1)
SQL> alter session set sqltune_category=abc;
Session altered.
2) After changing their session parameter, now scott user will not use the accepted profile.
SQL> set autotrace traceonly
select /*+ no_index(test test_idx) */ * from test where n=1;
SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3) Again change the session parameter to default then scott user again start using accepted profile for their query.
SQL> alter session set sqltune_category=default;
Session altered.
SQL>
SQL>
SQL>
SQL> set autotrace traceonly
select /*+ no_index(test test_idx) */ * from test where n=1;
SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| 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
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
No comments:
Post a Comment