SQL Profiles使用实例

在统计信息不完整或者缺失时，有时优化器会产生错误的判断，产生性能比较低下的执行计划。传统的方式，通过手工添加相关的提示生成正确的执行计划。但对于那些封装好的应用程序，修改代码不是一件容易的事情，相信这个问题也曾经困扰过很多的DBA。在Oracle10g中可以很方便的使用自动SQL调整（Automatic SQL Tuning）来调整这些SQL语句，而不用去修改应用代码。其中使用的关键技术就是强大的SQL Profiles。

下面通过一个小例子，看看SQL Profiles是如何进行SQL语句自动调整的。

1. 使用HR用户登录，创建测试用例

SQL> create table test (n number );

Table created.

SQL> begin
for i in 1 .. 10000 loop
insert into test values(i);
commit;
end loop;
end;
/

PL/SQL procedure successfully completed.

2. 创建索引并进行分析

SQL> create index test_idx on test(n);

Index created.

SQL> analyze table test estimate statistics;

Table analyzed.

3. 执行SQL语句，模拟一个性能低下的执行计划

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

Execution Plan
———————————————————-
Plan 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 |
————————————————————————–

4. 使用SYS用户登录，创建自动调整任务

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ’select /*+ no_index(test test_idx) */ * from hr.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 => ‘tuning_task_1′,
description => ‘Task to tune a query on a specified table’);
END;
/

PL/SQL procedure successfully completed.

5. 执行自动调整任务并输出结果

SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘tuning_task_1′);

PL/SQL procedure successfully completed.

SQL> SET LONG 1000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET LINESIZE 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘tuning_task_1′) from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TUNING_TASK_1′)
——————————————————————————-
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : tuning_task_1
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 07/02/2010 15:48:07
Completed at : 07/02/2010 15:48:08
Number of SQL Profile Findings : 1

——————————————————————————-
Schema Name: SCOTT
SQL ID : 8m2dnmd4a7vx0
SQL Text : select /*+ no_index(test test_idx) */ * from hr.test where n=1

——————————————————————————-
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: 84.11%)
——————————————
– Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => ‘tuning_task_1′,
replace => TRUE);

——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-

1- Original With Adjusted Cost
——————————
Plan 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”=1)

2- Using SQL Profile
——————–
Plan 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)

——————————————————————————-

从上面的输出可以看出，自动调整任务发现了一个更好的执行计划，并推荐使用这个计划。

6. 接受执行计划

DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => ‘tuning_task_1′,
name => ‘my_sql_profile’);
END;
/

PL/SQL procedure successfully completed.

SQL> select to_char(sql_text) from dba_sql_profiles;

TO_CHAR(SQL_TEXT)
————————————————————————
select /*+ no_index(test test_idx) */ * from hr.test where n=1

7. 再次执行相同的语句，验证结果

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

Execution Plan
———————————————————-
Plan 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 |
—————————————————————————–

这里可以看出，Oracle已经使用了不同的执行计划。