1、oracle 幾個(gè)重要的關(guān)聯(lián)技術(shù)執(zhí)行計(jì)劃 優(yōu)化器 Hints analyze dbms_stats explain plan Oracle對(duì)數(shù)據(jù)的訪問(wèn)方式 今天特別回顧了一下這幾個(gè)非常非常重要的技術(shù)。(oralce太深了)SQL> ?/rdbms/admin/utlxplan.sql #創(chuàng)建 plan_table; 表SQL> explain plan for select count(*) from scott.temp01;Explained.完成explain plan之后，會(huì)把分析結(jié)果寫(xiě)入plan_table表中2 SQL跟蹤文件，參數(shù)timed_statistics ,m

2、ax_dump_file_size, user_dump_des 三個(gè)參數(shù)分別設(shè)計(jì)時(shí)間，大小，以及路徑。設(shè)計(jì)sql_trace 參數(shù)開(kāi)啟3 設(shè)計(jì)set autotrace on set timing on命令 SQL> select count(*) from scott.emp;COUNT(*)-14Execution Plan-Plan hash value: 2937609675-| Id | Operation | Name | Rows | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

3、1 | SORT AGGREGATE | | 1 | | | 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01#在這里會(huì)發(fā)現(xiàn)，此處提到一個(gè)關(guān)于對(duì)表的訪問(wèn)方式 全表掃描 索引掃描（也就是rowid）可以使用hint來(lái)強(qiáng)制3 第三種就是使用pl/sql了，這部就不說(shuō)明了二 優(yōu)化器 oracle的優(yōu)化器有CBO與RBO兩種，用參數(shù) optimizer_mode 優(yōu)化模式設(shè)定，共有Rule,Choose,First rows,All rows這四種方式-Rule Based Optimizer（RBO）基于規(guī)則Cost Based Optimizer

4、（CBO）基于成本，或者講統(tǒng)計(jì)信息ORACLE 提供了CBO、RBO兩種SQL優(yōu)化器。CBO在ORACLE7 引入，但在ORACLE8i 中才成熟。ORACLE 已經(jīng)明確聲明在ORACLE9i之后的版本中(ORACLE 10G )，RBO將不再支持。因此選擇CBO 是必然的趨勢(shì)。CBO和 RBO作為不同的SQL優(yōu)化器，對(duì)SQL語(yǔ)句的執(zhí)行計(jì)劃產(chǎn)生重大影響，如果要對(duì)現(xiàn)有的應(yīng)用程序從RBO向CBO移植，則必須充分考慮這些影響，避免SQL 語(yǔ)句性能急劇下降；但是，對(duì)新的應(yīng)用系統(tǒng)，則可以考慮直接使用CBO，在CBO模式下進(jìn)行SQL語(yǔ)句編寫(xiě)、分析執(zhí)行計(jì)劃、性能測(cè)試等工作，這需要開(kāi)發(fā)者對(duì) CBO的特性比較熟

5、悉。以下小結(jié)幾點(diǎn)在CBO下寫(xiě)SQL語(yǔ)句的注意事項(xiàng)：1、RBO自O(shè)RACLE 6版以來(lái)被采用，有著一套嚴(yán)格的使用規(guī)則，只要你按照它去寫(xiě)SQL語(yǔ)句，無(wú)論數(shù)據(jù)表中的內(nèi)容怎樣，也不會(huì)影響到你的“執(zhí)行計(jì)劃”，也就是說(shuō)對(duì)數(shù)據(jù)不“敏 感”；CBO計(jì)算各種可能“執(zhí)行計(jì)劃”的“代價(jià)”，即cost，從中選用cost最低的方案，作為實(shí)際運(yùn)行方案。各“執(zhí)行計(jì)劃”的cost的計(jì)算根據(jù)，依 賴(lài)于數(shù)據(jù)表中數(shù)據(jù)的統(tǒng)計(jì)分布，ORACLE數(shù)據(jù)庫(kù)本身對(duì)該統(tǒng)計(jì)分布并不清楚，必須要分析表和相關(guān)的索引（使用ANALYZE 命令），才能搜集到CBO所需的數(shù)據(jù)。2、使用CBO 時(shí)，編寫(xiě)SQL語(yǔ)句時(shí)，不必考慮"FROM"

6、子句后面的表或視圖的順序和"WHERE" 子句后面的條件順序；ORACLE自7版以來(lái)采用的許多新技術(shù)都是基于CBO的，如星型連接排列查詢(xún)，哈希連接查詢(xún)，函數(shù)索引，和并行查詢(xún)等。3、一般而言，CBO所選擇的“執(zhí)行計(jì)劃”都不會(huì)比RBO的“執(zhí)行計(jì)劃”差，而且相對(duì)而言，CBO對(duì)程序員的要求沒(méi)有RBO那么苛刻，節(jié)省了程序員 為了從多個(gè)可能的“執(zhí)行計(jì)劃”中選擇一個(gè)最優(yōu)的方案而花費(fèi)的調(diào)試時(shí)間，但在某些場(chǎng)合下也會(huì)存在問(wèn)題。較典型的問(wèn)題有：有時(shí)，表明明建有索引，但查詢(xún)過(guò)程顯 然沒(méi)有用到相關(guān)的索引，導(dǎo)致查詢(xún)過(guò)程耗時(shí)漫長(zhǎng)，占用資源巨大，這時(shí)就需要仔細(xì)分析執(zhí)行計(jì)劃，找出原因。例如，可以看連接順序是

7、否允許使用相關(guān)索引。假設(shè)表 emp的deptno列上有索引，表dept的列deptno上無(wú)索引，WHERE語(yǔ)句有emp.deptno=dept.deptno條件。在做NL連 接時(shí)，emp做為外表，先被訪問(wèn)，由于連接機(jī)制原因，外表的數(shù)據(jù)訪問(wèn)方式是全表掃描，emp.deptno上的索引顯然是用不上，最多在其上做索引全掃描 或索引快速全掃描。4、如果一個(gè)語(yǔ)句使用 RBO的執(zhí)行計(jì)劃確實(shí)比CBO 好，則可以通過(guò)加 " rule" 提示，強(qiáng)制使用RBO。5、使用CBO 時(shí)，SQL語(yǔ)句 "FROM" 子句后面的表，必須全部使用ANALYZE 命令分析過(guò)，如果"

8、;FROM" 子句后面的是視圖，則此視圖的基礎(chǔ)表，也必須全部使用ANALYZE 命令分析過(guò)；否則，ORACLE 會(huì)在執(zhí)行此SQL語(yǔ)句之前，自動(dòng)進(jìn)行ANALYZE 命令分析，這會(huì)極大導(dǎo)致SQL語(yǔ)句執(zhí)行極其緩慢。6、使用CBO 時(shí)，SQL語(yǔ)句 "FROM" 子句后面的表的個(gè)數(shù)不宜太多，因?yàn)镃BO在選擇表連接順序時(shí)，會(huì)對(duì)"FROM" 子句后面的表進(jìn)行階乘運(yùn)算，選擇最好的一個(gè)連接順序。假如"FROM" 子句后有6個(gè)表，則其可選擇的連接順序就是6*5*4*3*2*1 = 720 種，CBO 選擇其中一種，而如果"FROM&q

9、uot; 子句后有12個(gè)表，則其可選擇的連接順序就是12*11*10*9*8*7*6*5*4*3*2*1= 479001600 種，可以想象從中選擇一種，會(huì)消耗多少CPU 時(shí)間？如果實(shí)在是要訪問(wèn)很多表，則最好使用 ORDER 提示，強(qiáng)制使用"FROM" 子句表固定的訪問(wèn)順序。7、使用CBO 時(shí)，SQL語(yǔ)句中不能引用系統(tǒng)數(shù)據(jù)字典表或視圖，因?yàn)橄到y(tǒng)數(shù)據(jù)字典表都未被分析過(guò)，可能導(dǎo)致極差的“執(zhí)行計(jì)劃”。但是不要擅自對(duì)數(shù)據(jù)字典表做分析，否則可 能導(dǎo)致死鎖，或系統(tǒng)性能?chē)?yán)重下降。8、使用CBO 時(shí)，要注意看采用了哪種類(lèi)型的表連接方式。ORACLE的共有Sort Merge Join（SM

10、J）、Hash Join（HJ）和Nested Loop Join（NL）。CBO有時(shí)會(huì)偏重于SMJ 和 HJ，但在OLTP 系統(tǒng)中，NL 一般會(huì)更好，因?yàn)樗咝У氖褂昧怂饕Ｔ趦蓮埍磉B接，且內(nèi)表的目標(biāo)列上建有索引時(shí)，只有Nested Loop才能有效地利用到該索引。SMJ即使相關(guān)列上建有索引，最多只能因索引的存在，避免數(shù)據(jù)排序過(guò)程。HJ由于須做HASH運(yùn)算，索引的存在對(duì)數(shù)據(jù)查 詢(xún)速度幾乎沒(méi)有影響。9、使用CBO 時(shí)，必須保證為表和相關(guān)的索引搜集足夠的統(tǒng)計(jì)數(shù)據(jù)。對(duì)數(shù)據(jù)經(jīng)常有增、刪、改的表最好定期對(duì)表和索引進(jìn)行分析，可用SQL語(yǔ)句“analyze table xxx compute statis

11、tics for all indexes;"ORACLE掌握了充分反映實(shí)際的統(tǒng)計(jì)數(shù)據(jù)，才有可能做出正確的選擇。10、使用CBO 時(shí)，要注意被索引的字段的值的數(shù)據(jù)分布，會(huì)影響SQL語(yǔ)句的執(zhí)行計(jì)劃。例如：表emp，共有一百萬(wàn)行數(shù)據(jù)，但其中的emp.deptno列，數(shù)據(jù)只有4種 不同的值，如10、20、30、40。雖然emp數(shù)據(jù)行有很多，ORACLE缺省認(rèn)定表中列的值是在所有數(shù)據(jù)行均勻分布的，也就是說(shuō)每種deptno值各 有25萬(wàn)數(shù)據(jù)行與之對(duì)應(yīng)。假設(shè)SQL搜索條件DEPTNO=10，利用deptno列上的索引進(jìn)行數(shù)據(jù)搜索效率，往往不比全表掃描的高，ORACLE理所 當(dāng)然對(duì)索引“視而不見(jiàn)”，認(rèn)為該索引的選擇性不高。我們考慮另一種情況，如果一百萬(wàn)數(shù)據(jù)行實(shí)際不是在4種deptno值間平均分配，其中