1、准备测试数据
create table hy19 as select * from dba_objects;
2、创建索引并分析
CREATE INDEX INX_19 ON SCOTT.HY19(OBJECT_ID);
ANALYZE TABLE SCOTT.HY19 COMPUTE STATISTICS;
3. 执行SQL语句，模拟一个性能低下的执行计划
SELECT /*+ no_index(HY19 INX_19)*/ * FROM HY19 WHERE OBJECT_ID=1;
（此处用别名访问表时发现hint不起作用）
SQL> SELECT /*+ no_index(HY19 INX_19)*/ * FROM HY19 WHERE OBJECT_ID=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3188441247
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 159 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| HY19 | 1 | 87 | 159 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
4、创建优化任务：
–使用SQL TEXT
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ no_index(HY19 INX_19)*/ * FROM HY19 WHERE OBJECT_ID=1';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tuning_task_1',
description => 'Task to tune a query on a specified table');
END;
/
–使用SQL ID:
DECLARE
my_task_name VARCHAR2(50);
my_sql_id VARCHAR2(64);
BEGIN
my_sql_id := 'gh991ctttx3k7';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => my_sql_id,
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'rockey_sql_tuning_task_001',
description => 'Task to tune a query on a specified table');
END;
/
5、执行优化任务：
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'rockey_sql_tuning_task_001');
end;
/
6、查看优化建议：
SET WRAP ON
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 130
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'rockey_sql_tuning_task_001') from DUAL;
或者
SELECT dbms_advisor.GET_TASK_REPORT(task_name)
FROM dba_advisor_tasks
where task_name = 'rockey_sql_tuning_task_001';
7、SQL语句绑字SQL Profile：
begin
dbms_sqltune.accept_sql_profile(task_name => 'rockey_sql_tuning_task_001',
name => 'my_sql_profile_001');
end;
/
8. 再次执行相同的语句，验证结果
SQL> SELECT /*+ no_index(HY19 INX_19)*/ * FROM HY19 WHERE OBJECT_ID=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2066044106
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HY19 | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INX_19 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
这里可以看出，Oracle已经使用了不同的执行计划
9、查看语句是否使用了SQL Profile：
select sql_text, sql_id, sql_profile, executions, plan_hash_value
from v$sql
where sql_profile is not null;
10、其它常用功能：
删除优化任务：
begin
dbms_sqltune.drop_tuning_task('rockey_sql_tuning_task_001');
end;
/
begin
dbms_advisor.delete_task('rockey_sql_tuning_task_001');
end;
/
删除SQL Profile：
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile_001');
END;
/
与优化任务相关的常用视图：
select * from dba_sql_profiles;
select * from DBA_ADVISOR_TASKS;
############
SQL Profiles可以看作是SQL语句的统计信息。只是这个统计信息对特定SQL语句才能起作用，不对会语句的对象、其它语句产生影响。
使用SQL Profiles前要用SQL Tuning Advisor收集对语句的优化建议，再根据优化建议创建SQL Profiles。
SQL Profiles 使用也比较灵活，可以在会话级、系统级应用。
语句绑定SQL Profile后，测试了下SQL Profile与Bind Peeking的关系。测试发现，Bind Peeking的特性还是会起作用。这从另一方面说明SQL Profile与OUTLINE的不同：绑定OUTLINE后，执行计划是被固化的；绑定SQL Profile后，执行计划不是不变，而是优化器在执行该语句时，会参考SQL Profile中的信息。
############
