Oracle筆記-優化器計劃穩定性
2024-08-29 13:39:24
供稿：網友
第 11 章 優化器計劃穩定性 11.1 概覽 CREATE OR REPLACE ONLINE MyOutLine FOR CATEGORY mycategory ON SELECT ……； 需要CREATE OUTLINE權限 使用時指定會話的CATEGORY即可： ALTER session SET USE_STORED_OUTLINES = mycategory； 11.2 OPS的使用 對已封裝的應用中SQL進行的優化方法 ALTER SESSION SET CREATE_STORED_OUTLINES = test； 執行應用，如一個存儲過程等 ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE； SET LONG 5000 SELECT name， sql_text FROM user_outlines WHERE category = ‘test’； 此時可以看到所運行的SQL語句。也可以通過一個ON LOGON觸發器來實現，即一登陸就ALTER SESSION… 優化時修改OPTIMIZER_GOAL后： ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS； ALTER OUTLINE name REBUILD； ALTER SESSION SET OPTIMIZER_GOAL = CHOOSE； 此時就固定為OPTIMIZER_GOAL = FIRST_ROWS時的執行計劃了?！ ∫粋€開發工具 由于開發環境與實際部署環境可能不一致，為了保證執行計劃與開發環境一致，可以建立一個ON LOGON觸發器來將執行計劃歸入一個category中，然后eXP/imp到新環境中?！ ∮脕碛^察是否使用了索引 SELECT name， hint FROM user_outline_hints WHERE hink LIKE ‘INDEX%’； 用來觀察應用使用了什么SQL語句 11.3 OPS如何工作 OUTLINES與OUTLINE_HITS 均分別有DBA_、USER_、ALL_三張視圖，其中DBA_多一個owner字段，說明創建者，另兩張與用戶有關系。 DBA_OUTLINES： NAME OUTLINE名，若創建時未指定，則使用系統命名 OWNER 創建時的方案名 CATEGORY 創建的列別，若未指定則為DEFAULT USED 是否使用過 TIMESTAMP 創建的時間 VERSION 創建時的數據庫版本 SQL_TEXT SQL查詢語句 DBA_OUTLINE_HINTS： NAME OUTLINE名，若創建時未指定，則使用系統命名 OWNER 創建時的方案名 NODE 提示應用的層次，從最外層查詢（1）開始累加計數 STAGE 提示應用的階段，即提示在編譯的哪個階段寫入 JOIN_POS 提示應用的表名，對非訪問方式提示為0 HINT 提示 11.4 創建存儲概要 相關的權限 CREATE ANY OUTLINE – 創建概要，否則報ORA-18005錯誤 ALTER ANY OUTLINE – 修改或重新計算概要 DROP ANY OUTLINE – 刪除概要 EXECUTE ON OUTLN_PKG – 執行OUTLINE包 注重這里權限都是全局的，概要不存在真正的所有者。 使用DDL CREATE <OR REPLACE> OUTLINE OUTLINE_NAME <FOR CATEGORY CATEGORY_NAME> ON STATEMENT 使用ALTER SESSION ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE； ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE； ALTER SESSION SET CREATE_STORED_OUTLINES = mycategory； 當設為TRUE時，所創建的概要歸類入DEFAULT. 11.5 OUTLN用戶 所有8i數據庫中均缺省創建，缺省密碼為OUTLN，并可在安裝后立即更改。方案含有兩個表和一些索引，存放于SYSTEM表空間中，若需要大量使用概要，可用如下方法轉移表空間（其中一張表含有LONG類型字段，無法ALTER TABLE MOVE）。
EXP USERID=OUTLN/OUTLN OWNER=OUTLN ALTER USER OUTLN DEFAULT TABLESPACE tools； REVOKE UNLIMITED TABLESPACE FROM OUTLN； ALTER USER OUTLN QUOTA 0K ON SYSTEM； ALTER USER OUTLN QUOTA UNLIMITED ON tools； DROP TABLE ol$； DROP TABLE ol$hints； IMP USERID=OUTLN/OUTLN FULL=YES 若系統已經使用了概要，則操作應盡量在單用戶模式下執行，數據庫無其它活動終端用戶。 11.6 在數據庫間轉移概要 EXP USERID=OUTLN/OUTLN QUERY=“where category=‘test’” tables=（ol$， ol$hints） IMP USERID=OUTLN/OUTLN FULL=Y IGNORE=YES 這里也可以使用參數文件來定義導出的查詢條件。 11.7 獲得正確的概要 有時僅修改某些參數是無法獲得所需要的執行計劃的，還要添加提示。但概要的使用是基于相同的SQL文本，為了不修改應用但使用添加了提示的執行計劃，可以采用如下方法： 例如需要SELECT FROM （SELECT /*+ use_hash（emp） */ FROM emp） emp， ?。⊿ELECT /*+ use_hash（dept） */ FROM dept） dept WHERE emp.deptno=dept.deptno； 則可以在另一個方案中刪除emp、dept表，將內層查詢語句建立成名為emp和dept的視圖，然后對SELECT * FROM emp，dept WHERE emp.deptno=dept.deptno； 建立概要。則此后可以指定應用使用此概要（SQL文本一致）?！ ∵@也是利用了OPS是全局的，并不關心所引用對象，而是純粹根據SQL文本進行轉換。 11.8 治理概要 通過DDL ALTER OUTLINE outline_name RENAME TO new_name； ALTER OUTLINE outline_name CHANGE CATEGORY TO new_category_name； ALTER OUTLINE outline_name REBUILD； DROP OUTLINE outline_name； OUTLN_PKG包 作用：提供批量治理的功能；提供EXP/IMP的API 由DBMSOL.SQL和PRVTOL.PLB腳本（%Oracle_HOME%/RDBMS/ADMIN）創建，而這兩個腳本由CATPROC.SQL調用并缺省安裝到數據庫。 DROP_UNUSED – 刪除所有類別中所有未使用的概要?！ XEC OUTLN_PKG.DROP_UNUSED； DROP_BY_CAT – 刪除指定類別中的所有概要。 EXEC OUTLN_PKG.DROP_BY_CAT（category_name）； UPDATE_BY_CAT – 重命名一個類別或將其合并入另一個類別?！ XEC OUTLN_PKG.UPDATE_BY_CAT（old_category_name， new_category_name）； 若新名已被用，則合并，且若新舊類別存在相同SQL文本的概要，保留新類別中的，而此重復的概要仍保留于原類別中。 11.9 最后說明 創建概要需要CREATE ANY OUTLINE權限，若無權限，利用ALTER SESSION方式來創建概要時不會提示錯誤，但不會創建概要。 刪除用戶時即便指定CASCADE選項，也不會刪除其創建的概要?！ 〖偃鏑URSOR_SHARING設為force，則用DDL和ALTER SESSION兩種方法獲得的SQL文本可能是不同的，前者就是輸入的SQL，而后者是系統內部已經轉換過綁定變量的SQL. 概要的使用依靠文本完全匹配，即便是大小寫不同也會造成SQL文本不匹配?！ R擴展問題：由于WHERE條件中有OR的SQL會被改寫為UNION ALL模式，概要記錄的提示可能無法正常使用，而只是作用到第一個條件上。因此要注重USER_OUTLINE_HINTS表中HINT LIKE ‘USE_CONCAT%’的概要和提示，應當刪除或移走?！ ∈褂酶乓獙π阅苡绊懞苄?。創建概要時接近首次分析該條語句的時間，此后第一次分析慢于正常分析時間，而隨后概要已經進入緩存，將不會觀察到性能影響。 11.10 可能碰到的錯誤 ORA-18001 – 使用ALTER OUTLINE語法錯誤 ORA-18002 – 所引用的概要不存在（從未創建過或者被刪除） ORA-18003 – 概要的數字簽名已存在，數字簽名用于快速查找到合適的概要，此錯誤極少發生 ORA-18004 – 概要已存在，一般是命名沖突 ORA-18005 – 需要CREATE ANY OUTLINE權限 ORA-18006 – 需要DROP ANY OUTLINE權限 ORA-18007 – 需要ALTER ANY OUTLINE權限
