当前位置：首页 > news >正文

SQL Tuning Advisor

news 来源：原创 2025/7/20 22:00:02

什么是SQL Tuning Advisor

STA可以用来优化那些已经被发现的高负载SQL. 默认情况下, Oracle数据库在自动维护窗口中自动认证那些有问题的SQL并且执行优化建议，找寻提升高负载SQL执行计划性能的方法.

** 如何查看自动优化维护窗口产生的报告? **

SQL> set serveroutput on size 100000;
SQL> var my_rept CLOB;
SQL> begin
:my_rept :=DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(
begin_exec => NULL,
end_exec => NULL,
type => ‘TEXT’,
level => ‘TYPICAL’,
section => ‘ALL’,
object_id => NULL,
result_limit => NULL);
END;
/

PL/SQL procedure successfully completed.

PRINT :my_rept

** 输入 **

ADDM(Automatic Database Diagnostic Monitor)
主要的输入来源就是ADDM, 默认情况下, ADDM每小时主动运行一次并通过分析awr收集的重要的统计信息来印证高负载的问题SQL.
AWR
第二个比较重要的输入源就是awr.
Shared SQL Area(Shared Pool)
SQL Tuning Sets(STS)
需要了解SQL Tuning Set请参考文档sts.txt, SQL Tuning Set涵盖了SQL负载的大部分信息, 包括runtime统计信息,执行计划等信息.

** 优化选项 **

SQL Tuning Advisor提供了一些额外的选项用来管理优化任务的范围和和周期.您可将优化范围设置为以下值:

Limited
该情况下, SQLTA根据统计信息检查, access路径分析,SQL结构分析来生成建议.SQL Profile建议将不会产生.
Comprehension
该情况下, SQLTA产生Limitted情况下所有的输出并且增加SQL Profile的内容. 在Comprehension情况下,你也可以指定调优任务的限制时间, 默认情况下是30min.

** 运行SQLTA **

使用dbms_sqltune运行SQLTA需要以下步骤:

创建SQL Tuning Set(如果需要调优多个SQL)
创建SQL调优任务
执行sQL调优任务
查看调优任务结果
产生合适的建议
```
 	STS|create tuning task|execute tuning task|report tuning task|执行建议|
```
| | | | |
收集统计信息创建SQL_Profile 创建索引改写SQL SQL_Plan_Baseline

创建测试环境

(11G)NJL_TEST@testdb1> create table t1 (id number , name varchar2(20));

Table created.

(11G)NJL_TEST@testdb1> begin
2 for i in 1…10000
3 loop
4 insert into t1 values(i, ‘A’||i);
5 commit;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

(11G)NJL_TEST@testdb1> select count(1) from t1;

COUNT(1)

Creating a SQL Tuninig Task

SQL> DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘select count(1) from t1’;

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
–bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => ‘NJL_TEST’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘task_01’,
description => ‘Task to tune a query on a specified employee’);
END;
/

PL/SQL procedure successfully completed.

执行SQL调优任务

SQL> BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task’ );
END;
/

PL/SQL procedure successfully completed.

Configuring a SQL Tuning Task

(11G)NJL_TEST@testdb1> BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => ‘task_01’,
parameter => ‘TIME_LIMIT’, value => 300);
END;
/

PL/SQL procedure successfully completed.

调优参数列表:

Parameter Description

MODE

Specifies the scope of the tuning task:

LIMITED takes approximately 1 second to tune each SQL statement but does not recommend a SQL profile
COMPREHENSIVE performs a complete analysis and recommends a SQL profile, when appropriate, but may take much longer.

USERNAME

Username under which the SQL statement is parsed

DAYS_TO_EXPIRE

Number of days before the task is deleted

DEFAULT_EXECUTION_TYPE

Default execution type if not specified by the EXECUTE_TUNING_TASK function when the task is executed

TIME_LIMIT

Time limit (in number of seconds) before the task times out

LOCAL_TIME_LIMIT

Time limit (in number of seconds) for each SQL statement

TEST_EXECUTE

Determines if the SQL Tuning Advisor test executes the SQL statements to verify the recommendation benefit:
FULL - Test executes SQL statements for as much of the local time limit as necessary
AUTO - Test executes SQL statements using an automatic time limit
OFF - Does not test execute SQL statements

BASIC_FILTER

Basic filter used for SQL tuning set

OBJECT_FILTER

Object filter used for SQL tuning set

PLAN_FILTER

Plan filter used for SQL tuning set

RANK_MEASURE1

First ranking measure used for SQL tuning set

RANK_MEASURE2

Second ranking measure used for SQL tuning set

RANK_MEASURE3

Third ranking measure used for SQL tuning set

RESUME_FILTER

Extra filter used for SQL tuning set (besides BASIC_FILTER)

SQL_LIMIT

Maximum number of SQL statements to tune

SQL_PERCENTAGE

Percentage filter of statements from SQL tuning set

查看SQL调优任务的状态

(11G)NJL_TEST@testdb1> SELECT status
FROM USER_ADVISOR_TASKS
WHERE task_name = ‘my_sql_tuning_task’; 2 3

STATUS

INITIAL

查看SQL调优任务的结果

SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘task_01’)
FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_01’)

GENERAL INFORMATION SECTION

Tuning Task Name : task_01
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 01/31/2018 15:04:07
Completed at : 01/31/2018 15:04:07

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_01’)

Schema Name: NJL_TEST
SQL ID : 8ntdmqt9pv0dq
SQL Text : select count(1) from t1

FINDINGS SECTION (1 finding)

1- Statistics Finding

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_01’)

Table “NJL_TEST”.“T1” was not analyzed.

Recommendation

Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => ‘NJL_TEST’, tabname =>
‘T1’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> ‘FOR ALL COLUMNS SIZE AUTO’);

Rationale

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_01’)

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: 3724264953

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_01’)

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

| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 7 (0)| 00:00:01 |

执行建议

begin
dbms_stats.gather_table_stats(ownname => ‘NJL_TEST’, tabname =>
‘T1’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> ‘FOR ALL COLUMNS SIZE AUTO’);
end;
/

PL/SQL procedure successfully completed.

这里Oracle并没有建议我们创建索引. 我们来尝试创建索引.

(11G)SYS@testdb1> alter table njl_test.t1 add constraints pk1 primary key (id);

Table altered.

11G)SYS@testdb1> set autotrace on
(11G)SYS@testdb1>
(11G)SYS@testdb1> select count(1) from njl_test.t1;

COUNT(1)

Execution Plan

Plan hash value: 574704543

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

| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| PK1 | 10000 | 5 (0)| 00:00:01 |

Statistics

  1  recursive calls0  db block gets24  consistent gets21  physical reads0  redo size
526  bytes sent via SQL*Net to client
523  bytes received via SQL*Net from client2  SQL*Net roundtrips to/from client0  sorts (memory)0  sorts (disk)1  rows processed

我们可以看到, 使用索引可以带来更低的cost, 可见, SQLTA并没有特别只智能, 我们可以根据该SQLTA工具进行SQL优化的建议. 同时还要使用SAA工具进行索引的建议或者根据经验手动调优.

删除调优任务:

SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK(‘task_01’);

PL/SQL procedure successfully completed.

什么是SQL Tuning Advisor

创建测试环境

COUNT(1)

Creating a SQL Tuninig Task

执行SQL调优任务

Configuring a SQL Tuning Task

调优参数列表:

Parameter Description

查看SQL调优任务的状态

STATUS

查看SQL调优任务的结果

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_01’)

GENERAL INFORMATION SECTION

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_01’)

FINDINGS SECTION (1 finding)

1- Statistics Finding

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_01’)

Recommendation

Rationale

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_01’)

EXPLAIN PLANS SECTION

1- Original

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_01’)

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

| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T1 | 10000 | 7 (0)| 00:00:01 |

执行建议

COUNT(1)

Execution Plan

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

| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| PK1 | 10000 | 5 (0)| 00:00:01 |

Statistics

相关文章：

| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 7 (0)| 00:00:01 |

| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| PK1 | 10000 | 5 (0)| 00:00:01 |