关于Oracle9i的Peeking of User-Defined Bind Variables

原创 eygle 2004-09-16

629

我们知道,由于使用绑定变量,在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)

********************************************************************************

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

关于Oracle9i的Peeking of User-Defined Bind Variables

评论