SQL調優工具包DBMS

144次閱讀

共計 9300 個字符，預計需要花費 24 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 SQL 調優工具包 DBMS_SQLTUNE 怎么用，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

oracle 提供了優化建議功能包 DBMS_SQLTUNE，該包可以幫助我們分析 SQL，并提供優化建議。

原有執行計劃
alter session set statistics_level=all;
set serveroutput off
select * from test.emp where ename= SCOTT and DEPTNO=20;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, runstats_last

PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID 8k1gbrapm7zpd, child number 0
————————————-
select * from test.emp where ename= SCOTT and DEPTNO=20

Plan hash value: 3956160932

————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 4 |
————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
1 – filter((ENAME = SCOTT AND DEPTNO =20)) 下面就用 DBMS_SQLTUNE 優化該 SQL
–1. 賦予用戶 ADVISOR 權限
grant ADVISOR to test;

–2. 創建 sql tuning 任務
conn test/test

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := select * from emp where ename= :name and DEPTNO= :deptno
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text = my_sqltext,
bind_list = sql_binds(anydata.convertvarchar2(10),anydata.convertnumber(2)),
user_name = TEST ,
scope = COMPREHENSIVE ,
time_limit = 60,
task_name = test_sql_tuning ,
description = Task to tune a query on emp
END;
/

參數說明：
bind_list：多個綁定變量以 , 逗號分隔。參數值一定要根據綁定變量對應的列的類型書寫.
如：emp.ename 類型是 VARCHAR2(10)，那么就要寫成
bind_list = sql_binds(anydata.convertvarchar2(10)),

time_limit：執行的最長時間，默認是 60。

scope：
LIMITED，用大概 1 秒時間去優化 SQL 語句，但是并不進行 SQL Profiling 分析。
COMPREHENSIVE，進行全面分析，包含 SQL Profiling 分析；比 LIMITED 用時更長。

/*2014-4- 8 日增加 begin*/
** 也可以用 sql_id 創建 sql tunning 任務，比用 sql_text 方便很多
FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SCOPE VARCHAR2 IN DEFAULT
TIME_LIMIT NUMBER IN DEFAULT
TASK_NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT

DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
SQL_ID = ddw7j6yfnw0vz ,
scope = COMPREHENSIVE ,
time_limit = 60,
task_name = tunning_task_ddw7j6yfnw0vz ,
description = Task to tune a query on ddw7j6yfnw0vz
END;
/

/*2014-4- 8 日增加 end*/

–3. 查看任務名 SELECT TASK_NAME
FROM DBA_ADVISOR_LOG
WHERE OWNER = TEST
TASK_NAME
——————————
test_sql_tuning

–4. 執行 sql tuning 任務
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name = test_sql_tuning
END;
/

–5. 查看 sql tunning 任務狀態
SELECT status
FROM USER_ADVISOR_TASKS
WHERE task_name = test_sql_tuning
STATUS
———–
COMPLETED

–6. 展示 sql tunning 結果
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(test_sql_tuning)
FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : test_sql_tuning
Tuning Task Owner : TEST
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 04/01/2014 16:45:16
Completed at : 04/01/2014 16:45:17

DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
——————————————————————————-
Schema Name: TEST
SQL ID : 95fv6dbj64d0f
SQL Text : select * from emp where ename= :name and DEPTNO= :deptno

——————————————————————————-
FINDINGS SECTION (2 findings)
——————————————————————————-
1- Statistics Finding
———————

DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
Table TEST . EMP was not analyzed.

Recommendation
————–
– Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname = TEST , tabname =
EMP , estimate_percent = DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt = FOR ALL COLUMNS SIZE AUTO

Rationale
———

DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.

2- Index Finding (see explain plans section below)
————————————————–
The execution plan of this statement can be improved by creating one or more
indices.

Recommendation (estimated benefit: 66.67%)
——————————————
– Consider running the Access Advisor to improve the physical schema designDBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
or creating the recommended index.
create index TEST.IDX$$_00D80001 on TEST.EMP(ENAME , DEPTNO

Rationale
———
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run Access Advisor
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.

DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-

1- Original
———–
Plan hash value: 3956160932

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————
1 – filter(ENAME =:NAME AND DEPTNO =:DEPTNO)

2- Using New Indices
——————–

DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
Plan hash value: 2106247215
———————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_00D80001 | 1 | | 1 (0)| 00:00:01 |
———————————————————————————————-

Predicate Information (identified by operation id):
DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
—————————————————
2 – access(ENAME =:NAME AND DEPTNO =:DEPTNO)

——————————————————————————- 建議報告總結：
1 收集 EMP 表的統計信息
execute dbms_stats.gather_table_stats(ownname = TEST , tabname = EMP , estimate_percent = DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt = FOR ALL COLUMNS SIZE AUTO

2 創建索引
create index TEST.IDX$$_00D80001 on TEST.EMP(ENAME , DEPTNO 優化后執行計劃
——————————————————————————————————–
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
——————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | IDX$$_00D80001 | 1 | 1 | 1 |00:00:00.01 | 2 |
——————————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access(ENAME = SCOTT AND DEPTNO =20)–7. 完成后刪除 sql tunning 任務
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(test_sql_tuning

–8. 其他
–sql tunning 任務創建后，也可以修改參數
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name = test_sql_tuning ,
parameter = TIME_LIMIT , value = 300);
END;
/

– 查看 SQL Tuning Advisor 的進展（task 執行很久）
col opname for a20
col ADVISOR_NAME for a20
SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK
FROM V$ADVISOR_PROGRESS
WHERE USERNAME = TEST 以上根據 oracle doc 整理

看完了這篇文章，相信你對“SQL 調優工具包 DBMS_SQLTUNE 怎么用”有了一定的了解，如果想了解更多相關知識，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！

正文完