www.Orasql.Com
 
用户名: 密码:  验证码: 注册|找回密码 oracleawen's blog  
 
         当前位置 >> 技术论坛  发表帖子     论坛版块     查找帖子
bind variable peeking-技术论坛||Oracle高手之路—Orasql.Com!
            bind variable peeking
 
发贴人:oracle_awen
oracleawen@gmail.com448315233127.0.0.1
用户级别:管理员
发贴总数:1245
注册时间:2011-05-05 09:23:03
2011-06-01 08:15:45
bind variable peeking 复习以下这个知识点,估计很多朋友都很熟悉这个,随便写了点,见笑。 众所周知,我们平时在sql中经常使用捆绑变量来使sql在shared pool中可以被重用,大大减少了sql的hard parse.  oracle会在hard parse一个sql的时候,如果收集了histogram的信息,如果隐藏参数_optim_peek_user_binds被设置成true(default to true),那么cbo会去偷窥一下捆绑变量的值,来选择一个更好的执行计划。当然,捆绑变量的偷窥只发生在hard parse 但是捆绑变量是有副作用的。来看一个例子 ================================================================================== 043847 oracle[sqlplus]@lnwasp1[nygespappd1]> create table test_20091117 as select from dba_objects; table created. 043913 oracle[sqlplus]@lnwasp1[nygespappd1]> select count() from test_20091117; count() ---------- 10385 1 row selected. 043932 oracle[sqlplus]@lnwasp1[nygespappd1]> update test_20091117 set status ='valid'; 10385 rows updated. 043952 oracle[sqlplus]@lnwasp1[nygespappd1]> commit; commit complete. 043956 oracle[sqlplus]@lnwasp1[nygespappd1]> select count() from test_20091117 where status='valid'; count() ---------- 10385 1 row selected. 044008 oracle[sqlplus]@lnwasp1[nygespappd1]> update test_20091117 set status='invalid' where rownum<=100; 100 rows updated. 044109 oracle[sqlplus]@lnwasp1[nygespappd1]> commit; commit complete. 044112 oracle[sqlplus]@lnwasp1[nygespappd1]> update test_20091117 set status='test' where rownum<=3; 3 rows updated. 044128 oracle[sqlplus]@lnwasp1[nygespappd1]> commit; commit complete. 044131 oracle[sqlplus]@lnwasp1[nygespappd1]> select count() from test_20091117 where status='invalid'; count() ---------- 97 1 row selected. 044143 oracle[sqlplus]@lnwasp1[nygespappd1]> select count() from test_20091117 where status='valid'; count() ---------- 10285 1 row selected. 044155 oracle[sqlplus]@lnwasp1[nygespappd1]> select count() from test_20091117 where status = 'test'; count() ---------- 3 1 row selected. 044207 oracle[sqlplus]@lnwasp1[nygespappd1]> create index idx_test_20091117 on test_20091117 (status); index created. 044234 oracle[sqlplus]@lnwasp1[nygespappd1]> exec dbms_stats.gather_table_stats('oracle','test_20091117'); pl/sql procedure successfully completed. =================================================================================== 如上,test_20091117 的status='valid'的有10285行,status='invalid'的有97行,status='test'的有三行,如果select from test_20091117 where status='valid', 那么必定是用全表扫描比较好,如果select from test_20091117 where status='invalid' 或者select from test_20091117 where status='test'那么必定是走索引比较好。 ==================================================================================== 044341 oracle[sqlplus]@lnwasp1[nygespappd1]> select from dba_histograms where table_name='test_20091117' and column_name='status'; owner table_name ------------------------------ ------------------------------ column_name ------------------------------------------------------------------------------------------------------------------------------ endpoint_number endpoint_value --------------- -------------- endpoint_actual_value ------------------------------------------------------------------------------------------------------------------------------ oracle test_20091117 status 97 3.8063e+35 oracle test_20091117 status 100 4.3756e+35 oracle test_20091117 status 10385 4.4786e+35 3 rows selected. ================================================================================ 如上,histogram里三个bucket明确表明了该列数据分布情况. ================================================================================ 044409 oracle[sqlplus]@lnwasp1[nygespappd1]> var test varchar2(10); 044708 oracle[sqlplus]@lnwasp1[nygespappd1]> exec test='valid'; pl/sql procedure successfully completed. 044745 oracle[sqlplus]@lnwasp1[nygespappd1]> select object_id,status from test_20091117 where status=test; 10285 rows selected. 045500 oracle[sqlplus]@lnwasp1[nygespappd1]> select sql_id,child_number from v$sql where sql_text like '%select object_id,status from test_20091117 where status%'; sql_id child_number ------------- ------------ 78b4w66djtj5y 0 1 row selected. 045549 oracle[sqlplus]@lnwasp1[nygespappd1]> select from table(dbms_xplan.display_cursor('78b4w66djtj5y',0,'advanced')); plan_table_output ------------------------------------------------------------------------------------------------------------------------------ sql_id 78b4w66djtj5y, child number 0 ------------------------------------- select object_id,status from test_20091117 where status=test plan hash value 2645479270 ----------------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (%cpu)| time | ----------------------------------------------------------------------------------- | 0 | select statement | | | | 41 (100)| | | 1 | table access full| test_20091117 | 10285 | 120k| 41 (3)| 000001 | ----------------------------------------------------------------------------------- peeked binds (identified by position) -------------------------------------- 1 - test (varchar2(30), csid=1) 'valid' ========================================================================================= 可以看到上面这个例子,第一次hard parse, 偷窥了绑定变量,选择了正确的full table scan, 绑定变量的值在最后,test 'valid' ========================================================================================= 050209 oracle[sqlplus]@lnwasp1[nygespappd1]> exec test='invalid'; pl/sql procedure successfully completed. 050301 oracle[sqlplus]@lnwasp1[nygespappd1]> select object_id,status from test_20091117 where status=test; 97 rows selected. 050305 oracle[sqlplus]@lnwasp1[nygespappd1]> select sql_id,child_number,last_active_time from v$sql where sql_text like '%select object_id,status from test_20091117 where status%'; sql_id child_number last_active_time ------------- ------------ ------------------- ------------------- ------------------- 78b4w66djtj5y 0 2009-11-17 050303 1 row selected. 050316 oracle[sqlplus]@lnwasp1[nygespappd1]> select from table(dbms_xplan.display_cursor('78b4w66djtj5y',0,'advanced')); plan_table_output ------------------------------------------------------------------------------------------------------------------------------ sql_id 78b4w66djtj5y, child number 0 ------------------------------------- select object_id,status from test_20091117 where status=test plan hash value 2645479270 ----------------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (%cpu)| time | ----------------------------------------------------------------------------------- | 0 | select statement | | | | 41 (100)| | | 1 | table access full| test_20091117 | 10285 | 120k| 41 (3)| 000001 | ----------------------------------------------------------------------------------- peeked binds (identified by position) -------------------------------------- 1 - test (varchar2(30), csid=1) 'valid' ======================================================================= 可以看到,虽然绑定变量的值变了,但是oracle没有硬解析,没有重新偷窥绑定变量,还是走了错误的全表扫描 ======================================================================= 050400 oracle[sqlplus]@lnwasp1[nygespappd1]> alter system flush shared_pool; system altered. ================================================================ flush shared pool, 让sql重新被硬解析 ================================================================= 050700 oracle[sqlplus]@lnwasp1[nygespappd1]> exec test='invalid'; pl/sql procedure successfully completed. 050710 oracle[sqlplus]@lnwasp1[nygespappd1]> select object_id,status from test_20091117 where status=test; 97 rows selected. 050726 oracle[sqlplus]@lnwasp1[nygespappd1]> select sql_id,child_number,first_load_time,last_load_time,last_active_time from v$sql where sql_text like '%select object_id,status from test_20091117 where status%'; sql_id child_number first_load_time last_load_time last_active_time ------------- ------------ ------------------- ------------------- ------------------- 78b4w66djtj5y 0 2009-11-17/045102 2009-11-17/050725 2009-11-17 050725 050742 oracle[sqlplus]@lnwasp1[nygespappd1]> select from table(dbms_xplan.display_cursor('78b4w66djtj5y',0,'advanced')); plan_table_output ------------------------------------------------------------------------------------------------------------------------------ sql_id 78b4w66djtj5y, child number 0 ------------------------------------- select object_id,status from test_20091117 where status=test plan hash value 2768913761 ------------------------------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (%cpu)| time | ------------------------------------------------------------------------------------------------- | 0 | select statement | | | | 2 (100)| | | 1 | table access by index rowid| test_20091117 | 97 | 1164 | 2 (0)| 000001 | | 2 | index range scan | idx_test_20091117 | 97 | | 1 (0)| 000001 | ------------------------------------------------------------------------------------------------- peeked binds (identified by position) -------------------------------------- 1 - test (varchar2(30), csid=1) 'invalid' ============================================================================= 可以看到,sql被重新hard parse后,走了正确的索引路线 ============================================================================= 如果碰到sql用了绑定变量,但是发生了如上的bind variable peeking的副作用,怎么办? 解决办法 让sql在shared pool里失效,就是比如comment一下table,或者recompile一下之类的,让cbo重新hard parse 或者dbms_shared_pool.purge,或者alter system flush shared pool. 当然,后两者不推荐,尤其是最后一个!! 不过,oracle 11g里好像可以动态偷窥绑定变量,放多个执行计划在shared pool, 对这个副作用做了很大改进。具体新特性可以去看11g文档 [ 本帖最后由 icedmocha 于 2010-11-18 0651 编辑 ]

      
 
  
 Orasql.Com|E-Mail:oracleawen@gmail.com|Phone:15271942915|Copyright © 2011|All Rights Reserved|