根據(jù)上一篇文章中生產(chǎn)的SPA任務(wù)，通過DBMS_SQLTUNE提出的建議優(yōu)化sql
一. dbms_sqltune.create_tuning_task可以基于sql text,sql_id, workload repository,sqlset,SPA Task。本處以前面實(shí)驗(yàn)用的SPA Task為例。
--根據(jù)spa task_name創(chuàng)建任務(wù)(通過其他方式創(chuàng)建任務(wù)理應(yīng)更簡(jiǎn)單)
DECLARE
? L_SQL_TUNE_TASK_ID VARCHAR2(100);
BEGIN
? L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK(SPA_TASK_NAME ? ?=> 'compare_2sets2',
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? SPA_TASK_OWNER ? => 'HNYD',
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? SPA_COMPARE_EXEC => NULL,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? BASIC_FILTER ? ? => NULL,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TIME_LIMIT ? ? ? => 18000,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TASK_NAME ? ? ? ?=> 'T_SPA_TASK1',
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? DESCRIPTION ? ? ?=> 'Test create_tuning_task - SPA Task format');
? DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
END;
/
----上面發(fā)現(xiàn)DJANGO_SET1，DJANGO_SET2以及compare_2sests不存在，這與個(gè)人清理掉system和sysaux表空間的操作有關(guān)，用上篇中類似方式重新創(chuàng)建即哥。
---而后發(fā)現(xiàn)超時(shí)（執(zhí)行上面的過程不會(huì)報(bào)錯(cuò)，最后sqltune的結(jié)果展示不全，內(nèi)容顯示錯(cuò)誤），因?yàn)檎麄€(gè)sql tune sets 內(nèi)容太多，我第一次超時(shí)時(shí)間只設(shè)置了3分鐘，后面重新設(shè)置較長(zhǎng)時(shí)間后成功獲取整個(gè)spa任務(wù)比較中所有sql。
2.DBMS_SQLTUNE常見利用
????begin DBMS_SQLTUNE.execute_tuning_task(task_name => 'T_SPA_TASK1'); end;
?SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('T_SPA_TASK1') ?FROM dual;
--結(jié)果展示
?GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name ? ? ? ? ? ? ? ? ?: T_SPA_TASK1
Tuning Task Owner ? ? ? ? ? ? ? ? : HNYD
Workload Type ? ? ? ? ? ? ? ? ? ? : SQL Tuning Set
Scope ? ? ? ? ? ? ? ? ? ? ? ? ? ? : COMPREHENSIVE
Time Limit(seconds) ? ? ? ? ? ? ? : 18000
Completion Status ? ? ? ? ? ? ? ? : COMPLETED
Started at ? ? ? ? ? ? ? ? ? ? ? ?: 03/05/2016 22:41:51
Completed at ? ? ? ? ? ? ? ? ? ? ?: 03/05/2016 23:15:21
SQL Tuning Set (STS) Name ? ? ? ? : DJANGO_SET3
SQL Tuning Set Owner ? ? ? ? ? ? ?: HNYD
Number of Statements in the STS ? : 604


-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
? ? ? ? ? ? ? ? ? ? ? Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed ? ? ? ? ? ? ? ? ? ? ?: 232
Number of SQLs in the Report ? ? ? ? ? ? ? ? : 138
Number of SQLs with Findings ? ? ? ? ? ? ? ? : 79
Number of SQLs with Statistic Findings ? ? ? : 1
Number of SQLs with Alternative Plan Findings: 29
Number of SQLs with SQL profiles recommended : 30
Number of SQLs with Index Findings ? ? ? ? ? : 1
Number of SQLs with SQL Restructure Findings : 14
Number of SQLs with Errors ? ? ? ? ? ? ? ? ? : 59


-------------------------------------------------------------------------------
? ? SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID ?SQL ID ? ? ? ?statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
? ? ? ?155 3gjhh2hgfnnb2 ? ? ? ? ? ? ? ? ? ? ?99.94% ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ?176 24bu9cpx0wmxq ? ? ? ? ? ? ? ? ? ? ?99.94% ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? 68 bn4b3vjw2mj3u ? ? ? ? ? ? ? ? ? ? ?99.62% ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? 30 g5m0bnvyy37b1 ? ? ? ? ? ? ? ? ? ? ?99.52% ? ? ? ? ? ? ? ? ? ? ? ? ?1
? ? ? ? 60 7aw21806wpgzp ? ? ? ? ? ? ? ? ? ? ?99.50% ? ? ? ? ? ? ? ? ? ? ? ? ?1
? ? ? ?138 a7a75aupxfgq1 ? ? ? ? ? ? ? ? ? ? ?96.63% ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? 13 0mdckw03m07v3 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 95.00% ? ? ? ? ? ?
? ? ? ?163 3nc51h4uv661j ? ? ? ? ? ? ? ? ? ? ?89.40% ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? 20 g5m0bnvyy37b1 ? ? ? ? ? ? ? ? ? ? ?89.37% ? ? ? ? ? ? ? ? ? ? ? ? ?1
? ? ? ? 45 aamdkbbaajjgz ? ? ? ? ? ? ? ? ? ? ?84.03% ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ?203 1tgukkrqj3zhw ? ? ? ? ? ? ? ? ? ? ?82.42% ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? 15 f5yun5dynkskv ? ? ? ? ? ? ? ? ? ? ?81.85% ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? 99 22yqbyxqx2fd6 ? ? ? ? ? ? ? ? ? ? ?81.62% ? ? ? ? ? ? ? ? ? ? ? ? ??
......
對(duì)很多sql都給了建議，結(jié)果過長(zhǎng)，貼一小部分。
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------


1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
? 為此語(yǔ)句找到了性能更好的執(zhí)行計(jì)劃。


? Recommendation (estimated benefit: 99.62%)
? ------------------------------------------
? - 考慮接受推薦的 SQL 概要文件。
? ? execute dbms_sqltune.accept_sql_profile(task_name => 'T_SPA_TASK1',
? ? ? ? ? ? object_id => 68, task_owner => 'HNYD', replace => TRUE);


2- Alternative Plan Finding
---------------------------
? 通過搜索系統(tǒng)的實(shí)時(shí)和歷史性能數(shù)據(jù)找到了此語(yǔ)句的某些替代執(zhí)行計(jì)劃。


? The following table lists these plans ranked by their average elapsed time.
? See section "ALTERNATIVE PLANS SECTION" for detailed information on each
? plan.


? id plan hash ?last seen ? ? ? ? ? ?elapsed (s) ?origin ? ? ? ? ?note ? ? ? ? ? ?
? -- ---------- -------------------- ------------ --------------- ----------------
? ?1 1781002567 ?2016-03-05/18:25:58 ? ? ? ?0.006 AWR ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? ?2 2672219561 ? ? ? ? ? ? ?unknown ? ? ? ?0.011 SPA ? ? ? ? ? ? original plan ??
? ?3 ?930046576 ?2016-03-01/17:00:34 ? ? ? ?0.016 AWR ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? ?4 3270362138 ?2016-03-05/18:25:58 ? ? ? ?0.043 AWR ? ? ? ? ? ? ? ? ? ? ? ? ? ??


? Recommendation
? --------------
? - 請(qǐng)考慮使用最佳平均用時(shí)為計(jì)劃創(chuàng)建 SQL 計(jì)劃基線。
? ? execute dbms_sqltune.create_sql_plan_baseline(task_name => 'T_SPA_TASK1',
? ? ? ? ? ? object_id => 68, owner_name => 'HNYD', plan_hash_value =>
? ? ? ? ? ? 1781002567);


-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- 此 SQL 語(yǔ)句至少缺少一個(gè)重要綁定值。指導(dǎo)分析的準(zhǔn)確性可能取決于提供的所有重要綁定值。


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


1- Original With Adjusted Cost
------------------------------
Plan hash value: 2672219561


-----------------------------------------------------------------------------------------------------------------------------
| Id ?| Operation ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| Name ? ? ? ? ? ? ? ? ? ? ? | Rows ?| Bytes |TempSpc| Cost (%CPU)| Time ? ? |
-----------------------------------------------------------------------------------------------------------------------------
| ? 0 | SELECT STATEMENT ? ? ? ? ? ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 90001 | ? ?13M| ? ? ? | ?3124 ? (1)| 00:00:38 |
| ? 1 | ?SORT ORDER BY ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 90001 | ? ?13M| ? ?16M| ?3124 ? (1)| 00:00:38 |
|* ?2 | ? FILTER ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | ? ? ? ? ? ? ? ? ? ? ? ? ? ?| ? ? ? | ? ? ? | ? ? ? | ? ? ? ? ? ?| ? ? ? ? ?|
……

二、由于分析整個(gè)任務(wù)中的所有sql太耗時(shí)，且spa任務(wù)用到的sql tune sets也沒有過濾，針對(duì)性不強(qiáng)，下面以一個(gè)sql為例，同樣是DBMS_SQLTUNE With SQL Performance Analyzer
--?--過濾條件BASIC_FILTER ? ? => 'sql_id=''67255mtgs6xv4''',TASK_NAME ? ? ? ?=> 'T_SPA_TASK_ONESQL',實(shí)用性相對(duì)上面較強(qiáng)?DECLARE
? L_SQL_TUNE_TASK_ID VARCHAR2(100);
BEGIN
? L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK(SPA_TASK_NAME ? ?=> 'compare_2sets2',
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? SPA_TASK_OWNER ? => 'HNYD',
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? SPA_COMPARE_EXEC => NULL,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? BASIC_FILTER ? ? => 'sql_id=''67255mtgs6xv4''',
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TIME_LIMIT ? ? ? => 1800,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TASK_NAME ? ? ? ?=> 'T_SPA_TASK_ONESQL',
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? DESCRIPTION ? ? ?=> 'Test create_tuning_task - SPA Task format');
? DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
END;
/

begin DBMS_SQLTUNE.execute_tuning_task(task_name => 'T_SPA_TASK_ONESQL',execution_name => 'exec_onesql'); end;
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('T_SPA_TASK_ONESQL') ?FROM dual;

結(jié)果展示：

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name ? ? ? ? ? ? ? ? ?: T_SPA_TASK_ONESQL
Tuning Task Owner ? ? ? ? ? ? ? ? : HNYD
Workload Type ? ? ? ? ? ? ? ? ? ? : SQL Tuning Set
Scope ? ? ? ? ? ? ? ? ? ? ? ? ? ? : COMPREHENSIVE
Time Limit(seconds) ? ? ? ? ? ? ? : 1800
Completion Status ? ? ? ? ? ? ? ? : COMPLETED
Started at ? ? ? ? ? ? ? ? ? ? ? ?: 03/05/2016 23:32:23
Completed at ? ? ? ? ? ? ? ? ? ? ?: 03/05/2016 23:32:29
SQL Tuning Set (STS) Name ? ? ? ? : DJANGO_SET3
SQL Tuning Set Owner ? ? ? ? ? ? ?: HNYD
Number of Statements in the STS ? : 604


-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
? ? ? ? ? ? ? ? ? ? ? Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed ? ? ? ? ? ? ? ? ? ? ?: 2
Number of SQLs in the Report ? ? ? ? ? ? ? ? : 1
Number of SQLs with Findings ? ? ? ? ? ? ? ? : 1
Number of SQLs with Statistic Findings ? ? ? : 1
Number of SQLs with Alternative Plan Findings: 1


-------------------------------------------------------------------------------
? ? SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID ?SQL ID ? ? ? ?statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
? ? ? ? ?2 67255mtgs6xv4 ? ? ? ? ?1 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? ? ? ?3 67255mtgs6xv4 ? ? ? ? ?1 ? ? ? ? ? ? ? ?
……
------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------


1- Statistics Finding
---------------------
? 表 "HNYD"."DB_GROUP_SCHEDULERS" 的優(yōu)化程序統(tǒng)計(jì)信息已失效。


? Recommendation
? --------------
? - 考慮收集此表及其索引的優(yōu)化程序統(tǒng)計(jì)信息。
? ? execute dbms_stats.gather_table_stats(ownname => 'HNYD', tabname =>
? ? ? ? ? ? '表名保密..', estimate_percent =>
? ? ? ? ? ? DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
? ? ? ? ? ? AUTO', cascade => TRUE);


? Rationale
? ---------
? ? 為了選擇好的執(zhí)行計(jì)劃, 優(yōu)化程序需要此表及其索引的最新統(tǒng)計(jì)信息。


2- Alternative Plan Finding
---------------------------
? 通過搜索系統(tǒng)的實(shí)時(shí)和歷史性能數(shù)據(jù)找到了此語(yǔ)句的某些替代執(zhí)行計(jì)劃。


? The following table lists these plans ranked by their average elapsed time.
? See section "ALTERNATIVE PLANS SECTION" for detailed information on each
? plan.


? id plan hash ?last seen ? ? ? ? ? ?elapsed (s) ?origin ? ? ? ? ?note ? ? ? ? ? ?
? -- ---------- -------------------- ------------ --------------- ----------------
? ?1 ?838008028 ? ? ? ? ? ? ?unknown ? ? ? ?0.022 SPA ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? ?2 ?776620345 ?2016-03-05/20:00:11 ? ? ? ?0.061 AWR ? ? ? ? ? ? original plan ??


? Recommendation
? --------------
? - 請(qǐng)考慮使用最佳平均用時(shí)為計(jì)劃創(chuàng)建 SQL 計(jì)劃基線。
? ? execute dbms_sqltune.create_sql_plan_baseline(task_name =>
? ? ? ? ? ? 'T_SPA_TASK_ONESQL', object_id => 2, owner_name => 'HNYD',
? ? ? ? ? ? plan_hash_value => 838008028);


-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- 優(yōu)化程序不能合并位于執(zhí)行計(jì)劃的行 ID 12 處的視圖。. 優(yōu)化程序不能合并包含 "ORDER BY" 子句的視圖, 除非此語(yǔ)句為 "DELETE"
? 或 "UPDATE", 并且父查詢?yōu)榇苏Z(yǔ)句中的頂級(jí)查詢。.
- 優(yōu)化程序不能合并位于執(zhí)行計(jì)劃的行 ID 5 處的視圖。. 優(yōu)化程序不能合并包含 "ORDER BY" 子句的視圖, 除非此語(yǔ)句為 "DELETE" 或
? "UPDATE", 并且父查詢?yōu)榇苏Z(yǔ)句中的頂級(jí)查詢。.
- 優(yōu)化程序不能合并位于執(zhí)行計(jì)劃的行 ID 2 處的視圖。. 優(yōu)化程序不能合并包含 "ROWNUM" 偽列的視圖。.

結(jié)果后面列出了各個(gè)計(jì)劃及按推薦方式處理的變化方式。可做性能優(yōu)化參考。

來(lái)自 “ ITPUB博客 ” ，鏈接：http://blog.itpub.net/27349469/viewspace-2048767/，如需轉(zhuǎn)載，請(qǐng)注明出處，否則將追究法律責(zé)任。

轉(zhuǎn)載于:http://blog.itpub.net/27349469/viewspace-2048767/

總結(jié)

以上是生活随笔為你收集整理的DBMS_SQLTUNE + SQL Performance Analyzer 实战小记的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。