Oracle11.2.0.1.0下ACS测试
在版本11.2.0.1.0,ACS(adaptiver cursor sharing)测试的结果是:
1. 在绑定窥探有效的情况下,使用绑定变量,在直方图准确的情况下,相同的SQL,CBO会对比之前的执行计划,以前执行过此SQL,如果第一次发现效率比以前低,第二次会重新生成执行计划。
2. 在绑定窥探失效的情况下,使用绑定变量,CBO完全忽略直方图。
SQL> select * from v$version; BANNER ---------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> show parameter _optimizer_adaptive_cursor_sharing; NAME TYPE VALUE ------------------------------------ ----------- ---------------------- _optimizer_adaptive_cursor_sharing boolean TRUE SQL> show parameter _optimizer_extended_cursor_sharing; NAME TYPE VALUE ------------------------------------ ----------- --------------------- _optimizer_extended_cursor_sharing string UDO _optimizer_extended_cursor_sharing_rel string SIMPLE --制造数据 drop table test purge; create table test(id number,name varchar2(10),descri varchar2(1000)); begin for i in 1 .. 100000 loop insert into test values(i,'100',lpad('a',900,'b')); end loop; commit; end; / update test set name='200' where rownum=1; commit; create index ind_name on TEST(name) nologging
;
--由于在11.2.0.1.0版本下用dbms_stats收集直方图不准确,所以用analyze
--在Oracle 11.2.0.1.0下dbms_stats.gather_table_stats收集直方图不准
SQL> analyze table test compute statistics for table for columns name size 2; --exec dbms_stats.gather_table_stats(user,'test',cascade => true,method_opt =>'for all columns size skewonly'); --数据分布 SQL> select name,count(1) from test group by name; NAME COUNT(1) ---------- ---------- 100 99999 200 1 SQL> set autotrace traceonly SQL> select * from test where name='100'; 已选择99999行。 执行计划 ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99999 | 86M| 2664 (1)| 00:00:38 | |* 1 | TABLE ACCESS FULL| TEST | 99999 | 86M| 2664 (1)| 00:00:38 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("NAME"='100')
统计信息
---------------------------------------------------------- 1 recursive calls 0 db block gets 13515 consistent gets 133 physical reads 0 redo size 1564422 bytes sent via SQL*Net to client 73663 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 99999 rows processed SQL> select * from test where name='200'; 执行计划 ---------------------------------------------------------- Plan hash value: 688048857 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 909 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 909 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_NAME | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("NAME"='200')
统计信息
---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1342 bytes sent via SQL*Net to client 337 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off
--使用绑定窥探,先使用查大部分数据的条件,可以看到两个都是全表扫描
--先看到绑定窥探是有效的
SQL> show parameter _optim_peek_user_binds; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _optim_peek_user_binds boolean TRUE SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache; SQL> var ccc varchar2(10); SQL> exec :ccc:='100'; SQL> set autotrace trace stat SQL> select * from test where name=:ccc;
已选择99999行。
统计信息
---------------------------------------------------------- 354 recursive calls 0 db block gets 13565 consistent gets 6853 physical reads 0 redo size 1564422 bytes sent via SQL*Net to client 73664 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 99999 rows processed SQL> set autotrace off SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0 SQL> select * from table(dbms_xplan.display_cursor(1301684711,0)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- HASH_VALUE 1301684711, child number 0 -------------------------------------- select * from test where name=:ccc Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2664 (100)| | |* 1 | TABLE ACCESS FULL| TEST | 99999 | 86M| 2664 (1)| 00:00:38 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("NAME"=:CCC)
第一次执行:ccc:='200'
SQL> exec :ccc:='200'; SQL> set autotrace trace stat SQL> select * from test where name=:ccc; 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 6849 consistent gets 0 physical reads 0 redo size 1342 bytes sent via SQL*Net to client 338 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0
第二次执行:ccc:='200'
SQL> exec :ccc:='200'; SQL> set autotrace trace stat SQL> select * from test where name=:ccc; 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 8 physical reads 0 redo size 1342 bytes sent via SQL*Net to client 338 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0 1301684711 1 SQL> select * from table(dbms_xplan.display_cursor(1301684711,1)); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------- HASH_VALUE 1301684711, child number 1 -------------------------------------- select * from test where name=:ccc Plan hash value: 688048857 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 909 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_NAME | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NAME"=:CCC)
--使用绑定窥探,先使用查小部分数据的条件
SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache; SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; 未选定行 SQL> exec :ccc:='200'; SQL> set autotrace trace stat SQL> select * from test where name=:ccc; 统计信息 ---------------------------------------------------------- 354 recursive calls 0 db block gets 54 consistent gets 27 physical reads 0 redo size 1342 bytes sent via SQL*Net to client 338 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0 SQL> select * from table(dbms_xplan.display_cursor(1301684711,0)); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ HASH_VALUE 1301684711, child number 0 -------------------------------------- select * from test where name=:ccc Plan hash value: 688048857 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 909 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_NAME | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NAME"=:CCC) 第一次执行ccc:='100' SQL> exec :ccc:='100'; SQL> set autotrace trace stat SQL> select * from test where name=:ccc; 已选择99999行。 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 20106 consistent gets 6884 physical reads 0 redo size 1564422 bytes sent via SQL*Net to client 73664 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 99999 rows processed SQL> set autotrace off SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0 第二次执行ccc:='100' SQL> exec :ccc:='100'; SQL> set autotrace trace stat SQL> select * from test where name=:ccc; 已选择99999行。 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 13515 consistent gets 133 physical reads 0 redo size 1564422 bytes sent via SQL*Net to client 73664 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 99999 rows processed SQL> set autotrace off SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0 1301684711 1 SQL> select * from table(dbms_xplan.display_cursor(1301684711,1)); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ HASH_VALUE 1301684711, child number 1 -------------------------------------- select * from test where name=:ccc Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2664 (100)| | |* 1 | TABLE ACCESS FULL| TEST | 99999 | 86M| 2664 (1)| 00:00:38 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME"=:CCC) --不使用绑定窥探,先使用查小部分数据的条件 SQL> alter session set "_optim_peek_user_binds"=false; SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache; SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; SQL> exec :ccc:='200'; SQL> set autotrace trace stat SQL> select * from test where name=:ccc; 统计信息 ---------------------------------------------------------- 354 recursive calls 0 db block gets 6899 consistent gets 6853 physical reads 0 redo size 1342 bytes sent via SQL*Net to client 338 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0 --看到是全表扫描,正确的是索引扫描 SQL> select * from table(dbms_xplan.display_cursor(1301684711,0)); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------- HASH_VALUE 1301684711, child number 0 -------------------------------------- select * from test where name=:ccc Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2663 (100)| | |* 1 | TABLE ACCESS FULL| TEST | 50000 | 43M| 2663 (1)| 00:00:38 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME"=:CCC) SQL> exec :ccc:='100'; SQL> set autotrace trace stat SQL> select * from test where name=:ccc; 已选择99999行。 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 13515 consistent gets 0 physical reads 0 redo size 1564422 bytes sent via SQL*Net to client 73664 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 99999 rows processed SQL> set autotrace off SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0 SQL> exec :ccc:='100'; SQL> set autotrace trace stat SQL> select * from test where name=:ccc; 已选择99999行。 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 13515 consistent gets 0 physical reads 0 redo size 1564422 bytes sent via SQL*Net to client 73664 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 99999 rows processed SQL> set autotrace off SQL> select hash_value, child_number from v$sql where sql_text = 'select * from test where name=:ccc'; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。