oracle sql tuning 11--sql tuning advisor使用-优快云博客

本文介绍如何使用Oracle数据库中的SQL Tuning Advisor进行SQL语句优化。包括创建测试表、调用dbms_sqltune包启动任务及获取优化建议的过程。同时展示了如何使用dbms_advisor.quick_tune针对单个SQL语句进行快速调优。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一直都想用下10G中自带的sql tuning advior，今天终于大致了解下如何用，记录下

1.SYS用户建测试表,测试数据
SQL> create table student (id number,name varchar2(50));

表已创建。
SQL> insert into student values (1,'xxx');

已创建 1 行。

SQL> insert into student values (2,'yyy');

已创建 1 行。

SQL> create table grade (id number,score number);

表已创建。

SQL> insert into grade values (1,100);

已创建 1 行。

SQL> insert into grade values (2,90);

已创建 1 行。

SQL> commit;

提交完成。

2.通过包dbms_sqltune调用SQL TUNING ADVISOR

SQL> declare
2
3 l_task_id     varchar2(20);
4 l_sql         varchar2(2000);
5 begin
6 l_sql := 'select s.name,g.score from student s,grade g where s.id=g.id';
7 --dbms_sqltune.drop_tuning_task ('TEST');
8 l_task_id := dbms_sqltune.create_tuning_task (
9 sql_text => l_sql,
10 user_name => 'SYS',
11 scope      => 'COMPREHENSIVE',
12 time_limit => 120,
13 task_name => 'TEST'
14     );
15 dbms_sqltune.execute_tuning_task ('TEST');
16 end;
17 /

PL/SQL 过程已成功完成。

3.查看SQL TUNING ADVISOR给的优化建议

select dbms_sqltune.report_tuning_task ('TEST') from dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : daibo
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 120
Completion Status                 : COMPLETED
Started at                        : 04/11/2010 00:19:15
Completed at                      : 04/11/2010 00:19:15
Number of Statistic Findings      : 2

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 4y830pqda3mzs
SQL Text : select s.name,g.score from student s,grade g where s.id=g.id

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
尚未分析表 "SYS"."GRAD

Recommendation
--------------
- 考虑收集此表的优
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
            'GRADE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Rationale
---------
为了选择好的执行计划, 优化程序需

2- Statistics Finding
---------------------
尚未分析表 "SYS"."STUDEN

Recommendation
--------------
- 考虑收集此表的优
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
            'STUDENT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Rationale
---------
为了选择好的执行计划, 优化程序需

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1192007014

------------------------------------------------------------------------------
| Id | Operation          | Name    | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     2 |   132 |     5 (20)| 00:00:01 |
|* 1 | HASH JOIN         |         |     2 |   132 |     5 (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| STUDENT |     2 |    80 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| GRADE   |     2 |    52 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("S"."ID"="G"."ID")

-------------------------------------------------------------------------------

如果想查看系统对单个语句的优化建议，该如何处理呢，也有相关包供使用,那就是dbms_advisor.quick_tune

例如:

exec dbms_advisor.quick_tune(

advisor_name => dbms_advisor.SQLACCESS_ADVISOR,

task_name => 'test6',

attr1 => 'select avg(a) from dbtest1 where a=''5''');

查看建议结果：