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>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1012965/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/19602/viewspace-1012965/