??? SQL是的全稱(chēng)是Structured Query Language(結(jié)構(gòu)化查詢(xún)語(yǔ)言)。SQL是一個(gè)在80年代中期被使用的工業(yè)標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)查詢(xún)語(yǔ)言。不要把SQL語(yǔ)言
與商業(yè)化產(chǎn)品如Microsoft SQL server或開(kāi)源產(chǎn)品MySQL相混淆。所有的使用SQL縮略詞的這些都是SQL標(biāo)準(zhǔn)的一部分。
一、SQL tuning之前的調(diào)整
?? ?下面這個(gè)粗略的方法能夠節(jié)省數(shù)千小時(shí)乏味的SQL tuning，因?yàn)橐坏┱{(diào)整它將影響數(shù)以百計(jì)的SQL查詢(xún)。記住，你必須優(yōu)先調(diào)整它，否則后
?? ?續(xù)的優(yōu)化器參數(shù)改變或統(tǒng)計(jì)信息可能不會(huì)有助于你的SQL調(diào)整。
1、優(yōu)化系統(tǒng)內(nèi)核
?? ?首先應(yīng)當(dāng)考慮調(diào)整磁盤(pán)和網(wǎng)絡(luò)I/O子系統(tǒng)(象RAID，DASD帶寬，網(wǎng)絡(luò)等)去最小化I/O時(shí)間，網(wǎng)絡(luò)包的大小以及調(diào)度頻率。
2、調(diào)整優(yōu)化器統(tǒng)計(jì)信息
?? ?應(yīng)當(dāng)定期收集和存儲(chǔ)優(yōu)化器的統(tǒng)計(jì)信息以便優(yōu)化器根據(jù)數(shù)據(jù)的分布生成最佳的執(zhí)行計(jì)劃。此外，直方圖有助于優(yōu)化表的連接以及為有傾斜的
??? where 子句謂詞信息做出正確的訪問(wèn)決定。
3、調(diào)整優(yōu)化器參數(shù)
?? ?下列優(yōu)化器參數(shù)應(yīng)當(dāng)被調(diào)整
?? ?optimizer_mode， optimizer_index_caching， optimizer_index_cost_adj
4、優(yōu)化實(shí)例
?? ?下列實(shí)例/會(huì)話級(jí)別參數(shù)將影響SQL性能
?? ?db_block_size，db_cache_size， and OS parameters (db_file_multiblock_read_count， cpu_count， &c)，
5、使用索引或物化視圖調(diào)整SQL訪問(wèn)負(fù)載
?? ?Oracle 10g之后可以使用SQL Access advisor來(lái)為SQL生成索引或物化視圖的建議。應(yīng)當(dāng)總是使用索引來(lái)優(yōu)化SQL，特別是基于函數(shù)的索引。
??? Oracle 11g的改進(jìn)：
?? ?Oracle 11g中新增的SQL Performance Analyzer (SPA)是一個(gè)從整體上加快SQL調(diào)整的新特性。
?? ?通過(guò)SPA，一旦創(chuàng)建一個(gè)負(fù)載(稱(chēng)為SQL tuning set，或者STS)，Oracle將根據(jù)不同環(huán)境情況，使用復(fù)雜的預(yù)測(cè)模塊重復(fù)的執(zhí)行工作負(fù)載(使
?? ?用回歸測(cè)試方法)，來(lái)得到當(dāng)前負(fù)載的最佳SQL執(zhí)行計(jì)劃。使用SPA，我們可以預(yù)測(cè)一個(gè)SQL負(fù)載基于系統(tǒng)變化造成的影響，以及預(yù)測(cè)象參數(shù)
?? ?調(diào)整，系統(tǒng)schema調(diào)整，硬件調(diào)整，操作調(diào)整，Oracle升級(jí)之后當(dāng)前SQL語(yǔ)句的響應(yīng)時(shí)間。更多詳細(xì)的細(xì)節(jié)請(qǐng)參考:Oracle 11g New Feature
?? ?當(dāng)運(yùn)行環(huán)境，Oracle實(shí)例以及對(duì)象被調(diào)整之后，更多地關(guān)注則是數(shù)據(jù)庫(kù)中的性能影響最大的單個(gè)單個(gè)的SQL語(yǔ)句。下面將針對(duì)單個(gè)SQL調(diào)整給
?? ?出一些常規(guī)建議以提高 Oracle 性能。
二、Oracle SQL tuning的目標(biāo)
?? ?Oracle SQL tuning是一個(gè)復(fù)雜的課題。Oracle Tuning: The Definitive Reference 這整本書(shū)描述了關(guān)于SQL tuning的細(xì)節(jié)。盡管如此，
??? 為了提高系統(tǒng)系能，Oracle DBA應(yīng)當(dāng)遵從下面一些總的指導(dǎo)原則。
1、SQL tuning 目標(biāo)
?? 是以最小的數(shù)據(jù)庫(kù)訪問(wèn)次數(shù)提取更多地?cái)?shù)據(jù)行來(lái)生成最佳的執(zhí)行計(jì)劃(盡可能最小化物理讀(PIO)與邏輯讀(LIO)。
?? ?指導(dǎo)原則
?? ??? ? 移除不必要的大型全表掃描
?? ??? ??? ?大型表的全表掃描將產(chǎn)生龐大的系統(tǒng)I/O且使得整個(gè)數(shù)據(jù)庫(kù)性能下降。優(yōu)化專(zhuān)家首先會(huì)評(píng)估當(dāng)前SQL查詢(xún)所返回的行數(shù)。最常見(jiàn)的辦
?? ??? ?法是為走全表掃描的大表增加索引。B樹(shù)索引，位圖索引，以及基于函數(shù)的索引等能夠避免全表掃描。有時(shí)候，對(duì)一些不必要的全表掃
?? ??? ?描通過(guò)添加提示的方法來(lái)避免全表掃描。
?? ??? ? 緩存小表全表掃描
?? ??? ??? ?有時(shí)候全表掃描是最快的訪問(wèn)方式，管理員應(yīng)當(dāng)確保專(zhuān)用的數(shù)據(jù)緩沖區(qū)(keep buffer cache，nk buffer cache)對(duì)這些表可用。在
?? ??? ??? ?Oracle 8 以后小表可以被強(qiáng)制緩存到 keep 池。
?? ??? ? 使用最佳索引
?? ??? ??? ?Oracle 訪問(wèn)對(duì)象有時(shí)候會(huì)有一個(gè)以上的索引選擇。因此應(yīng)當(dāng)檢查當(dāng)前查詢(xún)對(duì)象上的每一個(gè)索引以確保Oracle使用了最佳索引。
?? ??? ? 物化聚合運(yùn)算以靜態(tài)化表統(tǒng)計(jì)
?? ??? ??? ?Oracle 10g的特性之一SQL Access advisor 會(huì)給出索引建議以及物化視圖的建議。物化視圖可以預(yù)連接表和預(yù)摘要表數(shù)據(jù)。(譯者
?? ??? ??? ?按，即Oracle可以根據(jù)特定的更新方式來(lái)提前更新物化視圖中的數(shù)據(jù)，而在查詢(xún)時(shí)僅僅查詢(xún)物化視圖即可得到最終所需的統(tǒng)計(jì)數(shù)據(jù)
?? ??? ??? ?結(jié)果。物化視圖實(shí)際上是一張實(shí)體表)
?? ?以上這些概括了SQL tuning的目標(biāo)。然而看是簡(jiǎn)單，調(diào)整起來(lái)并不容易，因?yàn)檫@需要對(duì)Oracle SQL內(nèi)部有一個(gè)徹底的了解。接下來(lái)讓我們從
?? ?整體上來(lái)認(rèn)識(shí) Oracle SQL 優(yōu)化。
2、Oracle SQL 優(yōu)化器
?? ?Oracle DBA首先要查看的是當(dāng)前數(shù)據(jù)庫(kù)缺省的優(yōu)化器模式。Oracle初始化參數(shù)提供很多基于成本優(yōu)化的優(yōu)化器模式以及之前廢棄的基于規(guī)則
?? ?的優(yōu)化器模式(或hint)供選擇?；诔杀镜膬?yōu)化器主要依賴(lài)于表對(duì)象使用analyze命令收集的統(tǒng)計(jì)信息。Oracle根據(jù)表上的統(tǒng)計(jì)信息得以決定
?? ?并為當(dāng)前的SQL生成最高效的執(zhí)行計(jì)劃。需要注意的是在一些場(chǎng)合基于成本優(yōu)化器可能會(huì)做出不正確的決定?；诔杀镜膬?yōu)化器在不斷的改進(jìn)，
?? ?但是依然有很多場(chǎng)合使用基于規(guī)則的優(yōu)化器能夠使得查詢(xún)更高效。
?? ?在Oracle 10g之前，Oracle 缺省的優(yōu)化器模式是CHOOSE模式。在該模式下，如果表對(duì)象上缺乏統(tǒng)計(jì)信息則此時(shí)Oracle使用基于規(guī)則的優(yōu)化
?? ?器；如果統(tǒng)計(jì)信息存在則使用基于成本的優(yōu)化器。使用CHOOSE模式存在的隱患即是對(duì)一些復(fù)雜得查詢(xún)有些對(duì)象上有統(tǒng)計(jì)信息，而另一些對(duì)象
?? ?缺乏統(tǒng)計(jì)信息。
?? ?在Oracle 10g開(kāi)始，缺省的優(yōu)化器模式是 ALL_ROWS，這有助于全表掃描優(yōu)于索引掃描。ALL_ROWS優(yōu)化器模式被設(shè)計(jì)成最小化計(jì)算資源且有
?? ?助于全表掃描。索引掃描(first_rows_n)增加了額外的I/O開(kāi)銷(xiāo)。但是他們能更快地返回?cái)?shù)據(jù)。
?? ?當(dāng)僅有一些表包含CBO統(tǒng)計(jì)信息，而另一些缺乏統(tǒng)計(jì)信息時(shí)，Oracle使用基于成本的優(yōu)化模式來(lái)預(yù)估其他表在運(yùn)行時(shí)的統(tǒng)計(jì)信息(即動(dòng)態(tài)采樣
?? ?)，這在很大程度上影響單個(gè)查詢(xún)性能下降。
?? ?總之，Oracle 數(shù)據(jù)庫(kù)管理員應(yīng)當(dāng)總是將嘗試改變優(yōu)化器模式作為SQL tuning的第一步。Oracle SQL tuning的首要原則是避免可怕的全表掃
?? ?描。一個(gè)特性之一是一個(gè)非高效的SQL語(yǔ)句為提高查詢(xún)性能使用所有的索引此仍然為一個(gè)失敗的SQL語(yǔ)句。
?? ?當(dāng)然，有些時(shí)候使用全表掃描是合適的，尤其是在做聚合操作象sum，avg等操作，因?yàn)闉榱双@得結(jié)果，表上的絕大部分?jǐn)?shù)據(jù)行必須被讀入到
?? ?緩存。SQL tuning 高手應(yīng)當(dāng)合理的評(píng)估每一個(gè)全表掃描并要核實(shí)使用索引能否提高性能。
?? ?在大多數(shù)Oracle 系統(tǒng)，SQL語(yǔ)句檢索的僅僅是表上數(shù)據(jù)一個(gè)子集。Oracle 優(yōu)化器會(huì)檢查使用索引是否會(huì)導(dǎo)致更多的I/O。然而，如果構(gòu)建了
?? ?一個(gè)低效的查詢(xún)，基于成本的優(yōu)化器難以選擇最佳的數(shù)據(jù)訪問(wèn)路徑，轉(zhuǎn)而傾向于使用全表掃描。故Oracle數(shù)據(jù)庫(kù)管理員應(yīng)當(dāng)總是審查那些走
?? ?全表掃描的SQL語(yǔ)句。
?? ?更多有關(guān)全表掃描的問(wèn)題，以及選擇正確的優(yōu)化模式請(qǐng) ："Oracle Tuning: The Definitive Reference"
三、SQL 調(diào)整戰(zhàn)略步驟
?? ?很多人問(wèn)SQL tuning從哪里著手。首先應(yīng)當(dāng)是從Library cache去根據(jù)他們的活動(dòng)狀況捕獲SQL語(yǔ)句。
1、尋找影響較大的SQL語(yǔ)句
?? ?我們可以根據(jù)SQL語(yǔ)句執(zhí)行次數(shù)的多少進(jìn)行排序來(lái)獲得執(zhí)行次數(shù)較多的SQL語(yǔ)句。在v$sqlarea視圖中executions 列以及表stats$sql_summary
?? ?或 dba_hist_sql_summary 能夠去定位當(dāng)前最頻繁使用的SQL語(yǔ)句。注:也可以按照下列方式列出SQL語(yǔ)句。
?? ??? ?Rows processed
?? ??? ??? ?處理的行數(shù)越多，則相應(yīng)會(huì)有很高的I/O，也有可能耗用大量的臨時(shí)表空間
?? ??? ?
?? ??? ?Buffer gets
?? ??? ??? ?Buffer gets過(guò)高可能表明資源被過(guò)度集中化查詢(xún)，存在熱塊現(xiàn)象
?? ??? ?
?? ??? ?Disk reads
?? ??? ??? ?高的磁盤(pán)讀將引起過(guò)度的I/O
?? ??? ?
?? ??? ?Memory KB
?? ??? ??? ?內(nèi)存的分配大小可以鑒別該SQL語(yǔ)句是否在內(nèi)存中使用了大量的表連接
?? ??? ?
?? ??? ?CPU secs
?? ??? ??? ?CPU的開(kāi)銷(xiāo)表明哪些SQL語(yǔ)句耗用了大量的CPU資源
?? ??? ?
?? ??? ?Sorts
?? ??? ??? ?排序越多，則SQL性能越差，而且會(huì)占用大量的臨時(shí)表空間
?? ??? ?
?? ??? ?Executions
?? ??? ??? ?執(zhí)行次數(shù)表明了當(dāng)前SQL語(yǔ)句的頻繁度，應(yīng)當(dāng)被首先考慮調(diào)整，因?yàn)檫@些語(yǔ)句影響了數(shù)據(jù)庫(kù)的整體性能
2、決定SQL的執(zhí)行計(jì)劃
?? ?每一個(gè)SQL語(yǔ)句都可以根據(jù)SQL_ID來(lái)獲得其執(zhí)行計(jì)劃。有大量的第三方工具來(lái)獲得SQL語(yǔ)句的執(zhí)行計(jì)劃。而獲得執(zhí)行最常用的方式是使用Oracle
?? ?自帶的explain plan程序。通過(guò)使用該程序，Oracle DBA能夠在不執(zhí)行SQL 語(yǔ)句的情形下解析并顯示該SQL語(yǔ)句的執(zhí)行計(jì)劃。
?? ?查看SQL執(zhí)行計(jì)劃的輸出，必須首先創(chuàng)建一個(gè)plan table. Oracle提供一個(gè)utlxplan.sql腳本來(lái)創(chuàng)建該表。執(zhí)行該腳本并且為該表創(chuàng)建一個(gè)
?? ?公共同義詞。
?? ?sqlplus > @utlxplan
?? ?Table created.
?? ?
?? ?sqlplus > create public synonym plan_table for sys.plan_table;
?? ?Synonym created.
?? ?大多數(shù)關(guān)系數(shù)據(jù)庫(kù)使用解釋程序?qū)QL語(yǔ)句作為輸入，然后運(yùn)行SQL優(yōu)化器，輸出訪問(wèn)的路徑信息到一個(gè)plan_table。以便我們能夠查看及調(diào)
?? ?整其訪問(wèn)方式。下面的是一個(gè)復(fù)雜的SQL查詢(xún)。
?? ?EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR
?? ?SET STATEMENT_ID = 'RUN1'
?? ?INTO plan_table
?? ?FOR
?? ?SELECT?? 'T'||plansnet.terr_code, 'P'||detplan.pac1
?? ?|| detplan.pac2 || detplan.pac3, 'P1', sum(plansnet.ytd_d_ly_tm),
?? ? sum(plansnet.ytd_d_ty_tm),
?? ? sum(plansnet.jan_d_ly),
?? ? sum(plansnet.jan_d_ty),
?? ?FROM plansnet, detplan
?? ?WHERE
?? ??? ?plansnet.mgc = detplan.mktgpm
?? ?AND
?? ??? ?detplan.pac1 in ('N33','192','195','201','BAI',
?? ??? ?'P51','Q27','180','181','183','184','186','188',
?? ??? ?'198','204','207','209','211')
?? ?GROUP BY 'T'||plansnet.terr_code, 'P'||detplan.pac1 || detplan.pac2 || detplan.pac3;
?? ?這個(gè)語(yǔ)法使用管道輸入到SQL優(yōu)化器，解析SQL，存儲(chǔ)執(zhí)行計(jì)劃信息到表plan_table，且RUN1作為鑒別當(dāng)前SQL語(yǔ)句的標(biāo)識(shí)符。注意，該查詢(xún)
?? ?并沒(méi)有執(zhí)行，它僅僅是創(chuàng)建了一個(gè)內(nèi)部訪問(wèn)信息且輸出到plan_table。plan 表包含下列字段。
?? ?
?? ??? ?operation
?? ??? ??? ?表明當(dāng)前語(yǔ)句完成的操作，通常包括table access, table merge, sort, or index operation
?? ??? ?
?? ??? ?options
?? ??? ??? ?補(bǔ)充說(shuō)明operation，像full table, range table, join
?? ??? ?
?? ??? ?object_name
?? ??? ??? ?查詢(xún)組件的名字
?? ??? ?
?? ??? ?Process ID
?? ??? ??? ?查詢(xún)組件的ID號(hào)
?? ??? ?
?? ??? ?Parent_ID
?? ??? ??? ?查詢(xún)組建的父ID，注意，有些查詢(xún)會(huì)有一個(gè)相同的父ID
?? ?現(xiàn)在plan_table已經(jīng)被填充，可以使用下面的查詢(xún)來(lái)查看當(dāng)前SQL語(yǔ)句的執(zhí)行計(jì)劃。
?? ?
?? ??? ?plan.sql - displays contents of the explain plan table
?? ??? ?SET PAGES 9999;
?? ??? ?SELECT? lpad(' ',2*(level-1))||operation operation,
?? ??? ??? ??? ?options,
?? ??? ??? ??? ?object_name,
?? ??? ??? ??? ?position
?? ??? ?FROM plan_table
?? ??? ?START WITH id=0
?? ??? ?AND
?? ??? ?statement_id = 'RUN1'
?? ??? ?CONNECT BY prior id = parent_id
?? ??? ?AND
?? ??? ?statement_id = 'RUN1';
?? ?下面給出了當(dāng)前語(yǔ)句執(zhí)行計(jì)劃信息以及各個(gè)部分的執(zhí)行順序。
?? ?SQL> @list_explain_plan
?? ?
?? ?OPERATION
?? ?-------------------------------------------------------------------------------------
?? ?OPTIONS?????????????????????????? OBJECT_NAME??????????????????? POSITION
?? ?------------------------------ -------------------------------------------------------
?? ?SELECT STATEMENT
?? ?SORT
?? ?GROUP BY????????????????????????????????????????????????????? 1
?? ??? ??? CONCATENATION?????????????????????????????????? 1
?? ?NESTED LOOPS??????????????????????????????????? 1
?? ?TABLE ACCESS FULL???????? PLANSNET?????????????????? 1
?? ?TABLE ACCESS BY ROWID???? DETPLAN??????????????????? 2
?? ??? ??? ?? INDEX RANGE SCAN?????? DETPLAN_INDEX5???????????? 1
?? ?NESTED LOOPS
?? ?
?? ?從上面的執(zhí)行計(jì)劃中得知當(dāng)前的SQL語(yǔ)句存在表掃描現(xiàn)象。去調(diào)整該SQL語(yǔ)句，我們應(yīng)當(dāng)尋找表where 子句中為planset上的列。在這里我們
?? ?看到了在where子句存在一個(gè)且屬于表planset上的列mgc被用作連接條件。這說(shuō)明一個(gè)基于表planset.mgs列上的索引是必要的。
?? ?plan table并不能展現(xiàn)整個(gè)SQL語(yǔ)句的細(xì)節(jié)，但對(duì)于獲得數(shù)據(jù)訪問(wèn)路徑是非常有用的。SQL優(yōu)化器知道每一個(gè)表的行數(shù)(基數(shù))以及一些索引字
?? ?段的狀況。但并不了解數(shù)據(jù)的分布象如一個(gè)組件期待返回的行數(shù)。
3、調(diào)整SQL語(yǔ)句
?? ?對(duì)于那些存在可優(yōu)化的子執(zhí)行計(jì)劃，SQL應(yīng)當(dāng)按照下面的方式進(jìn)行調(diào)整。
?? ? 通過(guò)添加提示來(lái)修改SQL的執(zhí)行計(jì)劃
?? ? 使用全局臨時(shí)表來(lái)重寫(xiě)SQL
?? ? 使用PL/SQL來(lái)重寫(xiě)SQL。 對(duì)于一些特定查詢(xún)?cè)摲椒軌蛴?0倍左右的提升。將這些SQL封裝到包含存儲(chǔ)過(guò)程的包中去完成查詢(xún)。
?? ? 使用提示來(lái)調(diào)整SQL
?? ?大多數(shù)SQL tuning工具中使用較多的莫過(guò)于使用提示。一個(gè)提示添加的SQL語(yǔ)句后使得SQL查詢(xún)的按指定路徑訪問(wèn)。
?? ?Troubleshooting tip!
?? ?為便于測(cè)試，我們能夠隨時(shí)使用alter session命令來(lái)修改一個(gè)優(yōu)化參數(shù)的值來(lái)觀察調(diào)整前后的結(jié)果比較。使用新的 opt_param 提示能獲得
?? ?同樣的效果。
?? ?
?? ?select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .
??? select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .
?? ?Oracle 發(fā)布了大量的SQL提示，而且提示隨著Oracle版本的不同不斷的增強(qiáng)和復(fù)雜化。
?? ? 注意：提示通常用于調(diào)試SQL，最佳的辦法是調(diào)整優(yōu)化器的統(tǒng)計(jì)信息使的CBO模式自動(dòng)獲取最佳執(zhí)行路徑，等同于使用提示的功能。
?? ?我們來(lái)看看提高性能最常用的提示
?? ??? ?Mode hints:? first_rows_10, first_rows_100
?? ??? ?Oracle leading and ordered hints? Also see how to tune table join order with histograms
?? ??? ?
?? ??? ?Dynamic sampling: dynamic_sampling
?? ??? ?
?? ??? ?Oracle SQL undocumented tuning hints - Guru's only
?? ??? ?The cardinality hint?? ?
?? ?
?? ? 表連接順序
?? ???? 當(dāng)表連接的順序可優(yōu)化時(shí)，我們可以使用 ORDERED提示來(lái)強(qiáng)制表按照f(shuō)rom子句中出現(xiàn)的先后順序來(lái)進(jìn)行連接
?? ?
?? ?first_rows_n提示
?? ???? Oracle 有兩個(gè)基于成本優(yōu)化的提示，一個(gè)是first_rows_n，一個(gè)是all_rows。first_rows模式將盡可能在一查詢(xún)到數(shù)據(jù)時(shí)就返回個(gè)客
?? ???? 戶端。而 all_rows 模式則為優(yōu)化資源而設(shè)計(jì)，需要等到所有結(jié)果計(jì)算執(zhí)行完畢才返回?cái)?shù)據(jù)給客戶端。
?? ?
?? ??? ?SELECT /*+ first_rows */
?? ?
4、案例
?? ?同一個(gè)SQL語(yǔ)句有不同的寫(xiě)法。即簡(jiǎn)單的SQL查詢(xún)能夠以不同的方式來(lái)產(chǎn)生相同的結(jié)果集，但其執(zhí)行效率和訪問(wèn)方式則千差萬(wàn)別。
?? ?
?? ?下面的例子中的SQL語(yǔ)句使用了3種不同的寫(xiě)法來(lái)返回相同的結(jié)果
?? ?
?? ?A standard join:? -->標(biāo)準(zhǔn)連接
?? ?
?? ?SELECT *
?? ?FROM STUDENT, REGISTRATION
?? ?WHERE
?? ??? ?STUDENT.student_id = REGISTRATION.student_id
?? ?AND
?? ??? ?REGISTRATION.grade = 'A';
?? ?
?? ?A nested query:? -->嵌套查詢(xún)
?? ?SELECT *
?? ?FROM STUDENT
?? ?WHERE
?? ??? ?student_id =
?? ??? ?(SELECT student_id
?? ??? ??? ?FROM REGISTRATION
?? ??? ??? ?WHERE
?? ??? ??? grade = 'A'
?? ??? ?);
?? ?A correlated subquery:? -->相關(guān)子查詢(xún)
?? ?SELECT *
?? ?FROM STUDENT
?? ?WHERE
?? ??? ?0 <
?? ??? ?(SELECT count(*)
?? ??? ??? ?FROM REGISTRATION
?? ??? ??? ?WHERE
?? ??? ??? ?grade = 'A'
?? ??? ??? ?AND
?? ??? ??? ?student_id = STUDENT.student_id
?? ??? ?);
?? ??? ?
?? ?我們應(yīng)該根據(jù)基本的SQL原則來(lái)優(yōu)化當(dāng)前的SQL語(yǔ)句。
5、書(shū)寫(xiě)高效SQL語(yǔ)句的技巧
?? ???? 下面給出一些編寫(xiě)高效SQL語(yǔ)句的總的指導(dǎo)原則，而不論Oracle優(yōu)化器選擇何種優(yōu)化模式。這些看是簡(jiǎn)單的方式但是按照他們
?? ?去做將收到事半功倍的效果(已經(jīng)在實(shí)踐中被證實(shí))。
?? ??? ?
?? ? a.使用臨時(shí)表重寫(xiě)復(fù)雜的子查詢(xún)
?? ??? ?Oracle 使用全局臨時(shí)表以及WITH操作符去解決那些復(fù)雜的SQL子查詢(xún)。尤其是那些where子句中的子查詢(xún)，SELECT 字句標(biāo)量子查詢(xún)，
?? ??? ?FROM 子句的內(nèi)聯(lián)視圖。使用臨時(shí)表實(shí)現(xiàn)SQL tuning(以及使用WITH的物化視圖)能夠使得性能得以驚人的提升。
?? ??? ?
?? ? b.使用MINUS 代替EXIST子查詢(xún)
?? ??? ?使用MINUS操作代替NOT IN 或NOT EXISTS將產(chǎn)生更高效的執(zhí)行計(jì)劃(譯者按：此需要測(cè)試)。
?? ??? ?
?? ? c.使用SQL分析函數(shù)
?? ??? ?Oracle 分析函數(shù)能夠一次提取數(shù)據(jù)來(lái)做多維聚合運(yùn)算(象ROLLUP,CUBE)以提高性能。
?? ??? ?
?? ? d.重寫(xiě)NOT EXISTS和查詢(xún)作為外部連接NOT EXISTS 子查詢(xún)
?? ??? ?在一些案例中的NOT 查詢(xún)(where 中一個(gè)列被定義為NULL值)，能夠?qū)⑵涓膶?xiě)這個(gè)非相關(guān)子查詢(xún)到IS NULL 的外部鏈接。如下例：
?? ??? ?select book_key from book
?? ??? ?where
?? ??? ?book_key NOT IN (select book_key from sales);
?? ??? ?下面我們?cè)趙here子句中使用了外部連接來(lái)替代原來(lái)的not exits，得到一個(gè)更高效的執(zhí)行計(jì)劃。
?? ??? ?select b.book_key from book b, sales s
?? ??? ?where
?? ??? ??? b.book_key = s.book_key(+)
?? ??? ?and
?? ??? ??? s.book_key IS NULL;
?? ? e.索引NULL值列
?? ??? ?如果你的SQL語(yǔ)句頻繁使用到NULL值，應(yīng)當(dāng)考慮基于NULL值創(chuàng)建索引。為使該查詢(xún)最優(yōu)化，可以創(chuàng)建一個(gè)使用基于NULL值索引函數(shù)。
?? ??? ?(譯者按，如 create index i_tb_col on tab(nvl(col,null)); create index i_tb_col on tab(col,0);)
?? ? f.避免基于索引的運(yùn)算
?? ??? ?不要基于索引列做任何運(yùn)算，除非你創(chuàng)建了一個(gè)相應(yīng)的索引函數(shù)。或者重設(shè)設(shè)計(jì)列以使得where子句列上的謂詞不需要轉(zhuǎn)換。
?? ??? ?-->下面都是低效的SQL寫(xiě)法
?? ??? ?where salary*5??????????? > :myvalue? ?
?? ??? ?where substr(ssn,7,4)???? = "1234"
?? ??? ?where to_char(mydate,mon) = "january"
?? ?g.避免使用NOT IN 和HAVING
?? ??? ?在合適的時(shí)候使用not exists子查詢(xún)更高效。
?? ? h.避免使用LIKE謂詞
?? ??? ?在合適地時(shí)候，如果能夠使用 = 運(yùn)算應(yīng)盡可能避免LIKE操作。
?? ? i.避免數(shù)據(jù)類(lèi)型轉(zhuǎn)換
?? ??? ?如果一個(gè)where 子句列是數(shù)字型，則不要使用引號(hào)。而對(duì)一個(gè)字符索引列，總是使用引號(hào)。下面是數(shù)據(jù)類(lèi)型混用的情形。
?? ??? ?where cust_nbr = "123"
?? ??? ?where substr(ssn,7,4) = 1234
?? ? j.使用decode與case
?? ??? ?使用decode 與case 函數(shù)能夠最小化查詢(xún)表的次數(shù)。
?? ? k.不要害怕全表掃描
?? ??? ?并不是所有的OLTP系統(tǒng)在使用索引時(shí)是最優(yōu)化的。如果你的查詢(xún)返回了表中的絕大部分?jǐn)?shù)據(jù)，則全表掃描性能優(yōu)于索引掃描。這取決于
?? ???? 一些因素包括你的配置(db_file_multiblock_read_count, db_block_size)，并行查詢(xún)，以及表塊和索引塊在buffer cache中的數(shù)量。
?? ? l.使用別名
?? ??? ?在參照列的地方總是使用表別名。
??? --> Author : Robinson Cheng
?? ?--> Blog?? : http://blog.csdn.net/robinson_0612
四、結(jié)論：
?? ?這篇文章從整體上描述SQL tuning的一些步驟，并未涉及SQL tuning的具體細(xì)節(jié)。更多參考： "Oracle Tuning: The Definitive Reference"
與商業(yè)化產(chǎn)品如Microsoft SQL server或開(kāi)源產(chǎn)品MySQL相混淆。所有的使用SQL縮略詞的這些都是SQL標(biāo)準(zhǔn)的一部分。
一、SQL tuning之前的調(diào)整
?? ?下面這個(gè)粗略的方法能夠節(jié)省數(shù)千小時(shí)乏味的SQL tuning，因?yàn)橐坏┱{(diào)整它將影響數(shù)以百計(jì)的SQL查詢(xún)。記住，你必須優(yōu)先調(diào)整它，否則后
?? ?續(xù)的優(yōu)化器參數(shù)改變或統(tǒng)計(jì)信息可能不會(huì)有助于你的SQL調(diào)整。
?? ?記住，你應(yīng)當(dāng)總是優(yōu)先考慮系統(tǒng)級(jí)別的SQL tuning，否則在SQL tuning之后再進(jìn)行調(diào)整可能會(huì)使得你先前調(diào)整的SQL功虧一簣。
1、優(yōu)化系統(tǒng)內(nèi)核
?? ?首先應(yīng)當(dāng)考慮調(diào)整磁盤(pán)和網(wǎng)絡(luò)I/O子系統(tǒng)(象RAID，DASD帶寬，網(wǎng)絡(luò)等)去最小化I/O時(shí)間，網(wǎng)絡(luò)包的大小以及調(diào)度頻率。
2、調(diào)整優(yōu)化器統(tǒng)計(jì)信息
?? ?應(yīng)當(dāng)定期收集和存儲(chǔ)優(yōu)化器的統(tǒng)計(jì)信息以便優(yōu)化器根據(jù)數(shù)據(jù)的分布生成最佳的執(zhí)行計(jì)劃。此外，直方圖有助于優(yōu)化表的連接以及為有傾斜的
??? where 子句謂詞信息做出正確的訪問(wèn)決定。
3、調(diào)整優(yōu)化器參數(shù)
?? ?下列優(yōu)化器參數(shù)應(yīng)當(dāng)被調(diào)整
?? ?optimizer_mode， optimizer_index_caching， optimizer_index_cost_adj
4、優(yōu)化實(shí)例
?? ?下列實(shí)例/會(huì)話級(jí)別參數(shù)將影響SQL性能
?? ?db_block_size，db_cache_size， and OS parameters (db_file_multiblock_read_count， cpu_count， &c)，
5、使用索引或物化視圖調(diào)整SQL訪問(wèn)負(fù)載
?? ?Oracle 10g之后可以使用SQL Access advisor來(lái)為SQL生成索引或物化視圖的建議。應(yīng)當(dāng)總是使用索引來(lái)優(yōu)化SQL，特別是基于函數(shù)的索引。
??? Oracle 11g的改進(jìn)：
?? ?Oracle 11g中新增的SQL Performance Analyzer (SPA)是一個(gè)從整體上加快SQL調(diào)整的新特性。
?? ?通過(guò)SPA，一旦創(chuàng)建一個(gè)負(fù)載(稱(chēng)為SQL tuning set，或者STS)，Oracle將根據(jù)不同環(huán)境情況，使用復(fù)雜的預(yù)測(cè)模塊重復(fù)的執(zhí)行工作負(fù)載(使
?? ?用回歸測(cè)試方法)，來(lái)得到當(dāng)前負(fù)載的最佳SQL執(zhí)行計(jì)劃。使用SPA，我們可以預(yù)測(cè)一個(gè)SQL負(fù)載基于系統(tǒng)變化造成的影響，以及預(yù)測(cè)象參數(shù)
?? ?調(diào)整，系統(tǒng)schema調(diào)整，硬件調(diào)整，操作調(diào)整，Oracle升級(jí)之后當(dāng)前SQL語(yǔ)句的響應(yīng)時(shí)間。更多詳細(xì)的細(xì)節(jié)請(qǐng)參考:Oracle 11g New Feature
?? ?當(dāng)運(yùn)行環(huán)境，Oracle實(shí)例以及對(duì)象被調(diào)整之后，更多地關(guān)注則是數(shù)據(jù)庫(kù)中的性能影響最大的單個(gè)單個(gè)的SQL語(yǔ)句。下面將針對(duì)單個(gè)SQL調(diào)整給
?? ?出一些常規(guī)建議以提高 Oracle 性能。
二、Oracle SQL tuning的目標(biāo)
?? ?Oracle SQL tuning是一個(gè)復(fù)雜的課題。Oracle Tuning: The Definitive Reference 這整本書(shū)描述了關(guān)于SQL tuning的細(xì)節(jié)。盡管如此，
??? 為了提高系統(tǒng)系能，Oracle DBA應(yīng)當(dāng)遵從下面一些總的指導(dǎo)原則。
1、SQL tuning 目標(biāo)
?? 是以最小的數(shù)據(jù)庫(kù)訪問(wèn)次數(shù)提取更多地?cái)?shù)據(jù)行來(lái)生成最佳的執(zhí)行計(jì)劃(盡可能最小化物理讀(PIO)與邏輯讀(LIO)。
?? ?指導(dǎo)原則
?? ??? ? 移除不必要的大型全表掃描
?? ??? ??? ?大型表的全表掃描將產(chǎn)生龐大的系統(tǒng)I/O且使得整個(gè)數(shù)據(jù)庫(kù)性能下降。優(yōu)化專(zhuān)家首先會(huì)評(píng)估當(dāng)前SQL查詢(xún)所返回的行數(shù)。最常見(jiàn)的辦
?? ??? ?法是為走全表掃描的大表增加索引。B樹(shù)索引，位圖索引，以及基于函數(shù)的索引等能夠避免全表掃描。有時(shí)候，對(duì)一些不必要的全表掃
?? ??? ?描通過(guò)添加提示的方法來(lái)避免全表掃描。
?? ??? ? 緩存小表全表掃描
?? ??? ??? ?有時(shí)候全表掃描是最快的訪問(wèn)方式，管理員應(yīng)當(dāng)確保專(zhuān)用的數(shù)據(jù)緩沖區(qū)(keep buffer cache，nk buffer cache)對(duì)這些表可用。在
?? ??? ??? ?Oracle 8 以后小表可以被強(qiáng)制緩存到 keep 池。
?? ??? ? 使用最佳索引
?? ??? ??? ?Oracle 訪問(wèn)對(duì)象有時(shí)候會(huì)有一個(gè)以上的索引選擇。因此應(yīng)當(dāng)檢查當(dāng)前查詢(xún)對(duì)象上的每一個(gè)索引以確保Oracle使用了最佳索引。
?? ??? ? 物化聚合運(yùn)算以靜態(tài)化表統(tǒng)計(jì)
?? ??? ??? ?Oracle 10g的特性之一SQL Access advisor 會(huì)給出索引建議以及物化視圖的建議。物化視圖可以預(yù)連接表和預(yù)摘要表數(shù)據(jù)。(譯者
?? ??? ??? ?按，即Oracle可以根據(jù)特定的更新方式來(lái)提前更新物化視圖中的數(shù)據(jù)，而在查詢(xún)時(shí)僅僅查詢(xún)物化視圖即可得到最終所需的統(tǒng)計(jì)數(shù)據(jù)
?? ??? ??? ?結(jié)果。物化視圖實(shí)際上是一張實(shí)體表)
?? ?以上這些概括了SQL tuning的目標(biāo)。然而看是簡(jiǎn)單，調(diào)整起來(lái)并不容易，因?yàn)檫@需要對(duì)Oracle SQL內(nèi)部有一個(gè)徹底的了解。接下來(lái)讓我們從
?? ?整體上來(lái)認(rèn)識(shí) Oracle SQL 優(yōu)化。
2、Oracle SQL 優(yōu)化器
?? ?Oracle DBA首先要查看的是當(dāng)前數(shù)據(jù)庫(kù)缺省的優(yōu)化器模式。Oracle初始化參數(shù)提供很多基于成本優(yōu)化的優(yōu)化器模式以及之前廢棄的基于規(guī)則
?? ?的優(yōu)化器模式(或hint)供選擇?；诔杀镜膬?yōu)化器主要依賴(lài)于表對(duì)象使用analyze命令收集的統(tǒng)計(jì)信息。Oracle根據(jù)表上的統(tǒng)計(jì)信息得以決定
?? ?并為當(dāng)前的SQL生成最高效的執(zhí)行計(jì)劃。需要注意的是在一些場(chǎng)合基于成本優(yōu)化器可能會(huì)做出不正確的決定?；诔杀镜膬?yōu)化器在不斷的改進(jìn)，
?? ?但是依然有很多場(chǎng)合使用基于規(guī)則的優(yōu)化器能夠使得查詢(xún)更高效。
?? ?在Oracle 10g之前，Oracle 缺省的優(yōu)化器模式是CHOOSE模式。在該模式下，如果表對(duì)象上缺乏統(tǒng)計(jì)信息則此時(shí)Oracle使用基于規(guī)則的優(yōu)化
?? ?器；如果統(tǒng)計(jì)信息存在則使用基于成本的優(yōu)化器。使用CHOOSE模式存在的隱患即是對(duì)一些復(fù)雜得查詢(xún)有些對(duì)象上有統(tǒng)計(jì)信息，而另一些對(duì)象
?? ?缺乏統(tǒng)計(jì)信息。
?? ?在Oracle 10g開(kāi)始，缺省的優(yōu)化器模式是 ALL_ROWS，這有助于全表掃描優(yōu)于索引掃描。ALL_ROWS優(yōu)化器模式被設(shè)計(jì)成最小化計(jì)算資源且有
?? ?助于全表掃描。索引掃描(first_rows_n)增加了額外的I/O開(kāi)銷(xiāo)。但是他們能更快地返回?cái)?shù)據(jù)。
?? ?因此，大多數(shù)OLTP系統(tǒng)選擇first_rows，first_rows_100 或者 first_rows_10以使得Oracle使用索引掃描來(lái)減少讀塊數(shù)量。
?? ?注意：從Oracle 9i R2開(kāi)始，Oracle 性能調(diào)整指導(dǎo)指出了first_rows 優(yōu)化器模式已經(jīng)被廢棄，且使用first_rows_n代替
?? ?當(dāng)僅有一些表包含CBO統(tǒng)計(jì)信息，而另一些缺乏統(tǒng)計(jì)信息時(shí)，Oracle使用基于成本的優(yōu)化模式來(lái)預(yù)估其他表在運(yùn)行時(shí)的統(tǒng)計(jì)信息(即動(dòng)態(tài)采樣
?? ?)，這在很大程度上影響單個(gè)查詢(xún)性能下降。
?? ?總之，Oracle 數(shù)據(jù)庫(kù)管理員應(yīng)當(dāng)總是將嘗試改變優(yōu)化器模式作為SQL tuning的第一步。Oracle SQL tuning的首要原則是避免可怕的全表掃
?? ?描。一個(gè)特性之一是一個(gè)非高效的SQL語(yǔ)句為提高查詢(xún)性能使用所有的索引此仍然為一個(gè)失敗的SQL語(yǔ)句。
?? ?當(dāng)然，有些時(shí)候使用全表掃描是合適的，尤其是在做聚合操作象sum，avg等操作，因?yàn)闉榱双@得結(jié)果，表上的絕大部分?jǐn)?shù)據(jù)行必須被讀入到
?? ?緩存。SQL tuning 高手應(yīng)當(dāng)合理的評(píng)估每一個(gè)全表掃描并要核實(shí)使用索引能否提高性能。
?? ?在大多數(shù)Oracle 系統(tǒng)，SQL語(yǔ)句檢索的僅僅是表上數(shù)據(jù)一個(gè)子集。Oracle 優(yōu)化器會(huì)檢查使用索引是否會(huì)導(dǎo)致更多的I/O。然而，如果構(gòu)建了
?? ?一個(gè)低效的查詢(xún)，基于成本的優(yōu)化器難以選擇最佳的數(shù)據(jù)訪問(wèn)路徑，轉(zhuǎn)而傾向于使用全表掃描。故Oracle數(shù)據(jù)庫(kù)管理員應(yīng)當(dāng)總是審查那些走
?? ?全表掃描的SQL語(yǔ)句。
?? ?更多有關(guān)全表掃描的問(wèn)題，以及選擇正確的優(yōu)化模式請(qǐng) ："Oracle Tuning: The Definitive Reference"
三、SQL 調(diào)整戰(zhàn)略步驟
?? ?很多人問(wèn)SQL tuning從哪里著手。首先應(yīng)當(dāng)是從Library cache去根據(jù)他們的活動(dòng)狀況捕獲SQL語(yǔ)句。
1、尋找影響較大的SQL語(yǔ)句
?? ?我們可以根據(jù)SQL語(yǔ)句執(zhí)行次數(shù)的多少進(jìn)行排序來(lái)獲得執(zhí)行次數(shù)較多的SQL語(yǔ)句。在v$sqlarea視圖中executions 列以及表stats$sql_summary
?? ?或 dba_hist_sql_summary 能夠去定位當(dāng)前最頻繁使用的SQL語(yǔ)句。注:也可以按照下列方式列出SQL語(yǔ)句。
?? ??? ?Rows processed
?? ??? ??? ?處理的行數(shù)越多，則相應(yīng)會(huì)有很高的I/O，也有可能耗用大量的臨時(shí)表空間
?? ??? ?
?? ??? ?Buffer gets
?? ??? ??? ?Buffer gets過(guò)高可能表明資源被過(guò)度集中化查詢(xún)，存在熱塊現(xiàn)象
?? ??? ?
?? ??? ?Disk reads
?? ??? ??? ?高的磁盤(pán)讀將引起過(guò)度的I/O
?? ??? ?
?? ??? ?Memory KB
?? ??? ??? ?內(nèi)存的分配大小可以鑒別該SQL語(yǔ)句是否在內(nèi)存中使用了大量的表連接
?? ??? ?
?? ??? ?CPU secs
?? ??? ??? ?CPU的開(kāi)銷(xiāo)表明哪些SQL語(yǔ)句耗用了大量的CPU資源
?? ??? ?
?? ??? ?Sorts
?? ??? ??? ?排序越多，則SQL性能越差，而且會(huì)占用大量的臨時(shí)表空間
?? ??? ?
?? ??? ?Executions
?? ??? ??? ?執(zhí)行次數(shù)表明了當(dāng)前SQL語(yǔ)句的頻繁度，應(yīng)當(dāng)被首先考慮調(diào)整，因?yàn)檫@些語(yǔ)句影響了數(shù)據(jù)庫(kù)的整體性能
2、決定SQL的執(zhí)行計(jì)劃
?? ?每一個(gè)SQL語(yǔ)句都可以根據(jù)SQL_ID來(lái)獲得其執(zhí)行計(jì)劃。有大量的第三方工具來(lái)獲得SQL語(yǔ)句的執(zhí)行計(jì)劃。而獲得執(zhí)行最常用的方式是使用Oracle
?? ?自帶的explain plan程序。通過(guò)使用該程序，Oracle DBA能夠在不執(zhí)行SQL 語(yǔ)句的情形下解析并顯示該SQL語(yǔ)句的執(zhí)行計(jì)劃。
?? ?查看SQL執(zhí)行計(jì)劃的輸出，必須首先創(chuàng)建一個(gè)plan table. Oracle提供一個(gè)utlxplan.sql腳本來(lái)創(chuàng)建該表。執(zhí)行該腳本并且為該表創(chuàng)建一個(gè)
?? ?公共同義詞。
?? ?sqlplus > @utlxplan
?? ?Table created.
?? ?
?? ?sqlplus > create public synonym plan_table for sys.plan_table;
?? ?Synonym created.
?? ?大多數(shù)關(guān)系數(shù)據(jù)庫(kù)使用解釋程序?qū)QL語(yǔ)句作為輸入，然后運(yùn)行SQL優(yōu)化器，輸出訪問(wèn)的路徑信息到一個(gè)plan_table。以便我們能夠查看及調(diào)
?? ?整其訪問(wèn)方式。下面的是一個(gè)復(fù)雜的SQL查詢(xún)。
?? ?EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR
?? ?SET STATEMENT_ID = 'RUN1'
?? ?INTO plan_table
?? ?FOR
?? ?SELECT?? 'T'||plansnet.terr_code, 'P'||detplan.pac1
?? ?|| detplan.pac2 || detplan.pac3, 'P1', sum(plansnet.ytd_d_ly_tm),
?? ? sum(plansnet.ytd_d_ty_tm),
?? ? sum(plansnet.jan_d_ly),
?? ? sum(plansnet.jan_d_ty),
?? ?FROM plansnet, detplan
?? ?WHERE
?? ??? ?plansnet.mgc = detplan.mktgpm
?? ?AND
?? ??? ?detplan.pac1 in ('N33','192','195','201','BAI',
?? ??? ?'P51','Q27','180','181','183','184','186','188',
?? ??? ?'198','204','207','209','211')
?? ?GROUP BY 'T'||plansnet.terr_code, 'P'||detplan.pac1 || detplan.pac2 || detplan.pac3;
?? ?這個(gè)語(yǔ)法使用管道輸入到SQL優(yōu)化器，解析SQL，存儲(chǔ)執(zhí)行計(jì)劃信息到表plan_table，且RUN1作為鑒別當(dāng)前SQL語(yǔ)句的標(biāo)識(shí)符。注意，該查詢(xún)
?? ?并沒(méi)有執(zhí)行，它僅僅是創(chuàng)建了一個(gè)內(nèi)部訪問(wèn)信息且輸出到plan_table。plan 表包含下列字段。
?? ?
?? ??? ?operation
?? ??? ??? ?表明當(dāng)前語(yǔ)句完成的操作，通常包括table access, table merge, sort, or index operation
?? ??? ?
?? ??? ?options
?? ??? ??? ?補(bǔ)充說(shuō)明operation，像full table, range table, join
?? ??? ?
?? ??? ?object_name
?? ??? ??? ?查詢(xún)組件的名字
?? ??? ?
?? ??? ?Process ID
?? ??? ??? ?查詢(xún)組件的ID號(hào)
?? ??? ?
?? ??? ?Parent_ID
?? ??? ??? ?查詢(xún)組建的父ID，注意，有些查詢(xún)會(huì)有一個(gè)相同的父ID
?? ?現(xiàn)在plan_table已經(jīng)被填充，可以使用下面的查詢(xún)來(lái)查看當(dāng)前SQL語(yǔ)句的執(zhí)行計(jì)劃。
?? ?
?? ??? ?plan.sql - displays contents of the explain plan table
?? ??? ?SET PAGES 9999;
?? ??? ?SELECT? lpad(' ',2*(level-1))||operation operation,
?? ??? ??? ??? ?options,
?? ??? ??? ??? ?object_name,
?? ??? ??? ??? ?position
?? ??? ?FROM plan_table
?? ??? ?START WITH id=0
?? ??? ?AND
?? ??? ?statement_id = 'RUN1'
?? ??? ?CONNECT BY prior id = parent_id
?? ??? ?AND
?? ??? ?statement_id = 'RUN1';
?? ?下面給出了當(dāng)前語(yǔ)句執(zhí)行計(jì)劃信息以及各個(gè)部分的執(zhí)行順序。
?? ?SQL> @list_explain_plan
?? ?
?? ?OPERATION
?? ?-------------------------------------------------------------------------------------
?? ?OPTIONS?????????????????????????? OBJECT_NAME??????????????????? POSITION
?? ?------------------------------ -------------------------------------------------------
?? ?SELECT STATEMENT
?? ?SORT
?? ?GROUP BY????????????????????????????????????????????????????? 1
?? ??? ??? CONCATENATION?????????????????????????????????? 1
?? ?NESTED LOOPS??????????????????????????????????? 1
?? ?TABLE ACCESS FULL???????? PLANSNET?????????????????? 1
?? ?TABLE ACCESS BY ROWID???? DETPLAN??????????????????? 2
?? ??? ??? ?? INDEX RANGE SCAN?????? DETPLAN_INDEX5???????????? 1
?? ?NESTED LOOPS
?? ?
?? ?從上面的執(zhí)行計(jì)劃中得知當(dāng)前的SQL語(yǔ)句存在表掃描現(xiàn)象。去調(diào)整該SQL語(yǔ)句，我們應(yīng)當(dāng)尋找表where 子句中為planset上的列。在這里我們
?? ?看到了在where子句存在一個(gè)且屬于表planset上的列mgc被用作連接條件。這說(shuō)明一個(gè)基于表planset.mgs列上的索引是必要的。
?? ?plan table并不能展現(xiàn)整個(gè)SQL語(yǔ)句的細(xì)節(jié)，但對(duì)于獲得數(shù)據(jù)訪問(wèn)路徑是非常有用的。SQL優(yōu)化器知道每一個(gè)表的行數(shù)(基數(shù))以及一些索引字
?? ?段的狀況。但并不了解數(shù)據(jù)的分布象如一個(gè)組件期待返回的行數(shù)。
3、調(diào)整SQL語(yǔ)句
?? ?對(duì)于那些存在可優(yōu)化的子執(zhí)行計(jì)劃，SQL應(yīng)當(dāng)按照下面的方式進(jìn)行調(diào)整。
?? ? 通過(guò)添加提示來(lái)修改SQL的執(zhí)行計(jì)劃
?? ? 使用全局臨時(shí)表來(lái)重寫(xiě)SQL
?? ? 使用PL/SQL來(lái)重寫(xiě)SQL。 對(duì)于一些特定查詢(xún)?cè)摲椒軌蛴?0倍左右的提升。將這些SQL封裝到包含存儲(chǔ)過(guò)程的包中去完成查詢(xún)。
?? ? 使用提示來(lái)調(diào)整SQL
?? ?大多數(shù)SQL tuning工具中使用較多的莫過(guò)于使用提示。一個(gè)提示添加的SQL語(yǔ)句后使得SQL查詢(xún)的按指定路徑訪問(wèn)。
?? ?Troubleshooting tip!
?? ?為便于測(cè)試，我們能夠隨時(shí)使用alter session命令來(lái)修改一個(gè)優(yōu)化參數(shù)的值來(lái)觀察調(diào)整前后的結(jié)果比較。使用新的 opt_param 提示能獲得
?? ?同樣的效果。
?? ?
?? ?select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .
??? select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .
?? ?Oracle 發(fā)布了大量的SQL提示，而且提示隨著Oracle版本的不同不斷的增強(qiáng)和復(fù)雜化。
?? ? 注意：提示通常用于調(diào)試SQL，最佳的辦法是調(diào)整優(yōu)化器的統(tǒng)計(jì)信息使的CBO模式自動(dòng)獲取最佳執(zhí)行路徑，等同于使用提示的功能。
?? ?我們來(lái)看看提高性能最常用的提示
?? ??? ?Mode hints:? first_rows_10, first_rows_100
?? ??? ?Oracle leading and ordered hints? Also see how to tune table join order with histograms
?? ??? ?
?? ??? ?Dynamic sampling: dynamic_sampling
?? ??? ?
?? ??? ?Oracle SQL undocumented tuning hints - Guru's only
?? ??? ?The cardinality hint?? ?
?? ?
?? ? 表連接順序
?? ???? 當(dāng)表連接的順序可優(yōu)化時(shí)，我們可以使用 ORDERED提示來(lái)強(qiáng)制表按照f(shuō)rom子句中出現(xiàn)的先后順序來(lái)進(jìn)行連接
?? ?
?? ?first_rows_n提示
?? ???? Oracle 有兩個(gè)基于成本優(yōu)化的提示，一個(gè)是first_rows_n，一個(gè)是all_rows。first_rows模式將盡可能在一查詢(xún)到數(shù)據(jù)時(shí)就返回個(gè)客
?? ???? 戶端。而 all_rows 模式則為優(yōu)化資源而設(shè)計(jì)，需要等到所有結(jié)果計(jì)算執(zhí)行完畢才返回?cái)?shù)據(jù)給客戶端。
?? ?
?? ??? ?SELECT /*+ first_rows */
?? ?
4、案例
?? ?同一個(gè)SQL語(yǔ)句有不同的寫(xiě)法。即簡(jiǎn)單的SQL查詢(xún)能夠以不同的方式來(lái)產(chǎn)生相同的結(jié)果集，但其執(zhí)行效率和訪問(wèn)方式則千差萬(wàn)別。
?? ?
?? ?下面的例子中的SQL語(yǔ)句使用了3種不同的寫(xiě)法來(lái)返回相同的結(jié)果
?? ?
?? ?A standard join:? -->標(biāo)準(zhǔn)連接
?? ?
?? ?SELECT *
?? ?FROM STUDENT, REGISTRATION
?? ?WHERE
?? ??? ?STUDENT.student_id = REGISTRATION.student_id
?? ?AND
?? ??? ?REGISTRATION.grade = 'A';
?? ?
?? ?A nested query:? -->嵌套查詢(xún)
?? ?SELECT *
?? ?FROM STUDENT
?? ?WHERE
?? ??? ?student_id =
?? ??? ?(SELECT student_id
?? ??? ??? ?FROM REGISTRATION
?? ??? ??? ?WHERE
?? ??? ??? grade = 'A'
?? ??? ?);
?? ?A correlated subquery:? -->相關(guān)子查詢(xún)
?? ?SELECT *
?? ?FROM STUDENT
?? ?WHERE
?? ??? ?0 <
?? ??? ?(SELECT count(*)
?? ??? ??? ?FROM REGISTRATION
?? ??? ??? ?WHERE
?? ??? ??? ?grade = 'A'
?? ??? ??? ?AND
?? ??? ??? ?student_id = STUDENT.student_id
?? ??? ?);
?? ??? ?
?? ?我們應(yīng)該根據(jù)基本的SQL原則來(lái)優(yōu)化當(dāng)前的SQL語(yǔ)句。
5、書(shū)寫(xiě)高效SQL語(yǔ)句的技巧
?? ???? 下面給出一些編寫(xiě)高效SQL語(yǔ)句的總的指導(dǎo)原則，而不論Oracle優(yōu)化器選擇何種優(yōu)化模式。這些看是簡(jiǎn)單的方式但是按照他們
?? ?去做將收到事半功倍的效果(已經(jīng)在實(shí)踐中被證實(shí))。
?? ??? ?
?? ? a.使用臨時(shí)表重寫(xiě)復(fù)雜的子查詢(xún)
?? ??? ?Oracle 使用全局臨時(shí)表以及WITH操作符去解決那些復(fù)雜的SQL子查詢(xún)。尤其是那些where子句中的子查詢(xún)，SELECT 字句標(biāo)量子查詢(xún)，
?? ??? ?FROM 子句的內(nèi)聯(lián)視圖。使用臨時(shí)表實(shí)現(xiàn)SQL tuning(以及使用WITH的物化視圖)能夠使得性能得以驚人的提升。
?? ??? ?
?? ? b.使用MINUS 代替EXIST子查詢(xún)
?? ??? ?使用MINUS操作代替NOT IN 或NOT EXISTS將產(chǎn)生更高效的執(zhí)行計(jì)劃(譯者按：此需要測(cè)試)。
?? ??? ?
?? ? c.使用SQL分析函數(shù)
?? ??? ?Oracle 分析函數(shù)能夠一次提取數(shù)據(jù)來(lái)做多維聚合運(yùn)算(象ROLLUP,CUBE)以提高性能。
?? ??? ?
?? ? d.重寫(xiě)NOT EXISTS和查詢(xún)作為外部連接NOT EXISTS 子查詢(xún)
?? ??? ?在一些案例中的NOT 查詢(xún)(where 中一個(gè)列被定義為NULL值)，能夠?qū)⑵涓膶?xiě)這個(gè)非相關(guān)子查詢(xún)到IS NULL 的外部鏈接。如下例：
?? ??? ?select book_key from book
?? ??? ?where
?? ??? ?book_key NOT IN (select book_key from sales);
?? ??? ?下面我們?cè)趙here子句中使用了外部連接來(lái)替代原來(lái)的not exits，得到一個(gè)更高效的執(zhí)行計(jì)劃。
?? ??? ?select b.book_key from book b, sales s
?? ??? ?where
?? ??? ??? b.book_key = s.book_key(+)
?? ??? ?and
?? ??? ??? s.book_key IS NULL;
?? ? e.索引NULL值列
?? ??? ?如果你的SQL語(yǔ)句頻繁使用到NULL值，應(yīng)當(dāng)考慮基于NULL值創(chuàng)建索引。為使該查詢(xún)最優(yōu)化，可以創(chuàng)建一個(gè)使用基于NULL值索引函數(shù)。
?? ??? ?(譯者按，如 create index i_tb_col on tab(nvl(col,null)); create index i_tb_col on tab(col,0);)
?? ? f.避免基于索引的運(yùn)算
?? ??? ?不要基于索引列做任何運(yùn)算，除非你創(chuàng)建了一個(gè)相應(yīng)的索引函數(shù)。或者重設(shè)設(shè)計(jì)列以使得where子句列上的謂詞不需要轉(zhuǎn)換。
?? ??? ?-->下面都是低效的SQL寫(xiě)法
?? ??? ?where salary*5??????????? > :myvalue? ?
?? ??? ?where substr(ssn,7,4)???? = "1234"
?? ??? ?where to_char(mydate,mon) = "january"
?? ?g.避免使用NOT IN 和HAVING
?? ??? ?在合適的時(shí)候使用not exists子查詢(xún)更高效。
?? ? h.避免使用LIKE謂詞
?? ??? ?在合適地時(shí)候，如果能夠使用 = 運(yùn)算應(yīng)盡可能避免LIKE操作。
?? ? i.避免數(shù)據(jù)類(lèi)型轉(zhuǎn)換
?? ??? ?如果一個(gè)where 子句列是數(shù)字型，則不要使用引號(hào)。而對(duì)一個(gè)字符索引列，總是使用引號(hào)。下面是數(shù)據(jù)類(lèi)型混用的情形。
?? ??? ?where cust_nbr = "123"
?? ??? ?where substr(ssn,7,4) = 1234
?? ? j.使用decode與case
?? ??? ?使用decode 與case 函數(shù)能夠最小化查詢(xún)表的次數(shù)。
?? ? k.不要害怕全表掃描
?? ??? ?并不是所有的OLTP系統(tǒng)在使用索引時(shí)是最優(yōu)化的。如果你的查詢(xún)返回了表中的絕大部分?jǐn)?shù)據(jù)，則全表掃描性能優(yōu)于索引掃描。這取決于
?? ???? 一些因素包括你的配置(db_file_multiblock_read_count, db_block_size)，并行查詢(xún)，以及表塊和索引塊在buffer cache中的數(shù)量。
?? ? l.使用別名
?? ??? ?在參照列的地方總是使用表別名。
??? --> Author : Robinson Cheng
?? ?--> Blog?? : http://blog.csdn.net/robinson_0612
四、結(jié)論：
?? ?這篇文章從整體上描述SQL tuning的一些步驟，并未涉及SQL tuning的具體細(xì)節(jié)。更多參考： "Oracle Tuning: The Definitive Reference"
??? 原文鏈接： http://www.dba-oracle.com/art_sql_tune.htm
五、更多參考：父游標(biāo)、子游標(biāo)及共享游標(biāo)
dbms_xplan之display_cursor函數(shù)的使用
使用 EXPLAIN PLAN 獲取SQL語(yǔ)句執(zhí)行計(jì)劃
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
