|
反映SQL執(zhí)行方式的唯一證明就是執(zhí)行計劃。能夠獲得、閱讀執(zhí)行計劃是SQL調(diào)整最基本的技能。
一、獲得執(zhí)行計劃
1、Explain Plan For·····
SQL>explain plan for select ````````;
SQL>select * from table(dbms_xplan.display); 2、SET AUTOTRACE ON EXPLAIN （set autot on exp) SQLPLUS的命令，在執(zhí)行SQL語句的同時顯示執(zhí)行計劃，設(shè)置EXP(LAIN)的目的是只顯示執(zhí)行計劃而不顯示統(tǒng)計信息.。 執(zhí)行了set autotrace on explain語句之后，接下來的查詢、插入、更新、刪除語句就會顯示執(zhí)行計劃，直到執(zhí)行“set autotrace off;”語句。如果是設(shè)置了set autotrace on，除了會顯示執(zhí)行計劃之外，還會顯示一些有用的統(tǒng)計信息。set autotrace off ---------------- 不生成autotrace 報告，這是缺省模式 set autotrace on explain ------ autotrace只顯示優(yōu)化器執(zhí)行路徑報告 set autotrace on statistics -- 只顯示執(zhí)行統(tǒng)計信息 set autotrace on ----------------- 包含執(zhí)行計劃和統(tǒng)計信息 set autotrace traceonly ------ 同set autotrace on，但是不顯示查詢輸 (1). set autotrace on explain; --只顯示執(zhí)行計劃
SQL> set autotrace on explain; SQL>
3、SQL Trace和10046事件
兩者的功能一致，都是跟蹤某個session的活動情況。
10046事件包括5個級別：
0級：相當于SQL_TRACE=FALSE，禁用跟蹤功能；
1級：相當于SQL_TRACE-TRUE,起用標準跟蹤，也就等同于SQL TRACE；
4級：在1級的基礎(chǔ)上還會跟蹤綁定變量信息；
8級：在1級的基礎(chǔ)上還會跟蹤等待事件信息；
12級：在1級的基礎(chǔ)上加上綁定變量、等待事件信息，相當于1級+4級+8級。
啟用10046事件：
alter system set events='10046 trace name context forever,level 8';
alter session set events='10046 trace name context forever,level 12';
關(guān)閉跟蹤：
alter session set events='10046 trace name context off';
注：設(shè)置這個事件后，會在短時間內(nèi)產(chǎn)生大量trace文件，如不及時關(guān)閉，很容易迅速耗盡磁盤空間。
select spid from v$process where addr in (select paddr from v$session where sid=&sid);
DBA也可自定義跟蹤文件格式，以便區(qū)分：
alter session set tracefile_identifier='20100802'
這時生成的Trace文件名就是sid_ora_spid_idenfier的格式了。
使用TKPROF工具來閱讀跟蹤文件：
tkprof .trc .txt
在跟蹤文件中，最需要關(guān)注的內(nèi)容：DISK 、QUERY、CURRENT。這3列代表著Oracle的工作量，而所有的調(diào)整最終也是為了降低這3個統(tǒng)計量，而其他如CPU、ELAPSED其實都是一個間接指標。
DISK：代表物理的磁盤讀取次數(shù)。
QUERY：一致性讀，查詢語句一般會使用一致性讀。
CURRENT：當前讀，DML語句常用這種讀取。
跟蹤其他會話信息：
方法一：使用DBMS_SUPPORT包。
如果沒有預(yù)先創(chuàng)建這個包，$ORACLE_HOME/rdbms/admin/dbmssupp.sql手工創(chuàng)建。
exec dbms_support.start_trace_in_session(sid=>703,serial=>846,waits=>true,binds=>true);
sid 和serial分別對應(yīng)進程的SID和序號，可以從V$SESSION視圖獲得。而參數(shù)WAITS、BINDS如果設(shè)置成True，就是收集等待事件和綁定變量的信息。相當于12級的10046事件。
exec dbms_support.stop_trace_in_session(sid=>703,serial=>846);
方法二：oradebug
這是oracle提供的在SLQ*PLUS環(huán)境下使用的工具。使用這個工具需要提供進程的OS PID和Oracle PID。
SQL>select spid,pid from v$process where addr in (select paddr from v$session where sid=(select distinct sid from v$mystat));
SPID PID
8458 358
如果使用OS PID作為參數(shù)，需要使用setospid子命令，注意命令執(zhí)行的輸出結(jié)果會同時顯示OS PID和Oracle PID。
SQL>oradebug setospid 8458；
如果使用Oracle PID，就需要使用setorapid，注意命令提示中不再提示Oracle PID，只提示OS pid。
SQL>oradebug setorapid 358;
此時定義了要跟蹤的進程后，接下來就要定義跟蹤什么信息了，比如跟蹤10046事件，就使用下面的命令。
SQL>oradebug unlimit；
SQL>oradebug event 10046 trace name context forever,level 12；
SQL>oradebug event 10046 trace name context off;
4、從Library Cache中獲取（查詢已執(zhí)行過的SQL語句，而不需要重新執(zhí)行，直接從內(nèi)存中取出真正的執(zhí)行計劃，而非估計值）
V$SQL
V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_WORKAREA
V$SQL_PLAN_STATISTICS_ALL(該視圖是前三個視圖的一個匯總，可以獲得前3個視圖的信息)
V$SESSION
oracle 10g中：
dbms_xplan.display_cursor(sql_id => ,
cursor_child_no => ,（如沒指定，會羅列出所有的子游標的執(zhí)行計劃） format => );（最常見的內(nèi)置格式：BASIC、TYPICAL（default）、SERIAL、ALL） select * from table(dbms_xplan.display_cursor(' ',null,'TYPICAL')
5、DISPLAY_AWR
用戶通過這個包來獲取AWR中的執(zhí)行計劃，其信息來源于DBA_HIST_SQL_PLAN、DBA_HIST_SQLTEXT。
dbms_xplan.display_awr(sql_id => ,plan_hash_value => ,db_id => ,format => );
AWR產(chǎn)生的報告中會帶有SQL ID一列，把這個值作為第一個參數(shù)傳遞方法就可以獲得當時的執(zhí)行計劃。
SQL>select * from table(dbms_xplan.display_awr(' '));
二、閱讀執(zhí)行計劃
oracle執(zhí)行計劃的一些概念：
Rowid：系統(tǒng)給oracle數(shù)據(jù)的每行附加的一個偽列，包含數(shù)據(jù)表名稱，數(shù)據(jù)庫id，存儲數(shù)據(jù)庫id以及一個流水號等信息，rowid在行的生命周期內(nèi)唯一。 Recursive sql：為了執(zhí)行用戶語句，系統(tǒng)附加執(zhí)行的額外操作語句，譬如對數(shù)據(jù)字典的維護等。 Row source（行源）：oracle執(zhí)行步驟過程中，由上一個操作返回的符合條件的行的集合。 Predicate（謂詞）：where后的限制條件。 Driving table（驅(qū)動表）：又稱為連接的外層表，主要用于嵌套與hash連接中。一般來說是將應(yīng)用限制條件后，返回較少行源的表作為驅(qū)動表。在后面的描述中，將driving table稱為連接操作的row source 1。 Probed table（被探查表）：連接的內(nèi)層表，在我們從 driving table得到具體的一行數(shù)據(jù)后，在probed table中尋找符合條件的行，所以該表應(yīng)該為較大的row source，并且對應(yīng)連接條件的列上應(yīng)該有索引。在后面的描述中，一般將該表稱為連接操作的row source 2. Concatenated index（組合索引）：一個索引如果由多列構(gòu)成，那么就稱為組合索引，組合索引的第一列為引導列，只有謂詞中包含引導列時，索引才可用。 可選擇性：表中某列的不同數(shù)值數(shù)量/表的總行數(shù)如果接近于1，則列的可選擇性為高。 Oracle訪問數(shù)據(jù)的存取方法： Full table scans, FTS(全表掃描)：通過設(shè)置db_block_multiblock_read_count可以設(shè)置一次IO能讀取的數(shù)據(jù)塊個數(shù)，從而有效減少全表掃描時的IO總次數(shù)，也就是通過預(yù)讀機制將將要訪問的數(shù)據(jù)塊預(yù)先讀入內(nèi)存中。只有在全表掃描情況下才能使用多塊讀操作。 Table access by rowed（通過rowid存取表，rowid lookup）：由于rowid中記錄了行存儲的位置，所以這是oracle存取單行數(shù)據(jù)的最快方法。 Index scan（索引掃描index lookup）：在索引中，除了存儲每個索引的值外，索引還存儲具有此值的行對應(yīng)的rowid值，索引掃描分兩步1，掃描索引得到rowid；2，通過 rowid讀取具體數(shù)據(jù)。每步都是單獨的一次IO，所以如果數(shù)據(jù)經(jīng)限制條件過濾后的總量大于原表總行數(shù)的5%－10％,則使用索引掃描效率下降很多。而如果結(jié)果數(shù)據(jù)能夠全部在索引中找到，則可以避免第二步操作，從而加快檢索速度。 根據(jù)索引類型與where限制條件的不同，有4種類型的索引掃描： l Index unique scan（索引唯一掃描）：存在unique或者primary key的情況下，返回單個rowid數(shù)據(jù)內(nèi)容。 l Index range scan（索引范圍掃描）：1，在唯一索引上使用了range操作符（>,<,<>,>=,<=,between）；2，在組合索引上，只使用部分列進行查詢；3，對非唯一索引上的列進行的查詢。 l Index full scan（索引全掃描）：需要查詢的數(shù)據(jù)從索引中可以全部得到。 l Index fast full scan（索引快速掃描）：與index full scan類似，但是這種方式下不對結(jié)果進行排序。 目前為止，典型的連接類型有3種： l Sort merge join（SMJ排序－合并連接）：首先生產(chǎn)driving table需要的數(shù)據(jù)，然后對這些數(shù)據(jù)按照連接操作關(guān)聯(lián)列進行排序；然后生產(chǎn)probed table需要的數(shù)據(jù)，然后對這些數(shù)據(jù)按照與driving table對應(yīng)的連接操作列進行排序；最后兩邊已經(jīng)排序的行被放在一起執(zhí)行合并操作。排序是一個費時、費資源的操作，特別對于大表。所以smj通常不是一個特別有效的連接方法，但是如果driving table和probed table都已經(jīng)預(yù)先排序，則這種連接方法的效率也比較高。 l Nested loops（NL嵌套循環(huán)）：連接過程就是將driving table和probed table進行一次嵌套循環(huán)的過程。就是用driving table的每一行去匹配probed table 的所有行。Nested loops可以先返回已經(jīng)連接的行，而不必等待所有的連接操作處理完成才返回數(shù)據(jù)，這可以實現(xiàn)快速的響應(yīng)時間。 l Hash join（哈希連接）：較小的row source被用來構(gòu)建hash table與bitmap，第二個row source用來被hashed，并與第一個row source生產(chǎn)的hash table進行匹配。以便進行進一步的連接。當被構(gòu)建的hash table與bitmap能被容納在內(nèi)存中時，這種連接方式的效率極高。但需要設(shè)置合適的hash_area_size參數(shù)且只能用于等值連接中。 l Cartesian product（笛卡爾積）：表的每一行依次與另外一表的所有行匹配。 |
|
|
