什么是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)
10000
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)
10000
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.
)
