ORACLE執(zhí)行計劃和SQL調(diào)優(yōu)

ORACLE調(diào)優(yōu)吳志忠2009.09面向?qū)ο蠖緮?shù)據(jù)庫常識有ORACLE開發(fā)使用經(jīng)驗樂于接受性能優(yōu)化探討內(nèi)容安排第一局部：背景知識第二局部：SQL調(diào)優(yōu)第三局部：工具介紹第四局部ROWID高級應(yīng)用第五局部附錄調(diào)優(yōu)方面1商業(yè)邏輯2優(yōu)化數(shù)據(jù)設(shè)計3優(yōu)化應(yīng)用程序設(shè)計4優(yōu)化數(shù)據(jù)庫邏輯設(shè)計5優(yōu)化數(shù)據(jù)庫操作6

優(yōu)化訪問路徑7優(yōu)化內(nèi)存分配8優(yōu)化I/O和物理結(jié)構(gòu)9優(yōu)化資源爭用10優(yōu)化所采用的平臺*越靠前越重要第一局部背景知識SQL處理流程優(yōu)化器緩沖池執(zhí)行方案變量綁定索引表連接SQL執(zhí)行的步驟解析：平安性檢查，語法檢查；創(chuàng)立：評估多個執(zhí)行方案，并選擇一個最優(yōu)的執(zhí)行方案；執(zhí)行：捆綁變量，執(zhí)行已經(jīng)創(chuàng)立的執(zhí)行方案；獲?。韩@取結(jié)果集，進行轉(zhuǎn)換，排序等；SQL處理流程·第1步:CreateaCursor創(chuàng)立游標(biāo)·第2步:ParsetheStatement分析語句·第5步:BindAnyVariables綁定變量·第7步:RuntheStatement運行語句·第9步:ClosetheCursor關(guān)閉游標(biāo)如果使用了并行功能，還會包含下面這個階段：·第6步:ParallelizetheStatement并行執(zhí)行語句如果是查詢語句，那么需要以下幾個額外的步驟·第3步:DescribeResultsofaQuery描述查詢的結(jié)果集·第4步:DefineOutputofaQuery定義查詢的輸出數(shù)據(jù)·第8步:FetchRowsofaQuery取查詢出來的行

SQL優(yōu)化器(Optimizer)概念：是一個為所有的sql語句創(chuàng)立執(zhí)行方案的工具。目的：生成最快的，消耗資源最少的執(zhí)行方案。兩種優(yōu)化器a.RBO,Rule-BasedOptimizerb.CBO,Cost-BasedOptimizerRBO特性總是使用索引〔不識別位圖索引或基于函數(shù)的索引〕總是從驅(qū)動表開始只有在不可防止的情況下，才使用全表掃描索引選擇的隨機特性*從ORACLE10G開始，開始廢棄RBO優(yōu)化器。這句話并不是指在ORACLE10G中不能使用RBO，而是從ORACLE10G開始開始，不再為RBO的BUG提供修補效勞CBO特性前提條件：存在表和索引的統(tǒng)計資料；使用analyzetable和analyzeindex命令從表或索引中收集統(tǒng)計資料〔表的記錄平均長度，記錄數(shù)等〕；如果沒有現(xiàn)存的統(tǒng)計資料，將在sql運行時收集資料，會大大降低性能；影響CBO執(zhí)行方案本錢評估的初始化參數(shù)較多〔optimizer_search_limit,optimizer_max_permutations,optimizer_index_caching,hash_area_size,hash_join_enable,hash_multiblock_io_count,star_transformation_enable,optimizer_index_cost_adj等〕SQL優(yōu)化器模式實例級通過對init.ora中OPTIMIZER_MODE參數(shù),2會話級ALTERSESSIONSETOPTIMIZER_MODE=;3語句別hintCHOOSE不是優(yōu)化器,決定用什么優(yōu)化器的參數(shù):如果表有分析資料,便用CBO,否那么用RBO在缺省情況下,ORACLE采用CHOOSE優(yōu)化器模式CHOOSERBOCBOFIRST_ROWS_n(OLTP系統(tǒng))FIRST_ROWSALL_ROWS(DSS

系統(tǒng))表分析為了使用CBO,必須經(jīng)常運行analyze命令,以增加數(shù)據(jù)庫中的對象統(tǒng)計信息多表連接時只要有一個表分析過，就用CBO7i:ANALYZE和DBMS_UTILITY從815開始有DBMS_STATUS從10g以后可以自動分析表,有個系統(tǒng)job(GATHER_STATS_JOB)。SQL優(yōu)化器處理體系結(jié)構(gòu)解析程序優(yōu)化程序行源產(chǎn)生程序SQL執(zhí)行SQL優(yōu)化器處理流程圖-1SQL優(yōu)化器處理流程圖-2SQL優(yōu)化器處理流程圖-3緩沖池PGAProgramGlobalArea是為每個連接到Oracledatabase的用戶進程保存的內(nèi)存SGASystemGlobalArea是OracleInstance的根本組成局部，在實例啟動時分配;系統(tǒng)全局域SGA主要由三局部構(gòu)成：共享池、數(shù)據(jù)緩沖區(qū)、日志緩沖區(qū)。

緩沖池-SGA共享池(Sharedpool)

SQL語句緩沖(LibraryCache)也叫庫緩沖區(qū)

數(shù)據(jù)字典緩沖區(qū)(DataDictionaryCache)塊緩沖區(qū)高速緩存(DatabaseBufferCache)重做日志緩沖區(qū)(Redologbuffer)Java程序緩沖區(qū)(JavaPool)大池(LargePool)執(zhí)行方案概念

就是對一個查詢?nèi)蝿?wù)，做出一份怎樣去完成任務(wù)的詳細方案.工具

變量綁定為什么DBA要求我們變量綁定預(yù)編譯概念怎樣處理(?,?Procedure等)變量綁定必須滿足的條件字符級的比較兩個SQL語句中必須使用相同的名字的綁定變量兩個語句所指的對象必須完全相同用戶

對象名

如何訪問Jack

sal_limit

privatesynonym

Work_city

publicsynonym

Plant_detail

publicsynonymJill

sal_limit

privatesynonym

Work_city

publicsynonym

Plant_detail

tableownerSQL能否共享原因selectmax(sal_cap)fromsal_limit;不能每個用戶都有一個privatesynonym-sal_limit,它們是不同的對象selectcount(*0fromwork_citywheresdesclike'NEW%';能兩個用戶訪問相同的對象publicsynonym-work_cityselecta.sdesc,b.locationfromwork_citya,plant_detailbwherea.city_id=b.city_id不能用戶jack通過privatesynonym訪問plant_detail而jill是表的所有者,對象不同.Statement和preparedStatementPreparedStatement對象的開銷比Statement大，對于一次性操作并不會帶來額外的好處preparedstatement是預(yù)編譯

preparedstatement支持批處理SQL注入，平安,強制類型轉(zhuǎn)換不同數(shù)據(jù)庫不同IBATIS預(yù)編譯證據(jù)packagecom.ibatis.sqlmap.engine.execution;-->SqlExecutorpublicintexecuteUpdate(RequestScoperequest,Connectionconn,Stringsql,Object[]parameters)throwsSQLException{ErrorContexterrorContext=request.getErrorContext();errorContext.setActivity("executingupdate");errorContext.setObjectId(sql);PreparedStatementps=null;setupResultObjectFactory(request);introws=0;try{errorContext.setMoreInfo("ChecktheSQLStatement(preparationfailed).");ps=prepareStatement(request.getSession(),conn,sql);setStatementTimeout(request.getStatement(),ps);errorContext.setMoreInfo("Checktheparameters(setparametersfailed).");request.getParameterMap().setParameters(request,ps,parameters);errorContext.setMoreInfo("Checkthestatement(updatefailed).");ps.execute();rows=ps.getUpdateCount();}finally{closeStatement(request.getSession(),ps);}returnrows}何時Oracle使用綁定變量性能更差索引訪問路徑

ROWID索引及優(yōu)缺點其它概念索引分類索引掃描方式索引的創(chuàng)立Oracle訪問數(shù)據(jù)庫的存取方式全表掃描〔FullTableScans〕通過ROWID的表存取〔TableAccessbyROWID〕索引掃描〔IndexScan〕ORACLE的訪問路徑排序通過rowid單行訪問通過cluster連接的單行訪問通過散列鍵或主鍵的單行訪問通過主鍵字的單行訪問cluster連接散列簇鍵索引簇鍵復(fù)合鍵單列索引在索引列上的有界搜索在索引列上的無界搜索排序〔orderby〕,合并〔union〕連接索引列的最大(max)到最小(min)通過索引列排序全表掃描了解SQL語句這些特性后，就應(yīng)該少用orderby等之類的語句Rowid的概念rowid是一個偽列，既然是偽列，那么這個列就不是用戶定義，而是系統(tǒng)自己給加上的。對每個表都有一個rowid的偽列，但是表中并不物理存儲ROWID列的值。不過你可以像使用其它列那樣使用它，但是不能刪除改列，也不能對該列的值進行修改、插入。一旦一行數(shù)據(jù)插入數(shù)據(jù)庫，那么rowid在該行的生命周期內(nèi)是唯一的，即即使該行產(chǎn)生行遷移，行的rowid也不會改變。為什么使用ROWIDrowid對訪問一個表中的給定的行提供了最快的訪問方法，通過ROWID可以直接定位到相應(yīng)的數(shù)據(jù)塊上，然后將其讀到內(nèi)存。我們創(chuàng)立一個索引時，該索引不但存儲索引列的值，而且也存儲索引值所對應(yīng)的行的ROWID，這樣我們通過索引快速找到相應(yīng)行的ROWID后，通過該ROWID，就可以迅速將數(shù)據(jù)查詢出來。這也就是我們使用索引查詢時，速度比較快的原因索引的物理表現(xiàn)形式其實就是一張物理表ROWID，索引列因為如上原因盡量索引表空間與數(shù)據(jù)表空間分開存放,減少資源競爭索引優(yōu)點可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)立索引的最主要的原因。通過創(chuàng)立唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能索引缺點創(chuàng)立索引和維護索引要消耗時間，這種時間隨著數(shù)據(jù)量的增加而增加索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大當(dāng)對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度容易濫建索引要求專業(yè)知識怎樣優(yōu)化索引很難把握*相對于大幅提高速度的優(yōu)點而言，做這些犧牲都是很劃算的索引經(jīng)驗了解了優(yōu)缺點，到底怎么衡量這個度一般建議少于5個。并不是所有的表都是走索引是最正確選擇。如小表常用的根底信息表，通常緩存在內(nèi)存中1.在應(yīng)用程序端可以緩存數(shù)據(jù)2.ORACLE效勞器端亦可 ALTERTABLEempCACHE;altertableempstorage(buffer_pool

keep)這樣就被緩存到keep池中了RecursiveSQL概念有時為了執(zhí)行用戶發(fā)出的一個sql語句，Oracle必須執(zhí)行一些額外的語句，我們將這些額外的語句稱之為‘recursivecalls’或‘recursiveSQLstatements’。如當(dāng)一個DDL語句發(fā)出后，ORACLE總是隱含的發(fā)出一些recursiveSQL語句，來修改數(shù)據(jù)字典信息，以便用戶可以成功的執(zhí)行該DDL語句。當(dāng)需要的數(shù)據(jù)字典信息沒有在共享內(nèi)存中時，經(jīng)常會發(fā)生Recursivecalls，這些Recursivecalls會將數(shù)據(jù)字典信息從硬盤讀入內(nèi)存中。用戶不比關(guān)心這些recursiveSQL語句的執(zhí)行情況，在需要的時候，ORACLE會自動的在內(nèi)部執(zhí)行這些語句。當(dāng)然DML語句也都可能引起recursiveSQL。簡單的說，我們可以將觸發(fā)器視為recursiveSQL。RowSourceandPredicateRowSource(行源)：用在查詢中，由上一操作返回的符合條件的行的集合，即可以是表的全部行數(shù)據(jù)的集合；也可以是表的局部行數(shù)據(jù)的集合；也可以為對上2個rowsource進行連接操作(如join連接)后得到的行數(shù)據(jù)集合。Predicate(謂詞)：一個查詢中的WHERE限制條件DrivingTableDrivingTable(驅(qū)動表)：該表又稱為外層表(OUTERTABLE)。這個概念用于嵌套與HASH連接中。如果該rowsource返回較多的行數(shù)據(jù)，那么對所有的后續(xù)操作有負面影響。注意此處雖然翻譯為驅(qū)動表，但實際上翻譯為驅(qū)動行源(drivingrowsource)更為確切。一般說來，是應(yīng)用查詢的限制條件后，返回較少行源的表作為驅(qū)動表，所以如果一個大表在WHERE條件有有限制條件(如等值限制)，那么該大表作為驅(qū)動表也是適宜的，所以并不是只有較小的表可以作為驅(qū)動表，正確說法應(yīng)該為應(yīng)用查詢的限制條件后，返回較少行源的表作為驅(qū)動表。在執(zhí)行方案中，應(yīng)該為靠上的那個rowsource。索引分類邏輯上：

Singlecolumn單列索引

Concatenated多列索引(連接索引/復(fù)合索引/組合索引)

Unique唯一索引

NonUnique非唯一索引

Function-based函數(shù)索引

Domain域索引

物理上：

Partitioned分區(qū)索引

NonPartitioned非分區(qū)索引

B-tree：

Normal正常型B樹

ReverKey反轉(zhuǎn)型B樹

Bitmap位圖索引組合索引(concatenatedindex)由多個列構(gòu)成的索引，如createindexidx_emponemp(col1,col2,col3,……)，那么我們稱idx_emp索引為組合索引。在組合索引中有一個重要的概念：引導(dǎo)列(leadingcolumn)，在上面的例子中，col1列為引導(dǎo)列。當(dāng)我們進行查詢時可以使用”wherecol1=?”，也可以使用”wherecol1=?andcol2=?”，這樣的限制條件都會使用索引，但是”wherecol2=?”查詢就不會使用該索引。所以限制條件中包含先導(dǎo)列時，該限制條件才會使用該組合索引。39樹最底層的塊稱為葉子節(jié)點，其中分別包括索引鍵以及rowid，葉子節(jié)點上的塊為分支塊，用于在結(jié)構(gòu)中實現(xiàn)導(dǎo)航。B*樹索引B-tree及反向B-tree適合于大量的增、刪、改〔OLTP〕；

不能用包含OR操作符的查詢；

適合高基數(shù)的列〔唯一值多〕

典型的樹狀結(jié)構(gòu)；

每個結(jié)點都是數(shù)據(jù)塊；

大多都是物理上一層、兩層或三層不定，邏輯上三層；

葉子塊數(shù)據(jù)是排序的，從左向右遞增；

在分支塊和根塊中放的是索引的范圍；反向B-tree：適用于OPS或RAC環(huán)境；

反轉(zhuǎn)了索引碼中每列的字節(jié)，降低索引葉塊的爭用Bitmap適合于決策支持系統(tǒng)；(OLAP),數(shù)據(jù)倉庫非常適合OR操作符的查詢加快查詢速度是，節(jié)省存儲空間，位圖采用了壓縮技術(shù)實現(xiàn)磁盤空間縮減基數(shù)比較少的時候才能建位圖索引BITMAP每一個BIT對應(yīng)著一個ROWID，它的值是1還是0，如果是1，表示著BIT對應(yīng)的ROWID有值B-treevsBitmap默認情況下大多使用Btree索引,Btree用在OLTP，加快查詢速度;位圖索引用在OLAP(聯(lián)機數(shù)據(jù)分析)方面，也就是數(shù)據(jù)倉庫方面,目的是在加快查詢速度是，節(jié)省存儲空間.Btree索引都要消耗比較大的存儲空間，位圖采用了壓縮技術(shù)實現(xiàn)磁盤空間縮減Btree用在高基數(shù)(即列的數(shù)據(jù)相異度大)，位圖用在低基數(shù)列.位圖索引的根本原理是在索引中使用位圖而不是列值。通常在事實表和維表的鍵之間有很低的集的勢(cardinality)，使用位圖索引，存儲更為有效，與B*Tree索引比較起來，只需要更少的存儲空間，這樣每次讀取可以讀到更多的記錄.與B*Tree索引相比,位圖索引將比較連接和聚集都變成了位算術(shù)運算,大大減少了運行時間，從而得到性能上的極大的提升。合理的使用位圖索引如果要使用位圖索引，初始化參數(shù)STAR_TRANSFORMATION_ENABLED應(yīng)該設(shè)置為TRUE.優(yōu)化模式應(yīng)該是CBO。對于數(shù)據(jù)倉庫的環(huán)境中，總是應(yīng)該考慮使用CBO(COST-BASEDOPTIMIZER)。位圖索引應(yīng)該建立在每一個事實表的外鍵列上。(這只是一個一般的規(guī)那么.)索引掃描方式索引唯一掃描〔indexuniquescan〕索引范圍掃描〔indexrangescan〕索引全掃描〔indexfullscan〕索引快速掃描〔indexfastfullscan〕索引跳躍式掃描(indexskipscan)索引唯一掃描通過唯一索引查找一個數(shù)值經(jīng)常返回單個ROWID。如果該唯一索引有多個列組成(即組合索引)，那么至少要有組合索引的引導(dǎo)列參與到該查詢中，如創(chuàng)立一個索引：createindexidx_testonemp(ename,deptno,loc)。那么selectenamefromempwhereename=‘JACK’anddeptno=‘DEV’語句可以使用該索引。如果該語句只返回一行，那么存取方法稱為索引唯一掃描。而selectenamefromempwheredeptno=‘DEV’語句那么不會使用該索引，因為where子句種沒有引導(dǎo)列。如果存在UNIQUE或PRIMARYKEY約束〔它保證了語句只存取單行〕的話，Oracle經(jīng)常實現(xiàn)唯一性掃描。

使用唯一性約束的例子：

SQL>explainplanfor

selectempno,enamefromempwhereempno=10;

QueryPlan

------------------------------------

SELECTSTATEMENT[CHOOSE]Cost=1

TABLEACCESSBYROWIDEMP[ANALYZED]

INDEXUNIQUESCANEMP_I1

索引范圍掃描使用一個索引存取多行數(shù)據(jù)，同上面一樣，如果索引是組合索引，如(1)所示，而且selectenamefromempwhereename=‘JACK’anddeptno=‘DEV’語句返回多行數(shù)據(jù)，雖然該語句還是使用該組合索引進行查詢，可此時的存取方法稱為索引范圍掃描。在唯一索引上使用索引范圍掃描的典型情況下是在謂詞(where限制條件)中使用了范圍操作符(如>、<、<>、>=、<=、between)

使用索引范圍掃描的例子：

SQL>explainplanforselectempno,enamefromemp

whereempno>7876orderbyempno;

QueryPlan

--------------------------------------------------------------------------------

SELECTSTATEMENT[CHOOSE]Cost=1

TABLEACCESSBYROWIDEMP[ANALYZED]

INDEXRANGESCANEMP_I1[ANALYZED]

在非唯一索引上，謂詞col=5可能返回多行數(shù)據(jù)，所以在非唯一索引上都使用索引范圍掃描。

使用indexrangscan的3種情況：

(a)在唯一索引列上使用了range操作符(><<>>=<=between)

(b)在組合索引上，只使用局部列進行查詢，導(dǎo)致查詢出多行

(c)對非唯一索引列上進行的任何查詢。

索引全掃描與全表掃描對應(yīng)，也有相應(yīng)的全索引掃描。在某些情況下，可能進行全索引掃描而不是范圍掃描，需要注意的是全索引掃描只在CBO模式下才有效。CBO根據(jù)統(tǒng)計數(shù)值得知進行全索引掃描比進行全表掃描更有效時，才進行全索引掃描，而且此時查詢出的數(shù)據(jù)都必須從索引中可以直接得到。

全索引掃描的例子：

IndexBE_IXisaconcatenatedindexonbig_emp(empno,ename)

SQL>explainplanforselectempno,enamefrombig_emporderbyempno,ename;

QueryPlan

--------------------------------------------------------------------------------

SELECTSTATEMENT[CHOOSE]Cost=26

INDEXFULLSCANBE_IX[ANALYZED]

索引快速掃描掃描索引中的所有的數(shù)據(jù)塊，與indexfullscan很類似，但是一個顯著的區(qū)別就是它不對查詢出的數(shù)據(jù)進行排序，即數(shù)據(jù)不是以排序順序被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用并行讀入，以便獲得最大吞吐量與縮短執(zhí)行時間。索引快速掃描的例子：

BE_IX索引是一個多列索引：big_emp(empno,ename)

SQL>explainplanforselectempno,enamefrombig_emp;

QueryPlan

------------------------------------------

SELECTSTATEMENT[CHOOSE]Cost=1

INDEXFASTFULLSCANBE_IX[ANALYZED]

只選擇多列索引的第2列：

SQL>explainplanforselectenamefrombig_emp;

QueryPlan

------------------------------------------

SELECTSTATEMENT[CHOOSE]Cost=1

INDEXFASTFULLSCANBE_IX[ANALYZED]

索引跳躍式掃描索引跳躍式掃描〔indexskipscan〕是Oracle9i用來提高性能的新特性，對于使用復(fù)合索引的數(shù)據(jù)庫應(yīng)用程序意義尤為重大在一次跳躍式掃描中，每個復(fù)合索引前導(dǎo)字段的獨特值〔DISTINCTVALUE〕只會被搜索一次，ORACLE9i會在復(fù)合索引前導(dǎo)字段每個獨特值區(qū)間結(jié)合WHERE子句中的其它復(fù)合索引字段搜索符合條件的目標(biāo)記錄，這種做法的結(jié)果導(dǎo)致了在索引中的跳躍式掃描。索引跳躍式掃描主要有兩個優(yōu)點：1、以前版本中的表掃描〔TABLESCAN〕可能會轉(zhuǎn)變?yōu)樗饕龗呙?，提高了某些查詢的?zhí)行效率2、應(yīng)用程序使用較少的索引就能到達提高效能的目的，并且既節(jié)省存儲空間，又能提高DML和維護操作的效率。

索引跳躍式掃描范例createindexsex_emp_idonemp(sex,emp_id);在Oracle9i版本之前，當(dāng)SQL查詢中包含性別和emp_id時，或者查詢指定性別行的時候才可以使用這一索引.Selectemp_idfromempwhereemp_id=123;

Oracle9i的索引跳躍式掃描執(zhí)行規(guī)那么允許使用連接索引，即使SQL查詢中不指定性別。這一特性使得無需在emp_id行中提供第二個索引。Oracle成認索引跳躍式掃描沒有直接索引查詢速度快，但可以這樣說，相比于整個表掃描〔tablescan〕，索引跳躍式掃描的速度要快得多.SELECTSTATEMENTOptimizer=CHOOSE(Cost=6Card=1Bytes=5)0SORT(AGGREGATE)1INDEX(SKIPSCAN)OF'SEX_EMP_ID'(NON-UNIQUE)在內(nèi)部里，Oracle生成了兩個查詢，并連接結(jié)果的ROWID表selectemp_namefromemp_wheresex='F'andemp_id=123UNIONselectemp_namefromemp_wheresex='M'andemp_id=123;

索引跳躍式掃描很多情況是發(fā)生在第一列索引有，第二列跳躍的情況居多。如createindexidx_emponemp(group_id,sex,emp_id);Selectemp_idfromempwheregroup_id=‘guanggao’andemp_id=123;對于高順序鍵〔highorderkey〕中的獨特值數(shù)目，Oracle的索引跳躍式掃描性能將會降低。如果主列有50個值，Oracle要發(fā)出50條查詢才能找回結(jié)果。索引跳躍式掃描只適用于硬盤空間和存儲空間相當(dāng)緊缺的情況。*前面提到可能會用到啊，這個得看執(zhí)行方案。和環(huán)境及數(shù)據(jù)量有很大關(guān)系。所以關(guān)于索引的應(yīng)用方面一切以執(zhí)行方案為準(zhǔn)，不能想當(dāng)然。

索引可選擇性(selectivity)比較一以下中唯一鍵的數(shù)量和表中的行數(shù)，就可以判斷該列的可選擇性。如果該列的”唯一鍵的數(shù)量/表中的行數(shù)”的比值越接近1，那么該列的可選擇性越高，該列就越適合創(chuàng)立索引，同樣索引的可選擇性也越高。在可選擇性高的列上進行查詢時，返回的數(shù)據(jù)就較少，比較適合使用索引查詢。創(chuàng)立索引準(zhǔn)那么在經(jīng)常需要搜索的列上，可以加快搜索的速度在作為主鍵的列上，強制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)在經(jīng)常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；在經(jīng)常需要根據(jù)范圍進行搜索的列上創(chuàng)立索引，因為索引已經(jīng)排序，其指定的范圍是連續(xù)的在經(jīng)常需要排序的列上創(chuàng)立索引，因為索引已經(jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時間在經(jīng)常使用在WHERE子句中的列上面創(chuàng)立索引，加快條件的判斷速度表連接表連接方法嵌套循環(huán)連接〔nestedloopjoin〕：一個小的內(nèi)部表和一個外部表。比較內(nèi)部表的每一條記錄和外部表的每一條記錄，返回滿足條件的記錄；散列連接〔hashjoin〕：為較小的表在RAM創(chuàng)立散列表〔可以用來從較大的那個表讀取記錄〕；排序合并連接〔sortmergejoin〕：使用連接字段將兩個記錄集排序然后合并；星型連接〔starjoin〕：幾個小型表〔將組成虛擬表〕和一個大型事實表，然后進行嵌套循環(huán)連接；嵌套循環(huán)連接NestedLoop1、Oracle首先選擇一張表作為連接的驅(qū)動表，這張表也稱為外部表〔OuterTable〕。由驅(qū)動表進行驅(qū)動連接的表或數(shù)據(jù)源稱為內(nèi)部表〔InnerTable〕。提取驅(qū)動表中符合條件的記錄，與被驅(qū)動表的連接列進行關(guān)聯(lián)查詢符合條件的記錄.2、NESTEDLOOP<OuterLoop><InnerLoop>嵌套循環(huán)連接適用于查詢的選擇性強、約束性高并且僅返回小局部記錄的結(jié)果集。常見的誤區(qū)是驅(qū)動表要選擇小表，其實這是不對的.驅(qū)動表是由過濾條件限制返回記錄最少的那張表，而不是根據(jù)表的大小來選擇的.嵌套循環(huán)連接返回前幾行的記錄是非?？斓模@是因為使用了嵌套循環(huán)后，不需要等到全部循環(huán)結(jié)束再返回結(jié)果集，而是不斷地將查詢出來的結(jié)果集返回。嵌套循環(huán)連接(續(xù))在這個查詢中，優(yōu)化器選擇emp作為驅(qū)動表，根據(jù)唯一性索引PK_EMP快速返回符合條件empno為7900的記錄，然后再與被驅(qū)動表dept的deptno關(guān)聯(lián)查詢相應(yīng)的dname并最終返回結(jié)果集。由于dept表上面的deptno有唯一索引PK_DEPT，故查詢能夠快速地定位deptno對應(yīng)dname為SALES的記錄并返回。排序合并連接SortMerge排序合并連接的方法非常簡單。在排序合并連接中是沒有驅(qū)動表的概念的，兩個互相連接的表按連接列的值先排序，排序完后形成的結(jié)果集再互相進行合并連接提取符合條件的記錄。相比嵌套循環(huán)連接，排序合并連接比較適用于返回大數(shù)據(jù)量的結(jié)果排序合并連接在數(shù)據(jù)表預(yù)先排序好的情況下效率是非常高的，也比較適用于非等值連接的情況，比方>、>=、<=等情況下的連接〔哈希連接只適用于等值連接〕。排序合并連接(續(xù))上述查詢首先按dept、emp兩張表的deptno先排序，然后排序好的結(jié)果集再進行合并連接返回最終的記錄。哈希連接Hashjoin由于Oracle中排序操作的開銷是非常消耗資源的，當(dāng)結(jié)果集很大連接的性能很差，于是Oracle在7.3之后時排序合并推出了新的連接方式——哈希連接。1、構(gòu)建階段：優(yōu)化器首先選擇一張小表做為驅(qū)動表，運用哈希函數(shù)對連接列進行計算產(chǎn)生一張哈希表。通常這個步驟是在內(nèi)存〔hash_area_size〕里面進行的，因此運算很快。探測階段：優(yōu)化器對被驅(qū)動表的連接列運用同樣的哈希函數(shù)計算得到的結(jié)果與前面形成的哈希表進行探測返回符合條件的記錄。這個階段中如果被驅(qū)動表的連接列的值沒有與驅(qū)動表連接列的值相等的話，那么這些記錄將會被丟棄而不進行探測。哈希連接比較適用于返回大數(shù)據(jù)量結(jié)果集的連接。使用哈希連接必須是在CBO模式下，參數(shù)hash_join_enabled設(shè)置為true，且只適用于等值連接從Oracle9i開始，哈希連接由于其良好的性能漸漸取代了原來的排序合并連接。哈希連接(續(xù))在這個查詢中優(yōu)化器首先選擇dept這張表為驅(qū)動表，對列deptno運算哈希函數(shù)構(gòu)建一張哈希表，然后再對被驅(qū)動表emp的deptno列運算同樣的哈希函數(shù)計算得到的結(jié)果進行探測，最終連接得出符合條件的記錄。星型連接STARJOIN應(yīng)用于數(shù)據(jù)倉庫，數(shù)據(jù)集龐大，很方便做多維分析一般是一個事實表，很大，有很多數(shù)據(jù)

如一個銷售情況表

他下面有幾個可以用于多維分析的字段

如地域、時間、銷售代表都可以是這個事實表的維

而這幾個維又用專門的表來存放并與大表建立關(guān)系

就構(gòu)成了一個簡單的星型圖常建立位圖索引表連接類型等連接：標(biāo)準(zhǔn)連接；…froma,bwherea.f1=b.f1；oracle提供nestedloop、hashjoin、sortmerge三種連接方式；外部連接:通過在where子句的等式謂詞展覽館放置一個〔＋〕來實現(xiàn)；…froma,bwherea.f1=b.f1(+)，將包括b表中不匹配的字段；自連接：一個表與自己連接的情況；…fromempa,empbwherea.f1-b.f1=10…；經(jīng)常調(diào)用nestedloop連接；表連接類型〔續(xù)〕反連接：指使用包含notin或notexists子句的子查詢進行的連接；默認使用嵌套循環(huán)算法；半連接：指在子查詢中使用in或exists子句時進行的操作；不同表連接方法的相對速度星型連接嵌套循環(huán)連接散列連接排序合并連接連接速度連接表中記錄的數(shù)目少多慢快第二局部SQL調(diào)優(yōu)SQL調(diào)整的目標(biāo)可能導(dǎo)致全表掃描的操作調(diào)整原那么分而治之SQL調(diào)整的目標(biāo)去掉不必要的大表全表掃描；緩存小表全表掃描；盡量使用主機變量代替直接量，減少SQL語句的解析時間；優(yōu)化索引的使用；優(yōu)化表連接方法；優(yōu)化子查詢；常見可能導(dǎo)致全表掃描的操作使用null條件的查詢：wherexxxisnull;對沒有索引的字段查詢；帶有l(wèi)ike條件的查詢：wherexxxlike‘%x’；帶有notequals條件的查詢：<>,!=,notin等內(nèi)置函數(shù)使索引無效：substr()，to_char()等；列隱式變量轉(zhuǎn)換何時使用索引查詢返回的記錄數(shù)排序表<40%非排序表

<7%表的碎片較多〔頻繁增加、刪除〕

優(yōu)化程序操作優(yōu)化程序自動簡化ＳＱＬ語句中的某些常用結(jié)構(gòu)，如果結(jié)果簡化執(zhí)行的話，這些語句變得非常簡單，如2000/10簡化為200。也可能變復(fù)雜，如將帶OR的運算語句轉(zhuǎn)換為兩個復(fù)合的子查詢。對于前者可以隨時進行，但后者那么取決于where子句的列上是否有索引以及選擇哪種優(yōu)化方法。此外，還有一些其它的轉(zhuǎn)換，包括：化簡算術(shù)表達式將IN算子轉(zhuǎn)化為一系列的OR條件將一個BETWEEN…AND轉(zhuǎn)換為一比照較表達式將OR轉(zhuǎn)換為復(fù)合查詢視圖的定義合并到條件語句中將一個復(fù)雜語句轉(zhuǎn)換成連接條件語句調(diào)整原那么調(diào)整SQL不建議用IN用集合表代替與其它表關(guān)聯(lián)，參見tstr2numlist,str2varlist曾測過:fain(‘a(chǎn)’,’b’)fa=‘a(chǎn)’orfa=‘b’編譯成這樣執(zhí)行用EXISTS替代IN用notexists或者〔外聯(lián)結(jié)+判斷為空〕來代替NOTIN用表連接替換EXISTS用EXISTS替換DISTINCT其它相同功能的操作運算代替<>;a<>0改為a>0ora<0

a<>’’改為a>’’a>0或a>’’代替aisnotnullA>=2代替A>1Like‘a(chǎn)aa%’可以利用索引like‘%aaa’肯定不走索引，其它方案全文搜索或利用三方引擎技術(shù)如Lucene調(diào)整SQL〔續(xù)〕UNIONALL代替UNION查詢表順序的影響;RBOWHERE后面的條件順序影響RBO

禁止使用連接列,除非有相應(yīng)該索引禁止對列使用函數(shù)和表達式,除非有相應(yīng)該索引顯視指定列類型，如fa=’99’禁止用fa=99來對應(yīng)varchar字段分頁時先將當(dāng)前頁的記錄rowid取出來，然后再關(guān)聯(lián)取其它信息禁止使用select*，因?qū)?進行解析用TRUNCATE替代DELETE(刪除全表，必要時用)在事物完整時盡量多提交事務(wù)。可以釋放相當(dāng)多的資源Where子句替換HAVING(后面有例)減少對表的查詢(后面有例)使用表的別名

計算記錄條數(shù)和一般的觀點相反，count〔*〕比count〔1〕稍快，當(dāng)然如果可以通過索引檢索，對索引列的計數(shù)仍舊是最快的。例如COUNT〔EMPNO〕

這個問題在各大論壇中，都有過相當(dāng)熱烈的討論，我也并不十分準(zhǔn)確，通過實際的測試，上述三種方法并沒有顯著的性能差異按DBA要求做。count〔*〕

用Where子句替換HAVING子句防止使用HAVING子句，HAVING只會在檢索出所有記錄之后才對結(jié)果集進行過濾。這個處理需要排序，總計等操作。如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷。

例如：低效：

SELECTREGION，AVG(LOG_SIZE)

FROMLOCATION

GROUPBYREGION

HAVINGREGIONREGION!=‘SYDNEY’

ANDREGION!=‘PERTH’高效

SELECTREGION，AVG(LOG_SIZE)

FROMLOCATION

WHEREREGIONREGION!=‘SYDNEY’

ANDREGION!=‘PERTH’

GROUPBYREGIONHAVING中的條件一般用于對一些集合函數(shù)的比較，如COUNT〔〕等等。除此而外，一般的條件應(yīng)該寫在WHERE子句中

減少對表的查詢在含有子查詢的SQL語句中，要特別注意減少對表的查詢。

例如：低效

SELECTTAB_NAME

FROMTABLES

WHERETAB_NAME=(SELECTTAB_NAME

FROMTAB_COLUMNS

WHEREVERSION=604)

AND

DB_VER=(SELECTDB_VER

FROMTAB_COLUMNS

WHEREVERSION=604)

高效

SELECTTAB_NAME

FROMTABLES

WHERE

(TAB_NAME,DB_VER)

=(SELECTTAB_NAME,DB_VER)

FROMTAB_COLUMNS

WHEREVERSION=604)

減少對表的查詢(2)Update多個Column例子：低效：

UPDATEEMP

SETEMP_CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES),

SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)

WHEREEMP_DEPT=‘0020’;高效：

UPDATEEMP

SET(EMP_CAT,SAL_RANGE)

=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)

FROMEMP_CATEGORIES)

WHEREEMP_DEPT=‘0020’;

使用表的別名當(dāng)在SQL語句中連接多個表時，請使用表的別名并把別名前綴于每個Column上。這樣一來，就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。用EXISTS替代IN在許多基于根底表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯(lián)接。在這種情況下，使用EXISTS〔或NOTEXISTS〕通常將提高查詢的效率。

低效：SELECT*FROMEMP(根底表)WHEREEMPNO>0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=‘MELB’)

高效：SELECT*FROMEMP(根底表)WHEREEMPNO>0ANDEXISTS(SELECT‘X’FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB’)用NOTEXISTS替代NOTIN在子查詢中，NOTIN子句將執(zhí)行一個內(nèi)部的排序和合并。無論在哪種情況下，NOTIN都是最低效的〔因為它對子查詢中的表執(zhí)行了一個全表遍歷〕。

為了防止使用NOTIN，我們可以把它改寫成外連接〔OuterJoins〕或NOTEXISTS.例如：SELECT…FROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT=’A’);為了提高效率。改寫為：〔方法一：高效〕SELECT….FROMEMPA,DEPTBWHEREA.DEPT_NO=B.DEPT(+)ANDB.DEPT_NOISNULLANDB.DEPT_CAT(+)=‘A’〔方法二：最高效〕SELECT….FROMEMPEWHERENOTEXISTS(SELECT‘X’

FROMDEPTD

WHERED.DEPT_NO=E.DEPT_NO

ANDDEPT_CAT=‘A’);用表連接替換EXISTS通常來說，采用表連接的方式比EXISTS更有效率

SELECTENAME

FROMEMPE

WHEREEXISTS(SELECT‘X’

FROMDEPT

WHEREDEPT_NO=E.DEPT_NO

ANDDEPT_CAT=‘A’);〔更高效〕

SELECTENAME

FROMDEPTD,EMPE

WHEREE.DEPT_NO=D.DEPT_NO

ANDDEPT_CAT=‘A’;用EXISTS替換DISTINCT當(dāng)提交一個包含一對多表信息〔比方部門表和雇員表〕的查詢時，防止在SELECT子句中使用DISTINCT.一般可以考慮用EXIST替換例如：低效：

SELECTDISTINCTDEPT_NO,DEPT_NAME

FROMDEPTD,EMPE

WHERED.DEPT_NO=E.DEPT_NO高效：

SELECTDEPT_NO,DEPT_NAME

FROMDEPTD

WHEREEXISTS(SELECT‘X’

FROMEMPE

WHEREE.DEPT_NO=D.DEPT_NO);EXISTS使查詢更為迅速，因為RDBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回結(jié)果。基于RBO的調(diào)整原那么驅(qū)動表的設(shè)置：在RBO中，驅(qū)動表是from子句的最后一個表；驅(qū)動表應(yīng)該是返回記錄最少的那個表；Where子句設(shè)置：限制性最強的布爾表達式放在最底層；添加基于本錢的提示，來獲得更快的執(zhí)行方案；基于CBO的調(diào)整原那么優(yōu)化器自動執(zhí)行表連接調(diào)整原那么RBO只能調(diào)用nestedloop和mergesort連接；hashjoin和star連接只能在CBO中得到；盡量不要使用notin反連接子查詢，把它替換成標(biāo)準(zhǔn)等連接，用外連接和wherecolumnisnull子句刪除多余的記錄；或者盡量替換成notexists子查詢，因為它將調(diào)用相關(guān)聯(lián)的子查詢；半連接子查詢可以重新書寫成標(biāo)準(zhǔn)等連接，用selectdistinct子句刪除重復(fù)的記錄；表連接調(diào)整原那么〔續(xù)〕如果驅(qū)動表較小，可以完全裝入hash_area_size內(nèi)存中，使用散列連接〔hashjoin〕速度比嵌套循環(huán)連接〔nestedloop〕快；在兩個表非常大的情況下，經(jīng)常使用嵌套循環(huán)連接〔nestedloop〕；生成大型結(jié)果集的查詢、不使用where子句的大表連接或表中無可用索引的查詢，經(jīng)常使用排序合并連接〔srotmerge)；對于多個小的維表和一個大的事實表的情況下〔數(shù)據(jù)倉庫〕，經(jīng)常使用星型連接(star)；SQL子查詢調(diào)整子查詢類型標(biāo)準(zhǔn)子查詢：in和exists；反連接子查詢：notin和notexists；關(guān)聯(lián)子查詢：指在子查詢內(nèi)部引用外部數(shù)據(jù)表；…fromtable1awhere…(select…fromtable2bWherea.f1=b.f1…)；對于外部數(shù)據(jù)集的每一條記錄，都將重新執(zhí)行一次內(nèi)部子查詢；非關(guān)聯(lián)子查詢：指在子查詢內(nèi)部不會引用外部的數(shù)據(jù)表；…fromtable1awhere…(select…fromtable2b…)；內(nèi)部子查詢只執(zhí)行一次；子查詢調(diào)整原那么只要可能的話，盡可能的防止使用子查詢，而用標(biāo)準(zhǔn)的連接操作來代替，這樣可以使用提示來更改執(zhí)行方案；先考慮子查詢的合法性，再考慮進行改寫；使用一個關(guān)聯(lián)子查詢時，in與exists子句的子查詢的執(zhí)行方案根本相同；在外部查詢返回相對較少的記錄時，關(guān)聯(lián)子查詢比非關(guān)聯(lián)子查詢執(zhí)行得更快；子查詢調(diào)整原那么〔續(xù)〕在內(nèi)部子查詢只有少量的記錄時，非關(guān)聯(lián)子查詢比關(guān)聯(lián)子查詢執(zhí)行得更快；關(guān)聯(lián)子查詢使用in子句是多余的；而非關(guān)聯(lián)子查詢使用exists子句是不恰當(dāng)?shù)?。使用in子句的非關(guān)聯(lián)子查詢可以轉(zhuǎn)換為標(biāo)準(zhǔn)連接操作以及使用selectdistinct來刪除重復(fù)的記錄；使用exists子句的關(guān)聯(lián)子查詢可以轉(zhuǎn)換為標(biāo)準(zhǔn)連接，但子查詢最好只能返回一個記錄；子查詢調(diào)整原那么〔續(xù)〕非關(guān)聯(lián)子查詢使用notexists子句是沒有意義的；使用notin子句的非關(guān)聯(lián)子查詢可以轉(zhuǎn)sqlminus子句，性能相對會高一些；使用notin子句的關(guān)聯(lián)子查詢，可以使用帶有selectdistinct子句的外部連接操作改寫；各種子查詢技術(shù)總結(jié)標(biāo)準(zhǔn)子查詢反連接子查詢inexistsNotinNotexists關(guān)聯(lián)子查詢多余的自動轉(zhuǎn)換為嵌套的循環(huán)連接可以重寫為selectdistinct外部連接可以重寫為selectdistinct外部連接非關(guān)聯(lián)子查詢自動轉(zhuǎn)換為嵌套的循環(huán)連接不適合可以重寫為minus操作符的嵌套循環(huán)連接不適合例子：使用in子句的非關(guān)聯(lián)子查詢可以使用標(biāo)準(zhǔn)連接操作以及使用selectdistinct來刪除重復(fù)的記錄；原sql語句：SelectenameFromempWhereempnoin(selectempnofrombad_creditwherebad_credit_date>sysdate-365);改寫后：Selectdistinct/*+rule*/enameFromemp,bad_creditWherea.empno=b.empnoandbad_credit_date>sysdate-365;使用提示進行調(diào)整提示簡介歷史：第一次引入是在oracle7，用來彌補CBO的缺陷，oracle8i中工作得較好；目的：用來更改SQL語句的執(zhí)行方案；格式：select(update,delete)/*+hints*/…或select(update,delete)--+hint…使用提示遵循的原那么注意檢查語法：select/*+hint*/…，/*和+之間不能有空格，必須緊跟在select之后，否那么無效使用表別名：如果指定了表別名，就不能使用表名稱；不能使用模式名稱：如果指定了模式所有者，那么提示將被忽略；檢驗提示：如果指定不可用的訪問路徑，如：first_rows優(yōu)化器模式與orderby子句不兼容，那么提示將被忽略；警告：如果該提示語句書寫不正確，那么Oracle就忽略掉該語句。使提示無效的條件Cluster，hash：與非簇表一起使用；Merge_aj,push_subq,Hash_aj：不存在子查詢；Index：指定的索引不存在；Index_combine：不存在位圖索引；Parellel：調(diào)用的不是全表掃描方案；Star：事實表中存在不恰當(dāng)索引；Use_concat：在where子句中不存在多個索引；Use_nl：表中不存在索引；常用索引提示。rule

。all_rows

。first_rows

。use_nl

。use_hash

。use_merge

。index

。index_asc

。no_index

。index_desc〔常用于使用max內(nèi)置函數(shù)〕

。index_combine(強制使用位圖索引)

。index_ffs〔索引快速完全掃描〕

。use_concat(將查詢中所有or條件使用union

all)

。parallel

。noparallel

。full

。ordered〔基于本錢〕

提示之一：優(yōu)化器提示Rule：使oracle為查詢應(yīng)用基于規(guī)那么的優(yōu)化模式。在疑心CBO使用了非優(yōu)化的執(zhí)行方案時，使用rule提示；它將忽略表和索引的統(tǒng)計資料；/*+rule*/All_rows：基于本錢的優(yōu)化方法。目的是提供最正確的吞吐量和最小的資源消耗。傾向于全表掃描，不適用于OLTP系統(tǒng)；依賴于表和索引的統(tǒng)計資料；/*+all_rows*/First_rows:基于本錢的優(yōu)化方法。目的是提供最快的反映時間。依賴于表和索引的統(tǒng)計資料；/*+first_rows*/提示之二：表連接提示Use_hash：對指定的表執(zhí)行一個散列連接；如果有一個表較小，通?？煊谇短籽h(huán)連接；在兩個表非常大的情況下，散列連接經(jīng)常與并行查詢連接結(jié)合使用；select/*+use_hash(a,b)parallel(a,4)parallel(b,4)*/;Use_merge：強制執(zhí)行一個排序合并操作；對表執(zhí)行全表掃描；通常與并行查詢結(jié)合使用；最適用于生成大型結(jié)果集的查詢、不使用where子句的大表連接或表中無可用索引的查詢；select/*+use_merge(a,b)parallel(a,4)parallel(b,4)*/表連接提示〔續(xù)〕Use_nl：強制對目標(biāo)表執(zhí)行嵌套循環(huán)連接；對包含兩個大表的連接通常最快；可以不用更改from子句表的順序來更改驅(qū)動表〔使用CBO時，from子句的第一個表〕；是CBO的默認行為，比較少用；/*+use_nl(a)*/Star：強制使用星型查詢方案；查詢中至少存在三個表〔一個事實表和幾個維表〕，而且事實表〔大表〕存在恰當(dāng)?shù)乃饕?i可以使用位圖索引〕；/*+star*/提示之三：反連接提示反連接是在SQL語句中包含notin或notexist子句時執(zhí)行的操作；如果子查詢返回的任何一條記錄包含空值，那么該查詢將不會返回記錄；應(yīng)盡量防止使用。Hash_aj，Merge_aj：在notin子查詢的字段中不存在空值的時候，根據(jù)連接的類型，考慮使用這兩個提示之一，可以在很大程度上提高notin子查詢的性能；/*+hash_aj*/提示之四：索引提示Index：優(yōu)化器將使用指定的索引；如果沒有指定索引，優(yōu)化器將使用表中最正確的索引；/*+index(table,index)*/Index_join：要求優(yōu)化器使用索引連接作為訪問路徑；And_equal：如果表擁有非唯一的單獨字段索引，而且期望使用多個索引效勞于該查詢，那么使用該提示將合并這些索引；至少兩個索引名，但不能超過五個；/*+and_equal(table,index1,index2,…)*/索引提示〔續(xù)〕Index_asc：要求在范圍掃描中使用升序索引；優(yōu)化器的默認行為，不常用；No_index：強制優(yōu)化器忽略索引得存在。一般用在并行全表掃描性能高于索引范圍掃描性能的情況下；等同于full提示；Index_desc：要求在范圍掃描中使用降序索引；如在max〔〕計算字段的最大值的時候使用；/*+index_desc(table,index)*/索引提示〔續(xù)〕Index_combine：強制使用位圖索引作為表的訪問路徑，對兩個位圖索引執(zhí)行ROWID交集操作。如果沒有指定索引作參數(shù)，優(yōu)化器將自動選擇最正確的位圖索引；/*+table(bitmap1,bitmap2)*/Index_ffs：強制使用快速完全索引掃描；如果大表中不存在被查詢字段的高層索引主鍵，比方需要選擇復(fù)合索引中第二個字段的值時，那么快速完全索引掃描總是比全表掃描速度更快；/*+index_ffs(table,comindex)*/索引提示〔續(xù)〕Use_concat：要求為查詢中所有or條件使用unionall執(zhí)行方案；一般用在where子句中存在大量的or條件；提示之五：并行提示Parallel：要求表查詢以并行模式執(zhí)行；一般用在多個cpu的效勞器上，與full提示一起使用；/*+full(table)parallel(table,8)*/Noparallel：不希望對全表掃描使用并行機制，如對小表執(zhí)行的全表掃描時，使用該提示；提示之六：表訪問提示Full：要求避開索引，調(diào)用全表掃描；讀取表中大量的數(shù)據(jù)塊時；與parallel一起使用；Hash：選擇散列掃描來訪問指定的簇表；/*+hash*/Cluster：選擇簇掃描來訪問指定的簇表；/*+cluster*/Nocache：指定為keep池分配的表數(shù)據(jù)塊放置在default池的中點；很少使用；表訪問提示〔續(xù)〕Ordered：要求表按照from子句指定的順序進行連接；對連接多于4個表的查詢非常有用，可以節(jié)省sql解析的時間；/*+ordered*/Ordered_predicates：用來指定where子句中布爾條件評估的順序；Push_subq：要求查詢數(shù)據(jù)塊中的所有子查詢在執(zhí)行方案中盡可能早的被執(zhí)行；用在子查詢相對來說不很昂貴，并且在很大程度上降低返回到記錄數(shù)；如果子查詢使用的是排序合并連接或調(diào)用遠程表，提示將不起作用；調(diào)整索引索引調(diào)整使用索引來消除一些不必要〔如表中沒有索引，排序合并連接〕的排序操作；通過增加索引來防止不合法的全表掃描〔一般來說，在有序表中查詢返回的記錄數(shù)少于表記錄數(shù)的40％，或者在無序表中返回的記錄數(shù)少于表中記錄數(shù)的7％〕；對于數(shù)據(jù)列的唯一值較少的字段，建立位圖索引，以提高性能；創(chuàng)立基于函數(shù)的索引來防止全表掃描；索引調(diào)整〔續(xù)〕當(dāng)數(shù)據(jù)列中數(shù)據(jù)值不均勻時，建立字段矩形圖，以幫助系統(tǒng)選擇恰當(dāng)?shù)卦L問方式來提高性能；使用快速完全索引掃描來代替訪問表的記錄；/*+index_ffs*/分而治之對于那些在處理中需要連接多個表的應(yīng)用，如果每個表的記錄數(shù)都相當(dāng)大時，不要想一次就完成所有的操作。這樣會消耗大量的內(nèi)存及回滾段等。從而影響速度。建議你采用分段處理的方法盡量將處理分為不同階段來處理。常見的統(tǒng)計信息，主要在設(shè)計階段優(yōu)化分區(qū)，分表，分庫，日統(tǒng)計信息等設(shè)計思想，防止大數(shù)據(jù)量的操作。第三局部工具介紹開發(fā)工具測試語句性能工具查看執(zhí)行方案工具開發(fā)工具SQL*PLUSPL/SQLDEVELOPERTOAD(強大，功能眾多)其它測試語句性能工具常用于測試語句性能的方法有TKPROF實用程序EXPLAINPLANAUTOTRACE當(dāng)設(shè)置SQL_Trace為有效，那么系統(tǒng)對每條SQL語句的執(zhí)行情況提供EXPLAINPLAN解析、執(zhí)行、取數(shù)據(jù)的計數(shù)；CPU時間和占用時間；物理讀和邏輯讀；處理行數(shù)目；所解析的用戶名；每次提交和回滾的情況。SQL_Trace實用工具常用于測試語句性能的方法有ALTERSYSTEMSETSQL_TRACE=TRUE;(實例內(nèi))AltersessionSQL_TRACE=TRUE;(會話內(nèi))警告：由于SQL_TRACE實用程序會增加系統(tǒng)的開銷，建議用完后及時設(shè)置為FALSE。另外，如果將整個系統(tǒng)都進行跟蹤的話，那么在INITsid.ORA中設(shè)置sql_trace=true。這樣會使系統(tǒng)付出更大的代價。用TKPROF格式化跟蹤文件TKPROF將跟蹤文件作為輸入文件，在經(jīng)過格式化后產(chǎn)生輸出文件。由于跟蹤文件是一系列的文件，在使用TKPROF時可以對單個文件進行格式化，也可以將所有的跟蹤文件串在一切在進行格式化。逐一對單個文件進行TKPROF，分別產(chǎn)生相應(yīng)的輸出文件。對所有跟蹤文件級聯(lián)在一起在進行TKPROF處理，從而產(chǎn)生整個實例的格式化輸出文件。TKPROF命令語法：TKPROFfilename1,filename2[SORT=[opion][,option]][PRINT=integer][AGGREGATE=[YES|NO]][INSERT=filename3][SYS=[YES|NO]][[TABLE=schema.table]|[EXPLAIN=user/password]][RECORD=filename]用TKPROF格式化跟蹤文件(續(xù))經(jīng)過tkprof處理輸出的結(jié)構(gòu)如select,fromobj$o,user$u,trigger$twheret.baseobject=:1andt.obj#=o.obj#ando.owner#=u.user#orderbyo.obj#callcountcpuelapseddiskquerycurrentrows---------------------------------------------------------------------------------------------------------parse10.010.010000execute10.010.010000getch10.000.000100---------------------------------------------------------------------------------------------------------total30.020.020100Insertintoemployee(employee_id,last_name,first_name)Values(295,‘Joe’,‘Johnson’);callcountcpuelapseddiskquerycurrentrows---------------------------------------------------------------------------------------------------------parse10.080.120000execute10.040.2053111getch00.000.000100---------------------------------------------------------------------------------------------------------total20.120.32