« 在RAC环境中如何管理日志(redolog file)组 |
Blog首页
| Undo Internal的研究 »
关于Oracle9i的Peeking of User-Defined Bind Variables
我们知道,由于使用绑定变量,在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)
********************************************************************************
|
历史上的今天...
>>
2019-09-16文章:
>>
2018-09-16文章:
>>
2010-09-16文章:
>>
2009-09-16文章:
>>
2008-09-16文章:
>>
2007-09-16文章:
>>
2005-09-16文章:
By eygle on 2004-09-16 16:38 |
Comments (2) |
SQL.PLSQL | 54 |
Oracle9iSQL
请问盖老师
绑定变量的这个问题现在已经开始严重影响到我们的系统。
最开始所有的查询都是使用的绑定变量。随着数据量的增多,第一次窥视出来的结果已经不能满足每次查询。导致特定值时查询很快,其他值时查询慢的一沓糊涂。
为了解决这个问题。我们用动态SQL重写了大约5%的最常用查询语句。查询速度倒是提高了不少,但是用TOAD看SQL AREA HIT RATE 居然降低到了6X%。。
那么您认为这个问题在10.2.0上有什么好的解决方法呢(升级到11g就先不考虑了)
可以动态写程序,计算统计信息的选择行,然后在执行的前加HINT。