[转帖]Oracle 11.2.0.1.0下ACS测试_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方技术论坛-国内领先的Weblogic、Tuxedo中间件技术论坛
联动北方-国内领先的云技术服务提供商
»  游客             当前位置：  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2447 | 回复: 0   主题： [转帖]Oracle 11.2.0.1.0下ACS测试        上一篇   下一篇 
lei.shao
注册用户
等级：新兵
经验：66
发帖：1
精华：0
注册：1970-1-1
状态：离线
发送短消息息给lei.shao 加好友    发送短消息息给lei.shao 发消息
发表于： IP：您无权察看 2019-9-18 16:37:50 | [全部帖] [楼主帖] 楼主

   在版本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)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码： 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论

Weblogic中间件技术论坛

 |

Tuxedo中间件技术论坛

 |

数据库论坛

 |

Java论坛

 |

Linux/unix论坛

 |

网站地图


Powered by LandingBJ     @ 2008-2018     联动北方   
京ICP证号090597    京ICP备案09017624号    京公网安备110112000093号    
400-810-2327     Email：service@landingbj.com   

Powered by landingbj @ 2008-2015 联动北方
京ICP证号090597 京ICP备案09017624号 京公网安备110112000093号
86-27-87056338 Email：service@landingbj.com