在Oracle SQL優(yōu)化中我們會經(jīng)常用到固定optimizer plan，在固定optimizer plan中我們要進行stored outline的創(chuàng)建，以及如何使用stored outline等相關(guān)實際操作，以下就是相關(guān)內(nèi)容的具體介紹。

1、優(yōu)化器模式

Oracle9i有兩種優(yōu)化器模式可以選擇：

• Rule-based:  
– Uses a ranking system  
– Syntax- and data dictionary–driven  
• Cost-based:  
– Chooses least-cost path  
– Statistics-driven 

Rule-based模式滿足向后兼容，而Cost-based模式中的成本大部分來自于邏輯讀的次數(shù)，推薦使用Cost-based模式。

2、固定optimizer plan

1）概念

對于每一個查詢，optimizer都會準備一個定義了操作執(zhí)行順序和方法的操作樹（執(zhí)行計劃），oracle server根據(jù)這個執(zhí)行計劃執(zhí)行語句。通過固定執(zhí)行計劃，可以強制應(yīng)用通過一種理想的方式訪問數(shù)據(jù)，并且一個穩(wěn)定的執(zhí)行計劃可以經(jīng)歷數(shù)據(jù)庫的變化而保持不變。固定執(zhí)行計劃通過創(chuàng)建stored outline實現(xiàn)，outline使用cost-based的optimizer，因為其由一系列的hints組成。

執(zhí)行計劃的固定依賴于當判定一個查詢是否存在stored outline時查詢語句是否完全一致，與判定shared pool里一個執(zhí)行計劃是否可以重用時的匹配方式是一致的。

Outline被保存在outln schema中。

2） 創(chuàng)建stored outline

alter session set CREATE_STORED_OUTLINES = train;  
create or replace OUTLINE co_cl_join  
FOR CATEGORY train ON  
select co.crs_id, ...  
from courses co，classes cl  
where co.crs_id = cl.crs_id; 

stored outline通過category組織，相同的sql語句可以在多個category同時擁有stored outline，如果categoey沒有指定，缺省是default category。

當CREATE_STORED_OUTLINES等于true或category名時，oracle會為所有被執(zhí)行的sql語句創(chuàng)建stored outline，也可以通過create outline手工創(chuàng)建。

3） 使用stored outline

將USE_STORED_OUTLINES設(shè)置為true或category名。

alter session set USE_STORED_OUTLINES = train; 

當為一個查詢尋找stored outline時，查詢語句與stored outline里的語句必須完全一致，在outline里的hints也必須在查詢語句中出現(xiàn)。

3、private outline

在Oracle SQL優(yōu)化中Private outline是當前保存的stored outline的副本，可以被編輯而不影響正在運行的系統(tǒng)，一個private outline只能被當前session看到，它的數(shù)據(jù)被保存在當前被解析的schema里。，知道顯示的將其公布。

當USE_PRIVATE_OUTLINES=TRUE時，一個已有outline的sql被提交時，optimizer會檢查是否存在private outline，如果不存在，optimizer就不使用optimizer編譯語句，而不會去檢查公布的stored outline。

4、在sql中使用hints

Create index gen_idx on customers(cust_gender);   
Select /*+ index(customers gen_idx)*/   
Cust_last_name,cust_street_address,cust_postal_code   
From sh.customers where upper(gender)=’M’; 

5、EXPLAIN PLAN

可以不通過tracing，需要建立plan_table表：

Sql>@oracle_home/rdbms/admin/utlxplan;  

建立explain plan：

Explain plan for select last_name from hr.emp;  

查詢plan_table中的explain plan，可以直接查詢，也可以通過腳本utlxplx.sql(隱藏并行查詢信息)、utlxplp.sql（顯示并行查詢信息）查詢。

6、管理統(tǒng)計信息

利用analyize命令收集或刪除信息。

參數(shù)：

Compute：統(tǒng)計精確的數(shù)據(jù)；

Estimate：估計的統(tǒng)計數(shù)據(jù)。

各類統(tǒng)計數(shù)據(jù)的位置：

表：dba_tables;

索引：dba_indexes;

列：user_tab_col_statistics;

柱狀圖（histogram）詳細的描述了一個特定列中數(shù)據(jù)的分布情況，可以通過analyize table ... for columns... 命令創(chuàng)建，保存在dba_histogram/dba_tab_histograms中。

上述的相關(guān)內(nèi)容就是對Oracle SQL優(yōu)化的描述，希望會給你帶來一些幫助在此方面。

【編輯推薦】

1. Oracle應(yīng)用的程序開發(fā)趨于向Web的緣由
2. Oracle RAC中錯誤觀點出現(xiàn)的原因
3. Oracle客戶關(guān)系管理論壇的內(nèi)容簡介
4. Oracle用戶密碼的修改
5. Oracle常用的命令如何查看表的結(jié)構(gòu)