Oracle優(yōu)化常用概念ppt課件

1、Oracle優(yōu)化常用概念解析.常見概念CBO/RBO表銜接方式執(zhí)行方案統(tǒng)計(jì)信息和柱狀圖索引分區(qū)表AWR表掃描方式hint.CBO/RBO什么是CBO/RBO，任務(wù)原理是什么應(yīng)該運(yùn)用哪種方式如何查看是哪種方式如何修正優(yōu)化器方式.什么是CBO/RBO，任務(wù)原理是什么CBO：cost-based optimizer，基于本錢的優(yōu)化器RBO：rule-based optimizer，基于規(guī)那么的優(yōu)化器CBO需求運(yùn)用統(tǒng)計(jì)信息，據(jù)此計(jì)算最正確的執(zhí)行方案；而RBO根據(jù)oracle設(shè)定好的規(guī)那么生成執(zhí)行方案。由于不能窺視到表中數(shù)據(jù)，RBO往往不能得到合理的執(zhí)行方案.應(yīng)該運(yùn)用哪種方式Oracle 10g的CBO

2、曾經(jīng)很成熟，引薦運(yùn)用。Oracle公司也將運(yùn)用CBO替代RBO，而且RBO對(duì)開發(fā)人員的要求比較高，因此，CBO是不二選擇。.如何查看是哪種方式show parameter optimizer_modeoptimizer_mode的取值包括： rule：RBO choose：有統(tǒng)計(jì)信息時(shí)是CBO，否那么是RBO， 但運(yùn)用use_hash,all_rows等提示時(shí)都是CBO all_rows,first_rows：CBO.如何修正優(yōu)化器方式alter system set optimizer_mode = all_rows scope=both; scope的值：both、spfile、memor

3、y，默許是both.表銜接方式有哪幾種銜接方式每種方式的特點(diǎn)、任務(wù)原理.有哪幾種銜接方式hash joinnested loopmerge join.每種方式的特點(diǎn)、任務(wù)原理nested loop：運(yùn)用條件：任何銜接優(yōu)點(diǎn)：當(dāng)有高選擇性索引或進(jìn)展限制性搜索時(shí)效率比較高，可以快速前往第一次的搜索結(jié)果缺陷：當(dāng)索引喪失或者查詢條件限制不夠時(shí)，效率很低；當(dāng)表的紀(jì)錄數(shù)多時(shí)，效率低。sort merge：運(yùn)用條件：主要用于不等價(jià)銜接，但不包括；相關(guān)資源：內(nèi)存、暫時(shí)空間優(yōu)點(diǎn)：當(dāng)缺乏索引或者索引條件模糊時(shí)，排序合并銜接比嵌套循環(huán)有效。缺陷：一切的表都需求排序。它為最優(yōu)化的吞吐量而設(shè)計(jì)，并且在結(jié)果沒有全部找到前不

4、前往數(shù)據(jù)。hash join：運(yùn)用條件：僅用于等價(jià)銜接；相關(guān)資源：內(nèi)存、暫時(shí)空間優(yōu)點(diǎn)：當(dāng)缺乏索引或者索引條件模糊時(shí)，哈希銜接銜接比嵌套循環(huán)有效。通常比排序合并銜接快。在數(shù)據(jù)倉庫環(huán)境下，假設(shè)表的紀(jì)錄數(shù)多，效率高。缺陷：為建立哈希表，需求大量?jī)?nèi)存。第一次的結(jié)果前往較慢。.執(zhí)行方案什么是執(zhí)行方案如何生成執(zhí)行方案如何看懂執(zhí)行方案.什么是執(zhí)行方案Oracle執(zhí)行每一條SQL語句，都必需經(jīng)過Oracle優(yōu)化器的評(píng)價(jià)，選擇最正確的執(zhí)行途徑，包括索引的運(yùn)用、表的訪問、表之間的銜接等等。.如何生成執(zhí)行方案Pl/sql中按F5，最簡(jiǎn)單快捷的方式Explain planSql trace，最準(zhǔn)確Sql/plus a

5、utotrace，包含更多信息.如何看懂執(zhí)行方案沒有捷徑，多看執(zhí)行方案，網(wǎng)上查一下詳細(xì)含義方案開個(gè)專題討論.統(tǒng)計(jì)信息和柱狀圖為什么要搜集統(tǒng)計(jì)信息統(tǒng)計(jì)信息包括哪些內(nèi)容如何查看能否存在統(tǒng)計(jì)信息如何搜集統(tǒng)計(jì)信息什么是柱狀圖為什么要做柱狀圖如何生成柱狀圖.為什么要搜集統(tǒng)計(jì)信息cbo方式下計(jì)算cost所需求的信息，統(tǒng)計(jì)信息越準(zhǔn)確，oracle生成的執(zhí)行方案越高效，所以要定期搜集統(tǒng)計(jì)信息.統(tǒng)計(jì)信息包括哪些內(nèi)容表：行數(shù)、塊數(shù)、空塊數(shù)、塊的平均剩余空間等表的列：獨(dú)一值數(shù)、null值數(shù)等索引：級(jí)數(shù)、葉子塊數(shù)、獨(dú)一值數(shù)等.如何查看能否存在統(tǒng)計(jì)信息User_tab_statisticsuser_tables也存在相

6、應(yīng)列User_ind_statisticsuser_indexes也存在相應(yīng)列User_tab_col_statistics.如何搜集統(tǒng)計(jì)信息analyze table tb_name compute|estimate statistics; 該方式用于向后兼容exec dbms_stats.gather_* oracle建議運(yùn)用的方式.什么是柱狀圖Oracle的柱狀圖和web頁面展現(xiàn)的柱狀圖屬同一個(gè)概念，只是已數(shù)據(jù)的方式存在而沒有直觀展現(xiàn)出來而已。每個(gè)柱被稱為bucket桶。柱狀圖包含了列上的數(shù)據(jù)分布，大致可了解為列上每個(gè)值的記錄數(shù)，即數(shù)據(jù)傾斜度。.為什么要做柱狀圖Oracle在選擇索引時(shí)會(huì)

7、檢查索引的效率從而確定能否運(yùn)用索引，而柱狀圖正是提供了這種信息。假設(shè)不做柱狀圖，oracle就確定不了索引的效率，會(huì)傾向于運(yùn)用索引，能夠反而不如做全表掃描來得快.如何生成柱狀圖analyze table tb_name compute|estimate statistics for all indexed columns; 針對(duì)索引列analyze table tb_name compute|estimate statistics for columns col_name size ; 針對(duì)某一列analyze table tb_name compute|estimate statistics

8、 for all columns; 針對(duì)表一切列運(yùn)用dbms_stats包提供的過程，如：dbms_stats.gather_table_stats(ownname = ,tabname = ,method_opt = for all columns);.索引創(chuàng)建索引的目的索引為什么會(huì)加快查詢有哪些類型的索引索引的限制.創(chuàng)建索引的目的從根本來講就是為了加快查詢速度Oracle出于對(duì)效率的思索，某些約束會(huì)關(guān)聯(lián)到索引，從而可以更快的檢查約束，主鍵約束就是一個(gè)典型的例子。.索引為什么會(huì)加快查詢索引的構(gòu)造使得搜索索引的鍵值很快。oracle索引的構(gòu)造類似于平衡二叉樹，但每個(gè)非葉子節(jié)點(diǎn)存儲(chǔ)了多個(gè)值，降低

9、了樹的高度。每個(gè)索引鍵都存儲(chǔ)了索引列和rowid，經(jīng)過rowid訪問表是最快的方式。Rowid相當(dāng)于指針。假設(shè)取表中少量數(shù)據(jù)，先索引搜索再經(jīng)過rowid訪問表顯然代價(jià)比全表掃描小，但假設(shè)取表中大量數(shù)據(jù)，索引搜索幾乎就成了多余的一步，因此，全表掃描更好，走索引反而效率低。這就是索引的效率問題，也是做柱狀圖的目的。.有哪些類型的索引獨(dú)一索引、非獨(dú)一索引：關(guān)鍵字unique函數(shù)索引反轉(zhuǎn)索引：關(guān)鍵字reverse用于平衡索引樹，使之不會(huì)朝一側(cè)傾斜位圖索引：關(guān)鍵字bitmap列的基數(shù)比較小時(shí)非常適宜，但dml操作的代價(jià)很高，不適宜oltp系統(tǒng).索引的限制不等、!=、=、is null操作符對(duì)索引列運(yùn)用函

10、數(shù)或者索引列存在隱身轉(zhuǎn)換索引效率不高，適宜做全表掃描錯(cuò)誤的統(tǒng)計(jì)信息數(shù)據(jù)傾斜而未做柱狀圖，或運(yùn)用了綁定變量而oracle未窺視到運(yùn)用hint強(qiáng)迫做full scan以%開頭的like操作符并行查詢小表，oracle以為不需求走索引.分區(qū)表有哪些類型的分區(qū)表索引的創(chuàng)建.有哪些類型的分區(qū)表rangehashlistcomplex.索引的創(chuàng)建global or local？建議運(yùn)用local索引.AWR什么是awr如何生成awrAwr包含哪些內(nèi)容.什么是awr全稱automatic workload repository自開任務(wù)負(fù)荷存儲(chǔ)。對(duì)于運(yùn)轉(zhuǎn)中的數(shù)據(jù)庫，oracle會(huì)定時(shí)對(duì)運(yùn)轉(zhuǎn)形狀抓取快照，包括內(nèi)

11、存的占用、磁盤的讀寫、語句執(zhí)行情況等信息。Awr保管了時(shí)間最長(zhǎng)的快照信息，默許是7天。與awr對(duì)應(yīng)的概念還有addm和ash，是時(shí)間較短的快照。.如何生成awr登陸到數(shù)據(jù)庫效力器，執(zhí)行腳本awrrptPl/sql和sql/plus中也可以。獨(dú)家抓取腳本，無須登陸效力器，運(yùn)用更方便，每license 10元，收費(fèi)公正，童叟無欺，歡迎訂購運(yùn)用.表掃描表掃描原理如何查看hwm如何降低hwm.表掃描原理從水線hwm向下掃描hwm hight water mark：oracle運(yùn)用的塊的最大高度，或者說是運(yùn)用到的最大塊號(hào).如何查看hwm查詢user_tables.blocks，需求先對(duì)表進(jìn)展分析其他一些

12、左道旁門的方法.如何降低hwmalter table tb_name shrink space; 該方法需求前提alter table tb_name enable row movement;alter table tb_name move; 該方法需求在move完后重建索引alter index ind_name rebuild;truncate.hint運(yùn)用hint的兩種方式有哪些hint.運(yùn)用hint的兩種方式-+，需求留意該方式會(huì)注釋掉其后同行的內(nèi)容/*+ */.有哪些hintuse_nl、use_hash、use_merge 表銜接方式leading 表銜接順序parallel 并行

13、查詢full、index全表掃描、索引掃描all_rows、first_rows、choose、rule優(yōu)化器方式Append、noappend直接途徑插入，慎用.語句慢怎樣辦？檢查語句書寫能否規(guī)范、業(yè)務(wù)邏輯能否可調(diào)一句話，死盯執(zhí)行方案，語句慢普通是走了錯(cuò)誤的執(zhí)行方案。以下緣由都能夠是罪魁禍?zhǔn)祝?、優(yōu)化器選擇了RBO2、統(tǒng)計(jì)信息缺失或錯(cuò)誤3、有hint進(jìn)展了錯(cuò)誤的強(qiáng)迫4、未做柱狀圖，oracle窺視不到值分布5、索引建的不合理6、隱式轉(zhuǎn)換導(dǎo)致索引運(yùn)用不上經(jīng)常會(huì)遇到7、某些時(shí)候oracle判別不了數(shù)據(jù)多少，選擇了錯(cuò)誤的表銜接方式.動(dòng)態(tài)性能視圖和數(shù)據(jù)字典視圖動(dòng)態(tài)性能視圖：v$開頭數(shù)據(jù)字典視圖：dba_*、all_*、user_*.動(dòng)態(tài)性能視圖v$session,v$session_wait,v$session_event,v$sqlv$parameter.數(shù)據(jù)字典視圖dba_data_filesuser_tablesuser_indexesuser_objectsuser_sourceuser_segments：對(duì)象實(shí)踐占用空間，非實(shí)踐運(yùn)用空間user_users,user