各位小伙伴，一般都用哪些優化工具，Oracle SQL Tuning Advisor (STA)用的多嗎，Profile就是它的其中1個產物，下一期再弄Profile，STA 的核心功能是自動化診斷高負載SQL的性能瓶頸?（如全表掃描、缺失索引），通過深度分析執行計劃提供優化建議（如索引創建、SQL結構重寫），并生成SQL Profile，不侵入、在不修改原SQL的前提下注入優化器指令，強制修正基數估計偏差或訪問路徑，從而提升查詢效率。

?解放雙手顯著降低人工調優成本，尤其對復雜查詢和大規模數據場景，能快速提供量化收益（如索引優化提升性能），還可以優化適配第三方封裝SQL；不過優化同樣不是萬能的，STA依賴統計信息準確性（過期統計會導致建議失效）且僅針對單條SQL優化，可能忽略全局影響（如索引增加寫負載），復雜邏輯場景需要人工參與和經驗的判斷。結合AWR定位TOP SQL后調用STA生成方案，關鍵建議需測試驗證，并定期復審Profile有效性，以適配數據與業務側的調整變更。

?1. 優化器雙模式機制?

?2. 四大分析維度?

?3. SQL Profile 工作（單開一期）?

二、演進過程與版本特性??

優化深度對比?：?

三、實踐腳本（10g 至 23ai）?

1. 自動捕獲高負載 SQL（10g~23ai）?

-- 查詢V$SQL視圖（實時TOP SQL）
SELECT sql_id, sql_text, executions, buffer_gets, disk_reads, elapsed_time
FROM v$sql 
WHERE buffer_gets > 10000   -- 過濾高內存消耗OR elapsed_time > 1000000 -- 過濾長耗時
ORDER BY elapsed_time DESC;  -- 按執行時間排序
-- 從 AWR 獲取 TOP SQL ID
-- 創建AWR快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');  -- 起始快照-- 等待高負載時段，后再次創建快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');  -- 結束快照-- 查詢快照ID
SELECT snap_id, begin_interval_time 
FROM dba_hist_snapshot 
ORDER BY snap_id DESC;
--
SELECT snap_id, begin_interval_time
FROM dba_hist_snapshot3  ORDER BY snap_id DESC;SNAP_ID BEGIN_INTERVAL_TIME
---------- ---------------------------------------------------------------------------7022 21-6月 -25 11.00.34.946 上午7021 21-6月 -25 10.00.22.850 上午7020 21-6月 -25 09.00.10.116 上午7019 21-6月 -25 08.00.58.180 上午7018 21-6月 -25 07.00.46.532 上午
-- 生成AWR報告中高負載SQL（查詢下面手工輸入變量快照ID）
SELECT sql_id, executions, buffer_gets, disk_reads, elapsed_time, sql_text
FROM dba_hist_sqlstat 
WHERE snap_id BETWEEN &start_snap AND &end_snap   -- 替換為實際快照ID
ORDER BY buffer_gets DESC;  -- 按內存消耗排序
--不用awr，直接用ASH查詢sql_id
-- 使用ASH實時監控（10g+）
SELECT ash.SQL_ID, ash.SESSION_ID, sq.SQL_TEXT,  -- 從 V$SQL 獲取文本ash.WAIT_TIME, ash.TIME_WAITED
FROM V$ACTIVE_SESSION_HISTORY ash
JOIN V$SQL sq ON ash.SQL_ID = sq.SQL_ID  -- 關聯 SQL 文本視圖
WHERE ash.SQL_ID IS NOT NULLAND ash.TIME_WAITED > 100
ORDER BY ash.SAMPLE_TIME DESC;  -- 按采樣時間排序
---

?2. 調優任務通過(sql_id、sql_text)建立

-- 創建任務（支持 SQL_ID 或 SQL 文本），從AWR或是top sql中查詢SQL ID
--通過
DECLAREtask_name VARCHAR2(30);
BEGINtask_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id        => 'dqucusk8avvuh',   -- 11g+ 支持--sql_text      => 'SELECT * FROM HR.EPMLOYEES WHERE salary > :1',--sql_text和上面的sql_id，二選一均可10g+scope         => 'COMPREHENSIVE',time_limit    => 60,task_name     => 'tuning_task1');DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name); -- 執行任務
END;
/
--PL/SQL 過程已成功完成。
-- 查看報告
SET LONG 1000000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tuning_task1') FROM DUAL; -- 輸出優化建議
--
SYS@test19> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tuning_task1') FROM DUAL;DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_TASK1')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------
---------------------------
Tuning Task Name   : tuning_task1
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 06/21/2025 12:12:15
Completed at       : 06/21/2025 12:12:15----------------------------------------------------------
---------------------
Schema Name   : SYS
Container Name: PDBRS6
SQL ID        : dqucusk8avvuh
SQL Text      : delete /* KSXM:CLEAN_COL_USAG
E *//*+ dynamic_sampling(4) */from sys.col_usage$ c   where
((timestamp < sysdate - 367)or not exists
(select /*+ unnest */ 1 fromsys.obj$ o where o.obj# = c.obj#))and c.obj# < :1  andrownum <=  :2
………………
ALTERNATIVE PLANS SECTION
---------------------------------------------------------
----------------------Plan 1
------Plan Origin                 :Cursor Ca
chePlan Hash Value             :159303012Executions                  :42Elapsed Time                :0.006 secCPU Time                    :0.005 secBuffer Gets                 :2680Disk Reads                  :2Disk Writes                 :0Notes:1. Statistics shown are averaged over multiple execu
tions.2. 在當前環境中無法重新生成具有 ID 1 的計劃。由于此原因, 無法創建 SQL 計劃基線以指示
Oracle 優化程序在將來選取該計劃。---------------------------------------------------------
----------------------------------------
------
| Id  | Operation                              | Name| Rows  | Bytes | Cost (%CPU)|
Time     |
-------------------------------------------------
----------------------------------------
--------------
|   0 | DELETE STATEMENT|              |       |       |    15(100)|          |
|   1 |  DELETE| COL_USAGE$   |       |       ||          |
|   2 |   COUNT STOPKEY                        ||       |       |            ||
|   3 |    FILTER                              ||       |       |            ||
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED|
COL_USAGE$   |   150 |  2700 |    15   (
0)| 00:00:01 |
|   5 |      INDEX RANGE SCAN| I_COL_USAGE$ |    27 |       |     2(0)| 00:00:01 |
|   6 |     INDEX SKIP SCAN| I_OBJ1       |     1 |     5 |2   (0)| 00:00:01 |
---------------------------------------------------------
SYS@test19>

3. SQL Profile 接受應用（11g+）?

-- 接受自動 Profile
BEGINDBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'tuning_task1',name      => 'profile_force_index',force_match => TRUE  -- 12c+ 支持結構相似 SQL 匹配);
END;
/-- 手工創建 Profile（強制索引）
BEGINDBMS_SQLTUNE.IMPORT_SQL_PROFILE(name     => 'manual_profile',sql_text => 'SELECT * FROM orders WHERE order_id=:1',profile  => SQLPROF_ATTR('INDEX(orders idx_order_id)') -- 注入 Hint);
END;
/

四、注意事項與驗證?

GRANT ADVISOR, SELECT_CATALOG_ROLE TO user_STA; -- 基礎權限
GRANT EXECUTE ON DBMS_SQLTUNE TO user_STA;      -- 必要執行權限

-- 對比優化前后執行計劃
--舉例HR.EMPLOYEES
EXPLAIN PLAN FOR SELECT * FROM HR.EMPLOYEES WHERE salary > 5000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);