Oracle效能最佳化之SQL最佳化(轉)

1、最佳化器模式

　　Oracle9i有兩種最佳化器模式可以選擇：

　　• Rule-based:

　　– Uses a ranking system

　　– Syntax- and data dictionary–driven

　　• Cost-based:

　　– Chooses least-cost path

　　– Statistics-driven

　　Rule-based模式滿足向後相容，而Cost-based模式中的成本大部分來自於邏輯讀的次數，推薦使用Cost-based模式。

　　2、固定optimizer plan

　　1)概念

　　對於每一個查詢，optimizer都會準備一個定義了操作執行順序和方法的操作樹(執行計劃)，oracle server根據這個執行計劃執行語句。透過固定執行計劃，可以強制應用透過一種理想的方式訪問資料，並且一個穩定的執行計劃可以經歷資料庫的變化而保持不變。固定執行計劃透過建立stored outline實現，outline使用cost-based的optimizer，因為其由一系列的hints組成。

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

　　Outline被儲存在outln schema中。

　　2) 建立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會為所有被執行的sql語句建立stored outline，也可以透過create outline手工建立。

　　3) 使用stored outline

　　將USE_STORED_OUTLINES設定為true或category名。

　　alter session set USE_STORED_OUTLINES = train;

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

　　3、private outline

　　Private outline是當前儲存的stored outline的副本，可以被編輯而不影響正在執行的系統，一個private outline只能被當前session看到，它的資料被儲存在當前被解析的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、管理統計資訊

　　利用analyize命令收集或刪除資訊。

　　引數：

　　Compute：統計精確的資料;

　　Estimate：估計的統計資料。

　　各類統計資料的位置：

　　表：dba_tables;

　　索引：dba_indexes;

　　列：user_tab_col_statistics;

　　柱狀圖(histogram)詳細的描述了一個特定列中資料的分佈情況，可以透過analyize table ... for columns... 命令建立，儲存在dba_histogram/dba_tab_histograms中。

[@more@]