ITPub博客

首页 > 数据库 > Oracle > Oracle+高性能SQL引擎剖析:SQL优化与调优机制详解-笔记之执行计划(一)

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

原创 Oracle 作者:nathanzhn 时间:2014-10-29 19:49:22 1 删除 编辑
笔记所在章节:第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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-01-23

  • 博文量
    49
  • 访问量
    263425