使用Explain進(jìn)行查詢及應(yīng)用優(yōu)化
2024-07-21 02:38:02
供稿：網(wǎng)友
應(yīng)用的優(yōu)化不僅需要知道應(yīng)用作了什么，還必須知道應(yīng)用是如何工作的以及使用何種數(shù)據(jù)庫設(shè)計(jì)來支持，必須了解使用哪種類型的SQL語句，語句中表與視圖的結(jié)構(gòu)及與這些表相關(guān)的各類索引。 另外，優(yōu)化整個(gè)應(yīng)用系統(tǒng)可能并不是必需的，了解應(yīng)用的各個(gè)部分可以讓我們了解哪些部分是需要優(yōu)化的。我們將主要討論使用Oracle RDBMS提供的性能優(yōu)化工具進(jìn)行SQL級(jí)的優(yōu)化。
EXPlain可以用來迅速方便地查出對(duì)于給定SQL語句中的查詢數(shù)據(jù)是如何得到的即搜索路徑(我們通常稱為access Path)。Access Path對(duì)性能會(huì)有非常大的影響。我們將會(huì)討論各種Access Path和使用的優(yōu)缺點(diǎn)。
使用Explain
使用Explain工具需要?jiǎng)?chuàng)建Explain_plan表，這必須先進(jìn)入相關(guān)應(yīng)用表、視圖和索引的所有者的帳戶內(nèi)。Oracle的介質(zhì)中包含有執(zhí)行此項(xiàng)工作的SQL源程序，例如：
ORA_RDBMS: XPLAINPL.SQL (VMS)
$ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX)
這個(gè)SQL程序應(yīng)與catalog.sql在同一目錄，這個(gè)程序會(huì)創(chuàng)建一個(gè)名為plan_table的表，表結(jié)構(gòu)如下：
Name Type
STATEMEN_ID VARCHAR2(30)
TIMESTAMP DATE
REMARKS VARCHAR2(80)
OperaTION VARCHAR2(30)
OPTIONS VARCHAR2(30)
Object_node VARCHAR2(128)
Object_owner VARCHAR2(30)
Object_name VARCHAR2(30)
Object_instance NUMBER(38)
Object_type VARCHAR2(30)
Search_columns NUMBER(38)
ID NUMBER(38)
PARENT_ID NUMBER(38)
POSITION NUMBER(38)
OTHER LONG
這里介紹一些我們將會(huì)討論的column的主要概念。假如需要每一個(gè)column的具體介紹，請看explain.doc文件。
STATEMENT_ID：為一條指定的SQL語句確定特定的執(zhí)行計(jì)劃名稱。假如在EXPLAN PLAN語句中沒有使用SET STATEMENT_ID，那么此值會(huì)被設(shè)為NULL。
OPERATION：在計(jì)劃的某一步驟執(zhí)行的操作名稱，例如：Filters，Index，Table，Marge Joins and Table等。
OPTION：對(duì)OPERATION操作的補(bǔ)充，例如：對(duì)一個(gè)表的操作，OPERATION可能是TABLE ACCESS，但OPTION可能為by ROWID或FULL。
Object_Owner：擁有此database Object的Schema名或Oracle帳戶名。
Object_name：Database Object名
Object_type：類型，例如：表、視圖、索引等等
ID：指明某一步驟在執(zhí)行計(jì)劃中的位置。
PARENT_ID：指明從某一操作中取得信息的前一個(gè)操作。通過對(duì)與ID和PARENT_ID使用Connect By操作，我們可以查詢整個(gè)執(zhí)行計(jì)劃樹。
這個(gè)PLAN表一旦創(chuàng)建成功，用戶就可在應(yīng)用中使用EXPLAIN。使用語法如下：
EXPLAIN PLAN [ SET STATEMENT_ID [=] < string literal > ]
[ INTO < table_name > ]
FOR < sql_statement >
其中：
STATEMENT_ID是一個(gè)唯一的字符串，把當(dāng)前執(zhí)行計(jì)劃與存儲(chǔ)在同一PLAN表中的其它執(zhí)行計(jì)劃區(qū)別開來。
TABLE_NAME是plan表名，它結(jié)構(gòu)如前所示，你可以任意設(shè)定這個(gè)名稱。
SQL_STATEMENT是真正的SQL語句。
例如：
EXPLAIN PLAN
SET STATEMENT_ID=‘QUERY1’
FOR
SELECT
FROM EMP
WHERE DEPTNO=10;
執(zhí)行后將會(huì)得到以下信息：
operation 50 sUCceeded
注重，假如在Explain語句中忽略INTO句，則EXPLAIN會(huì)使用PLAN_TABLE作為表名，我們可以用查詢plan table的方法來檢查執(zhí)行計(jì)劃，如：
SELECT OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, ID, PARENT_ID
FROM PLAN_TABLE
WHERE STATEMENT_ID=‘QUERY1’
ORDER BY ID;
將會(huì)返回如下：
OPERATION OPTION Object_name Object_type ID Parent_ID
TABLE ACCESS FULL EMP TABLE 1 1
1 row selected
這意味在這個(gè)查詢中將會(huì)使用全表掃描，假如在EMP表上沒有創(chuàng)建索引，對(duì)EMP的所有查詢都將使用全表掃描，但是假如在DEPTNO列上創(chuàng)建一個(gè)非唯一的索引：
CREATE INDEX EMP_IDX ON EMP(DEPTNO);
現(xiàn)在，假如我們重新解釋查詢：
EXPLAIN PLAN
SET STATEMENT_ID＝’QUERY2’
FOR
SELECT *
FROM EMP
WHERE DEPTNO=10;
然后檢查計(jì)劃表：
SELECT OPERATION， OPTIONS， OBJECT_NAME，OBJECT_TYPE，ID，PARENT_ID
FROM PLAN_TABLE
WHERE STATEMENT_ID=‘QUERY2’
ORDER BY IB;
將返回：
OPERATION OPTION Object_name Object_type ID Parent_ID
TABLE ACCESS BY RAWID EMP TABLE 1
INDEX RANGE SCAN EMP_IDX NON_UNIQUE 2 1
2 row selected
這樣，我們可以看到索引EMP_IDX被用于得到所有DEPTNO等于10的行，然后根據(jù)ROWID取得數(shù)據(jù)，索引存儲(chǔ)了表中每行的ROWID，每當(dāng)在索引中找到一行，就會(huì)根據(jù)ROWID去查詢該行的其余部分。
假如是對(duì)一個(gè)很大的表的操作，這樣的搜索路徑較前一種(全表掃描)會(huì)對(duì)減少磁盤 I / O 操作有明顯的效果。但是，假如索引是“低選擇性的”，那么一個(gè)全表掃描可能會(huì)更有效。
考慮以下的查詢及其執(zhí)行計(jì)劃：
EXPLAIN PLAN
SET STATEMENT_ID=‘QUERY3’
FOR
SELECT DEPTNO
FROM EMP
WHERE DEPTNO=10
執(zhí)行路徑的計(jì)劃是：
OPERATION OPTION Object_name Object_type ID Parent_ID
INDEX RANGE_SCAN EMP_IDX NON_UNIQUE 1
以上的執(zhí)行計(jì)劃表示不需在table中取得數(shù)據(jù)，此查詢只須使用索引。
EXPLAIN搜索路徑解釋
任何SQL語句的執(zhí)行計(jì)劃都遵循一些優(yōu)化原則，這些原則在Oracle數(shù)據(jù)庫治理員手冊中有具體介紹。同時(shí)，這些原則也被列在文本100040.163中。這些原則都試圖在從數(shù)據(jù)庫取出數(shù)據(jù)時(shí)找出一條最佳搜索路徑。一旦優(yōu)化器評(píng)估過查詢并確定了搜索路徑，優(yōu)化器就會(huì)創(chuàng)建一個(gè)執(zhí)行計(jì)劃樹。我們可以使用SQL*Plus查詢plan table從而看到執(zhí)行計(jì)劃樹：
COLUMN plan FORMAT a70
select lpad (‘ ‘, 3*level) operation (‘ options ’) ‘ object_name ‘ ‘ object_type
from plan_table
connect by PRior id=parent_id and statement_id=‘ & stmt_id’;
例如下面這個(gè)查詢
SELECT ENAME
FROM EMP
WHERE DEPTNO=10
ORDER BY ENAME;
使用以上SQL語句檢查plan table的結(jié)果是：
SORT ( ORDER BY )
TABLE ACCESS ( BY ROWID ) EMP
INDEX ( RANGE SCAN ) EMP_IDX N0N_UNIQUE
這個(gè)執(zhí)行計(jì)劃樹表示在EMP_IDX索引上執(zhí)行一個(gè)索引掃描，然后ENAME數(shù)據(jù)被按照ROWID從表中取了出來，最后這些數(shù)據(jù)被ORDER BY操作歸類。假如EMP表大的話，那么這個(gè)執(zhí)行計(jì)劃樹的最后一步可能花較長的時(shí)間。
假設(shè)我們解釋如下查詢：
select deptno, ename
from emp
where deptno between 1 and 30
order by deptno;
那么執(zhí)行樹為：
TABLE ACCESS ( BY ROWID) EMP
INDEX (RANGE SCAN) EMP_IDX NON_UNIQUE
請注重，雖然在查詢時(shí)使用了order by，但在執(zhí)行樹中并未出現(xiàn)SORT (ORDER BY)。為什么呢？不使用SORT有二個(gè)原因：1) deptno列上已經(jīng)建立了index，已作過sort；2)deptno被定義為not null(如：DEPTNO NOT NULL NUMBER)。
假設(shè)下面這個(gè)普通的連接查詢：
SELECT *
from emp. dept
where emp.deptno=dept.deptno
and sal >5000;
執(zhí)行樹為：
NESTED LOOPS ()
TABLE ACCESS (FULL)DEPT
TABLE ACCESS (BY ROWID)EMP
INDEX (RANGE SCAN) EMP_IDX NON_UNIQUE
NESTED LOOPS意味著在一個(gè)表(DEPT)上作了一個(gè)序列查詢，同時(shí)在EMP表上的索引EMP_IDX中，每一個(gè)DEPTNO均作查找。這個(gè)查詢被稱為一個(gè)驅(qū)動(dòng)表( driving table )。在這種情況下，驅(qū)動(dòng)表是DEPT。在這種類型的連接中，驅(qū)動(dòng)表是被列在后面的表。因?yàn)閮蓚€(gè)表有相同級(jí)別的搜索路徑 (都在deptno列上有非唯一的索引) ，既然至少有一個(gè)表上的所有記錄必須被檢索，那么在一個(gè)表上執(zhí)行全表掃描，同時(shí)在另一個(gè)表的索引上尋找符合條件的記錄是比較有效的。
這種情況下，我們應(yīng)把具有最少列的表作為驅(qū)動(dòng)表放在from子句的最后，注重，在這種類型的連接中，from子句中表的先后次序決
