eygle Eygle.
SQL.PLSQL 2004-09-16

关于Oracle9i的Peeking of User-Defined Bind Variables

eygle 2004-09-16 16:38
modb.pro

我们知道,由于使用绑定变量,在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 2004-09-16 16:38 评论 (2)
eygle
eygle

前Oracle ACE Director，云和恩墨创始人。技术为骨，历史为脉，人文为魂。

返回首页 →

2 Comments

zod | September 12, 2009 6:27 PM

请问盖老师
绑定变量的这个问题现在已经开始严重影响到我们的系统。

最开始所有的查询都是使用的绑定变量。随着数据量的增多，第一次窥视出来的结果已经不能满足每次查询。导致特定值时查询很快，其他值时查询慢的一沓糊涂。

为了解决这个问题。我们用动态SQL重写了大约5%的最常用查询语句。查询速度倒是提高了不少，但是用TOAD看SQL AREA HIT RATE 居然降低到了6X%。。

那么您认为这个问题在10.2.0上有什么好的解决方法呢（升级到11g就先不考虑了）

崔冑 | February 17, 2011 5:58 PM

可以动态写程序，计算统计信息的选择行，然后在执行的前加HINT。