Oracle+高性能SQL引擎剖析：SQL优化与调优机制详解-笔记之执行计划（一）

笔记所在章节：第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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26521853/viewspace-1313761/