【正文】 要看索引是否被使用我們要借助Oracle的一個(gè)叫做AUTOTRACE功能,它顯示了sql語(yǔ)句的執(zhí)行路徑，我們能看到Oracle內(nèi)部是怎么執(zhí)行sql的，這是一個(gè)非常好的輔助工具，在sql調(diào)優(yōu)里廣泛被運(yùn)用。CBO。字符型：在索引字段上加‘’，例如　　select * from tg_cdr01 where msisdn||’’=v_msisdn。數(shù)值型：在索引字段上加0，例如　　select * from emp where emp_no+0 = v_emp_no。 　如我們想要用到A表的IND_COL1索引的話，可采用以下方式： 　SELECT /*+ INDEX（A IND_COL1）*/ * FROM A WHERE COL1 = XXX。如果從以上幾個(gè)方面都查不出原因的話，我們只好用采用在語(yǔ)句中加hint的方式強(qiáng)制ORACLE使用最優(yōu)的“執(zhí)行計(jì)劃”。并行查詢將不會(huì)用到索引?！　　　?。如果索引列值可以是空值，在SQL語(yǔ)句中那些要返回NULL值的操作，將不會(huì)用到索引，如COUNT（*），而是用全表掃描?！　　　?。雖然emp數(shù)據(jù)行有很多，ORACLE缺省認(rèn)定表中列的值是在所有數(shù)據(jù)行均勻分布的，也就是說(shuō)每種deptno值各有25萬(wàn)數(shù)據(jù)行與之對(duì)應(yīng)。索引列的選擇性不高。ORACLE掌握了充分反映實(shí)際的統(tǒng)計(jì)數(shù)據(jù)，才有可能做出正確的選擇。對(duì)數(shù)據(jù)經(jīng)常有增、刪、改的表最好定期對(duì)表和索引進(jìn)行分析，可用SQL語(yǔ)句“analyze table xxxx pute statistics for all indexes。　　　　。是否存在潛在的數(shù)據(jù)類型轉(zhuǎn)換。如是，索引在查詢時(shí)用不上?！　　　?。由于系統(tǒng)數(shù)據(jù)字典表都未被分析過(guò)，可能導(dǎo)致極差的“執(zhí)行計(jì)劃”。　　　　。假設(shè)表emp的deptno列上有索引，表dept的列deptno上無(wú)索引，WHERE語(yǔ)句有=?！　　　　MJ即使相關(guān)列上建有索引，最多只能因索引的存在，避免數(shù)據(jù)排序過(guò)程。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）?！　　　?。　　　　　　Oracle 為什么不使用索引　　　　。可以使用外部連接優(yōu)化not in子句，例如：　　select ename from emp where dept_no not in　　(select dept_no from dept where dept_name =‘Math’)。非關(guān)聯(lián)：子查詢只執(zhí)行一次，存放在內(nèi)存中。使用use_merge　　　　使用臨時(shí)/中間表　　　　多個(gè)大表關(guān)聯(lián)時(shí)，可以分別把滿足條件的結(jié)果集存放到中間表，然后用中間表關(guān)聯(lián)；　　　　SQL子查詢的調(diào)整　　　　關(guān)聯(lián)與非關(guān)聯(lián)子查詢　　　　。使用原則：　　　　連接表子段中不存在可用索引；　　　　查詢返回兩個(gè)表中大部分的數(shù)據(jù)快；　　　　CBO認(rèn)為全表掃描比索引掃描執(zhí)行的更快?！　　　∨判蚝喜⑦B接　　　　。適合于大表和小表連接；　　。Hash連接將驅(qū)動(dòng)表加載在內(nèi)存中，并使用hash技術(shù)連接第二個(gè)表，提高等連接速度。使用use_nl?！　　　∏短籽h(huán)連接　　　　。　　　　。自連接　　　Select total, hard, ( ) soft ,　　Round(()*100,1) perc　　From v$sysstat a,v$sysstat b　　Where = 179　　and = 180。外部連接（左、右連接）　　　　在where條件子句的等式謂詞放置一個(gè)(+)來(lái)實(shí)現(xiàn)，例如：　　select , from emp a,bonus b where =(+)。ordered（基于成本）　　　　　　表連接的類型　　　　。noparallel　　。use_concat(將查詢中所有or條件使用union all)　　。index_bine(強(qiáng)制使用位圖索引)　　。no_index　　。index　　。use_hash　　。first_rows　　。rule (基于規(guī)則)　　。使用表別名:select /*+ index(e dept_idx)*/ * from emp e　　。避免排序合并連接　　　　使用提示進(jìn)行調(diào)整　　　　使用提示的原則　　　　。添加索引　　。union或minus　　。select distinct子句　　。order by 子句　　。單個(gè)查詢并行化　　select /*+ full(emp) parallel(emp,8)*/ * from emp。永久并行化（不推薦）　　alter table customer parallel degree 8。訪問(wèn)索引中所有數(shù)據(jù)塊，結(jié)果相當(dāng)于全表掃描，可以用索引掃描代替全表掃描，例如：　　　　Select serv_id,count(* ) from tg_cdr01 group by serv_id。返回記錄：未排序表40%，排序表7%，建議采用并行機(jī)制來(lái)提高訪問(wèn)速度，DDS；　　　　索引訪問(wèn)　　　　。用first_rows時(shí)，迅速返回記錄，但I(xiàn)/O量大，用all_rows時(shí)，返回記錄慢，但使用資源少。基于成本，以最快的速度返回記錄，會(huì)造成總體查詢速度的下降或消耗更多的資源，傾向索引掃描，適合OLTP系統(tǒng)　　all_rows模式　　　　?？偤雎訡BO和統(tǒng)計(jì)信息而基于規(guī)則　　choose模式　　　　。hints來(lái)改變sql的訪問(wèn)路徑。在optimizer_mode＝choose時(shí)候，系統(tǒng)首先會(huì)判斷FROM clause中的表是否有統(tǒng)計(jì)信息，如果有，則根據(jù)CBO來(lái)執(zhí)行sql，否則根據(jù)rbo來(lái)執(zhí)行。選擇小表作驅(qū)動(dòng)表是關(guān)鍵的。對(duì)于這類 Oracle ， freelist groups 應(yīng)該設(shè)置為訪問(wèn)該表格的 Oracle Parallel Server 實(shí)例的數(shù)目。例如，如果在任何時(shí)候，某個(gè)表最多有 20 個(gè)用戶執(zhí)行插入的操作，那么該表的參數(shù)應(yīng)該設(shè)置為 freelists=20 。freelist 中，而沒(méi)有其它包含有任何空閑塊的 freelists 出現(xiàn)。 　　 ．如果一個(gè)表有同時(shí)插入的 SQL 語(yǔ)句，那么它需要有同時(shí)刪除的語(yǔ)句。 　　 ．表格中 chained rows 的出現(xiàn)意味著 pctfree 太低或者是 db_block_size 太少。對(duì)于不能接受一行的 free blocks 對(duì)于我們來(lái)說(shuō)是沒(méi)有用的。對(duì)于 Oracle 來(lái)說(shuō)，擴(kuò)展一個(gè)表比管理 freelists 消耗更少的資源。那么塊將可以在滿之前接收更多的行，因此可以減少插入操作的 I/O 。因此，這些塊在再次滿之前只可以接受幾行記錄，從而導(dǎo)致更多的 I/O 。 　　 有效地使用空間和高性能之間是有矛盾的，而表格的存儲(chǔ)參數(shù)就是控制這個(gè)方面的矛盾： . 對(duì)于需要有效地重新使用空間，可以設(shè)置一個(gè)高的 pctused 值，不過(guò)副作用是需要額外的 I/O 。 　　 表格和索引存儲(chǔ)參數(shù)設(shè)置的要求總結(jié) 　　 以下的一些規(guī)則是用來(lái)設(shè)置 freelists, freelist groups, pctfree 和 pctused 存儲(chǔ)參數(shù)的。其實(shí)，一旦由于一個(gè)刪除的操作而令塊被重新加入到 freelist設(shè)置 pctused=40 意味著只有在塊的使用低于 40% 時(shí)才會(huì)回到表格的 freelists 中。設(shè)置 pctfree=10意味著每個(gè)塊都保留 10% 的空間用作行擴(kuò)展。雖然 freelist link 和 unlink 是簡(jiǎn)單的 Oracle 功能，不過(guò)設(shè)置 freelist link (pctused) 和 unlink (pctfree) 對(duì) Oracle 的性能確實(shí)有影響。你也許知道， freelist 串是放在表格或者索引的第一個(gè)塊中，這個(gè)塊也被稱為段頭（ segment header ）。freelists 是如何影響 Oracle 的性能的。Oracle 的表格和索引時(shí)，需要認(rèn)真考慮究竟需要高性能還是有效的空間重用，并且據(jù)此來(lái)設(shè)置表的參數(shù)。不過(guò)，高性能和有效地重新使用表的塊是對(duì)立的。Freelists 對(duì)于有效地重新使用 Oracle 表空間中的空間是很重要的，它和 pctfree 及 pctused 這兩個(gè)存儲(chǔ)參數(shù)的設(shè)置直接相關(guān)。當(dāng)這些設(shè)置不正確時(shí)，那些得到的 freelists 也是 dead 塊，因?yàn)樗鼈儧](méi)有足夠的空間來(lái)存儲(chǔ)一行，這樣將會(huì)導(dǎo)致明顯的處理延遲。 　　 對(duì)于初學(xué)者來(lái)說(shuō)，最常見(jiàn)的錯(cuò)誤是認(rèn)為默認(rèn)的 Oracle 參數(shù)對(duì)于所有的對(duì)象都是最佳的。 　　 要精通對(duì)象的調(diào)整，你需要懂得 freelists 和 freelist 組的行為，它們和 pctfree 及 pctused 參數(shù)的值有關(guān)。不過(guò)，對(duì)于有經(jīng)驗(yàn)的 Oracle 調(diào)優(yōu)專家來(lái)說(shuō)，他需要懂得 Oracle 是如何管理表的 extent 和空閑的數(shù)據(jù)塊。 調(diào)整 Oracle 的競(jìng)爭(zhēng) 　　Oracle 的其中一個(gè)優(yōu)點(diǎn)時(shí)它可以管理每個(gè)表空間中的自由空間。任務(wù)的數(shù)據(jù)塊由緩沖中移走。令任務(wù)變慢，并且會(huì)影響 Oracle 實(shí)例的當(dāng)前任務(wù)的執(zhí)行。 Oracle 還必須分配緩沖池塊來(lái)保持臨時(shí)表空間中的塊。首先，和內(nèi)存排序相比較，它們特別慢；而且磁盤排序會(huì)消耗臨時(shí)時(shí)表空間中進(jìn)行。中的 sort_area_retained_size 參數(shù)決定。 　　 上面我們已經(jīng)提到，私有排序區(qū)域的大小是有 中的 sort_area_size 參數(shù)決定的。當(dāng)然，當(dāng)排序的空間需求超出了 sort_area_size 的大小時(shí)，這時(shí)將會(huì)在 TEMP 表空間中分頁(yè)進(jìn)行磁盤排序。不幸的是，對(duì)于所有的 session ，用做排序的內(nèi)存量都必須是一樣的，我們不能為需要更大排序的操作分配額外的排序區(qū)域。通常，在以下的情況下 Oracle 會(huì)進(jìn)行排序的操作： 　　 使用 Order by 的 SQL 語(yǔ)句 　　 使用 Group by 的 SQL 語(yǔ)句 　　 在創(chuàng)建索引的時(shí)候 　　 進(jìn)行 table join 時(shí)，由于現(xiàn)有索引的不足而導(dǎo)致 SQL 優(yōu)化器調(diào)用 MERGE SORT 　　 當(dāng)與 Oracle 建立起一個(gè) session 時(shí)，在內(nèi)存中就會(huì)為該 session 分配一個(gè)私有的排序區(qū)域。 　　 調(diào)整 Oracle 的排序操作 　　 排序是 SQL 語(yǔ)法中一個(gè)小的方面，但很重要，在 Oracle 的調(diào)整中，它常常被忽略。 這些規(guī)則看來(lái)簡(jiǎn)單，不過(guò)它們占 SQL 調(diào)優(yōu)任務(wù)的 90% ，并且它們也無(wú)需完全懂得 Oracle SQL 的內(nèi)部運(yùn)作。 　　. 確保最優(yōu)的 JOIN 操作：有些查詢使用 NESTED LOOP join 快一些，有些則是 HASH join 快一些，另外一些則是有時(shí) Oracle 可以選擇多個(gè)索引來(lái)進(jìn)行查詢，調(diào)優(yōu)專家必須檢查每個(gè)索引并且確保 Oracle 使用正確的索引。 　 在強(qiáng)制使用一個(gè)index 來(lái)達(dá)到，只需要在 SQL 語(yǔ)句中加入一個(gè)索引的提示就可以了。的開(kāi)銷，它們的開(kāi)銷和數(shù)據(jù)塊的讀取和可能的并行執(zhí)行有關(guān)，并將兩者作對(duì)比。B 樹(shù)索引，也可以加入 bitmap 和基于函數(shù)的索引。對(duì)于不必要的全表搜索來(lái)說(shuō)，最常見(jiàn)的調(diào)優(yōu)方法是增加索引。調(diào)優(yōu)專家首先會(huì)根據(jù)查詢返回的行數(shù)目來(lái)評(píng)價(jià) SQL 。不過(guò)有一些基本的規(guī)則是每個(gè) Oracle DBA 都需要跟從的，這些規(guī)則可以改善他們系統(tǒng)的性能。 　　 一旦你已經(jīng)決定重新排序表中的行，你可以使用以下的工具之一來(lái)重新組織表格。 　　 行重新排序的作用是不可以小看的。如果 clustering_factor 的值和表中的塊數(shù)目大致一樣，那么你的表和索引的順序是一樣的。至于一個(gè)表中的行與索引的排序鍵的匹配程度，可以查看數(shù)據(jù)字典中的 dba_indexes 和 dba_tables 視圖得到。 　 這個(gè)查詢將會(huì)至少使用 100 次物理磁盤的讀取，因?yàn)?employee 的行存放在不同的數(shù)據(jù)塊中。B%39。 　　 讓我們看一下它是如何工作的。這個(gè)技巧僅在應(yīng)用選擇多行的時(shí)候有用，或者在使用索引范圍搜索和應(yīng)用發(fā)出多個(gè)查詢來(lái)得到連續(xù)的 key 時(shí)有效。通過(guò)與其它的 DBA 管理技巧一起使用，就可以在高 I/O 的系統(tǒng)中大大地減少響應(yīng)的時(shí)間。通過(guò)在物理上將行排序?yàn)楹?primarykey 索引一樣的順序，就可以加快獲得數(shù)據(jù)的速度。如果在一個(gè)事務(wù)中通過(guò)按一定的范圍搜索 primarykey因此，如果可以令 I/O 最小化，或者減少由于磁盤上的文件競(jìng)爭(zhēng)而帶來(lái)的瓶頸，就可以大大地改善 Oracle 數(shù)據(jù)庫(kù)的性能。其中磁盤 I/O 特別厲害，因?yàn)楫?dāng) Oracle 由磁盤上的一個(gè)數(shù)據(jù)文件得到一個(gè)數(shù)據(jù)塊時(shí)，讀的進(jìn)程就必須等待物理 I/O 操作完成。 　　 以下讓我們來(lái)看一下 Oracle 的 rowresequencing 是如何能夠極大地減少磁盤 I/O 的。 DBA 的目標(biāo)是確保數(shù)據(jù)庫(kù)服務(wù)器擁有足夠的 CPU 和內(nèi)存資源來(lái)處理補(bǔ)救的方法是增加更多的內(nèi)存，減少 Oracle SGA 的大小，或者關(guān)閉 Oracle 的多線程服務(wù)器。 　　2 、內(nèi)存分頁(yè)。 　　 在檢查 Oracle 的外部環(huán)境時(shí)，有兩個(gè)方面是需要注意的： 　　1 、當(dāng)運(yùn)行隊(duì)列的數(shù)目超過(guò)服務(wù)器的 CPU 數(shù)量時(shí)，服務(wù)器的性能就會(huì)受到 CPU 的限制。 　　 許多新手都錯(cuò)誤的認(rèn)為應(yīng)該首先調(diào)整 Oracle 數(shù)據(jù)庫(kù)，而不是先確認(rèn)外部資源是否足夠。 　　 ．內(nèi)存 可用于 Oralce 的內(nèi)存數(shù)量也會(huì)影響 SQL 的性能，特別是在數(shù)據(jù)緩沖和內(nèi)存排序方面。這些外部的條件包括有： 　　 ．CPUCPU 資源的不足令查詢變慢。 　　外