57-Oracle SQL Profile(23ai)實操

在上一期中說到了SQL Tuning Advisor其中一個影響對象就是SQL Profile，同樣在管理和應用開發中,SQL性能優化是個任重道遠的工作，低效的SQL語句讓應用響應緩慢,用戶整體體驗下降,拖垮搞蹦整個系統都有可能。Oracle數據庫提供了多種組合工具，有的免費有的是需要許可，系統而全面地解決這些性能問題。SQL自動調優(Automatic SQL Tuning)與SQL Profile是從10g就引入的重要特性，提供解決SQL性能問題強大的支持。

一、SQL是否需要自動調優，什么情況下可以接受自動？

數據庫的查詢優化器(Optimizer)在執行SQL語句前,會根據統計信息、系統參數等因素生成一個或多個執行計劃(Execution Plan),并選擇一個它認為當前成本已經是最優的計劃來執行。
  • 但是在一些因素的影響下，CBO會受影響出次優或是更差的選擇，例如：
  1. 統計信息過時或缺失：當表的數據量、數據分布發生顯著變化,而統計信息未能及時更新時,優化器基于舊信息做出的判斷可能不再準確。
  2. 復雜的查詢結構：對于包含多表連接、子查詢、復雜謂詞的SQL,優化器估算成本的難度會大大增加。（23ai之后有了機器學習是不是有提升）
  • 原生優化器局限性：優化器模型本身可能無法完美處理所有情況。
  • 傳統的解決方法通常是在SQL語句中手動添加提示(Hints),以指導優化器選擇特定的場景，但這種方式又降存在這些缺陷：
  1. 侵入式修改：需要修改應用程序代碼,對于已封裝的商業軟件或第三方系統,幾乎是不可能的任務，且不可延續。
  2. 維護困難：隨著數據和業務需求需求調整和運行環境的變化,也許當前有效的Hint，就在不遠的下一次失效甚至產生負面影響。
基于這樣的場景和選擇，Oracle引入了自動SQL調優機制,提供更智能、非侵入的方式改善SQL執行效率的更優雅的方式。

二、SQL Profile 分類與原理?

?1. Auto SQL Profile（基于SQL Tuning Advisor）??
? 本質?：優化器輔助統計信息（如基數校正因子 SCALE_ROWS），動態修正執行計劃成本計算。
? 工作原理?：
  • ?分析階段?：校驗統計信息、識別異常訪問路徑（如缺失索引或全表掃描成本失真）。
  • ?生成階段?：自動創建校正因子（如將表基數從100修正為100萬）。
  • ?生效機制?：優先使用Profile中的統計信息覆蓋默認值，優化器據此重新計算成本。
? 優點?：
  • ?自動化高效?：一鍵生成，適合批量優化AWR中的高頻低效SQL。
  • ?動態適應?：隨數據變化自動調整，避免計劃固化（如新增索引時自動啟用）。
  • ?低風險?：內置成本對比驗證，規避極端路徑（如不會強制禁用所有索引）。
? 缺點?：
  • ?優化局限?：無法突破優化器規則（如不能強制嵌套循環替代哈希連接）。
  • ?環境依賴?：嚴重依賴當前統計信息，跨環境遷移（測試→生產）可能失效。
  • ?黑盒操作?：修正邏輯存儲在隱藏表 SQLPROF$，問題追溯困難。
2. 手工SQL Profile?
? 本質?：Hint組合（從目標執行計劃提取Outline Data），強制鎖定執行路徑。
? 核心原理?：
  1. 提取原始SQL的Outline Data。
  2. 通過Hint改寫SQL生成目標Outline Data。
  3. 將腳改寫的帶有Hint的替換原始的版本，生成最終Profile。
? 優點?：
  • ?精準控制?：繞過優化器決策，強制固定最優路徑（如索引掃描、連接順序）。
  • ?優先級碾壓?：覆蓋SQL中的硬編碼Hint（如 /*+ FULL(T) */ 無效）。
  • ?靈活生效?：通過?CATEGORY 按會話/環境隔離（如測試環境用 DEV 類別、生產用PROD）。
? 缺點?：
  • ?技術門檻高?：需深入理解成本模型，錯誤設置引發性能惡化（如基數誤判導致笛卡爾積）。
  • ?維護成本大?：表結構變更（如索引刪除）需人工重新驗證Profile有效性。
  • ?過優化風險?：強制計劃可能失效（如索引失效仍強制掃描，引發全表掃描被禁用）。

三. 實操演示：23 ai上HR.T4SQLPROFILE 表實驗?

?步驟1：創建測試環境?
SYS@CDB$ROOT> alter session set container=FREEPDB1;
-- 建表并模擬插入不均衡數據
CREATE TABLE HR.T4SQLPROFILE AS 
SELECT * FROM DBA_OBJECTS WHERE ROWNUM <= 10000;INSERT INTO HR.T4SQLPROFILE 
SELECT * FROM DBA_OBJECTS 
WHERE object_id = 100 AND ROWNUM <= 9000;  -- 90% 數據集中CREATE INDEX HR.IDX_T4SQLPROFILE ON HR.T4SQLPROFILE(object_id);-- 收集統計信息
BEGINDBMS_STATS.GATHER_TABLE_STATS('HR','T4SQLPROFILE');
END;
/
--PL/SQL procedure successfully completed.
步驟2：自動 Profile 實驗

官方文檔DBMS_SQLTUNE.ACCEPT_SQL_PROFILE 的 FORCE_MATCH 參數默認值為 FALSE，即必須在 SQL 文本完全匹配時應用 SQL Profile才會被使用，若目標 SQL 文本有改動即使是where 后面的字句查詢不一致，原有 SQL Profile 就失效。。所以必須設置“DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name, force_match=>TRUE);”

-- 低效全表掃描（因 NO_INDEX Hint）
SELECT /*+ NO_INDEX(T) */ * FROM HR.T4SQLPROFILE T WHERE object_id=10;-- 創建調優任務并接受 Profile
DECLAREtask_name VARCHAR2(50);
BEGINtask_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => 'SELECT /*+ NO_INDEX(T) */ * FROM HR.T4SQLPROFILE T WHERE object_id=10',scope       => 'COMPREHENSIVE',task_name   => 'AUTO_TUNE_TASK');DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name, force_match=>TRUE);
END;
/-- 驗證：計劃轉為索引掃描
--驗證SQL Profile被接受，同時查詢走index
SELECT * FROM HR.T4SQLPROFILE WHERE object_id=10; 
--
SYS@CDB$ROOT> SELECT * FROM HR.T4SQLPROFILE WHERE object_id=10;
SYS      C_USER#     10                10 CLUSTER        24-APR-25    
--
SYS@CDB$ROOT> SET AUTOTRACE ON
Autotrace TraceOnlyExhibits the performance statistics with silent query output
SELECT * FROM HR.T4SQLPROFILE WHERE object_id=20;
SYS@CDB$ROOT> SELECT * FROM HR.T4SQLPROFILE WHERE object_id=20;1 row selected.SQL_ID  cy30yj4480y2q, child number 0
-------------------------------------
SELECT * FROM HR.T4SQLPROFILE WHERE object_id=20Plan hash value: 2117281514-------------------------------------------------------------------------
| Id  | Operation                           | Name             | E-Rows |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |        |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T4SQLPROFILE     |      1 |
|*  2 |   INDEX RANGE SCAN                  | IDX_T4SQLPROFILE |      1 |
-------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("OBJECT_ID"=20)Note
------ Warning: basic plan statistics not available. These are only collected when:* hint 'gather_plan_statistics' is used for the statement or* parameter 'statistics_level' is set to 'ALL', at session or system levelStatistics
-----------------------------------------------------------1  CPU used by this session1  CPU used when call started5  Requests to/from client4  SQL*Net roundtrips to/from client4  buffer is not pinned count944  bytes received via SQL*Net from client67782  bytes sent via SQL*Net to client5  calls to get snapshot scn: kcmgss2  calls to kcmgcs4  consistent gets1  consistent gets examination1  consistent gets examination (fastpath)4  consistent gets from cache3  consistent gets pin3  consistent gets pin (fastpath)1  enqueue releases1  enqueue requests2  execute count1  index range scans32768  logical read bytes from cache3  no work - consistent read gets10  non-idle wait count2  opened cursors cumulative1  opened cursors current1  parse count (hard)2  parse count (total)169  process last non-idle time1  recursive calls4  session logical reads1  sorts (memory)1620  sorts (rows)1  table fetch by rowid5  user calls
SYS@CDB$ROOT>

四、 SQL Profile 管理指南?

?常用操作

?操作?

命令/視圖

?查看 Profile?

SELECT name, category, status FROM dba_sql_profiles;

?禁用/啟用

DBMS_SQLTUNE.ALTER_SQL_PROFILE(name=>'PROF1', attribute_name=>'STATUS', value=>'DISABLED');

?刪除 Profile

DBMS_SQLTUNE.DROP_SQL_PROFILE('PROF1');

?遷移 Profile?

使用DBMS_SQLTUNE.PACK_STGTAB_SQLPROF導出導入

關鍵管理場景?
  • ?環境隔離?：通過 CATEGORY 控制 Profile 生效范圍（如測試環境用 DEV ，生產用PROD）。
  • ?版本升級?：使用數據泵導出 SQLPROF$ 表實現跨版本遷移。
  • ?性能監控?：結合 AWR 報告檢查 SQL Profile 的使用效果。
?管理SQL Profile
-- 查看所有 Profile
SELECT name, category, status, sql_text 
FROM dba_sql_profiles；
--
SYS@CDB$ROOT> SELECT name, category, status, sql_text FROM dba_sql_profiles;
SYS_SQLPROF_0197914a1f230000    DEFAULT     ENABLED    SELECT /*+ NO_INDEX(T) */ * FROM HR.T4SQLPROFILE T WHERE object_id=10-- 禁用/啟用 Profile
BEGINDBMS_SQLTUNE.ALTER_SQL_PROFILE(name           => 'SYS_SQLPROF_0197914a1f230000',attribute_name => 'STATUS',value          => 'DISABLED'  -- 或 'ENABLED');
END;
/-- 刪除 Profile
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_0197914a1f230000');

五、使用體驗?

  1. ?自動 Profile? 適合解決統計信息不準導致的偶發性性能問題，動態適應數據變化。
  2. ?手動 Profile? 用于鎖定最優計劃，尤其適合關鍵業務 SQL 或第三方系統不可改源碼的場景。
  3. ?最佳實踐?：
  • 優先嘗試自動 Profile
  • 對核心事務 SQL 使用手動 Profile 強控計劃
  • 通過 force_match 和 category 提升靈活性和安全性

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/diannao/87918.shtml
繁體地址，請注明出處：http://hk.pswp.cn/diannao/87918.shtml
英文地址，請注明出處：http://en.pswp.cn/diannao/87918.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

man的使用

man的使用

man的使用 文章目錄 man的使用基本用法&#xff1a;常見 man 命令操作&#xff1a;man 命令的章節&#xff1a;示例&#xff1a; man 是 Linux 和 macOS 系統中的命令&#xff0c;用于查看命令和程序的手冊頁&#xff08;manual pages&#xff09;。手冊頁包含了關于命令、函…
閱讀更多...
【藍牙】手機連接Linux系統藍牙配對，Linux Qt5分享PDF到手機

【藍牙】手機連接Linux系統藍牙配對，Linux Qt5分享PDF到手機

要實現手機連接 A40i Linux 系統并通過藍牙接收 PDF 文件&#xff0c;可以按照以下步驟操作&#xff1a; 1. 配置 Linux 藍牙功能 確保開發板上的藍牙模塊已正確驅動并支持藍牙協議棧。 安裝藍牙工具&#xff1a; bash sudo apt install bluetooth bluez bluez-tools 啟動藍…
閱讀更多...
1432. 改變一個整數能得到的最大差值

1432. 改變一個整數能得到的最大差值

1432. 改變一個整數能得到的最大差值 題目鏈接&#xff1a;1432. 改變一個整數能得到的最大差值 代碼如下&#xff1a; class Solution { public:int maxDiff(int num) {string s to_string(num);function<int(char, char)> replace_stoi [&](char old_char, cha…
閱讀更多...
解密 Spring MVC：從 Tomcat 到 Controller 的一次完整請求之旅

解密 Spring MVC：從 Tomcat 到 Controller 的一次完整請求之旅

今天&#xff0c;想和你聊一個我們每天都在打交道&#xff0c;但可能不曾深入思考的話題&#xff1a;當一個 HTTP 請求從瀏覽器發出&#xff0c;到最終被我們的 Spring Controller 處理&#xff0c;它到底經歷了一場怎樣的旅程&#xff1f; 理解這個流程&#xff0c;不僅僅是為…
閱讀更多...
在 Java 中操作 Map時，高效遍歷和安全刪除數據

在 Java 中操作 Map時，高效遍歷和安全刪除數據

在 Java 中操作 Map 時&#xff0c;高效遍歷和安全刪除數據可以通過以下方式實現&#xff1a; 一、遍歷 Map 的 4 種高效方式 1. 傳統迭代器&#xff08;Iterator&#xff09; Map<String, Integer> map new HashMap<>(); map.put("key1", 5); map.pu…
閱讀更多...
力扣-136.只出現一次的數字

力扣-136.只出現一次的數字

題目描述 給你一個 非空 整數數組 nums &#xff0c;除了某個元素只出現一次以外&#xff0c;其余每個元素均出現兩次。找出那個只出現了一次的元素。 你必須設計并實現線性時間復雜度的算法來解決此問題&#xff0c;且該算法只使用常量額外空間。 class Solution {public i…
閱讀更多...
Go 網絡編程：HTTP服務與客戶端開發

Go 網絡編程：HTTP服務與客戶端開發

Go 在標準庫中內置了功能強大的 net/http 包&#xff0c;可快速構建高并發、高性能的 HTTP 服務&#xff0c;廣泛應用于微服務、Web后端、API中間層等場景。 一、快速創建一個HTTP服務 示例&#xff1a;最簡Hello服務 package mainimport ("fmt""net/http&quo…
閱讀更多...
【Prism】 實現注入的幾個標準化步驟（相機舉例）

【Prism】 實現注入的幾個標準化步驟（相機舉例）

?? Prism 架構中如何優雅地注冊和注入相機服務 在開發基于 Prism + WPF 的應用時，合理使用依賴注入（DI）可以大大提高系統的可維護性和擴展性。本文以一個多相機平臺管理系統為例，展示如何通過接口、枚舉、容器注冊等方式，實現相機服務的靈活配置與使用。 ?? 一、定義…
閱讀更多...
vue3組件式開發示例

vue3組件式開發示例

1&#xff0c;定義組件&#xff08;根據實際調整提交分析結果方法&#xff09; <template><!-- 分析結果上傳對話框組件 --><el-dialogv-model"uploadResultDialog":title"title":width"width":before-close"handleBeforeC…
閱讀更多...
基于arm linux的bluealsa開啟藍牙A2DP和SCO錄音功能

基于arm linux的bluealsa開啟藍牙A2DP和SCO錄音功能

bluealsa的軟件架構 #mermaid-svg-ohITacCRHItwRR1t {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-ohITacCRHItwRR1t .error-icon{fill:#552222;}#mermaid-svg-ohITacCRHItwRR1t .error-text{fill:#552222;stroke:…
閱讀更多...
網頁后端開發（基礎3--Springboot框架）

網頁后端開發（基礎3--Springboot框架）

web的服務器資源&#xff1a; 靜態資源&#xff1a;服務器上存儲的不會改變的數據&#xff0c;通常不會根據用戶的請求而變化。比如&#xff1a;HTML、CSS、JS、圖片、視頻等&#xff08;負責頁面展示&#xff09; 動態資源&#xff1a;服務器端根據用戶請求和其他數據…
閱讀更多...
ROS通過urdf_to_graphiz對urdf和xacro文件進行結構可視化

ROS通過urdf_to_graphiz對urdf和xacro文件進行結構可視化

對機器人的urdf文件進行結構可視化&#xff1a; 舉例命令如下&#xff1a; urdf_to_graphiz go2_description.urdf 輸出 .gv 和 .pdf文件&#xff0c;打開 pdf文件如圖&#xff1a;
閱讀更多...
基于Uniapp+PHP的教育培訓系統開發指南：網校源碼實戰剖析

基于Uniapp+PHP的教育培訓系統開發指南：網校源碼實戰剖析

在線教育日益普及的今天&#xff0c;如何快速搭建一個功能完善、體驗良好的教育培訓系統&#xff0c;成為眾多教育機構、培訓企業、個體講師關注的焦點。與其從零開發&#xff0c;不如基于成熟框架快速部署。而UniappPHP正是當前“低成本高效率”開發網校系統的黃金組合。 本文…
閱讀更多...
鍵盤 AK35I Pro V2 分析

鍵盤 AK35I Pro V2 分析

文章目錄 AK35I Pro V21. MCU SN32F299SN32F299 內存映射 2. Bootloader3. TFT 135x240 1.14inch4. 鍵盤5. Flash PY25Q128HA6. 藍牙 CH582F7. 擴展板8. 電池 606090 3.7V 4000mAh AK35I Pro V2 AK35I Pro V2 測評視頻 鍵盤外殼使用卡扣固定, 外殼沒有螺絲, 將外框向外翹起, 用…
閱讀更多...
11. TypeScript 工具類型

11. TypeScript 工具類型

TypeScript 提供了一系列內置的“工具類型”&#xff08;Utility Types&#xff09;&#xff0c;它們是對已有類型進行變換的便捷方式。通過這些工具類型&#xff0c;開發者可以更靈活、可維護地進行類型設計&#xff0c;避免重復定義類型邏輯。 工具類型的作用主要有&#xf…
閱讀更多...
Kafka性能調優全攻略：從JVM參數到系統優化

Kafka性能調優全攻略：從JVM參數到系統優化

前言 在大數據處理領域&#xff0c;Kafka以其高吞吐、高并發的特性成為消息隊列的首選。然而&#xff0c;隨著業務規模的擴大和數據量的激增&#xff0c;若配置不當&#xff0c;Kafka的性能和穩定性會受到嚴重影響。其中&#xff0c;JVM參數的調整是優化Kafka性能的關鍵一環&a…
閱讀更多...
HarmonyOS 5 NPU支持哪些AI框架？

HarmonyOS 5 NPU支持哪些AI框架？

以下是HarmonyOS 5 NPU支持的AI框架及適配方案&#xff0c;結合關鍵技術和實測數據&#xff1a; 一、原生支持框架 MindSpore Lite? ?核心特性?&#xff1a; 原生適配昇騰達芬奇架構&#xff0c;支持INT8/FP16混合量化自動算子融合優化&#xff08;實測推理速度提升3.2倍…
閱讀更多...
鴻蒙uvc預覽

鴻蒙uvc預覽

簡單查看流程&#xff0c;如有錯誤請指出。 CameraNativePreview.ets--> 這里開始進入uvc_camera庫 (CameraDevice.ets/CameraManager.ets) --> CameraUtils.ets--> -->CameraNativeMethods(index.d.ts文件&#xff0c;路徑: uvc_camera\src\main\cpp\types\…
閱讀更多...
PHP的打印語句

PHP的打印語句

文章目錄 環境總結打印語句換行符括號數組&#xff08;對象&#xff09;和字符串之間的相互轉換 打印語句echoprint括號print_rvar_dump 數組&#xff08;對象&#xff09;和字符串之間的轉換json_encodejson_decodeimplodeexplode 環境 PHP 8.4.5 總結 如果不想看詳細介紹&…
閱讀更多...
功率MOSFET的SOA曲線

功率MOSFET的SOA曲線

功率MOSFET的SOA曲線 SOA區指的是MOSFET的安全工作區&#xff0c;英文表示為Safe Operating Area&#xff0c;是指MOSFET&#xff08;金屬氧化物半導體場效應晶體管&#xff09;的安全操作范圍。在線性模式運行的情況下&#xff0c;SOA特別有用。但開關模式下&#xff0c;一般…
閱讀更多...
最新文章

Copyright @ 2022~2023