Oracle 10G强大的SQL优化工具：SQL Tuning Advisor

SQL>
show user;

USER
is “SYS”

—授予普通用户advisor的权限

SQL>
grant
advisor to owner;

Grant
succeeded.

SQL>
conn owner/owner

Connected.

SQL>
create
table test1114 as select * from dba_objects;

SQL>
insert
into test1114 select * from dba_objects;

SQL>
set timing on

SQL>
set autot on

SQL>
select
* from test1114;

Elapsed:
00:01:09.81

Execution
Plan

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

Plan
hash value: 3447467757

——————————————————————————

|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

——————————————————————————

|
0 | SELECT STATEMENT | | 110K| 21M| 588 (1)|
00:00:08 |

|
1 | TABLE ACCESS FULL| TEST1114 | 110K| 21M| 588 (1)|
00:00:08 |

——————————————————————————

Note

—–

–
dynamic sampling used for this statement (level=2)

Statistics

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

29
recursive calls

1
db block gets

11646
consistent gets

1080
physical reads

176
redo size

16498937
bytes sent via SQL*Net to client

106046
bytes received via SQL*Net from client

9595
SQL*Net roundtrips to/from client

0
sorts (memory)

0
sorts (disk)

143898
rows processed

步骤一:
调用函数CREATE_TUNING_TASK来创建优化任务；

步骤二:
调用存储过程EXECUTE_TUNING_TASK执行该任务；

SQL>set
autotrace off

SQL>set
timing off

SQL>
declare

owner_sql
varchar2(30);

sqltext_me
clob;

begin

sqltext_me
:= ‘select * from test1114’;

—删除优化任务

dbms_sqltune.drop_tuning_task(task_name
=>’tuning_owner_test’);

—创建优化任务

owner_sql
:= dbms_sqltune.create_tuning_task
(

sql_text
=> sqltext_me,

user_name
=> ‘OWNER’,

scope
=> ‘comprehensive’,

time_limit
=> 60,

task_name
=> ‘tuning_owner_test’,

description
=> ‘task to tune a query on a specified table’) ;

—执行优化任务

dbms_sqltune.execute_tuning_task(
task_name => ‘tuning_owner_test’);

end;

/

PL/SQL
procedure successfully completed.

调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。

SQL>
exec
dbms_sqltune.execute_tuning_task(‘tuning_owner_test’);

PL/SQL
procedure successfully completed.

-通过查看dba_advisor_task或者suser_advisor_tasks可以检查优化任务的状态

SQL>
SELECT
task_name,status FROM USER_ADVISOR_TASKS WHERE task_name
=’tuning_owner_test’;

TASK_NAME     
STATUS

——————————
———–

tuning_owner_test   
COMPLETED

查询dbms_sqltune.report_tning_task函数可以获得优化任务的结果

SQL>
set
long 888888

SQL>
set
serveroutput on size 888888

SQL>
set
lines 100

SQL>
select
dbms_sqltune.report_tuning_task(‘tuning_owner_test’) from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TUNING_OWNER_TEST1500’)

——————————————————————————–

GENERAL
INFORMATION SECTION

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

Tuning
Task Name : tuning_owner_test1500

Tuning
Task Owner : OWNER

Workload
Type : Single SQL Statement

Execution
Count : 2

Current
Execution : EXEC_146

Execution
Type : TUNE SQL

Scope
: COMPREHENSIVE

Time
Limit(seconds): 60

Completion
Status : COMPLETED

Started
at : 11/14/2016 15:00:36

Completed
at : 11/14/2016 15:00:37

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

Schema
Name: OWNER

SQL
ID : dzrzmbsu9krk8

SQL
Text : select * from test1114

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

FINDINGS
SECTION (1 finding)

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

1-
Statistics Finding

———————

Table
“OWNER”.”TEST1114″ was not analyzed.

Recommendation

————–

–
Consider collecting optimizer statistics for this table.

execute
dbms_stats.gather_table_stats(ownname => ‘OWNER’, tabname =>

‘TEST1114’,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt
=> ‘FOR ALL COLUMNS SIZE AUTO’);

Rationale

———

The
optimizer requires up-to-date statistics for the table in order to

select
a good execution plan.

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

EXPLAIN
PLANS SECTION

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

1-
Original

———–

Plan
hash value: 3447467757

——————————————————————————

|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

——————————————————————————

|
0 | SELECT STATEMENT | | 139K| 27M| 588 (1)|
00:00:08 |

|
1 | TABLE ACCESS FULL| TEST1114 | 139K| 27M| 588 (1)|
00:00:08 |

——————————————————————————

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

分析语句为

execute
dbms_stats.gather_table_stats(ownname => ‘OWNER’, tabname =>
‘TEST1114’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);

调用dbms_sqltuen.drop_tuning_task删除已经存在的优化任务

SQL>
exec
dbms_sqltune.drop_tuning_task(‘tuning_kingsql_test1500’);

PL/SQL
procedure successfully completed.