Sql Tuning Advisor的大致過程測試！

可以透過em使用Sql Tuning Advisor來尋找sql的最佳化建議，這裡使用了package：DBMS_SQLTUNE獲得sql最佳化的建議，只是想大致瞭解下Sql Tuning Advisor的過程。[@more@]

SQL> create table t1(id int , name varchar2(10));

表已建立。
SQL> create table t2(id int , name char(1000));

表已建立。

SQL> begin
2 for i in 1..10000 loop
3 insert into t1 values(i,'a'||i);
4 insert into t2 values(i,'b'||i);
5 end loop
6 ;
7 commit;
8 end;
9 /

PL/SQL 過程已成功完成。

SQL> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select /*+ use_nl(t1 ,t2)*/ count(*) from t1,t2 where t1.i
d=t2.id and t1.id<:id> 6
7 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
8 sql_text => my_sqltext,
9 bind_list => sql_binds(anydata.ConvertNumber(10000)),
10 user_name => 'TEST',
11 scope => 'COMPREHENSIVE',
12 time_limit => 60,
13 task_name => 'my_sql_tuning_task',
14 description => 'Task to tune a query on a specified t1 and t2');
15 END;
16 /

PL/SQL 過程已成功完成。

SQL>
SQL> select owner,task_name from dba_advisor_tasks where owner='TEST' and task_n
ame='my_sql_tuning_task';

OWNER TASK_NAME
------------------------------ ------------------------------
TEST my_sql_tuning_task

SQL>
SQL> BEGIN
2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
3 END;
4 /

PL/SQL 過程已成功完成。

SQL> select message,more_info from dba_advisor_findings where owner='TEST' AND t
ask_name='my_sql_tuning_task';

MESSAGE MORE_INFO
------------------------------ -------------------------------------------------
-
透過建立一個或多個索引可以改進
此語句的執行計劃。

尚未分析表 "TEST"."T2"。
尚未分析表 "TEST"."T1"。
為此語句找到了效能更好的執行計
劃。

SQL>
SQL> select type,benefit from dba_advisor_recommendations where task_name='my_sq
l_tuning_task';

TYPE BENEFIT
------------------------------ ----------
INDEX 9576
STATISTICS 0
STATISTICS 0
SQL PROFILE 9952

SQL>
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')
--------------------------------------------------------------------------------
--------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task
Tuning Task Owner : TEST
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 11/05/2008 12:57:47
Completed at : 11/05/2008 12:57:48
Number of Statistic Findings : 2
Number of SQL Profile Findings : 1

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
--------------------
Number of Index Findings : 1

-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID : 00nrxn055pdj2
SQL Text : select /*+ use_nl(t1 ,t2)*/ count(*) from t1,t2 where
t1.id=t2.id and t1.id<:id>

-------------------------------------------------------------------------------
FINDINGS SECTION (4 findings)
-------------------------------------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
--------------------

1- Statist

SQL>