SQL profile是一个SQL语句的表或索引的对象级的统计数据。调用SQL Tuning Advisor进行SQL profile创建
在分析SQL语句时，SQL Tuning Advisor会使用一组特定的绑定值作为输入，然后比较优化估计与执行该语句的片段上的数据采样获得的值。当发现显著差异，SQL Tuning Advisor会捆绑纠正措施一起在一个SQL PROFILE，然后建议其接受。
在SQL PROFILE校正的统计信息可以改进优化的基数估计，这反过来又导致优化器选择更好的计划。
1不同于hints 和 stored
outline，SQL profile不指定优化器到一个特定的计划或子计划。 SQL profile修正不正确的估计，同时给优化器可以灵活地选择最佳的方案。
2不同于hints，无需更改应用程序源代码、。由数据库使用SQL访问是对用户透明。
创建环境
SQL> create table profile_test tablespace users as select * from dba_objects;
Table created.
SQL> create index ix_objd on profile_test(object_id);
Index created.
SQL> set linesize 200 pagesize 2000
SQL> exec dbms_stats.gather_table_stats('','PROFILE_TEST');
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly;
SQL> select /*+ FULL( profile_test) */ * from profile_test where object_id=5060;
Execution Plan
----------------------------------------------------------
Plan hash value: 663678050
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 444 (2)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| PROFILE_TEST | 1 | 93 | 444 (2)| 00:00:06 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=5060)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2470 consistent gets
0 physical reads
0 redo size
1413 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL> select /*+ FULL( profile_test) */ * from profile_test where object_id=5060;
OWNER OBJECT_NAME SUBOBJECT_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S
---------- -------------- ------------------- --------- --------- ------------------- ------- - - -
SYS SCHEDULER$_JOB_EXTERNAL
5060 TYPE 22-OCT-05 22-OCT-05 2005-10-22:21:49:42 VALID N N N
SQL> select sql_text,sql_id from v$sql a where a.SQL_FULLTEXT like '%profile_test%' and a.SQL_FULLTEXT like '%FULL%';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID
-------------
EXPLAIN PLAN SET STATEMENT_ID='PLUS4308718' FOR select /*+ FULL( profile_test) */ * from profile_test where object_id=5060
dsd1y6w0rgsj8
select /*+ FULL( profile_test) */ * from profile_test where object_id=5060
9xjbpjspsvws4
1 创建和执行优化任务
支持sql_id和sql text两种方式
SQL> DECLARE
2 my_task_name VARCHAR2(50);
3 my_sql_id VARCHAR2(64);
4 BEGIN
5 my_sql_id := '5xg48sy8tjbp5';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_id => '9xjbpjspsvws4',
8 scope => 'COMPREHENSIVE',
9 time_limit => 60,
10 task_name => '9xjbpjspsvws4_tuning_task',
11 description => 'Task to tune a query on a specified table');
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '9xjbpjspsvws4_tuning_task');
3 end;
4 /
PL/SQL procedure successfully completed.
2查看优化报告
SQL> SELECT to_char(DBMS_SQLTUNE.REPORT_TUNING_TASK( '9xjbpjspsvws4_tuning_task')) from DUAL;
TO_CHAR(DBMS_SQLTUNE.REPORT_TUNING_TASK('9XJBPJSPSVWS4_TUNING_TASK'))
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 9xjbpjspsvws4_tuning_task
Tuning Task Owner : TEST
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 07/24/2014 11:49:53
Completed at : 07/24/2014 11:49:53
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID : 9xjbpjspsvws4
SQL Text : select /*+ FULL( profile_test) */ * from profile_test where
object_id=5060
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.54%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'9xjbpjspsvws4_tuning_task', replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 663678050
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 444 (2)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| PROFILE_TEST | 1 | 93 | 444 (2)| 00:00:06 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=5060)
2- Using SQL Profile
--------------------
Plan hash value: 938196023
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PROFILE_TEST | 1 | 93 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_OBJD | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=5060)
3 接受sql profile
execute dbms_sqltune.accept_sql_profile(task_name =>
'9xjbpjspsvws4_tuning_task', replace => TRUE);
PL/SQL procedure successfully completed.
4 再次查看执行计划
SQL> set autotrace traceonly;
SQL> select /*+ FULL( profile_test) */ * from profile_test where object_id=5060;
Execution Plan
----------------------------------------------------------
Plan hash value: 938196023
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PROFILE_TEST | 1 | 93 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_OBJD | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=5060)
Note
-----
- SQL profile "SYS_SQLPROF_0151d99cf8b60000" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1417 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
5 删除优化任务
SQL> begin
2 dbms_sqltune.drop_tuning_task('9xjbpjspsvws4_tuning_task');
3 end;
4 /
PL/SQL procedure successfully completed
6 删除sql pfofile
SQL> BEGIN
2 DBMS_SQLTUNE.DROP_SQL_PROFILE (
3 name => 'SYS_SQLPROF_0151d99cf8b60000'
4 );
5 END;
6 /
PL/SQL procedure successfully completed.