SQL Profiles使用实例

时间：2010-08-31 来源：2jliu

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

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

SQL> conn oracle/oracle
Connected.
SQL> create table test(n number);

Table created.

SQL> begin
2 for i in 1 .. 100000 loop
3 insert into test values(i);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> create index test_idx on test(n);

Index created.

SQL> analyze table test estimate statistics;

Table analyzed.

SQL> explain plan for select /*+ no_index(test test_idx) */ * from test where n=1;

Explained.

SQL> set linesize 2000
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 44 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 44 (5)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

13 rows selected.

SQL> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select /*+ no_index(test test_idx) */ * from oracle.test where n=1';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text => my_sqltext,
8 user_name => 'ORACLE',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'tuning_task_1',
12 description => 'Task to tune a query on a specified table');
13 END;
14 /

PL/SQL procedure successfully completed.

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 : ORACLE
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 08/31/2010 15:26:53
Completed at : 08/31/2010 15:26:53
Number of SQL Profile Findings : 1

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK_1')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: ORACLE
SQL ID : 6xakxv5xdjwdb
SQL Text : select /*+ no_index(test test_idx) */ * from oracle.test where
n=1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK_1')
----------------------------------------------------------------------------------------------------
---------------------------------------------

SQL> DECLARE
2 my_sqlprofile_name VARCHAR2(30);
3 BEGIN
4 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
5 task_name => 'tuning_task_1',
6 name => 'my_sql_profile');
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> select to_char(sql_text) from dba_sql_profiles
2 ;

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

SQL> select /*+ no_index(test test_idx) */ * from hr.test where n=1;
select /*+ no_index(test test_idx) */ * from hr.test where n=1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> explain plan for select /*+ no_index(test test_idx) */ * from oracle.test where n=1;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
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):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

1 - access("N"=1)

Note
-----
- SQL profile "my_sql_profile" used for this statement

17 rows selected.

SQL>