Sql Tuning Advisor是10g以后出现的一个十分有用的调优工具，大多数情况下我们可以通过dbconsole或者Grid Control的web界面调用SQL Advisor；但如果系统中没有配置dbconsole或者Grid Control的话，我们则需要通过手动调用DBMS_SQLTUNE PL/SQL程序包来使用该特性。这里我列出一个针对单个SQL语句Autotune的脚本，具体脚本:
具体使用示例:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
begin
DBMS_SQLTUNE.drop_tuning_task(
'&task_name'
);
end
;
/
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
my_sqlid varchar2(30);
BEGIN
my_sqlid :=
'&sqlid'
;
my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
scope =>
'COMPREHENSIVE'
,
time_limit => 300,
task_name =>
'&task_name'
,
description =>
'comment'
);
END
;
/
BEGIN
dbms_sqltune.execute_tuning_task(task_name =>
'&task_name'
);
END
;
/
SELECT
status
FROM
USER_ADVISOR_TASKS
WHERE
task_name =
'&task_name'
;
SET
LONG 10000
SET
LONGCHUNKSIZE 10000
SET
LINESIZE 100
set
pages 60
SELECT
DBMS_SQLTUNE.REPORT_TUNING_TASK(
'&task_name'
)
FROM
DUAL;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
SQL>
begin
DBMS_SQLTUNE.drop_tuning_task(
'&task_name'
);
end
;
/
Enter value
for
task_name: newtask
old 3: DBMS_SQLTUNE.drop_tuning_task(
'&task_name'
);
new 3: DBMS_SQLTUNE.drop_tuning_task(
'newtask'
);
begin
*
ERROR
at
line 1:
ORA-13605: The specified task
or
object newtask does
not
exist
for
the
current
user
.
ORA-06512:
at
"SYS.PRVT_ADVISOR"
, line 2307
ORA-06512:
at
"SYS.DBMS_ADVISOR"
, line 172
ORA-06512:
at
"SYS.DBMS_SQLTUNE"
, line 751
ORA-06512:
at
line 3
SQL>
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
my_sqlid varchar2(30);
BEGIN
my_sqlid :=
'&sqlid'
;
my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
scope =>
'COMPREHENSIVE'
,
time_limit => 300,
task_name =>
'&task_name'
,
description =>
'comment'
);
END
;
/
Enter value
for
sqlid: 17usubxchdf2w
old 11: my_sqlid :=
'&sqlid'
;
new 11: my_sqlid :=
'17usubxchdf2w'
;
Enter value
for
task_name: new_task
old 19: task_name =>
'&task_name'
,
new 19: task_name =>
'new_task'
,
SQL>
BEGIN
dbms_sqltune.execute_tuning_task(task_name =>
'&task_name'
);
END
;
/
Enter value
for
task_name: new_task
old 3: dbms_sqltune.execute_tuning_task(task_name =>
'&task_name'
);
new 3: dbms_sqltune.execute_tuning_task(task_name =>
'new_task'
);
PL/SQL
procedure
successfully completed.
SQL>
SELECT
status
FROM
USER_ADVISOR_TASKS
WHERE
task_name =
'&task_name'
;
Enter value
for
task_name: new_task
old 1:
SELECT
status
FROM
USER_ADVISOR_TASKS
WHERE
task_name =
'&task_name'
new 1:
SELECT
status
FROM
USER_ADVISOR_TASKS
WHERE
task_name =
'new_task'
STATUS
-----------
COMPLETED
SQL>
SELECT
DBMS_SQLTUNE.REPORT_TUNING_TASK(
'&task_name'
)
FROM
DUAL;
Enter value
for
task_name: new_task
old 1:
SELECT
DBMS_SQLTUNE.REPORT_TUNING_TASK(
'&task_name'
)
FROM
DUAL
new 1:
SELECT
DBMS_SQLTUNE.REPORT_TUNING_TASK(
'new_task'
)
FROM
DUAL
DBMS_SQLTUNE.REPORT_TUNING_TASK(
'NEW_TASK'
)
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION
SECTION
-------------------------------------------------------------------------------
Tuning Task
Name
: new_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time
Limit(seconds): 300
Completion Status : COMPLETED
Started
at
: 03/25/2011 00:14:41
Completed
at
: 03/25/2011 00:14:45
-------------------------------------------------------------------------------
Schema
Name
: SYS
SQL ID : 17usubxchdf2w
SQL Text :
select
count
(t1)
from
hashtab
-------------------------------------------------------------------------------
FINDINGS
SECTION
(1 finding)
-------------------------------------------------------------------------------
1-
Statistics
Finding
---------------------
Table
"SYS"
.
"HASHTAB"
was
not
analyzed.
Recommendation
--------------
- Consider collecting optimizer
statistics
for
this
table
.
execute
dbms_stats.gather_table_stats(ownname =>
'SYS'
, tabname =>
'HASHTAB'
, 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: 485915006
------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 |
TABLE
ACCESS
FULL
| HASHTAB | 102 | 1326 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
|
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277736
