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 I

Oracle11.2.0.1.0下ACS测试(一)