Oracle+高效能SQL引擎剖析：SQL優化與調優機制詳解-筆記之執行計劃（一）

nathanzhn發表於2014-10-29
OracleSQL優化筆記
筆記所在章節：第2 章　解讀執行計劃
原文：
2.1　執行計劃的基本資料 
我們用程式碼清單2-1 中的查詢計劃為例，解釋計劃訪問中基本資料的含義。 
程式碼清單2-1 執行計劃查詢 
HELLODBA.COM>exec sql_explain('select o.owner, o.object_name, o.object_id from t_users u, t_objects o 
where u.username=o.owner and o.object_name like :A and u.user_id=:B','BASIC PREDICATE');

以上操作我可以成功執行，但是我發現我的執行計劃與書中的不同，
SQL> exec sql_explain('select o.owner, o.object_name, o.object_id from t_users u, t_objects o where u.username=o.owner and o.object_name like :A and u.user_id=:B','BASIC PREDICATE');
 
Plan hash value: 1644402662
 
--------------------------------------------------------
| Id  | Operation                     | Name           |
--------------------------------------------------------
|   0 | SELECT STATEMENT              |                |
|   1 |  NESTED LOOPS                 |                |
|   2 |   TABLE ACCESS BY INDEX ROWID | T_USERS        |
|*  3 |    INDEX UNIQUE SCAN          | T_USERS_PK     |
|*  4 |   TABLE ACCESS BY INDEX ROWID | T_OBJECTS      |
|   5 |    BITMAP CONVERSION TO ROWIDS|                |
|*  6 |     BITMAP INDEX SINGLE VALUE | T_OBJECTS_IDX4 |
--------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("U"."USER_ID"=TO_NUMBER(:B))
   4 - filter("O"."OBJECT_NAME" LIKE :A)
   6 - access("U"."USERNAME"="O"."OWNER")
 
PL/SQL procedure successfully completed

書中的第四步是：
|* 4 | TABLE ACCESS FULL | T_OBJECTS |
---------------------------------------------------
但是我將sql_explain裡最後一個引數設為false： 
exec sql_explain('select o.owner, o.object_name, o.object_id from t_users u, t_objects o where u.username=o.owner and o.object_name like :A and u.user_id=:B','BASIC PREDICATE', FALSE);
會執行報錯：
ORA-01008: 並非所有變數都已繫結 
ORA-06512: 在 "SYS.DBMS_SQL", line 1587 
ORA-06512: 在 "SYS.SQL_EXPLAIN", line 32 
ORA-06512: 在 line 2

我在sqlplus中嘗試過
var A varchar2(20);
exec :A := 'T_USERS';
var B number;
exec :B := 94;

exec sql_explain('select o.owner, o.object_name, o.object_id from t_users u, t_objects o where u.username=o.owner and o.object_name like :A and u.user_id=to_number(:B)','BASIC PREDICATE', FALSE);
還是會報錯，這是什麼原因？
SQL> select * from v$version where rownum=1; 

BANNER 
-------------------------------------------------------------------------------- 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

經與作者聯絡得到的答覆：
你好！
第三個引數指定了FASLE，這表示該過程將會實際執行該語句。所以你需要在語句中使用實際值，而不是使用繫結變數。
執行計劃不同是因為在演示這個計劃是，索引T_OBJECTS_IDX4還未建立。你可以使用提示/*+full(o)*/來重現該計劃

致，
黃瑋
你好!
你是指在sqlplus中用var命令來定義變數嗎？這樣是不行的，因為這是一個儲存過程，不會從sqlplus中讀取變數的。

致，
黃瑋
-----------------------------------------------------------------------------------------------------------------------------
我就重新做了實驗：
我在一個匿名塊裡定義變數，在裡面呼叫這個SP就可以：
declare 
A varchar2(10) := 'T_USERS'; 
B varchar2(2) := '94'; 
begin 
sql_explain('select o.owner, o.object_name, o.object_id from t_users u, t_objects o where u.username=o.owner and o.object_name like '''|| A || ''' and u.user_id=' || B,'BASIC PREDICATE',FALSE); 
end; 
/
就可以成功執行：
SQL>
declare
A varchar2(10) := 'T_USERS';
B varchar2(2) := '94';
begin
sql_explain('select o.owner, o.object_name, o.object_id from t_users u, t_objects o where u.username=o.owner and o.object_name like '''|| A || ''' and u.user_id=' || B,'BASIC PREDICATE',FALSE);
end;
/
 
before parse: select o.owner, o.object_name, o.object_id from t_users u, t_objects o where u.username=o.owner and o.object_name like 'T_USERS' and u.user_id=94
EXPLAINED SQL STATEMENT:
------------------------
select o.owner, o.object_name, o.object_id from t_users u, t_objects o
where u.username=o.owner and o.object_name like 'T_USERS' and
u.user_id=94
 
Plan hash value: 1644402662
 
--------------------------------------------------------
| Id  | Operation                     | Name           |
--------------------------------------------------------
|   0 | SELECT STATEMENT              |                |
|   1 |  NESTED LOOPS                 |                |
|   2 |   TABLE ACCESS BY INDEX ROWID | T_USERS        |
|*  3 |    INDEX UNIQUE SCAN          | T_USERS_PK     |
|*  4 |   TABLE ACCESS BY INDEX ROWID | T_OBJECTS      |
|   5 |    BITMAP CONVERSION TO ROWIDS|                |
|*  6 |     BITMAP INDEX SINGLE VALUE | T_OBJECTS_IDX4 |
--------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("U"."USER_ID"=94)
   4 - filter("O"."OBJECT_NAME" LIKE 'T_USERS')
   6 - access("U"."USERNAME"="O"."OWNER")
 
 
PL/SQL procedure successfully completed

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26521853/viewspace-1313761/，如需轉載，請註明出處，否則將追究法律責任。

相關文章