Oracle 性能收集資料

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

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

3、n-Line Analytical Processing）。OLTP是傳統(tǒng)的關(guān)系型數(shù)據(jù)庫的主要應(yīng)用，主要是基本的、日常的事務(wù)處理，例如銀行交易。OLAP是數(shù)據(jù)倉庫系統(tǒng)的主要應(yīng)用，支持復(fù)雜的分析操作，側(cè)重決策支持，并且提供直觀易懂的查詢結(jié)果。 OLTP與OLAP之間的比較: OLTPOLAP用戶操作人員，低層管理人員決策人員，高級(jí)管理人員功能日常操作處理分析決策DB 設(shè)計(jì)面向應(yīng)用面向主題數(shù)據(jù)最新的，細(xì)節(jié)的，二維的，分立的歷史的，聚集的，多維的，集成的存取規(guī)模讀/寫數(shù)條（甚至數(shù)百條）記錄讀上百萬（甚至上億）條記錄操作頻度非常頻繁（以秒計(jì)）比較稀松（以小時(shí)甚至以周計(jì)）工作單位嚴(yán)格的事務(wù)復(fù)雜

4、的查詢用戶數(shù)數(shù)百個(gè)-數(shù)千萬個(gè)數(shù)個(gè)-數(shù)百個(gè)DB 大小100MB-GB100GB-TB1、什么是OLTP     OLTP，也叫聯(lián)機(jī)事務(wù)處理(Online Transaction Processing)，表示事務(wù)性非常高的系統(tǒng)，一般都是高可用的在線系統(tǒng)，以小的事務(wù)以及小的查詢?yōu)橹?，評(píng)估其系統(tǒng)的時(shí)候，一般看其每秒執(zhí)行的transaction以及execute sql的數(shù)量。在這樣的系統(tǒng)中，每秒處理的transaction往往超過幾百個(gè)，或者是幾千個(gè)，select 語句的執(zhí)行量每秒幾千甚至幾萬個(gè)。典型的OLTP系統(tǒng)如電子商務(wù)系統(tǒng)，銀行，證卷等等，如美國ebay的

5、業(yè)務(wù)數(shù)據(jù)庫，就是很典型的OLTP數(shù)據(jù)庫。    OLTP系統(tǒng)最容易出現(xiàn)的瓶頸就是CPU與磁盤子系統(tǒng)。cpu則取決于邏輯讀以及內(nèi)部調(diào)用，如函數(shù)等等。一個(gè)執(zhí)行頻繁的SQL語句，如果每個(gè)語句可以減少很少的邏輯讀，也相當(dāng)于優(yōu)化了一些邏輯讀很差的大型語句。很多人不感覺不到這里的作用，覺得一個(gè)語句幾十個(gè)邏輯讀，執(zhí)行時(shí)間基本為0，就不需要優(yōu)化了，其實(shí)，只要他的執(zhí)行次數(shù)非常頻繁，而且有優(yōu)化的余地，就一定要優(yōu)化，如減少一定的邏輯讀或者降低執(zhí)行次數(shù)，都是優(yōu)化方法。    另外，一些計(jì)算性的函數(shù)，如sum，count，decode被非常頻繁的使用，也是非常

6、消耗cpu的，我遇到一個(gè)系統(tǒng)，因?yàn)橐粋€(gè)sql語句，大量的使用了sum與decode進(jìn)行行列轉(zhuǎn)換，結(jié)果這一個(gè)語句就耗費(fèi)了整個(gè)機(jī)器一半以上的CPU。    那么，在一般的OLTP系統(tǒng)中，如果不考慮我上面說的函數(shù)問題，那么，邏輯讀乘以執(zhí)行次數(shù)，決定了cpu的消耗程度，如一個(gè)語句，每秒執(zhí)行次數(shù)為500次，每個(gè)邏輯讀為15，但是，通過優(yōu)化，能讓每個(gè)語句的邏輯讀從15降到10，那么，每秒的邏輯讀就可以減少500*5=2500個(gè)，其實(shí)就是相當(dāng)于優(yōu)化了一個(gè)執(zhí)行頻率為每秒1次，每次邏輯讀為2500個(gè)的語句（注意，2500個(gè)邏輯讀，在oltp系統(tǒng)是非常差的語句）。再如，假定一個(gè)1GH

7、Z的cpu每秒能正常處理的邏輯讀是100,000個(gè)，如果是10個(gè)邏輯讀一個(gè)的語句，每秒可以處理10,000個(gè)，而1000個(gè)邏輯讀一個(gè)的語句，每秒則只能處理100個(gè)。    同以上道理，物理讀乘以執(zhí)行次數(shù)，則決定了存儲(chǔ)子系統(tǒng)的處理能力，在一個(gè)OLTP環(huán)境中，物理讀一般都是db file sequential read決定的，也就是單塊讀，一個(gè)典型的OLTP系統(tǒng)，db file sequential read應(yīng)當(dāng)基本等于磁盤子系統(tǒng)的讀的IOPS。而磁盤子系統(tǒng)的IOPS處理能力，與cache命中率以及磁盤個(gè)數(shù)有很大的關(guān)系。我的一些文章中，也分析到了這些問題，如一個(gè)15K

8、轉(zhuǎn)速的磁盤，每秒最多能處理的iops達(dá)到150個(gè)，基本就是極限了，如果cache不命中，那么100個(gè)磁盤，最多能處理的IOPS僅僅是15000個(gè)（但是，實(shí)際上，還基本達(dá)不到這個(gè)值）。    OLTP最常用的技術(shù)就是cache技術(shù)與btree索引，cache決定了很多語句不需要從磁盤子系統(tǒng)獲得數(shù)據(jù)，所以，web cache與oracle data buffer對(duì)OLTP系統(tǒng)是很重要的。另外，在索引使用方面，語句是越簡單越好，這樣執(zhí)行計(jì)劃也穩(wěn)定，而且一定要使用綁定變量，減少語句解析，盡量減少關(guān)聯(lián)。其它方面，基本不使用分區(qū)技術(shù)，MV技術(shù)，并行技術(shù)以及位圖索引，因?yàn)椴l(fā)量

9、很高，批量更新可能要盡量快速提交避免阻塞的發(fā)生。     在ebay的數(shù)據(jù)庫設(shè)計(jì)中，有一個(gè)很重要的點(diǎn)就是，數(shù)據(jù)庫只負(fù)責(zé)存放數(shù)據(jù)，業(yè)務(wù)邏輯盡量在業(yè)務(wù)層實(shí)現(xiàn)，因?yàn)閿?shù)據(jù)庫擴(kuò)展是困難的，而應(yīng)用服務(wù)器擴(kuò)展是簡單的。其實(shí)，也就是說，在高可用的OLTP環(huán)境中，數(shù)據(jù)庫使用越簡單的功能越好。2、什么是OLAP    OLAP，也叫聯(lián)機(jī)分析(Online Analytical Processing)，有的時(shí)候也叫DSS決策支持系統(tǒng)，就是我們說的數(shù)據(jù)倉庫。在這樣的系統(tǒng)中，語句的執(zhí)行量不是考核標(biāo)準(zhǔn)，因?yàn)橐粋€(gè)語句的執(zhí)行時(shí)間可能會(huì)非常長，讀取的數(shù)據(jù)也非

10、常多。所以，這樣的系統(tǒng)中，考核的標(biāo)準(zhǔn)往往決定于磁盤子系統(tǒng)的吞吐量。     磁盤子系統(tǒng)的吞吐量則直接取決于磁盤的個(gè)數(shù)，這個(gè)時(shí)候，cache基本是沒有效果的，這個(gè)時(shí)候數(shù)據(jù)庫的讀寫基本上是db file scattered read與direct path read/write。在我前面的一些文章中描述過，如果一個(gè)15K的磁盤的IO量每秒13M，那么，100個(gè)磁盤，最多能提供的吞吐量則是1300M/s（實(shí)際上，也基本達(dá)不到這個(gè)值）。如果磁盤個(gè)數(shù)足夠的話，還需要考慮采用比較大的帶寬，如4GB的光纖接口。    在OLAP系統(tǒng)中，常

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

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

13、是可以提高翻譯速度，但是，你現(xiàn)在只是翻譯一頁，你也去分配不同的人翻譯不同的行，再組合起來，這個(gè)時(shí)間，你一個(gè)人或者早就翻譯完了。    位圖索引在我前幾篇文章中有交代，如果用在oltp環(huán)境中，可能因?yàn)樽枞秶?，很容易阻塞與死鎖，但是，在olap環(huán)境中，可能會(huì)因?yàn)槠涮赜械奶匦?，提高olap的查詢速度。mv也是基本一樣，包括觸發(fā)器等等，在dml頻繁的oltp系統(tǒng)上，很容易成為瓶頸，而在olap環(huán)境上，則可能會(huì)因?yàn)槭褂们‘?dāng)而提高查詢速度。Oracle索引使用限制 和 建議什么是索引索引是建立在表的一列或多個(gè)列上的輔助對(duì)象，目的是加快訪問表中的數(shù)據(jù)； Oracle存儲(chǔ)索

14、引的數(shù)據(jù)結(jié)構(gòu)是B*樹，位圖索引也是如此，只不過是葉子節(jié)點(diǎn)不同B*數(shù)索引；索引由根節(jié)點(diǎn)、分支節(jié)點(diǎn)和葉子節(jié)點(diǎn)組成，上級(jí)索引塊包含下級(jí)索引塊的索引數(shù)據(jù)，葉節(jié)點(diǎn)包含索引數(shù)據(jù)和確定行實(shí)際位置的rowid。一、oracle的索引陷阱一個(gè)表中有幾百萬條數(shù)據(jù)，對(duì)某個(gè)字段加了索引，但是查詢時(shí)性能并沒有什么提高，這主要可能是oracle的索引限制造成的。 oracle的索引有一些索引限制，在這些索引限制發(fā)生的情況下，即使已經(jīng)加了索引，oracle還是會(huì)執(zhí)行一次全表掃描，查詢的性能不會(huì)比不加索引有所提高，反而可能由于數(shù)據(jù)庫維護(hù)索引的系統(tǒng)開銷造成性能更差。下面是一些常見的索引限制問題。 1、使用不等于操作符

15、(<>, !=)下面這種情況，即使在列dept_id有一個(gè)索引，查詢語句仍然執(zhí)行一次全表掃描 select * from dept where staff_num <> 1000; 但是開發(fā)中的確需要這樣的查詢，難道沒有解決問題的辦法了嗎？ 有！ 通過把用 or 語法替代不等號(hào)進(jìn)行查詢，就可以使用索引，以避免全表掃描：上面的語句改成下面這樣的，就可以使用索引了。 select * from dept shere staff_num < 1000 or dept_id > 1000;  2、使用 is null 或 is not null使用 is

16、null 或is nuo null也會(huì)限制索引的使用，因?yàn)閿?shù)據(jù)庫并沒有定義null值。如果被索引的列中有很多null，就不會(huì)使用這個(gè)索引（除非索引是一個(gè)位圖索引，關(guān)于位圖索引，會(huì)在以后的blog文章里做詳細(xì)解釋）。在sql語句中使用null會(huì)造成很多麻煩。 解決這個(gè)問題的辦法就是：建表時(shí)把需要索引的列定義為非空(not null) 3、使用函數(shù)如果沒有使用基于函數(shù)的索引，那么where子句中對(duì)存在索引的列使用函數(shù)時(shí)，會(huì)使優(yōu)化器忽略掉這些索引。下面的查詢就不會(huì)使用索引： select * from staff where trunc(birthdate) = '01-MAY-8

17、2' 但是把函數(shù)應(yīng)用在條件上，索引是可以生效的，把上面的語句改成下面的語句，就可以通過索引進(jìn)行查找。 select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);  4、比較不匹配的數(shù)據(jù)類型比較不匹配的數(shù)據(jù)類型也是難于發(fā)現(xiàn)的性能問題之一。下面的例子中，dept_id是一個(gè)varchar2型的字段，在這個(gè)字段上有索引，但是下面的語句會(huì)執(zhí)行全表掃描。 select * from dept where dept_id = 900198; 這是因?yàn)閛racle會(huì)自動(dòng)把where子句轉(zhuǎn)

18、換成to_number(dept_id)=900198，就是3所說的情況，這樣就限制了索引的使用。把SQL語句改為如下形式就可以使用索引 select * from dept where dept_id = '900198'  6簡單優(yōu)化 上面我們可以看到當(dāng)對(duì)索引安段加上一些選項(xiàng)時(shí)就不再使用索引了，為了在查詢時(shí)一定使用索引，可對(duì)參數(shù)optimizer_index_cost_adj進(jìn)行設(shè)置，它的取值范圍是1-10000，值越小就越使用索引，而值越大就越全盤掃描查看當(dāng)前參數(shù)據(jù)值SQL> show parameter optimizer_index_cost_adjN

19、AME TYPEVALUE- - -optimizer_index_cost_adj integer 100為了多使用索引，可減小它的值SQL> alter system set optimizer_index_cost_adj=10; 進(jìn)一步設(shè)置為了進(jìn)一步優(yōu)化，oracle建議對(duì)小表不要建索引，那什么為小表呢，oracle一次I/O就可讀寫完整個(gè)表的表為小表，查看oracle10g一次讀寫的數(shù)據(jù)量SQL> show parameter db_file_multiblock_read_countNAME TYPE VALUE- - -db_file_multiblock_

20、read_countinteger 128可見當(dāng)表小于128k時(shí)就不要建索引了二、各種索引使用場合及建議 （1）B*Tree索引。常規(guī)索引，多用于oltp系統(tǒng)，快速定位行，應(yīng)建立于高cardinality列（即列的唯一值除以行數(shù)為一個(gè)很大的值，存在很少的相同值）。 Create index indexname on tablename(columnnamecolumnname.)（2）反向索引。B*Tree的衍生產(chǎn)物，應(yīng)用于特殊場合，在ops環(huán)境加序列增加的列上建立，不適合做區(qū)域掃描。 Create index indexname on tablename(col

21、umnnamecolumnname.) reverse（3）降序索引。B*Tree的衍生產(chǎn)物，應(yīng)用于有降序排列的搜索語句中，索引中儲(chǔ)存了降序排列的索引碼，提供了快速的降序搜索。 Create index indexname on tablename(columnname DESCcolumnname.)（4）位圖索引。位圖方式管理的索引，適用于OLAP（在線分析）和DSS（決策處理）系統(tǒng)，應(yīng)建立于低cardinality列，適合集中讀取，不適合插入和修改，提供比B*Tree索引更節(jié)省的空間。 Create BITMAP index indexname on tablenam

22、e(columnnamecolumnname.)在實(shí)際應(yīng)用中，如果某個(gè)字段的值需要頻繁更新，那么就不適合在它上面創(chuàng)建位圖索引。在位圖索引中，如果你更新或插入其中一條數(shù)值為N的記錄，那么相應(yīng)表中數(shù)值為N的記錄（可能成百上千條）全部被Oracle鎖定，這就意味著其它用戶不能同時(shí)更新這些數(shù)值為N的記錄，其它用戶必須要等第一個(gè)用戶提交后，才能獲得鎖，更新或插入數(shù)據(jù)，bitmap index它主要用于決策支持系統(tǒng)或靜態(tài)數(shù)據(jù)。（5）函數(shù)索引。B*Tree的衍生產(chǎn)物，應(yīng)用于查詢語句條件列上包含函數(shù)的情況，索引中儲(chǔ)存了經(jīng)過函數(shù)計(jì)算的索引碼值?？梢栽诓恍薷膽?yīng)用程序的基礎(chǔ)上能提高查詢效率。索引創(chuàng)建策略 1.導(dǎo)入數(shù)

23、據(jù)后再創(chuàng)建索引 2.不需要為很小的表創(chuàng)建索引 3.對(duì)于取值范圍很小的字段（比如性別字段）應(yīng)當(dāng)建立位圖索引 4.限制表中的索引的數(shù)目 5.為索引設(shè)置合適的PCTFREE值 6.存儲(chǔ)索引的表空間最好單獨(dú)設(shè)定 唯一索引和不唯一索引都只是針對(duì)B樹索引而言. Oracle最多允許包含32個(gè)字段的復(fù)合索引 由此估計(jì)出一個(gè)查詢?nèi)绻褂媚硞€(gè)索引會(huì)需要讀入的數(shù)據(jù)塊塊數(shù)。需要讀入的數(shù)據(jù)塊越多，則 cost 越大，Oracle 也就越有可能不選擇使用 index 三、能用唯一索引，一定用唯一索引 能加非空，就加非空約束 一定要統(tǒng)計(jì)表的信息，索引的信息，柱狀圖的信息。 聯(lián)合索引的順序不同，影響索引的選擇，盡

24、量將值少的放在前面 只有做到以上四點(diǎn)，數(shù)據(jù)庫才會(huì)正確的選擇執(zhí)行計(jì)劃。ORACLE索引介紹與高性能SQL優(yōu)化什么是索引索引是建立在表的一列或多個(gè)列上的輔助對(duì)象，目的是加快訪問表中的數(shù)據(jù)；Oracle存儲(chǔ)索引的數(shù)據(jù)結(jié)構(gòu)是B*樹，位圖索引也是如此，只不過是葉子節(jié)點(diǎn)不同B*數(shù)索引；索引由根節(jié)點(diǎn)、分支節(jié)點(diǎn)和葉子節(jié)點(diǎn)組成，上級(jí)索引塊包含下級(jí)索引塊的索引數(shù)據(jù)，葉節(jié)點(diǎn)包含索引數(shù)據(jù)和確定行實(shí)際位置的rowid。使用索引的目的加快查詢速度減少I/O操作消除磁盤排序何時(shí)使用索引查詢返回的記錄數(shù)排序表<40%非排序表 <7%表的碎片較多（頻繁增加、刪除）索引的種類非唯一索引（最常用）唯一索引位圖

25、索引局部有前綴分區(qū)索引局部無前綴分區(qū)索引全局有前綴分區(qū)索引散列分區(qū)索引基于函數(shù)的索引管理索引的準(zhǔn)則在表中插入數(shù)據(jù)后創(chuàng)建索引。在用SQL*Loader或import工具插入或裝載數(shù)據(jù)后，建立索引比較有效；索引正確的表和列。經(jīng)常檢索排序大表中40%或非排序表7%的行，建議建索引；。為了改善多表關(guān)聯(lián)，索引列用于聯(lián)結(jié)；。列中的值相對(duì)比較唯一；。取值范圍（大：B*樹索引，?。何粓D索引）；。Date型列一般適合基于函數(shù)的索引；。列中有許多空值，不適合建立索引為性能而安排索引列。經(jīng)常一起使用多個(gè)字段檢索記錄，組合索引比單索引更有效；。把最常用的列放在最前面，例：dx_groupid_serv_id(grou

26、pid,serv_id)，在where條件中使用groupid或groupid,serv_id，查詢將使用索引，若僅用到serv_id字段，則索引無效；。合并/拆分不必要的索引。限制每個(gè)表索引的數(shù)量。一個(gè)表可以有幾百個(gè)索引（你會(huì)這樣做嗎？），但是對(duì)于頻繁插入和更新表，索引越多系統(tǒng)CPU，I/O負(fù)擔(dān)就越重；。建議每張表不超過5個(gè)索引。刪除不再需要的索引。索引無效，集中表現(xiàn)在該使用基于函數(shù)的索引或位圖索引，而使用了B*樹索引；。應(yīng)用中的查詢不使用索引；。重建索引之前必須先刪除索引，若用alter index  rebuild重建索引，則不必刪除索引。索引數(shù)據(jù)塊空間使用

27、。創(chuàng)建索引時(shí)指定表空間，特別是在建立主鍵時(shí)，應(yīng)明確指定表空間；。合理設(shè)定pctfress，注意：不能給索引指定pctused；。估計(jì)索引的大小和合理地設(shè)置存儲(chǔ)參數(shù)，默認(rèn)為表空間大小，或initial與next設(shè)置成一樣大。考慮并行創(chuàng)建索引。對(duì)大表可以采用并行創(chuàng)建索引，在并行創(chuàng)建索引時(shí)，存儲(chǔ)參數(shù)被每個(gè)查詢服務(wù)器進(jìn)程分別使用，例如：initial為1M，并行度為8，則創(chuàng)建索引期間至少要消耗8M空間；考慮用nologging創(chuàng)建索引。對(duì)大表創(chuàng)建索引可以使用nologging來減少重做日志；。節(jié)省重做日志文件的空間；?？s短創(chuàng)建索引的時(shí)間；。改善了并行創(chuàng)建大索引時(shí)的性能。 怎樣建立最佳索引明確地創(chuàng)建索引

28、create index index_name on table_name(field_name)tablespace tablespace_namepctfree 5initrans 2maxtrans 255storage(minextents 1maxextents 16382pctincrease 0);創(chuàng)建基于函數(shù)的索引。常用與UPPER、LOWER、TO_CHAR(date)等函數(shù)分類上，例：create index idx_func on&#

29、160;emp (UPPER(ename) tablespace tablespace_name;創(chuàng)建位圖索引。對(duì)基數(shù)較小，且基數(shù)相對(duì)穩(wěn)定的列建立索引時(shí)，首先應(yīng)該考慮位圖索引，例：create bitmap index idx_bitm on class (classno) tablespace tablespace_name;明確地創(chuàng)建唯一索引?？梢杂胏reate unique index語句來創(chuàng)建唯一索引，例：create unique in

30、dex dept_unique_idx on dept(dept_no) tablespace idx_1;創(chuàng)建與約束相關(guān)的索引?？梢杂胾sing index字句，為與unique和primary key約束相關(guān)的索引，例如：alter table table_nameadd constraint PK_primary_keyname primary key (field_name)using index tablespace 

31、;tablespace_name；如何創(chuàng)建局部分區(qū)索引。基礎(chǔ)表必須是分區(qū)表；。分區(qū)數(shù)量與基礎(chǔ)表相同；。每個(gè)索引分區(qū)的子分區(qū)數(shù)量與相應(yīng)的基礎(chǔ)表分區(qū)相同；。基礎(chǔ)表的子分區(qū)中的行的索引項(xiàng)，被存儲(chǔ)在該索引的相應(yīng)的子分區(qū)中,例如:Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID)Pctfree 5Tablespace TBS_AK01_IDXStorage (MaxExtents 32768PctIncrease 0FreeLists 1FreeL

32、ist Groups 1)local/如何創(chuàng)建范圍分區(qū)的全局索引。基礎(chǔ)表可以是全局表和分區(qū)表。create index idx_start_date on tg_cdr01(start_date)global partition by range(start_date)(partition p01_idx vlaues less than (0106)partition p01_idx vlaues less tha

33、n (0111)partition p01_idx vlaues less than (0401 )/重建現(xiàn)存的索引重建現(xiàn)存的索引的當(dāng)前時(shí)刻不會(huì)影響查詢；重建索引可以刪除額外的數(shù)據(jù)塊；提高索引查詢效率；alter index idx_name rebuild nologging;對(duì)于分區(qū)索引：alter index idx_name rebuild partition partiton_name nologging;要?jiǎng)h除索引

34、的原因。不再需要的索引；。索引沒有針對(duì)其相關(guān)的表所發(fā)布的查詢提供所期望的性能改善；。應(yīng)用沒有用該索引來查詢數(shù)據(jù)；。該索引無效，必須在重建之前刪除該索引；。該索引已經(jīng)變的太碎了，必須在重建之前刪除該索引；。語句：drop index idx_name; drop index idx_name drop partition partition_name;建立索引的代價(jià)基礎(chǔ)表維護(hù)時(shí)，系統(tǒng)要同時(shí)維護(hù)索引，不合理的索引將嚴(yán)重影響系統(tǒng)資源，主要表現(xiàn)在CPU和I/O上；插入、更新、刪除數(shù)據(jù)產(chǎn)生大量db file seq

35、uential read鎖等待；SQL優(yōu)化器簡介基于規(guī)則的優(yōu)化器?？偸鞘褂盟饕？偸菑尿?qū)動(dòng)表開始（from子句最右邊的表）。只有在不可避免的情況下，才使用全表掃描。任何索引都可以基于成本的優(yōu)化器。需要表、索引的統(tǒng)計(jì)資料Analyze table customer compute statistics;Analyze table customer estimate statistics sample 5000 rows;。表中設(shè)置并行度、表分區(qū)優(yōu)化器模式rule模式?？偤雎訡B

36、O和統(tǒng)計(jì)信息而基于規(guī)則choose模式。Oracle根據(jù)情況選擇rule or first_rows or all_rowsfirst_rows 模式?；诔杀荆宰羁斓乃俣确祷赜涗?，會(huì)造成總體查詢速度的下降或消耗更多的資源，傾向索引掃描，適合OLTP系統(tǒng)all_rows模式?；诔杀?，確?？傮w查詢時(shí)間最短，傾向并行全表掃描例如：Select last_name from customer order by last_name;用first_rows時(shí)，迅速返回記錄，但I(xiàn)/O量大，用a

37、ll_rows時(shí)，返回記錄慢，但使用資源少。調(diào)整SQL表訪問全表掃描。返回記錄：未排序表>40%，排序表>7%，建議采用并行機(jī)制來提高訪問速度，DDS；索引訪問。最常用的方法，包括索引唯一掃描和索引范圍掃描，OLTP；快速完全索引掃描。訪問索引中所有數(shù)據(jù)塊，結(jié)果相當(dāng)于全表掃描，可以用索引掃描代替全表掃描，例如：Select serv_id,count(* ) from tg_cdr01 group by serv_id;評(píng)估全表掃描的合法性如何實(shí)現(xiàn)并行掃描。永久并行化（不推薦）alter table&#

38、160;customer parallel degree 8;。單個(gè)查詢并行化select /*+ full(emp) parallel(emp,8)*/ * from emp;分區(qū)表效果明顯優(yōu)化SQL語句排序排序的操作：。order by 子句。group by 子句。select distinct子句。創(chuàng)建索引時(shí)。union或minus。排序合并連接如何避免排序。添加索引。在索引中使用distinct子句。避免排序合并連接使用提示進(jìn)行調(diào)整使用提示的原則

39、。語法：/*+ hint */。使用表別名:select /*+ index(e dept_idx)*/ * from emp e。檢驗(yàn)提示常用的提示。rule。all_rows。first_rows。use_nl。use_hash。use_merge。index。index_asc。no_index。index_desc（常用于使用max內(nèi)置函數(shù)）。index_combine(強(qiáng)制使用位圖索引)。index_ffs（索引快速完全掃描）。use_concat(將查詢中所有or條件使用union 

40、all)。parallel。noparallel。full。ordered（基于成本）調(diào)整表連接表連接的類型。等連接where 條件中用等式連接；。外部連接（左、右連接）在where條件子句的等式謂詞放置一個(gè)(+)來實(shí)現(xiàn)，例如：select a.ename,m from emp a,bonus b where a.ename=b.ename(+);該語句返回所有emp表的記錄；。自連接Select a.value total, B.value hard, (A.va

41、lue - b.value) soft ,Round(b.value/a.value)*100,1) percFrom v$sysstat a,v$sysstat bWhere a.statistic# = 179and B.statistic# = 180;反連接反連接常用于not in or not exists中，是指在查詢中找到的任何記錄都不包含在結(jié)果集中的子查詢；不建議使用not in 

42、or not exists;。半連接查詢中使用exists，含義：即使在子查詢中返回多條重復(fù)的記錄，外部查詢也只返回一條記錄。嵌套循環(huán)連接。被連接表中存在索引的情況下使用；。使用use_nl。hash連接。Hash連接將驅(qū)動(dòng)表加載在內(nèi)存中，并使用hash技術(shù)連接第二個(gè)表，提高等連接速度。適合于大表和小表連接；。使用use_hash。排序合并連接。排序合并連接不使用索引。使用原則：連接表子段中不存在可用索引；查詢返回兩個(gè)表中大部分的數(shù)據(jù)快；CBO認(rèn)為全表掃描比索引掃描執(zhí)行的更快。使用use_merge使用臨時(shí)/中間表多個(gè)大表關(guān)聯(lián)時(shí)，可以分別把滿足條件的結(jié)果集存放到中間表，然后

43、用中間表關(guān)聯(lián)；SQL子查詢的調(diào)整關(guān)聯(lián)與非關(guān)聯(lián)子查詢。關(guān)聯(lián)：子查詢的內(nèi)部引用的是外部表，每行執(zhí)行一次；。非關(guān)聯(lián)：子查詢只執(zhí)行一次，存放在內(nèi)存中。調(diào)整not in 和not exists語句?？梢允褂猛獠窟B接優(yōu)化not in子句，例如：select ename from emp where dept_no not in(select dept_no from dept where dept_name =Math);改為：selec

44、t ename from emp,deptwhere emp.dept_no=dept.dept_noand dept.dept_name is null;使用索引調(diào)整SQLOracle 為什么不使用索引。檢查被索引的列或組合索引的首列是否出現(xiàn)在PL/SQL語句的WHERE子句中，這是“執(zhí)行計(jì)劃”能用到相關(guān)索引的必要條件?？床捎昧四姆N類型的連接方式。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Joi

45、n（NL）。在兩張表連接，且內(nèi)表的目標(biāo)列上建有索引時(shí)，只有Nested Loop才能有效地利用到該索引。SMJ即使相關(guān)列上建有索引，最多只能因索引的存在，避免數(shù)據(jù)排序過程。HJ由于須做HASH運(yùn)算，索引的存在對(duì)數(shù)據(jù)查詢速度幾乎沒有影響?？催B接順序是否允許使用相關(guān)索引。假設(shè)表emp的deptno列上有索引，表dept的列deptno上無索引，WHERE語句有emp.deptno=dept.deptno條件。在做NL連接時(shí)，emp做為外表，先被訪問，由于連接機(jī)制原因，外表的數(shù)據(jù)訪問方式是全表掃描，emp.deptno上的索引顯然是用不上，最多在其上做索引全掃描或索引快速全掃描。是否用到系

46、統(tǒng)數(shù)據(jù)字典表或視圖。由于系統(tǒng)數(shù)據(jù)字典表都未被分析過，可能導(dǎo)致極差的“執(zhí)行計(jì)劃”。但是不要擅自對(duì)數(shù)據(jù)字典表做分析，否則可能導(dǎo)致死鎖，或系統(tǒng)性能下降。索引列是否函數(shù)的參數(shù)。如是，索引在查詢時(shí)用不上。是否存在潛在的數(shù)據(jù)類型轉(zhuǎn)換。如將字符型數(shù)據(jù)與數(shù)值型數(shù)據(jù)比較，ORACLE會(huì)自動(dòng)將字符型用to_number()函數(shù)進(jìn)行轉(zhuǎn)換，從而導(dǎo)致上一種現(xiàn)象的發(fā)生。是否為表和相關(guān)的索引搜集足夠的統(tǒng)計(jì)數(shù)據(jù)。對(duì)數(shù)據(jù)經(jīng)常有增、刪、改的表最好定期對(duì)表和索引進(jìn)行分析，可用SQL語句“analyze table xxxx compute statistics for a

47、ll indexes;”。ORACLE掌握了充分反映實(shí)際的統(tǒng)計(jì)數(shù)據(jù)，才有可能做出正確的選擇。索引列的選擇性不高。 我們假設(shè)典型情況，有表emp，共有一百萬行數(shù)據(jù)，但其中的emp.deptno列，數(shù)據(jù)只有4種不同的值，如10、20、30、40。雖然emp數(shù)據(jù)行有很多，ORACLE缺省認(rèn)定表中列的值是在所有數(shù)據(jù)行均勻分布的，也就是說每種deptno值各有25萬數(shù)據(jù)行與之對(duì)應(yīng)。假設(shè)SQL搜索條件DEPTNO=10，利用deptno列上的索引進(jìn)行數(shù)據(jù)搜索效率，往往不比全表掃描的高。索引列值是否可為空（NULL）。如果索引列值可以是空值，在SQL語句中那些要返回NULL值的操作，將不

48、會(huì)用到索引，如COUNT（*），而是用全表掃描。這是因?yàn)樗饕写鎯?chǔ)值不能為全空?？词欠裼杏玫讲⑿胁樵儯≒QO）。并行查詢將不會(huì)用到索引。如果從以上幾個(gè)方面都查不出原因的話，我們只好用采用在語句中加hint的方式強(qiáng)制ORACLE使用最優(yōu)的“執(zhí)行計(jì)劃”。 hint采用注釋的方式，有行注釋和段注釋兩種方式。 如我們想要用到A表的IND_COL1索引的話，可采用以下方式： “SELECT /*+ INDEX（A IND_COL1）*/ * FROM A WHERE COL1 =

49、60;XXX;"如何屏蔽索引語句的執(zhí)行計(jì)劃中有不良索引時(shí)，可以人為地屏蔽該索引，方法：。數(shù)值型：在索引字段上加0，例如select * from emp where emp_no+0 = v_emp_no;。字符型：在索引字段上加，例如select * from tg_cdr01 where msisdn|=v_msisdn;SQLPLUS中的set小節(jié)在寫腳本的時(shí)候，往往需要對(duì)輸出的格式進(jìn)行規(guī)范，我們常常用set語句在設(shè)置一些需要的格式規(guī)范。下面，列舉了幾個(gè)常用的

50、set ，和他們的應(yīng)用。SQL> set echo on-設(shè)置運(yùn)行命令是是否顯示語句SQL> set feedback on-設(shè)置顯示“已選擇XX行”SQL> set colsep | -設(shè)置列與列之間的分割符號(hào)SQL> set pagesize 10-設(shè)置每一頁的行數(shù)SQL> SET SERVEROUTPUT ON-設(shè)置允許顯示輸出類似dbms_outputSQL> set heading on-設(shè)置顯示列名SQL> set timing on-設(shè)置顯示“已用時(shí)間：XXXX”SQL> set time on-設(shè)置顯示當(dāng)前時(shí)間SQL> se

51、t autotrace on-設(shè)置允許對(duì)執(zhí)行的sql進(jìn)行分析00:38:28 SQL> set echo on00:38:37 SQL> E:echo.sql00:38:41 SQL> select status from v$datafile where rownum<6;STATUS-SYSTEMONLINEONLINEONLINEONLINE已選擇5行。已用時(shí)間: 00: 00: 01.0800:38:43 SQL> set echo off00:38:58 SQL> E:echo.sqlSTATUS-SYSTEMONLINEONLINEONLINEO

52、NLINE已選擇5行。已用時(shí)間: 00: 00: 01.0900:39:08 SQL>SQL> set feedback onSQL> set colsep |SQL> set pagesize 10SQL> set serveroutput onSQL> set heading onSQL> set timing onSQL> set time on00:54:11 SQL> SELECT FILE#, STATUS, ENABLED FROM V$DATAFILE;FILE#|STATUS |ENABLED-|-|-1|SYSTEM |

53、READ WRITE2|ONLINE |READ WRITE3|ONLINE |READ WRITE4|ONLINE |READ WRITE5|ONLINE |READ WRITE6|ONLINE |READ WRITE7|ONLINE |READ WRITEFILE#|STATUS |ENABLED-|-|-9|ONLINE |READ WRITE10|ONLINE |READ WRITE11|ONLINE |READ WRITE12|ONLINE |READ WRITE11 rows selected.Elapsed: 00:00:01.0700:54:34 SQL> DECLARE

54、00:55:00 2 BEGIN00:55:00 3 DBMS_OUTPUT.PUT_LINE('=This is dbms_output.put_line!=');00:55:00 4 END;00:55:02 5 /=This is dbms_output.put_line!=PL/SQL procedure successfully completed.Elapsed: 00:00:00.0200:55:05 SQL>SQL> set autotrace onSQL> SELECT E.LAST_NAME, D.DEPARTMENT_NAME, D.LO

55、CATION_ID2 FROM HR.EMPLOYEES E3 JOIN HR.DEPARTMENTS D4 USING (DEPARTMENT_ID);LAST_NAME DEPARTMENT_NAME LOCATION_ID- - -King Executive 1700Kochhar Executive 1700De Haan Executive 1700Hunold IT 1400Ernst IT 1400106 rows selected.Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=106 Bytes

56、=3180)1 0 HASH JOIN (Cost=5 Card=106 Bytes=3180)2 1 TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=2 Card=27 Bytes=513)3 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=107 Bytes=1177)Statistics-7 recursive calls0 db block gets23 consistent gets12 physical reads0 redo size2801 bytes se

57、nt via SQL*Net to client580 bytes received via SQL*Net from client9 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)106 rows processedSQL>Start with connect by prior在SELECT命令中使用CONNECT BY和藹START WITH子句可以查詢表中的樹型結(jié)構(gòu)關(guān)系。其命令格式如下：SELECT。CONNECT BY PRIOR列名1=列名2|列名1=PRIOR裂名2START WITH；其中：CO

58、NNECT BY子句說明每行數(shù)據(jù)將是按層次順序檢索，并規(guī)定將表中的數(shù)據(jù)連入樹型結(jié)構(gòu)的關(guān)系中。PRIORY運(yùn)算符必須放置在連接關(guān)系的兩列中某一個(gè)的前面。對(duì)于節(jié)點(diǎn)間的父子關(guān)系，PRIOR運(yùn)算符在一側(cè)表示父節(jié)點(diǎn)，在另一側(cè)表示子節(jié)點(diǎn)，從而確定查找樹結(jié)構(gòu)是的順序是自頂向下還是自底向上。在連接關(guān)系中，除了可以使用列名外，還允許使用列表達(dá)式。START WITH子句為可選項(xiàng)，用來標(biāo)識(shí)哪個(gè)節(jié)點(diǎn)作為查找樹型結(jié)構(gòu)的根節(jié)點(diǎn)。若該子句被省略，則表示所有滿足查詢條件的行作為根節(jié)點(diǎn)。Start with.Connect By子句遞歸查詢一般用于一個(gè)表維護(hù)樹形結(jié)構(gòu)的應(yīng)用。創(chuàng)建示例表：CREATE TABLE TBL_TES

59、T(ID    NUMBER,NAME VARCHAR2(100 BYTE),PID   NUMBER                                  DEFAULT 0);插入測試數(shù)據(jù)：INSERT

60、INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','

61、;1');INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');從Root往樹末梢遞歸select * from TBL_TESTstart with id=1connect by prior id = pidorder by pid從末梢往樹ROOT遞歸select * from TBL_TESTstart with id=5connect by prior pid = idorder by id*CREATE TABLE xxuts_test_start(ID 

62、60;  varchar2(100),NAME VARCHAR2(100);select id,name,level from xxuts_test_startstart with id='A'connect by prior name=idorder by idid     name   levelA   B        1A    C    &#

63、160;   1A    D        1B    E         2B    F         2C    H        2E    G         3*