Oracle 性能收集資料

1、弄清楚你的業(yè)務(wù)類型OLTP or OLAP在Oracle數(shù)據(jù)庫(kù)系統(tǒng)中，很多人沒有弄清楚自己的業(yè)務(wù)類型到底是什么，就在開始盲目的尋求優(yōu)化方法，而往往是把OLAP的方法使用在OLTP上，或者是OLTP的方法使用在OLAP上。這樣的使用，有的時(shí)候，對(duì)性能沒有任何的提高，甚至是大大的影響了性能，得到適得其反的效果。所以，在優(yōu)化系統(tǒng)之前，弄清楚自己的業(yè)務(wù)類型。OLTP (聯(lián)機(jī)事務(wù)務(wù)處理系統(tǒng)）也稱為面向交易易的處理系統(tǒng)統(tǒng)，其基本特特征是顧客的的原始數(shù)據(jù)可可以立即傳送送到計(jì)算中心心進(jìn)行處理，并并在很短的時(shí)時(shí)間內(nèi)給出處處理結(jié)果。這這樣做的最大大優(yōu)點(diǎn)是可以以即時(shí)地處理理輸入的數(shù)據(jù)據(jù)，及時(shí)地回回答。也稱為為實(shí)時(shí)系

2、統(tǒng)(Real time Systeem)。衡量量聯(lián)機(jī)事務(wù)處處理系統(tǒng)的一一個(gè)重要性能能指標(biāo)是系統(tǒng)統(tǒng)性能，具體體體現(xiàn)為實(shí)時(shí)時(shí)響應(yīng)時(shí)間(Respoonse TTime)，即即用戶在終端端上送入數(shù)據(jù)據(jù)之后，到計(jì)計(jì)算機(jī)對(duì)這個(gè)個(gè)請(qǐng)求給出答答復(fù)所需要的的時(shí)間。OLAP（聯(lián)機(jī)機(jī)分析處理系系統(tǒng)）聯(lián)機(jī)分析處理 (OLAPP) 的概念念最早是由關(guān)關(guān)系數(shù)據(jù)庫(kù)之之父E.F.Codd于于1993年年提出的，他他同時(shí)提出了了關(guān)于OLAAP的12條條準(zhǔn)則。OLLAP的提出出引起了很大大的反響，OOLAP作為為一類產(chǎn)品同同聯(lián)機(jī)事務(wù)處處理 (OLLTP) 明明顯區(qū)分開來來。 當(dāng)今的數(shù)據(jù)處理理大致可以分分成兩大類：聯(lián)機(jī)事務(wù)處處理O

3、LTPP（on-lline ttransaactionn proccessinng）、聯(lián)機(jī)機(jī)分析處理OOLAP（OOn-Linne Anaalyticcal Prrocesssing）。OOLTP是傳傳統(tǒng)的關(guān)系型型數(shù)據(jù)庫(kù)的主主要應(yīng)用，主主要是基本的的、日常的事事務(wù)處理，例例如銀行交易易。OLAPP是數(shù)據(jù)倉(cāng)庫(kù)庫(kù)系統(tǒng)的主要要應(yīng)用，支持持復(fù)雜的分析析操作，側(cè)重重決策支持，并并且提供直觀觀易懂的查詢?cè)兘Y(jié)果。 OLTP與OLLAP之間的的比較:OLTPOLAP用戶操作人員，低層層管理人員決策人員，高級(jí)級(jí)管理人員功能日常操作處理分析決策DB 設(shè)計(jì)面向應(yīng)用面向主題數(shù)據(jù)最新的，細(xì)節(jié)的的，二維的，分分立的歷史的

4、，聚集的的，多維的，集集成的存取規(guī)模讀/寫數(shù)條（甚甚至數(shù)百條）記記錄讀上百萬（甚至至上億）條記記錄操作頻度非常頻繁（以秒秒計(jì)）比較稀松（以小小時(shí)甚至以周周計(jì)）工作單位嚴(yán)格的事務(wù)復(fù)雜的查詢用戶數(shù)數(shù)百個(gè)-數(shù)千萬萬個(gè)數(shù)個(gè)-數(shù)百個(gè)DB 大小100MB-GGB100GB-TTB1、什么是OLLTP OLLTP，也叫叫聯(lián)機(jī)事務(wù)處處理(Onlline TTransaactionn Proccessinng)，表示示事務(wù)性非常常高的系統(tǒng)，一一般都是高可可用的在線系系統(tǒng)，以小的的事務(wù)以及小小的查詢?yōu)橹髦?，評(píng)估其系系統(tǒng)的時(shí)候，一一般看其每秒秒執(zhí)行的trransacction以以及execcute ssql的數(shù)量量

5、。在這樣的的系統(tǒng)中，每每秒處理的ttransaactionn往往超過幾幾百個(gè)，或者者是幾千個(gè)，sselectt 語句的執(zhí)執(zhí)行量每秒幾幾千甚至幾萬萬個(gè)。典型的的OLTP系系統(tǒng)如電子商商務(wù)系統(tǒng)，銀銀行，證卷等等等，如美國(guó)國(guó)ebay的的業(yè)務(wù)數(shù)據(jù)庫(kù)庫(kù)，就是很典典型的OLTTP數(shù)據(jù)庫(kù)。 OLTTP系統(tǒng)最容容易出現(xiàn)的瓶瓶頸就是CPPU與磁盤子子系統(tǒng)。cppu則取決于于邏輯讀以及及內(nèi)部調(diào)用，如如函數(shù)等等。一一個(gè)執(zhí)行頻繁繁的SQL語語句，如果每每個(gè)語句可以以減少很少的的邏輯讀，也也相當(dāng)于優(yōu)化化了一些邏輯輯讀很差的大大型語句。很很多人不感覺覺不到這里的的作用，覺得得一個(gè)語句幾幾十個(gè)邏輯讀讀，執(zhí)行時(shí)間間基本為0，

6、就就不需要優(yōu)化化了，其實(shí)，只只要他的執(zhí)行行次數(shù)非常頻頻繁，而且有有優(yōu)化的余地地，就一定要要優(yōu)化，如減減少一定的邏邏輯讀或者降降低執(zhí)行次數(shù)數(shù)，都是優(yōu)化化方法。 另外，一一些計(jì)算性的的函數(shù)，如ssum，coount，ddecodee被非常頻繁繁的使用，也也是非常消耗耗cpu的，我我遇到一個(gè)系系統(tǒng)，因?yàn)橐灰粋€(gè)sql語語句，大量的的使用了suum與deccode進(jìn)行行行列轉(zhuǎn)換，結(jié)結(jié)果這一個(gè)語語句就耗費(fèi)了了整個(gè)機(jī)器一一半以上的CCPU。 那么，在在一般的OLLTP系統(tǒng)中中，如果不考考慮我上面說說的函數(shù)問題題，那么，邏邏輯讀乘以執(zhí)執(zhí)行次數(shù)，決決定了cpuu的消耗程度度，如一個(gè)語語句，每秒執(zhí)執(zhí)行次數(shù)為550

7、0次，每每個(gè)邏輯讀為為15，但是是，通過優(yōu)化化，能讓每個(gè)個(gè)語句的邏輯輯讀從15降降到10，那那么，每秒的的邏輯讀就可可以減少5000*5=22500個(gè)，其其實(shí)就是相當(dāng)當(dāng)于優(yōu)化了一一個(gè)執(zhí)行頻率率為每秒1次次，每次邏輯輯讀為25000個(gè)的語句句（注意，22500個(gè)邏邏輯讀，在ooltp系統(tǒng)統(tǒng)是非常差的的語句）。再再如，假定一一個(gè)1GHZZ的cpu每每秒能正常處處理的邏輯讀讀是100,000個(gè)，如如果是10個(gè)個(gè)邏輯讀一個(gè)個(gè)的語句，每每秒可以處理理10,0000個(gè)，而11000個(gè)邏邏輯讀一個(gè)的的語句，每秒秒則只能處理理100個(gè)。 同以上上道理，物理理讀乘以執(zhí)行行次數(shù)，則決決定了存儲(chǔ)子子系統(tǒng)的處理理能力

8、，在一一個(gè)OLTPP環(huán)境中，物物理讀一般都都是db ffile ssequenntial read決決定的，也就就是單塊讀，一一個(gè)典型的OOLTP系統(tǒng)統(tǒng)，db ffile ssequenntial read應(yīng)應(yīng)當(dāng)基本等于于磁盤子系統(tǒng)統(tǒng)的讀的IOOPS。而磁磁盤子系統(tǒng)的的IOPS處處理能力，與與cachee命中率以及及磁盤個(gè)數(shù)有有很大的關(guān)系系。我的一些些文章中，也也分析到了這這些問題，如如一個(gè)15KK轉(zhuǎn)速的磁盤盤，每秒最多多能處理的iiops達(dá)到到150個(gè)，基基本就是極限限了，如果ccache不不命中，那么么100個(gè)磁磁盤，最多能能處理的IOOPS僅僅是是150000個(gè)（但是，實(shí)實(shí)際上，還基基本

9、達(dá)不到這這個(gè)值）。 OLTTP最常用的的技術(shù)就是ccache技技術(shù)與btrree索引，ccache決決定了很多語語句不需要從從磁盤子系統(tǒng)統(tǒng)獲得數(shù)據(jù)，所所以，webb cachhe與oraacle ddata bbufferr對(duì)OLTPP系統(tǒng)是很重重要的。另外外，在索引使使用方面，語語句是越簡(jiǎn)單單越好，這樣樣執(zhí)行計(jì)劃也也穩(wěn)定，而且且一定要使用用綁定變量，減減少語句解析析，盡量減少少關(guān)聯(lián)。其它它方面，基本本不使用分區(qū)區(qū)技術(shù)，MVV技術(shù)，并行行技術(shù)以及位位圖索引，因因?yàn)椴l(fā)量很很高，批量更更新可能要盡盡量快速提交交避免阻塞的的發(fā)生。 在eebay的數(shù)數(shù)據(jù)庫(kù)設(shè)計(jì)中中，有一個(gè)很很重要的點(diǎn)就就是，數(shù)據(jù)庫(kù)庫(kù)

10、只負(fù)責(zé)存放放數(shù)據(jù)，業(yè)務(wù)務(wù)邏輯盡量在在業(yè)務(wù)層實(shí)現(xiàn)現(xiàn)，因?yàn)閿?shù)據(jù)據(jù)庫(kù)擴(kuò)展是困困難的，而應(yīng)應(yīng)用服務(wù)器擴(kuò)擴(kuò)展是簡(jiǎn)單的的。其實(shí)，也也就是說，在在高可用的OOLTP環(huán)境境中，數(shù)據(jù)庫(kù)庫(kù)使用越簡(jiǎn)單單的功能越好好。2、什么是OLLAP OLAAP，也叫聯(lián)聯(lián)機(jī)分析(OOnlinee Anallyticaal Proocessiing)，有有的時(shí)候也叫叫DSS決策策支持系統(tǒng)，就就是我們說的的數(shù)據(jù)倉(cāng)庫(kù)。在在這樣的系統(tǒng)統(tǒng)中，語句的的執(zhí)行量不是是考核標(biāo)準(zhǔn)，因因?yàn)橐粋€(gè)語句句的執(zhí)行時(shí)間間可能會(huì)非常常長(zhǎng)，讀取的的數(shù)據(jù)也非常常多。所以，這這樣的系統(tǒng)中中，考核的標(biāo)標(biāo)準(zhǔn)往往決定定于磁盤子系系統(tǒng)的吞吐量量。 磁盤盤子系統(tǒng)的吞吞吐量則直接

11、接取決于磁盤盤的個(gè)數(shù)，這這個(gè)時(shí)候，ccache基基本是沒有效效果的，這個(gè)個(gè)時(shí)候數(shù)據(jù)庫(kù)庫(kù)的讀寫基本本上是db file scatttered read與與direcct patth reaad/wriite。在我我前面的一些些文章中描述述過，如果一一個(gè)15K的的磁盤的IOO量每秒133M，那么，1100個(gè)磁盤盤，最多能提提供的吞吐量量則是13000M/s（實(shí)實(shí)際上，也基基本達(dá)不到這這個(gè)值）。如如果磁盤個(gè)數(shù)數(shù)足夠的話，還還需要考慮采采用比較大的的帶寬，如44GB的光纖纖接口。 在OLLAP系統(tǒng)中中，常使用的的技術(shù)有分區(qū)區(qū)技術(shù)，并行行技術(shù)。如分分區(qū)技術(shù)可以以使得一些大大表的掃描變變得很快（只只掃描單

12、個(gè)分分區(qū)），而且且方便管理。另另外，如果分分區(qū)結(jié)合并行行的話，也可可以使得整個(gè)個(gè)表的掃描也也會(huì)變得很快快。并行技術(shù)術(shù)除了與分區(qū)區(qū)技術(shù)結(jié)合外外，在oraacle 110g中，與與rac結(jié)合合實(shí)現(xiàn)多節(jié)點(diǎn)點(diǎn)的同時(shí)掃描描，效果也非非常不錯(cuò)，把把一個(gè)任務(wù)，如如selecct的全表掃掃描，平均的的分派到多個(gè)個(gè)rac的節(jié)節(jié)點(diǎn)上去。 在OLLAP系統(tǒng)中中，不需要使使用綁定變量量，因?yàn)檎麄€(gè)個(gè)系統(tǒng)的執(zhí)行行量很少，分分析時(shí)間對(duì)于于執(zhí)行時(shí)間來來說，可以忽忽略，而且避避免出現(xiàn)錯(cuò)誤誤的執(zhí)行計(jì)劃劃。但是OLLAP中可以以大量使用位位圖索引，物物化視圖，對(duì)對(duì)于大的事務(wù)務(wù)，盡量的尋尋求速度上的的優(yōu)化，沒有有必要象OLLTP需要

13、快快速提交，甚甚至要刻意減減慢執(zhí)行的速速度。3、總結(jié) 特別是是在高可用的的OLTP環(huán)環(huán)境中，不要要盲目的把OOLAP的技技術(shù)拿過來用用，如分區(qū)技技術(shù)，如果不不是大范圍的的使用了分區(qū)區(qū)關(guān)鍵字作為為wheree條件，而采采用其它的字字段作為whhere條件件，那么，如如果是本地索索引，你將不不得不掃描多多個(gè)索引，而而性能變的更更為低下。如如果是全局索索引，那分區(qū)區(qū)的意義又何何在，只是多多出一份分區(qū)區(qū)技術(shù)的liicensee而已。 并行技技術(shù)也是如此此，一般是在在大型任務(wù)的的時(shí)候才使用用，好比說，實(shí)實(shí)際生活中，一一個(gè)比較大型型的工作，如如翻譯一本書書，你可以先先安排多個(gè)人人，每個(gè)人翻翻譯不同的章章節(jié)

14、，這樣是是可以提高翻翻譯速度，但但是，你現(xiàn)在在只是翻譯一一頁，你也去去分配不同的的人翻譯不同同的行，再組組合起來，這這個(gè)時(shí)間，你你一個(gè)人或者者早就翻譯完完了。 位圖索索引在我前幾幾篇文章中有有交代，如果果用在olttp環(huán)境中，可可能因?yàn)樽枞秶?，很很容易阻塞與與死鎖，但是是，在olaap環(huán)境中，可可能會(huì)因?yàn)槠淦涮赜械奶匦孕裕岣遫llap的查詢?cè)兯俣?。mvv也是基本一一樣，包括觸觸發(fā)器等等，在在dml頻繁繁的oltpp系統(tǒng)上，很很容易成為瓶瓶頸，而在oolap環(huán)境境上，則可能能會(huì)因?yàn)槭褂糜们‘?dāng)而提高高查詢速度。 HYPERLINK /article/62/1187.html http:/

15、aarticlle/62/1187.htmlOracle索索引使用限制制 和 建議議什么是索引索引是建立在表表的一列或多多個(gè)列上的輔輔助對(duì)象，目目的是加快訪訪問表中的數(shù)數(shù)據(jù)； Oracle存存儲(chǔ)索引的數(shù)數(shù)據(jù)結(jié)構(gòu)是BB*樹，位圖圖索引也是如如此，只不過過是葉子節(jié)點(diǎn)點(diǎn)不同B*數(shù)數(shù)索引；索引引由根節(jié)點(diǎn)、分分支節(jié)點(diǎn)和葉葉子節(jié)點(diǎn)組成成，上級(jí)索引引塊包含下級(jí)級(jí)索引塊的索索引數(shù)據(jù)，葉葉節(jié)點(diǎn)包含索索引數(shù)據(jù)和確確定行實(shí)際位位置的rowwid。一、oraclle的索引陷陷阱一個(gè)表中有幾百百萬條數(shù)據(jù)，對(duì)對(duì)某個(gè)字段加加了索引，但但是查詢時(shí)性性能并沒有什什么提高，這這主要可能是是oraclle的索引限限制造成的。 or

16、acle的的索引有一些些索引限制，在在這些索引限限制發(fā)生的情情況下，即使使已經(jīng)加了索索引，oraacle還是是會(huì)執(zhí)行一次次全表掃描，查查詢的性能不不會(huì)比不加索索引有所提高高，反而可能能由于數(shù)據(jù)庫(kù)庫(kù)維護(hù)索引的的系統(tǒng)開銷造造成性能更差差。下面是一些常見見的索引限制制問題。1、使用不等于于操作符(, !=)下面這種情況，即即使在列deept_idd有一個(gè)索引引，查詢語句句仍然執(zhí)行一一次全表掃描描 select * froom deppt wheere sttaff_nnum 10000; 但是開發(fā)中的確確需要這樣的的查詢，難道道沒有解決問問題的辦法了了嗎？ 有！ 通過把用 orr 語法替代代不等號(hào)進(jìn)

17、行行查詢，就可可以使用索引引，以避免全全表掃描：上上面的語句改改成下面這樣樣的，就可以以使用索引了了。 select * froom deppt sheere sttaff_nnum 10000; 2、使用 iss nulll 或 iss not null使用 is nnull 或或is nuuo nulll也會(huì)限制制索引的使用用，因?yàn)閿?shù)據(jù)據(jù)庫(kù)并沒有定定義nulll值。如果被被索引的列中中有很多nuull，就不不會(huì)使用這個(gè)個(gè)索引（除非非索引是一個(gè)個(gè)位圖索引，關(guān)關(guān)于位圖索引引，會(huì)在以后后的blogg文章里做詳詳細(xì)解釋）。在在sql語句句中使用nuull會(huì)造成成很多麻煩。 解決這個(gè)問題的的辦法就是

18、：建表時(shí)把需需要索引的列列定義為非空空(not null)3、使用函數(shù)如果沒有使用基基于函數(shù)的索索引，那么wwhere子子句中對(duì)存在在索引的列使使用函數(shù)時(shí)，會(huì)會(huì)使優(yōu)化器忽忽略掉這些索索引。下面的的查詢就不會(huì)會(huì)使用索引： select * froom staaff whhere ttrunc(birthhdate) = 001-MAYY-82; 但是把函數(shù)應(yīng)用用在條件上，索索引是可以生生效的，把上上面的語句改改成下面的語語句，就可以以通過索引進(jìn)進(jìn)行查找。 select * froom staaff whhere bbirthddate shhow paarametter opptimizzer_

19、inndex_ccost_aadjNAME TYPPEVALUUE optimizzer_inndex_ccost_aadj integger 100為了多使用索引引，可減小它它的值SQL allter ssystemm set optimmizer_indexx_costt_adj=10;進(jìn)一步設(shè)置為了進(jìn)一步優(yōu)化化，oraccle建議對(duì)對(duì)小表不要建建索引，那什什么為小表呢呢，oraccle一次II/O就可讀讀寫完整個(gè)表表的表為小表表，查看orracle110g一次讀讀寫的數(shù)據(jù)量量SQL shhow paarametter dbb_filee_multtiblocck_reaad_couunt

20、NAME TYYPE VVALUE db_filee_multtiblocck_reaad_couuntinntegerr 128可見當(dāng)表小于1128k時(shí)就就不要建索引引了二、各種索引使使用場(chǎng)合及建建議（1）B*Trree索引。常規(guī)索引，多用用于oltpp系統(tǒng)，快速速定位行，應(yīng)應(yīng)建立于高ccardinnalityy列（即列的的唯一值除以以行數(shù)為一個(gè)個(gè)很大的值，存存在很少的相相同值）。Createe indeex inddexnamme on tableename(colummnnameecoluumnnamme.)（2）反向索引引。B*Tree的的衍生產(chǎn)物，應(yīng)應(yīng)用于特殊場(chǎng)場(chǎng)合，在opps環(huán)境加

21、序序列增加的列列上建立，不不適合做區(qū)域域掃描。Createe indeex inddexnamme on tableename(colummnnameecoluumnnamme.) revverse（3）降序索引引。B*Tree的的衍生產(chǎn)物，應(yīng)應(yīng)用于有降序序排列的搜索索語句中，索索引中儲(chǔ)存了了降序排列的的索引碼，提提供了快速的的降序搜索。Createe indeex inddexnamme on tableename(colummnnamee DESCCcoluumnnamme.)（4）位圖索引引。位圖方式管理的的索引，適用用于OLAPP（在線分析析）和DSSS（決策處理理）系統(tǒng)，應(yīng)應(yīng)建立于低

22、ccardinnalityy列，適合集中讀取，不不適合插入和和修改，提供供比B*Trree索引更更節(jié)省的空間間。Createe BITMMAP inndex iindexnname oon tabblenamme(collumnnaamecoolumnnname)在實(shí)際應(yīng)用中，如如果某個(gè)字段段的值需要頻頻繁更新，那那么就不適合合在它上面創(chuàng)創(chuàng)建位圖索引引。在位圖索引中，如如果你更新或或插入其中一一條數(shù)值為NN的記錄，那么相應(yīng)表中數(shù)數(shù)值為N的記記錄（可能成成百上千條）全全部被Oraacle鎖定定，這就意味著其它它用戶不能同同時(shí)更新這些些數(shù)值為N的的記錄，其它它用戶必須要要等第一個(gè)用用戶提交后，才能

23、獲得鎖，更更新或插入數(shù)數(shù)據(jù)，bittmap iindex它它主要用于決決策支持系統(tǒng)統(tǒng)或靜態(tài)數(shù)據(jù)據(jù)。（5）函數(shù)索引引。B*Tree的的衍生產(chǎn)物，應(yīng)應(yīng)用于查詢語語句條件列上上包含函數(shù)的的情況，索引中儲(chǔ)存了經(jīng)經(jīng)過函數(shù)計(jì)算算的索引碼值值。可以在不不修改應(yīng)用程程序的基礎(chǔ)上上能提高查詢?cè)冃?。索引?chuàng)建策略 1.導(dǎo)入數(shù)據(jù)后后再創(chuàng)建索引引 2.不需要為很很小的表創(chuàng)建建索引 3.對(duì)于取值范范圍很小的字字段（比如性性別字段）應(yīng)應(yīng)當(dāng)建立位圖圖索引 4.限制表中的的索引的數(shù)目目 5.為索引設(shè)置置合適的PCCTFREEE值 6.存儲(chǔ)索引的的表空間最好好單獨(dú)設(shè)定 唯一索引和不唯唯一索引都只只是針對(duì)B樹樹索引而言. Ora

24、cle最最多允許包含含32個(gè)字段段的復(fù)合索引引 由此估計(jì)出一個(gè)個(gè)查詢?nèi)绻故褂媚硞€(gè)索引引會(huì)需要讀入入的數(shù)據(jù)塊塊塊數(shù)。需要讀入的數(shù)據(jù)據(jù)塊越多，則則 costt 越大，OOraclee 也就越有有可能不選擇擇使用 inndex三、能用唯一索索引，一定用用唯一索引 能加非空，就加加非空約束 一定要統(tǒng)計(jì)表的的信息，索引引的信息，柱柱狀圖的信息息。 聯(lián)合索引的順序序不同，影響響索引的選擇擇，盡量將值值少的放在前前面 只有做到以上四四點(diǎn)，數(shù)據(jù)庫(kù)庫(kù)才會(huì)正確的的選擇執(zhí)行計(jì)計(jì)劃。ORACLE索索引介紹與高高性能SQLL優(yōu)化什么是索引索引是建立在表表的一列或多多個(gè)列上的輔輔助對(duì)象，目目的是加快訪訪問表中的數(shù)數(shù)據(jù)；

25、Oraacle存儲(chǔ)儲(chǔ)索引的數(shù)據(jù)據(jù)結(jié)構(gòu)是B*樹，位圖索索引也是如此此，只不過是是葉子節(jié)點(diǎn)不不同B*數(shù)索索引；索引由由根節(jié)點(diǎn)、分分支節(jié)點(diǎn)和葉葉子節(jié)點(diǎn)組成成，上級(jí)索引引塊包含下級(jí)級(jí)索引塊的索索引數(shù)據(jù)，葉葉節(jié)點(diǎn)包含索索引數(shù)據(jù)和確確定行實(shí)際位位置的rowwid。使用索引的目的的加快查詢速度減少I/O操作作消除磁盤排序何時(shí)使用索引查詢返回的記錄錄數(shù)排序表40%非排序表440%，排序序表7%，建建議采用并行行機(jī)制來提高高訪問速度，DDDS；索引訪問。最常用的方法法，包括索引引唯一掃描和和索引范圍掃掃描，OLTTP；快速完全索引掃掃描。訪問索引中所所有數(shù)據(jù)塊，結(jié)結(jié)果相當(dāng)于全全表掃描，可可以用索引掃掃描代替全表

26、表掃描，例如如：Selectserv_id,coount(*)frromtgg_cdr001grooupbyyservv_id;評(píng)估全表掃描的的合法性如何實(shí)現(xiàn)并行掃掃描。永久并行化（不不推薦）alterttablecustoomerpparallleldeegree8;。單個(gè)查詢并行行化select/*+ffull(eemp)pparalllel(emmp,8)*/*ffromeemp;分區(qū)表效果明顯顯優(yōu)化SQL語句句排序排序的操作：。orderby子句句。groupby子句句。selecttdisttinct子子句。創(chuàng)建索引時(shí)。union或或minuss。排序合并連接接如何避免排序。添加索引

27、。在索引中使用用distiinct子句句。避免排序合并并連接使用提示進(jìn)行調(diào)調(diào)整使用提示的原則則。語法：/*+hintt*/。使用表別名:selecct/*+indeex(eddept_iidx)*/*frromemmpe。檢驗(yàn)提示常用的提示。rule。all_roows。first_rows。use_nll。use_haash。use_meerge。index。index_asc。no_inddex。index_desc（常常用于使用mmax內(nèi)置函函數(shù)）。index_combiine(強(qiáng)制制使用位圖索索引)。index_ffs（索索引快速完全全掃描）。use_cooncat(將查詢中所所有or

28、條件件使用uniionalll)。paralllel。noparaallel。full。ordereed（基于成成本）調(diào)整表連接表連接的類型。等連接where條條件中用等式式連接；。外部連接（左左、右連接）在where條條件子句的等等式謂詞放置置一個(gè)(+)來實(shí)現(xiàn)，例例如：selecta.enaame,mfromempaa,bonuusbwwherea.enaame=b.enamee(+);該語句返回所有有emp表的的記錄；。自連接Selecta.valluetootal,B.valluehaard,(A.vallue-b.vallue)ssoft,Round(b.vallue/a.valuee

29、)*1000,1)ppercFromv$syssttata,v$syssstatbWhereaa.stattisticc#=1179andB.sstatisstic#=1800;反連接反連接常用于nnotinnornnotexxists中中，是指在查查詢中找到的的任何記錄都都不包含在結(jié)結(jié)果集中的子子查詢；不建建議使用nootinornootexiists;。半連接查詢中使用exxists，含含義：即使在在子查詢中返返回多條重復(fù)復(fù)的記錄，外外部查詢也只只返回一條記記錄。嵌套循環(huán)連接。被連接表中存存在索引的情情況下使用；。使用use_nl。hash連接。Hash連接接將驅(qū)動(dòng)表加加載在內(nèi)存中中，并使

30、用hhash技術(shù)術(shù)連接第二個(gè)個(gè)表，提高等等連接速度。適合于大表和和小表連接；。使用use_hash。排序合并連接。排序合并連接接不使用索引引。使用原則：連接表子段中不不存在可用索索引；查詢返回兩個(gè)表表中大部分的的數(shù)據(jù)快；CBO認(rèn)為全表表掃描比索引引掃描執(zhí)行的的更快。使用use_mergee使用臨時(shí)/中間間表多個(gè)大表關(guān)聯(lián)時(shí)時(shí)，可以分別別把滿足條件件的結(jié)果集存存放到中間表表，然后用中中間表關(guān)聯(lián)；SQL子查詢的的調(diào)整關(guān)聯(lián)與非關(guān)聯(lián)子子查詢。關(guān)聯(lián)：子查詢?cè)兊膬?nèi)部引用用的是外部表表，每行執(zhí)行行一次；。非關(guān)聯(lián)：子查查詢只執(zhí)行一一次，存放在在內(nèi)存中。調(diào)整notiin和nootexiists語句句?？梢允褂猛獠?/p>

31、部連接優(yōu)化nnotinn子句，例如如：selectenameefrommempwhereedeptt_nonnotinn(selecttdeptt_noffromddeptwwheredept_name=Matth);改為：selectenameefrommemp,deptwhereeemp.deept_noo=deptt.deptt_noanddeppt.deppt_nammeisnull;使用索引調(diào)整SSQLOracle為什么不使使用索引。檢查被索引的的列或組合索索引的首列是是否出現(xiàn)在PPL/SQLL語句的WHHERE子句句中，這是“執(zhí)執(zhí)行計(jì)劃”能能用到相關(guān)索索引的必要條條件?？床捎昧四姆N

32、種類型的連接接方式。ORRACLE的的共有SorrtMerrgeJooin（SMMJ）、HaashJooin（HJJ）和NesstedLLoopJJoin（NNL）。在兩兩張表連接，且且內(nèi)表的目標(biāo)標(biāo)列上建有索索引時(shí)，只有有NesteedLooop才能有效效地利用到該該索引。SMMJ即使相關(guān)關(guān)列上建有索索引，最多只只能因索引的的存在，避免免數(shù)據(jù)排序過過程。HJ由由于須做HAASH運(yùn)算，索索引的存在對(duì)對(duì)數(shù)據(jù)查詢速速度幾乎沒有有影響。看連接順序是是否允許使用用相關(guān)索引。假假設(shè)表empp的depttno列上有有索引，表ddept的列列deptnno上無索引引，WHERRE語句有eemp.deeptno

33、=dept.deptnno條件。在在做NL連接接時(shí)，empp做為外表，先先被訪問，由由于連接機(jī)制制原因，外表表的數(shù)據(jù)訪問問方式是全表表掃描，emmp.depptno上的的索引顯然是是用不上，最最多在其上做做索引全掃描描或索引快速速全掃描。是否用到系統(tǒng)統(tǒng)數(shù)據(jù)字典表表或視圖。由由于系統(tǒng)數(shù)據(jù)據(jù)字典表都未未被分析過，可可能導(dǎo)致極差差的“執(zhí)行計(jì)計(jì)劃”。但是是不要擅自對(duì)對(duì)數(shù)據(jù)字典表表做分析，否否則可能導(dǎo)致致死鎖，或系系統(tǒng)性能下降降。索引列是否函函數(shù)的參數(shù)。如如是，索引在在查詢時(shí)用不不上。是否存在潛在在的數(shù)據(jù)類型型轉(zhuǎn)換。如將將字符型數(shù)據(jù)據(jù)與數(shù)值型數(shù)數(shù)據(jù)比較，OORACLEE會(huì)自動(dòng)將字字符型用too_numb

34、ber()函函數(shù)進(jìn)行轉(zhuǎn)換換，從而導(dǎo)致致上一種現(xiàn)象象的發(fā)生。是否為表和相相關(guān)的索引搜搜集足夠的統(tǒng)統(tǒng)計(jì)數(shù)據(jù)。對(duì)對(duì)數(shù)據(jù)經(jīng)常有有增、刪、改改的表最好定定期對(duì)表和索索引進(jìn)行分析析，可用SQQL語句“aanalyzzetabblexxxxxcoomputeestattisticcsforrallindexxes;”。OORACLEE掌握了充分分反映實(shí)際的的統(tǒng)計(jì)數(shù)據(jù)，才才有可能做出出正確的選擇擇。索引列的選擇擇性不高。我們假設(shè)典典型情況，有有表emp，共共有一百萬行行數(shù)據(jù)，但其其中的empp.depttno列，數(shù)數(shù)據(jù)只有4種種不同的值，如如10、200、30、440。雖然eemp數(shù)據(jù)行行有很多，OORACL

35、EE缺省認(rèn)定表表中列的值是是在所有數(shù)據(jù)據(jù)行均勻分布布的，也就是是說每種deeptno值值各有25萬萬數(shù)據(jù)行與之之對(duì)應(yīng)。假設(shè)設(shè)SQL搜索索條件DEPPTNO=110，利用ddeptnoo列上的索引引進(jìn)行數(shù)據(jù)搜搜索效率，往往往不比全表表掃描的高。索引列值是否否可為空（NNULL）。如如果索引列值值可以是空值值，在SQLL語句中那些些要返回NUULL值的操操作，將不會(huì)會(huì)用到索引，如如COUNTT（*），而而是用全表掃掃描。這是因因?yàn)樗饕写娲鎯?chǔ)值不能為為全空?？词欠裼杏玫降讲⑿胁樵儯≒PQO）。并并行查詢將不不會(huì)用到索引引。如果從以上幾幾個(gè)方面都查查不出原因的的話，我們只只好用采用在在語句中加hhi

36、nt的方方式強(qiáng)制ORRACLE使使用最優(yōu)的“執(zhí)執(zhí)行計(jì)劃”。hintt采用注釋的的方式，有行行注釋和段注注釋兩種方式式。如我我們想要用到到A表的INND_COLL1索引的話話，可采用以以下方式：“SELLECT/*+INNDEX（AAIND_COL1）*/*FFROMAAWHERRECOLL1=XXXX;如何屏蔽索引語句的執(zhí)行計(jì)劃劃中有不良索索引時(shí)，可以以人為地屏蔽蔽該索引，方方法：。數(shù)值型：在索索引字段上加加0，例如select*froomemppwherreempp_no+00=v_emp_nno;。字符型：在索索引字段上加加，例如如select*froomtg_cdr011wherrems

37、iisdn|=v_msisddn; HYPERLINK /post/19858/207902 SQLPLUSS中的sett小節(jié)在寫腳本的時(shí)候候，往往需要要對(duì)輸出的格格式進(jìn)行規(guī)范范，我們常常常用set語句在在設(shè)置一些需需要的格式規(guī)規(guī)范。下面，列列舉了幾個(gè)常常用的sett ，和他們們的應(yīng)用。SQL seet echho on設(shè)置運(yùn)行命命令是是否顯顯示語句SQL seet feeedbackk on設(shè)設(shè)置顯示“已選擇XX行”SQL seet collsep | 設(shè)置列列與列之間的的分割符號(hào)SQL seet paggesizee 10設(shè)置每一頁頁的行數(shù)SQL SEET SERRVEROUUTPUT O

38、N設(shè)置置允許顯示輸輸出類似dbbms_ouutputSQL seet heaading on設(shè)置顯示列列名SQL seet timming oon設(shè)置顯示示“已用時(shí)間：XXXX”SQL seet timme on設(shè)置顯顯示當(dāng)前時(shí)間間SQL seet auttotracce on設(shè)置允許許對(duì)執(zhí)行的ssql進(jìn)行分分析00:38:228 SQLL sett echoo on00:38:337 SQLL E:echo.sql00:38:441 SQLL sellect sstatuss fromm v$daatafille wheere roownum sett echoo off00:38:558

39、SQLL E:echo.sqlSTATUSSYSTEMONLINEONLINEONLINEONLINE已選擇5行。已用時(shí)間: 000: 000: 01.0900:39:008 SQLLSQL seet feeedbackk onSQQL set colseep |SQQL set pagessize 110SQL set serveeroutpput onnSQL set hheadinng onSSQL set timinng onSSQL set time on00:54:111 SQL SELEECT FILE#, STATTUS, EENABLEED FROOM V$DDATAFIIL

40、E;FIILE#|SSTATUSS |ENAABLED|1|SYSTEEM |READD WRITEE2|ONLIINE |READD WRITEE3|ONLIINE |READD WRITEE4|ONLIINE |READD WRITEE5|ONLIINE |READD WRITEE6|ONLIINE |READD WRITEE7|ONLIINE |READD WRITEEFILE#|STATUUS |ENNABLEDD|99|ONLIINE |READD WRITEE10|ONLLINE |READ WRITEE11|ONLLINE |READ WRITEE12|ONLLINE |READ

41、 WRITEE11 rowws sellectedd.Elappsed: 00:000:01.00700:554:34 SQL DEECLAREE00:55:000 2 BBEGIN00:55:000 3 DDBMS_OOUTPUTT.PUT_LINE(=TThis iis dbmms_outtput.pput_liine!=);00:55:000 4 EEND;00:55:002 5 /=Thhis iss dbmss_outpput.puut_linne!=PL/SQLL proceedure succeessfullly coompletted.Ellapsedd: 00:00:000.

42、02000:55:005 SQLLSQL seet auttotracce onSSQL SELEECT E.LAST_NAME, D.DEEPARTMMENT_NNAME, D.LOCCATIONN_ID2 FROM HHR.EMPPLOYEEES E3 JOIN HHR.DEPPARTMEENTS DD4 USINNG (DEEPARTMMENT_IID);LAAST_NAAME DEEPARTMMENT_NNAME LLOCATIION_IDD Kiing Exxecutiive 17700Kocchhar Execuutive 1700DDe Haaan Exeecutivve 170

43、00Hunoold ITT 14000Ernstt IT 11400106 roows seelecteed.Exeecutioon Plaan00 SELEECT STATEEMENT Optimmizer=CHOOSSE (Costt=5 Caard=1006 Byttes=31180)1 0 HASH JOIN (Cost=55 Cardd=106 Bytess=31800)2 1 TABLEE ACCESSS (FULLL) OF DEEPARTMMENTS (Cosst=2 CCard=227 Byttes=5113)3 11 TABLLE ACCESSS (FULLL) OF EM

44、MPLOYEEES (Cost=22 Cardd=107 Bytes=1177)Statiisticss7 recurrsive callss0 db blockk getss23 connsisteent geets12 physiical rreads00 redoo sizee2801 bytess sentt via SQL*Neet to cliient5880 byttes reeceiveed viaa SQL*Neet froom cliient9 SQL*Neet rouundtriips too/from cclientt0 sorrts (mmemoryy)0 soort

45、s (disk)106 roows prrocesssedSQLLStart wwith connnect bby priior在SELECTT命令中使用用CONNEECT BYY和藹STAART WIITH子句可可以查詢表中中的樹型結(jié)構(gòu)構(gòu)關(guān)系。其命命令格式如下下：SELECT。CONNECTT BY PRIORR列名1=列列名2|列名名1=PRIIOR裂名22START WITH；其中：CONNNECT BBY子句說明明每行數(shù)據(jù)將將是按層次順順序檢索，并并規(guī)定將表中中的數(shù)據(jù)連入入樹型結(jié)構(gòu)的的關(guān)系中。PPRIORYY運(yùn)算符必須須放置在連接接關(guān)系的兩列列中某一個(gè)的的前面。對(duì)于于節(jié)點(diǎn)間的父父子關(guān)系

46、，PPRIOR運(yùn)運(yùn)算符在一側(cè)側(cè)表示父節(jié)點(diǎn)點(diǎn)，在另一側(cè)側(cè)表示子節(jié)點(diǎn)點(diǎn)，從而確定定查找樹結(jié)構(gòu)構(gòu)是的順序是是自頂向下還還是自底向上上。在連接關(guān)關(guān)系中，除了了可以使用列列名外，還允允許使用列表表達(dá)式。STTART WWITH子句句為可選項(xiàng)，用用來標(biāo)識(shí)哪個(gè)個(gè)節(jié)點(diǎn)作為查查找樹型結(jié)構(gòu)構(gòu)的根節(jié)點(diǎn)。若若該子句被省省略，則表示示所有滿足查查詢條件的行行作為根節(jié)點(diǎn)點(diǎn)。Start wwithConnnect BBy子句遞歸歸查詢一般用用于一個(gè)表維維護(hù)樹形結(jié)構(gòu)構(gòu)的應(yīng)用。創(chuàng)建示例表：CREATE TABLEE TBL_TEST(ID NNUMBERR,NAME VAARCHARR2(1000 BYTEE),PID NNU

47、MBERR DDEFAULLT 0);插入測(cè)試數(shù)據(jù)：INSERT INTO TBL_TTEST(IID,NAMME,PIDD) VALLUES(1,110,00);INSERT INTO TBL_TTEST(IID,NAMME,PIDD) VALLUES(2,111,11);INSERT INTO TBL_TTEST(IID,NAMME,PIDD) VALLUES(3,220,00);INSERT INTO TBL_TTEST(IID,NAMME,PIDD) VALLUES(4,112,11);INSERT INTO TBL_TTEST(IID,NAMME,PIDD) VALLUES(5,11

48、21,2);從Root往樹樹末梢遞歸select * froom TBLL_TESTTstart wwith iid=1connectt by pprior id = pidorder bby pidd從末梢往樹ROOOT遞歸select * froom TBLL_TESTTstart wwith iid=5connectt by pprior pid = idorder bby id*CREATE TABLEE xxutts_tesst_staart(ID vvarchaar2(1000),NAME VAARCHARR2(1000);select id,naame,leevel ffrom

49、xxxuts_test_starttstart wwith iid=Aconnectt by pprior name=idorder bby idid name levvelA B 1A C 1A D 1B E 2B F 2C H 2E G 3* SELLECT ttransaactionn_id, pprimarry_quaantityy, pprimarry_uniit_of_measuure, ssourcee_doc_unit_of_meeasuree, ttransaactionn_typee, pparentt_trannsactiion_idd FRROM rccv_traansacttions WHEERE innvoicee_stattus_coode INVVOICEDD addded trrx datte rannge ass per gap2228 - rsk 007/12/07/*ANND TRUUNC (ttransaactionn_datee)BETTWEEN TRUNCC (NVLL (to_date(200880601,YYYYYMMDDD