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