一 寫在前面
前一段時(shí)間寫了幾篇使用Oracle 自動(dòng)優(yōu)化工具分析和優(yōu)化數(shù)據(jù)庫SQL語句性能的文章，這篇文章的目的是把前邊寫的內(nèi)容串聯(lián)起來，形成一個(gè)從負(fù)載抓取、性能分析、性能優(yōu)化直至優(yōu)化效果分析一個(gè)流程性的、總體性的過程，順便也補(bǔ)充一下以前沒有提過的知識(shí)點(diǎn)。
二 負(fù)載選取
選取待優(yōu)化的負(fù)載的時(shí)間段，一般是典型的業(yè)務(wù)時(shí)間段，或者是業(yè)務(wù)高峰時(shí)間段，也可以是數(shù)據(jù)庫出現(xiàn)性能問題的時(shí)間段。時(shí)間段的選取不需要太長，一般是十幾幾分鐘，最長也不多是三四個(gè)小時(shí)，太長的時(shí)間段容易掩蓋高峰期的問題。
三 獲取分析ORACLE AWR 報(bào)告
oracle數(shù)據(jù)庫默認(rèn)每個(gè)小時(shí)生成一個(gè)AWR快照，這個(gè)值可以根據(jù)需要更改，很多生產(chǎn)環(huán)境數(shù)據(jù)庫改成了半小時(shí)生成一個(gè)快照。如果是現(xiàn)場(chǎng)捕捉，需要在開始捕捉和結(jié)束捕捉前手動(dòng)生成awr快照，使用下面的命令
exec dbms_workload_repository.create_snapshot();
AWR報(bào)告的分析這里不做全面的介紹，和SQL相關(guān)的在SQL Statistics部分，如下
另外還要結(jié)合等待事件和Time Model Statistics等部分進(jìn)行綜合分析，從Oracle 12C 以后，AWR報(bào)告也包括ADDM報(bào)告，里面也可能有對(duì)SQL進(jìn)行優(yōu)化的指導(dǎo)，也是SQL 優(yōu)化很好的參考。
四 創(chuàng)建STS（SQL tuning set），載入工作負(fù)載
STS可以捕獲數(shù)據(jù)庫負(fù)載，用作性能性能監(jiān)控和優(yōu)化的輸入，STS包括以下組件：
- 包括一系列SQL語句
- 語句的相關(guān)執(zhí)行背景（context），例如用戶模式, 應(yīng)用模塊名和和動(dòng)作（action）, 綁定變量值列表, 以及游標(biāo)的sql編譯環(huán)境。
- 語句的基本執(zhí)行統(tǒng)計(jì)信息, 例如執(zhí)行事件, CPU事件, 緩沖區(qū)讀次數(shù), 磁盤讀次數(shù), 處理的行數(shù),游標(biāo)獲取數(shù), 執(zhí)行次數(shù), 完成執(zhí)行次數(shù), 優(yōu)化器成本, 以及命令類型。
- 相關(guān)執(zhí)行計(jì)劃和行源統(tǒng)計(jì)信息。
使用STS，可以將一系列SQL語句和相關(guān)元數(shù)據(jù)打包到一個(gè)數(shù)據(jù)庫對(duì)象中，這個(gè)數(shù)據(jù)庫對(duì)象可以用作性能優(yōu)化建議和其它優(yōu)化工具輸入，也可以將其傳輸?shù)狡渌鼣?shù)據(jù)庫上使用。捕獲到STS中的負(fù)載可以是自動(dòng)負(fù)載倉庫(AWR), 共享SQL區(qū), 特定的SQL語句, 追蹤文件, 或者其它STS。從AWR報(bào)告捕獲數(shù)據(jù)庫負(fù)載是一個(gè)常用的方法，因?yàn)锳WR快照是自動(dòng)捕獲的，要做的只是將特定時(shí)間段特定用戶的負(fù)載捕獲到STS集中即可。
1 創(chuàng)建sts
BEGIN
DBMS_SQLSET.CREATE_SQLSET (
sqlset_name => 'STS_f_AWR' --STS集名稱
, description => 'STS from awr snapshots' --STS集描述
);
END;
2 載入負(fù)載
通過設(shè)置DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY函數(shù)的parsing_schema_name參數(shù)，只載入用戶TEST的負(fù)載，這里TEST前后面要使用雙引號(hào)。
DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(55,56,
'parsing_schema_name = ''TEST''',
NULL, NULL,NULL,NULL,
1,
NULL,
'ALL')) P;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS_f_AWR',
populate_cursor => cur,
load_option => 'MERGE',
update_option => 'ACCUMULATE');
END;
3 查看載入的負(fù)載
SQL> select NAME,DESCRIPTION,CREATED,STATEMENT_COUNT from user_sqlset where name='STS_f_AWR';
NAME DESCRIPTION CREATED STATEMENT_COUNT
-------------------------------- ------------------------------------------------ --------- ---------------
STS_f_AWR STS from awr snapshots 04-SEP-25 9
五 使用SQL tuning task 分析工作負(fù)載
1 創(chuàng)建SQL 優(yōu)化任務(wù)
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => 'STS_f_AWR',
scope => DBMS_SQLTUNE.scope_comprehensive,
basic_filter =>'parsing_schema_name=''TEST''',
rank1 => 'BUFFER_GETS',
result_limit => 20,
time_limit => 3600,
task_name => 'my_SQL_Capture_task',
description => 'Tuning task for captured SQL');
END;
rank1設(shè)置語句的排序依據(jù)，這里選擇的緩沖區(qū)讀，實(shí)際選擇哪個(gè)排序需要根據(jù)AWR報(bào)告的分析結(jié)果來確定，緩沖區(qū)讀是一個(gè)比較合適的指標(biāo)，因?yàn)榫彌_區(qū)讀比較大的SQL往往CPU執(zhí)行時(shí)間和物理IO也比較大。
2 執(zhí)行優(yōu)化任務(wù)
SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_SQL_Capture_task');
3 查看和分析優(yōu)化報(bào)告
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_SQL_Capture_task') FROM DUAL;
這里只摘錄報(bào)告比較重要的部分
SUMMARY SECTION
-------------------------------------------------------------------------------
Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed : 9
Number of SQLs in the Report : 3
Number of SQLs with Findings : 3
Number of SQLs with Alternative Plan Findings: 2
Number of SQLs with SQL profiles recommended : 1
Number of SQLs with Index Findings : 1
這一部分報(bào)告的匯總，分析了9條SQL語句，在報(bào)告中（可以優(yōu)化的）有3條，有兩條語句有替代的較優(yōu)的執(zhí)行計(jì)劃，一條語句有可以推薦的SQL Profile，一條語句可以通過索引優(yōu)化。
-------------------------------------------------------------------------------
SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID SQL ID statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
2 dbafg4pk1czwu 74.18%
7 c4uzwc14895n6 58.00%
3 b2n572dgtndrn
這一部分是SQL可以從優(yōu)化中獲得的收益。
-------------------------------------------------------------------------------
Tables with New Potential Indices (ordered by schema, number of times, table)
-------------------------------------------------------------------------------
Schema Name Table Name Index Name Nb Time
--------------------------- --------------------------- -------------- --------
TEST CUSTOMER_ORDERS IDX$$_00C80002 1
-------------------------------------------------------------------------------
這一部分是索引優(yōu)化影響到的表。下面是報(bào)告的詳細(xì)部分。
DETAILS SECTION
-------------------------------------------------------------------------------
Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
Object ID : 2
Schema Name : TEST
Container Name: FREEPDB1
SQL ID : dbafg4pk1czwu
SQL Text : SELECT
o.ORDER_ID,
o.CUSTOMER_ID,
........
這一部分是按照SQL語句的最大收益排序的，收益最大的在最前面，這條語句有兩個(gè)finding（優(yōu)化建議）
1- 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: 74.18%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index TEST.IDX$$_00C80002 on TEST.CUSTOMER_ORDERS("TOTAL_AMOUNT","OR
DER_DATE");
索引優(yōu)化，評(píng)估的收益是74.18%,Oracle 給出了兩個(gè)優(yōu)化建議，一是創(chuàng)建索引，在這里給出了索引的創(chuàng)建語句，二是運(yùn)行訪問建議來提高物理模式設(shè)計(jì)。
對(duì)于這條語句的第二個(gè)優(yōu)化措施是更優(yōu)的物理計(jì)劃
2- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
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 3272358955 2025-09-04/02:58:55 13.938 AWR
2 4246839055 2025-09-04/06:03:12 14.640 AWR original plan
3 1987091166 2025-09-04/02:58:55 17.939 AWR
從上面可以看到，這條語句的三個(gè)執(zhí)行計(jì)劃的執(zhí)行時(shí)間差別不是太大，優(yōu)化措施提升有限，應(yīng)該首先考慮第一個(gè)創(chuàng)建索引來優(yōu)化，如果因?yàn)槟撤N原因不能創(chuàng)建索引，可以考慮這個(gè)優(yōu)化措施。下面看對(duì)下一條語句的優(yōu)化措施
Object ID : 7
Schema Name : TEST
Container Name: FREEPDB1
SQL ID : c4uzwc14895n6
SQL Text : SELECT ORDER_ID FROM CUSTOMER_ORDERS WHERE ROWNUM <= 1000
ORDER BY DBMS_RANDOM.RANDOM
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 58%)
---------------------------------------
- Consider accepting the recommended SQL profile.
BEGIN
dbms_sqltune.accept_sql_profile(
task_name => 'my_SQL_Capture_task',
object_id => 7,
task_owner => 'SYS',
replace => TRUE);
END;
/
這一條語句可以通過SQL profile來優(yōu)化，報(bào)告里給出了接受這個(gè)profile的語句。看一下采用這個(gè)profile后這條語句的收益
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .002962 .002455 17.11 %
CPU Time (s): .002994 .002446 18.3 %
User I/O Time (s): 0 0
Buffer Gets: 36 15 58.33 %
這條語句的性能本身沒有明顯的問題，執(zhí)行時(shí)間才.002962秒，這個(gè)措施根據(jù)情況也可以不實(shí)施。下面看第三條語句的優(yōu)化措施
Object ID : 3
Schema Name : TEST
Container Name: FREEPDB1
SQL ID : b2n572dgtndrn
SQL Text : SELECT
c.CUSTOMER_ID,
c.CUSTOMER_NAME,
......
.......
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
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 660144547 2025-09-04/06:03:12 14.271 AWR original plan
2 305751537 2025-09-04/02:58:55 57.524 AWR
這條語句有更好的執(zhí)行計(jì)劃，可以設(shè)置為SQL plan基線，將這個(gè)執(zhí)行計(jì)劃固定下來，報(bào)告也給出了設(shè)定基線的模板
BEGIN
dbms_sqltune.create_sql_plan_baseline(
task_name => 'my_SQL_Capture_task',
object_id => 3,
owner_name => 'SYS',
plan_hash_value => xxxxxxxx);
END;
/
將plan_hash_value值改為性能更優(yōu)的執(zhí)行計(jì)劃的值，就可以創(chuàng)建sql plan 基線了。
六 使用SQL Access Advisory 分析工作負(fù)載
1 創(chuàng)建DBMS建議任務(wù)并關(guān)聯(lián)負(fù)載
1） 創(chuàng)建任務(wù)
begin
DBMS_ADVISOR.CREATE_TASK (advisor_name =>'SQL Access Advisor',
task_name=> 'my_task');
END; /
2）查看創(chuàng)建的任務(wù)
SQL> select TASK_NAME,ADVISOR_NAME,TASK_ID,STATUS from USER_ADVISOR_TASKS where TASK_NAME='my_task'
TASK_NAME ADVISOR_NAME TASK_ID STATUS
-------------------------------- -------------------------------- ---------- -----------
my_task SQL Access Advisor 205 INITIAL
3）關(guān)聯(lián)數(shù)據(jù)庫負(fù)載
SQL> EXECUTE DBMS_ADVISOR.ADD_STS_REF('my_task', null, 'STS_f_AWR');
4）設(shè)置任務(wù)參數(shù)
SQL> EXECUTE DBMS_ADVISOR.SET_TASK_PARAMETER('my_task','STORAGE_CHANGE', 1000000000);
5）執(zhí)行訪問建議任務(wù)
SQL> EXECUTE DBMS_ADVISOR.EXECUTE_TASK('my_task');
2 獲取任務(wù)分析報(bào)告
1) 檢查任務(wù)完成狀態(tài)
SQL> select TASK_NAME,ADVISOR_NAME,TASK_ID,STATUS from USER_ADVISOR_TASKS where TASK_NAME='my_task';
TASK_NAME ADVISOR_NAME TASK_ID STATUS
-------------------------------- -------------------------------- ---------- -----------
my_task SQL Access Advisor 205 COMPLETED
2)獲取報(bào)告
select DBMS_ADVISOR.GET_TASK_REPORT ('my_task') from dual;
3 查詢相關(guān)視圖分析
1） 查詢建議記錄及收益
SQL> SELECT REC_ID, RANK, BENEFIT
FROM USER_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME ='my_task'; 2
REC_ID RANK BENEFIT
---------- ---------- ----------
1 1 1766240
2 2 0
3 3 0
- 查詢建議對(duì)整個(gè)負(fù)載的影響
SQL> l
1 SELECT a.sql_id, a.rec_id, a.precost, a.postcost,
2 (a.precost-a.postcost)*100/a.precost AS percent_benefit,
3 dbms_lob.substr(h.sql_text,40) sql_text
4 FROM USER_ADVISOR_SQLA_WK_STMTS a left join dba_hist_sqltext h on a.sql_id=h.sql_id
5* WHERE TASK_NAME = 'my_task' order by 2,3
SQL> /
SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT SQL_TEXT
------------- ---------- ---------- ---------- --------------- ----------------------------------------------------------------
c4uzwc14895n6 0 7775 7775 0 SELECT ORDER_ID FROM CUSTOMER_ORDERS WHE
c4uzwc14895n6 0 7775 7775 0 SELECT ORDER_ID FROM CUSTOMER_ORDERS WHE
cr2852yv20dak 0 2499221 2499221 0 SELECT
order_date,
cr2852yv20dak 0 2499221 2499221 0 SELECT
order_date,
5k2dzhzjk2575 0 7487304 7487304 0 INSERT INTO CUSTOMER_ORDERS (ORDER_ID, C
5k2dzhzjk2575 0 7487304 7487304 0 INSERT INTO CUSTOMER_ORDERS (ORDER_ID, C
dbafg4pk1czwu 0 12094240 12094240 0 SELECT
o.ORDER_ID,
dbafg4pk1czwu 0 12094240 12094240 0 SELECT
o.ORDER_ID,
9s3d9fr4fxc9s 1 3859240 3039960 21.2290503 WITH customer_sales AS (
9s3d9fr4fxc9s 1 3859240 3039960 21.2290503 WITH customer_sales AS (
b2n572dgtndrn 1 4488092 3541132 21.0993892 SELECT
c.CUSTOMER_I
b2n572dgtndrn 1 4488092 3541132 21.0993892 SELECT
c.CUSTOMER_I
a6kst3w6cjppj 2 7161 7161 0 UPDATE CUSTOMERS SET STATUS = :1, LAST_U
a6kst3w6cjppj 2 7161 7161 0 UPDATE CUSTOMERS SET STATUS = :1, LAST_U
fft09zpbz57xf 2 9210 9210 0 SELECT * FROM CUSTOMERS WHERE CUSTOMER_I
fft09zpbz57xf 2 9210 9210 0 SELECT * FROM CUSTOMERS WHERE CUSTOMER_I
7fbag4fbuq5g1 3 4668 4668 0 DELETE FROM CUSTOMER_ORDERS WHERE ORDER_
7fbag4fbuq5g1 3 4668 4668 0 DELETE FROM CUSTOMER_ORDERS WHERE ORDER_
3) 查詢每條建議及操作
SQL> select REC_ID,ACTION_ID,SUBSTR(command,1,30) AS command,ATTR1,ATTR3,ATTR4 from user_advisor_actions
where task_name='my_task'
REC_ID ACTION_ID COMMAND ATTR1 ATTR3 ATTR4
---------- ---------- ------------------------ -------------------------------- ------------------------ ------------
1 3 CREATE INDEX "TEST"."CUSTOMERS_IDX$$_00CD0000 "TEST"."CUSTOMERS" BTREE
"
2 2 RETAIN INDEX "TEST"."SYS_C008516" "TEST"."CUSTOMERS" BTREE
3 1 RETAIN INDEX "TEST"."SYS_C008547" "TEST"."CUSTOMER_ORDERS" BTREE
- 生成建議的執(zhí)行腳本
SQL> select DBMS_ADVISOR.GET_TASK_SCRIPT('my_task') from dual;
DBMS_ADVISOR.GET_TASK_SCRIPT('MY_TASK')
--------------------------------------------------------------------------------
Rem SQL Access Advisor: Version 23.0.0.0.0 - Production
Rem
Rem Username: SYS
Rem Task: my_task
Rem Execution date: 04/09/2025 07:58
Rem
/* RETAIN INDEX "TEST"."SYS_C008547" */
/* RETAIN INDEX "TEST"."SYS_C008516" */
CREATE INDEX "TEST"."CUSTOMERS_IDX$$_00CD0000"
ON "TEST"."CUSTOMERS"
("CUSTOMER_ID","CUSTOMER_NAME")
COMPUTE STATISTICS;
六 應(yīng)用SQL profile
在SQL優(yōu)化任務(wù)報(bào)告中有一個(gè)推薦的SQL profile，接受這個(gè)profile，運(yùn)行報(bào)告中的相關(guān)命令
SQL> BEGIN
dbms_sqltune.accept_sql_profile(
task_name => 'my_SQL_Capture_task',
object_id => 7,
task_owner => 'SYS',
replace => TRUE);
END; 2 3 4 5 6 7
8 /
PL/SQL procedure successfully completed.
檢查創(chuàng)建的SQL profile
select * from DBA_SQL_PROFILES;
SYS_SQLPROF_019917db92610000 DEFAULT 10456046097614862085 SELECT ORDER_ID FROM CUSTOMER_ORDERS WHERE ROWNUM <= 1000 ORDER BY DBMS_RANDOM.RANDOM 2025-09-05 03:11:29.907 2025-09-05 03:11:29.000 MANUAL ENABLED NO 200 EXEC_171 7 6 4 0
檢查語句的執(zhí)行計(jì)劃
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 12g82ymb6dxa5, child number 0
-------------------------------------
SELECT ORDER_ID FROM CUSTOMER_ORDERS WHERE ROWNUM <= 1000
ORDER BY DBMS_RANDOM.RANDOM
Plan hash value: 1211848268
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT ORDER BY | | 1000 | 6000 | 2 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | INDEX FAST FULL SCAN| SYS_C008547 | 1001 | 6006 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=1000)
Note
-----
- SQL profile SYS_SQLPROF_019917db92610000 used for this statement
note部分優(yōu)化器在解析語句的過程中使用了SQL profile，兩條語句的sql_id并不相同，這是因?yàn)閮蓷l語句的空格可能不完全一樣，經(jīng)Oracle normalize（標(biāo)準(zhǔn)化）之后相同，所以SQL profile照樣可用。
語句原來的執(zhí)行計(jì)劃也附在下面
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3349094533
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 1893 (1)| 00:
00:01 |
| 1 | SORT ORDER BY | | 1000 | 6000 | 1893 (1)| 00:
00:01 |
|* 2 | COUNT STOPKEY | | | | |
|
| 3 | TABLE ACCESS FULL| CUSTOMER_ORDERS | 1009K| 5912K| 1893 (1)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=1000)
通過對(duì)比兩個(gè)執(zhí)行計(jì)劃，發(fā)現(xiàn)應(yīng)用了SQL profile后的執(zhí)行計(jì)劃使用在第三個(gè)操作使用了INDEX FAST FULL SCAN，成本降低到了2（原來是1893），調(diào)整后的執(zhí)行計(jì)劃更適應(yīng)大數(shù)據(jù)量下的查詢。
七 應(yīng)用SQL plan baseline
調(diào)優(yōu)報(bào)告推薦的SQL plan baseline，對(duì)語句的優(yōu)化幅度還是比較大的。也采用一下
SQL> BEGIN
dbms_sqltune.create_sql_plan_baseline(
task_name => 'my_SQL_Capture_task',
object_id => 3,
owner_name => 'SYS',
plan_hash_value => 660144547);
END;
/ 2 3 4 5 6 7 8
BEGIN
*
ERROR at line 1:
ORA-13846: Cannot create SQL plan baseline on the given plan
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 19873
ORA-06512: at "SYS.DBMS_SQLTUNE", line 11941
ORA-06512: at line 2
Help: https://docs.oracle.com/error-help/db/ora-13846/
創(chuàng)建的過程中報(bào)錯(cuò)了，這個(gè)報(bào)錯(cuò)有兩個(gè)原因，一個(gè)是輸入值對(duì)應(yīng)多個(gè)執(zhí)行計(jì)劃，另一種情況是執(zhí)行計(jì)劃不可重建，如果仍然試圖優(yōu)化，可以針對(duì)這條語句運(yùn)行SQL 調(diào)優(yōu)任務(wù)，步驟如下
---創(chuàng)建單條SQL調(diào)優(yōu)任務(wù)，使用sql_id作為參數(shù)
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'gs6dd5p7xj7ry',
task_name => 'singlesql');
END;
---執(zhí)行調(diào)優(yōu)任務(wù)
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('singlesql');
----查看調(diào)優(yōu)報(bào)告
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('singlesql') FROM DUAL;
檢查調(diào)優(yōu)報(bào)告，發(fā)現(xiàn)沒有任何優(yōu)化建議。如果想要將這條SQL語句現(xiàn)在的執(zhí)行計(jì)劃設(shè)置為SQL plan 基線，可以通過設(shè)置optimizer_capture_sql_plan_baselines參數(shù)自動(dòng)捕捉運(yùn)行語句的執(zhí)行計(jì)劃為SQL plan 基線，運(yùn)行下面語句
ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
--要捕捉的語句
ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;
檢查創(chuàng)建的基線
SQL>SELECT b.sql_handle,b.plan_name,b.notes from dba_sql_plan_baselines b;
SQL_HANDLE PLAN_NAME NOTES
------------------------------ ------------------------------------ ------------------------------------------------------------
SQL_600ff232b5fd1ed8 SQL_PLAN_603zk6auzu7qs23275cf2 <notes><plan_id>589782258</plan_id><flags>11</flags></notes>
刷新數(shù)據(jù)庫共享緩沖區(qū)后再次運(yùn)行SQL語句，執(zhí)行計(jì)劃如下
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gs6dd5p7xj7ry, child number 0
-------------------------------------
SELECT c.CUSTOMER_ID,
c.CUSTOMER_NAME,
COUNT(o.ORDER_ID) AS order_count,
SUM(o.TOTAL_AMOUNT) AS total_amount,
AVG(o.TOTAL_AMOUNT) AS avg_amount,
MAX(o.TOTAL_AMOUNT) AS max_amount,
MIN(o.TOTAL_AMOUNT) AS min_amount
FROM CUSTOMERS c
JOIN
CUSTOMER_ORDERS o.CUSTOMER_IDR_ID =
WHERE
o.ORDER_DATE >= ADD_MONTHS(SYSDATE, -6)
GROUP BY c.CUSTOMER_ID,
c.CUSTOMER_NAME HAVING
Plan hash value: 660144547
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 3601 (100)| |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 735 | 95550 | | 3601 (1)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY | | 735 | 75705 | | 3601 (1)| 00:00:01 |
|* 4 | FILTER | | | | | | |
| 5 | HASH GROUP BY | | 735 | 75705 | | 3601 (1)| 00:00:01 |
|* 6 | HASH JOIN | | 14688 | 1477K| 1352K| 3597 (1)| 00:00:01 |
| 7 | VIEW | VW_GBC_6 | 14688 | 1176K| | 1913 (2)| 00:00:01 |
| 8 | HASH GROUP BY | | 14688 | 243K| | 1913 (2)| 00:00:01 |
|* 9 | TABLE ACCESS FULL| CUSTOMER_ORDERS | 210K| 3502K| | 1908 (2)| 00:00:01 |
| 10 | TABLE ACCESS FULL | CUSTOMERS | 300K| 6152K| | 1149 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
4 - filter(NVL(SUM("ITEM_2"),0)>=5)
6 - access("C"."CUSTOMER_ID"="ITEM_1")
9 - filter("O"."ORDER_DATE">=ADD_MONTHS(SYSDATE@!,(-6)))
Note
-----
- SQL plan baseline SQL_PLAN_603zk6auzu7qs23275cf2 used for this statement
Note 部分顯示這條語句應(yīng)用了SQL plan baseline,使用的SQL plan同前面查詢的相同。查看這個(gè)基線的詳細(xì)內(nèi)容用下面的語句
SQL> SELECT *
FROM table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_600ff232b5fd1ed8')); 2
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_600ff232b5fd1ed8
SQL text: SELECT c.CUSTOMER_ID,
c.CUSTOMER_NAME,
---中間省略多行
total_amount DES FETCH FIRST
100 ROWS ONLY
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_603zk6auzu7qs23275cf2 Plan id: 660144547
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From Auto SQL Tuning Set
--------------------------------------------------------------------------------
Plan hash value: 660144547
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 3601 (100)| |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 735 | 95550 | | 3601 (1)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY | | 735 | 75705 | | 3601 (1)| 00:00:01 |
|* 4 | FILTER | | | | | | |
| 5 | HASH GROUP BY | | 735 | 75705 | | 3601 (1)| 00:00:01 |
|* 6 | HASH JOIN | | 14688 | 1477K| 1352K| 3597 (1)| 00:00:01 |
| 7 | VIEW | VW_GBC_6 | 14688 | 1176K| | 1913 (2)| 00:00:01 |
| 8 | HASH GROUP BY | | 14688 | 243K| | 1913 (2)| 00:00:01 |
|* 9 | TABLE ACCESS FULL| CUSTOMER_ORDERS | 211K| 3503K| | 1908 (2)| 00:00:01 |
| 10 | TABLE ACCESS FULL | CUSTOMERS | 300K| 6152K| | 1149 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
4 - filter(NVL(SUM("ITEM_2"),0)>=5)
6 - access("C"."CUSTOMER_ID"="ITEM_1")
9 - filter("O"."ORDER_DATE">=ADD_MONTHS(SYSDATE@!,(-6)))
可以看到基線的詳細(xì)信息包括其執(zhí)行計(jì)劃。使用下面語句可以顯示基線的hint
SQL> SELECT *
FROM table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_600ff232b5fd1ed8',
format => 'outline'));
---內(nèi)容比較多，只摘取基線的hint部分
Outline Data from SMB:
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$433063BA" GROUP_BY)
FULL(@"SEL$433063BA" "O"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$C4C0C15C" GROUP_BY)
USE_HASH(@"SEL$C4C0C15C" "C"@"SEL$1")
LEADING(@"SEL$C4C0C15C" "VW_GBC_6"@"SEL$9E8AE3DA" "C"@"SEL$1")
FULL(@"SEL$C4C0C15C" "C"@"SEL$1")
NO_ACCESS(@"SEL$C4C0C15C" "VW_GBC_6"@"SEL$9E8AE3DA")
NO_ACCESS(@"SEL$3" "from$_subquery$_004"@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE(@"SEL$58A6D7F6")
OUTLINE(@"SEL$9E8AE3DA")
OUTLINE_LEAF(@"SEL$3")
PLACE_GROUP_BY(@"SEL$58A6D7F6" ( "O"@"SEL$1" ) 6)
OUTLINE_LEAF(@"SEL$C4C0C15C")
OUTLINE_LEAF(@"SEL$433063BA")
ALL_ROWS
DB_VERSION('23.1.0')
OPTIMIZER_FEATURES_ENABLE('23.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
八 寫在后面
本文提供了一個(gè)使用Oracle SQL優(yōu)化工具系統(tǒng)進(jìn)行數(shù)據(jù)庫SQL進(jìn)行優(yōu)化的過程，使用這個(gè)過程可以節(jié)省DBA大量的分析工作，提高工作效率。
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵(lì)」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時(shí)必須標(biāo)注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，墨天輪將立刻刪除相關(guān)內(nèi)容。
