« 在RAC环境中如何管理日志(redolog file)组 |
Blog首页
| Undo Internal的研究 »
关于Oracle9i的Peeking of User-Defined Bind Variables
作者：
eygle |【转载时请务必以超链接形式标明文章
原始出处和作者信息及
本声明】
链接：
http://www.eygle.com/archives/2004/09/peeking_user_defined_bind_variables.html
我们知道,由于使用绑定变量,在Oracle9i之前会导致柱状图信息无法被用到
从Oracle9i开始Oracle提供了Peeking的方式,在使用绑定变量的SQL第一次执行时,使用参数传递成文本sql，此时可以有效的利用
存在的柱状图信息进行执行计划的评估,从而在某些数据分布不均和的情况下，可能可以产生更为精确的执行计划.这显然是一个有
益的提高,然而这个Peeking有时候也会存在问题,本文通过实例说明这个特性及其不足.
以下是完整的测试验证过程:
[oracle@jumper oracle]$ sqlplus eqsp/eqsp
SQL*Plus: Release 9.2.0.3.0 - Production on Thu Nov 20 23:13:20 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
准备试验数据
SQL>
SQL>
SQL> drop table t;
Table dropped.
SQL> create table t as select 1 id,a.* from dba_objects a;
Table created.
SQL> insert into t select * from t;
10168 rows created.
SQL> /
20336 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t;
COUNT(*)
----------
40672
SQL> update t set id = 99 where rownum <2; --构造试验数据,id=99的纪录只有一条.其余id=1
1 row updated.
SQL> commit;
Commit complete.
SQL> create index t_ind1 on t(id);
Index created.
SQL> analyze table t compute statistics for table for all indexed columns;
Table analyzed.
SQL> exit
|
测试脚本
|
[oracle@jumper oracle]$ vi eygle.sql
set term off
set head off
alter session set sql_trace = true;
select * from t where id = :v;
alter session set sql_trace = false;
~
~
~
"eygle.sql" [ò?×a??] 6L, 131C ò?D′è?
[oracle@jumper oracle]$ more eygle2.sql
set term off
set head off
alter session set sql_trace = true;
select * from t n_1 where id = :v;
alter session set sql_trace = false;
|
第一步测试:
|
SQL> var v number
SQL> exec :v :=1 ---传入参数为1 ,此时Oracle应该放弃索引,使用全表扫描
PL/SQL procedure successfully completed.
SQL> @$ORACLE_BASE/eygle.sql
[oracle@jumper udump]$ tkprof *.trc e1.log
TKPROF: Release 9.2.0.3.0 - Production on Thu Nov 20 23:49:28 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
[oracle@jumper udump]$ vi e1.log
TKPROF: Release 9.2.0.3.0 - Production on Thu Nov 20 23:49:28 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: hsjf_ora_16137.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace = true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 96
********************************************************************************
select *
from
t where id = :v
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2713 0.50 0.47 0 3239 0 40671
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2715 0.51 0.48 0 3239 0 40671
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 96
Rows Row Source Operation
------- ---------------------------------------------------
40671 TABLE ACCESS FULL T (cr=3239 r=0 w=0 time=246446 us)
-----------由于柱状图信息生效,此处使用了全表扫描
********************************************************************************
Peek here
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 13 0.00 0.00 0 0 0 0
Fetch 13 0.00 0.00 0 26 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 27 0.00 0.00 0 26 0 0
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
alter session set sql_trace = false
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
|
|
SQL> exec :v :=99 ---id=99的记录只有一条,此处应该使用索引
PL/SQL procedure successfully completed.
SQL> @$ORACLE_BASE/eygle.sql
[oracle@jumper udump]$ tkprof hsjf_ora_16137.trc e2.log
TKPROF: Release 9.2.0.3.0 - Production on Thu Nov 20 23:50:44 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
[oracle@jumper udump]$ vi e2.log
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace = true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 96
********************************************************************************
execute twice
select *
from
t where id = :v
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2715 0.55 0.52 12 3802 0 40672
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2719 0.56 0.52 12 3802 0 40672
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 96
Rows Row Source Operation
------- ---------------------------------------------------
40671 TABLE ACCESS FULL T (cr=3239 r=0 w=0 time=246446 us)
---------由于不再进行Peeking,所以这里仍然使用了全表扫描,这是错误的选择
********************************************************************************
peek once
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 13 0.00 0.00 0 0 0 0
Fetch 13 0.00 0.00 0 26 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 27 0.00 0.00 0 26 0 0
|
第二步测试
|
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> var v number
SQL> exec :v :=99
PL/SQL procedure successfully completed.
SQL> @eygle2.sql
SQL> !
|
|
[oracle@jumper udump]$ tkprof hsjf_ora_15042.trc 3.log
TKPROF: Release 9.2.0.3.0 - Production on Thu Nov 20 23:55:53 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
[oracle@jumper udump]$ vi 3.log
Optimizer goal: CHOOSE
TKPROF: Release 9.2.0.3.0 - Production on Thu Nov 20 23:55:53 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: hsjf_ora_15042.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace = true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 96
********************************************************************************
select *
from
t n_1 where id = :v
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 1 18 0 99
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.00 0.00 1 18 0 99
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 96
index scan is right here.
Rows Row Source Operation
------- ---------------------------------------------------
99 TABLE ACCESS BY INDEX ROWID T (cr=18 r=1 w=0 time=1963 us)
99 INDEX RANGE SCAN T_IND1 (cr=9 r=1 w=0 time=1264 us)(object id 13674)
********************************************************************************
|
|
SQL> exec :v :=1
PL/SQL procedure successfully completed.
SQL> @eygle2.sql
SQL> !
[oracle@jumper udump]$ tkprof hsjf_ora_15042.trc 4.log
TKPROF: Release 9.2.0.3.0 - Production on Thu Nov 20 23:59:07 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
[oracle@jumper udump]$ vi 4.log
TKPROF: Release 9.2.0.3.0 - Production on Thu Nov 20 23:59:07 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: hsjf_ora_15042.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set sql_trace = true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 96
********************************************************************************
execute twice here,use index scan
select *
from
t n_1 where id = :v
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2714 0.77 0.74 80 5988 0 40672
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2718 0.77 0.74 80 5988 0 40672
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 96
Rows Row Source Operation
------- ---------------------------------------------------
99 TABLE ACCESS BY INDEX ROWID T (cr=18 r=1 w=0 time=1963 us)
99 INDEX RANGE SCAN T_IND1 (cr=9 r=1 w=0 time=1264 us)(object id 13674)
********************************************************************************
|
By eygle on 2004-09-16 16:38 |
Comments (0) |
Posted to
SQL.PLSQL | Edit |Pageviews:
网上相关主题:
发表留言:
