在Oracle SQL優(yōu)化中經(jīng)常使用的方案
在Oracle SQL優(yōu)化中我們會(huì)經(jīng)常用到固定optimizer plan，在固定optimizer plan中我們要進(jìn)行stored outline的創(chuàng)建，以及如何使用stored outline等相關(guān)實(shí)際操作，以下就是相關(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模式中的成本大部分來(lái)自于邏輯讀的次數(shù)，推薦使用Cost-based模式。
2、固定optimizer plan
1）概念
對(duì)于每一個(gè)查詢，optimizer都會(huì)準(zhǔn)備一個(gè)定義了操作執(zhí)行順序和方法的操作樹(shù)（執(zhí)行計(jì)劃），oracle server根據(jù)這個(gè)執(zhí)行計(jì)劃執(zhí)行語(yǔ)句。通過(guò)固定執(zhí)行計(jì)劃，可以強(qiáng)制應(yīng)用通過(guò)一種理想的方式訪問(wèn)數(shù)據(jù)，并且一個(gè)穩(wěn)定的執(zhí)行計(jì)劃可以經(jīng)歷數(shù)據(jù)庫(kù)的變化而保持不變。固定執(zhí)行計(jì)劃通過(guò)創(chuàng)建stored outline實(shí)現(xiàn)，outline使用cost-based的optimizer，因?yàn)槠溆梢幌盗械膆ints組成。
執(zhí)行計(jì)劃的固定依賴于當(dāng)判定一個(gè)查詢是否存在stored outline時(shí)查詢語(yǔ)句是否完全一致，與判定shared pool里一個(gè)執(zhí)行計(jì)劃是否可以重用時(shí)的匹配方式是一致的。
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通過(guò)category組織，相同的sql語(yǔ)句可以在多個(gè)category同時(shí)擁有stored outline，如果categoey沒(méi)有指定，缺省是default category。
當(dāng)CREATE_STORED_OUTLINES等于true或category名時(shí)，oracle會(huì)為所有被執(zhí)行的sql語(yǔ)句創(chuàng)建stored outline，也可以通過(guò)create outline手工創(chuàng)建。
3） 使用stored outline
將USE_STORED_OUTLINES設(shè)置為true或category名。
- alter session set USE_STORED_OUTLINES = train;
當(dāng)為一個(gè)查詢尋找stored outline時(shí)，查詢語(yǔ)句與stored outline里的語(yǔ)句必須完全一致，在outline里的hints也必須在查詢語(yǔ)句中出現(xiàn)。
3、private outline
在Oracle SQL優(yōu)化中Private outline是當(dāng)前保存的stored outline的副本，可以被編輯而不影響正在運(yùn)行的系統(tǒng)，一個(gè)private outline只能被當(dāng)前session看到，它的數(shù)據(jù)被保存在當(dāng)前被解析的schema里。，知道顯示的將其公布。
當(dāng)USE_PRIVATE_OUTLINES=TRUE時(shí)，一個(gè)已有outline的sql被提交時(shí)，optimizer會(huì)檢查是否存在private outline，如果不存在，optimizer就不使用optimizer編譯語(yǔ)句，而不會(huì)去檢查公布的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
可以不通過(guò)tracing，需要建立plan_table表：
- Sql>@oracle_home/rdbms/admin/utlxplan;
建立explain plan：
- Explain plan for select last_name from hr.emp;
查詢plan_table中的explain plan，可以直接查詢，也可以通過(guò)腳本utlxplx.sql(隱藏并行查詢信息)、utlxplp.sql（顯示并行查詢信息）查詢。
6、管理統(tǒng)計(jì)信息
利用analyize命令收集或刪除信息。
參數(shù)：
Compute：統(tǒng)計(jì)精確的數(shù)據(jù)；
Estimate：估計(jì)的統(tǒng)計(jì)數(shù)據(jù)。
各類統(tǒng)計(jì)數(shù)據(jù)的位置：
表：dba_tables;
索引：dba_indexes;
列：user_tab_col_statistics;
柱狀圖（histogram）詳細(xì)的描述了一個(gè)特定列中數(shù)據(jù)的分布情況，可以通過(guò)analyize table ... for columns... 命令創(chuàng)建，保存在dba_histogram/dba_tab_histograms中。
上述的相關(guān)內(nèi)容就是對(duì)Oracle SQL優(yōu)化的描述，希望會(huì)給你帶來(lái)一些幫助在此方面。
【編輯推薦】
