Oracle 11.2.0.1.0下ACS测试

最新推荐文章于 2021-09-04 09:56:43 发布
最新推荐文章于 2021-09-04 09:56:43 发布
阅读量1.1k 收藏
点赞数
分类专栏： Oracle SQL优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/stevendbaguo/article/details/41847829
版权

   在版本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

深圳gg
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
关于Oracle ACS 服务的基本介绍
Simon0Ouyang的博客
12-26 4807
前言   最近应聘Oracle支持服务职位，想详细了解Oracle ACS 服务，但是发现互联网上的相关中文资料比较少，本文根据Oracle网站及互联网上的资料汇编而成，详细信息请参考链接。   什么是Oracle ACS   Oracle ACS 是Oracle Advanced Customer Support (甲骨文高级客户服务）的简称，这项服务由全球拥有数千名经验丰富的高级
Oracle Adaptive Cursor Sharing(ACS)
dbhang的博客
08-31 369
Adaptive Cursor Sharing(ACS) 解决了绑定变量窥探问题 开启自适应游标共享后测试绑定变量窥探: 确保开启自适应共享游标相关参数: alter system set "_optim_peek_user_binds"=true scope=spfile; alter system set "_optimizer_adaptive_cursor_sharing"=TRUE scope=spfile; alter system set "_optimizer_extended_c
探秘oracle11g acs (adaptiver cursor sharing)
Vincent的专栏
08-04 1388
一、什么是ACS(adaptiver cursor sharing) Oracle通过绑定变量技术解决了SQL语句硬解析过多的问题，降低了资源的争用。但是绑定变量在引入cursor sharing，增加了软解析的同时，也带来了CBO环境下的bind peeking问题。 所谓bind peeking是指，oracle在第一次解析sql的时候，会“偷偷”地(peek)查看一下输入的绑定变量值
Oracle Acs资深顾问罗敏 老罗技术核心感悟：11g的数据压缩技术
chairrat的专栏
09-04 842
作者为: SHOUG成员 – ORACLE ACS高级顾问罗敏 以下是各种表压缩算法特性的进一步描述： IT系统数据量与日俱增，对IT系统设计、开发、运维各方面人员都带来了巨大挑战。除在硬件层面不断进行投入和加强之外，在软件层面一个重要的应对策略就是数据压缩技术。但是，以本人的经历，国内Oracle数据库却很少采用数据压缩技术，这是为什么呢？Oracle 11g在数据压缩领域有什么新的发展？各种压缩算法的压缩比如何？压缩对性能到底有什么影响？如何合理运用11g压缩技术和管理压缩数据？ 本章.
关于oracle 11g acs的一点总结：
cpda53349199的博客
09-14 278
今天谈谈下面这几个参数对数据库性能和稳定性的影响： cursor_sharing：游标共享 _optim_peek_user_binds：绑定变量窥视 _optimizer_adapti...
oracle11g 新特征,重温Oracle11g新特点ACS
weixin_31002075的博客
04-12 321
1.ACS简介Oracle Database 11g提供了Adaptive Cursor Sharing(ACS)功能，以克服以往不该共享的游标被共享的可能性。ACS使用两个新指标：sensitivityandbindawareness来实施该特点。2.ACS机制2.1.Adaptive Cursor Sharing Metadata：Oracle 11g也提供了三个新视图和...
【转自Oracle ACS--惜总】绕过win中服务开启oracle数据库
msdnchina的专栏@JiNan,ShanDong
04-21 1276
绕过win中服务开启oracle数据库 发表于 2011 年 12 月 19 日 由 惜分飞 联系：手机(13429648788) QQ(107644445) 链接：http://www.xifenfei.com/2218.html 标题：绕过win中服务开启oracle数据库 作者：惜分飞©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任
oracle 11g 变量窥视和acs最佳实践
cpda53349199的博客
12-21 246
select t.column_name, t.num_distinct, ...
oracle 自适应游标,Oracle自适应游标共享
weixin_39757893的博客
04-06 405
自适应游标共享(下面简称为ACS)是Oracle 11g引入的新特性。目的是为了解决具有绑定变量的sql当相关列数据分布倾斜时不能根据绑定值生成恰当的执行计划的问题。这句话有点长，拆解开来说明：首先绑定变量的引入降低了硬解析，每次都直接使用第一次绑定变量窥视产生的执行计划。但这带来了一个问题，就是当列数据倾斜时，该执行计划很可能并不合理，造成执行效率低下。为了解决以上问题，Oracle引入了ACS...
【Oracle 优化器】自适应游标共享(Adaptive Cursor Sharing)功能
SQLplusDB的小院
06-30 8552
本文主要介绍Oracle优化器的自适应游标共享(Adaptive Cursor Sharing)功能和处理流程。
Oracle 11g 新特性 -- 自适应游标共享(Adaptive Cursor Sharing： ACS) 详解
rede
03-25 933
Oracle 11g 新特性 -- 自适应游标共享(Adaptive Cursor Sharing： ACS) 详解 版权声明： https://blog.csdn.net/tianlesoftware/article/details/7573502 一．自适应游标共享（Adaptive Cursor Sharing） 说明 1.1 ACS概述 绑定变量使Oracle DB 可以为多条S...
关于修改数据库参数的测试
congbao6525的博客
10-07 361
在性能调优的时候，会发现很多类型的问题，有些问题可能通过使用隐含参数就能够解决，不过这种变更需要特别注意，因为做隐含参数的变更无形中会影响到其它的sql语句运行。如果为了个别的问题而做了变更，导致了整个系统问题，那就得不偿...
Oracle gcs,acs,ssc
msdnchina的专栏@JiNan,ShanDong
03-29 3300
Oracle gcs,acs,ssc
查看oracle隐含参数
u010692693的专栏
05-08 736
本文参考http://blog.chinaunix.net/uid-20682026-id-3215008.html   SQL> col name format a30; SQL> col value format a10; SQL> col pdesc format a30; SQL> SQL> select a.ksppinm name, b.ksppstvl value, a
今天问了ORACLE ACS们9i 和 10gV$SQL在功能上的不同之处
Netcaster 的冥想空间
09-04 1024
 大家都知道v$SQL在9i和10g其实执行结果是不同的，但ORACLE在这功能上区别的描述，没有在任何文档中出现。其实对于我们客户是十分不对的。 今天我问了ORACLE ACS支持（每年可是花了不少钱在那里），他们说X$表是不公开的，我和他们说V$SQL是DBA常用的VIEW，很多产品已经监测SCRIPT都将会因为这一变化而显示不正确的结果，变化没有说ORACLE不对，但
浅谈Oracle RAC --概要
Yamainu
03-19 2205
浅谈Oracle RAC 一提到数据库，很多同学第一个反应就是装数据用的。然后有的同学会想起一大堆的SQL文，第一范式第二范式等等，搞得头都大了。 的确，在大学阶段我们所了解的数据库都局限在SQL编程的领域。回想起自己的大学时代，下载个MySQL，写几条SQL文，用Java开发个简单的图书借阅系统，然后给老师演示一下，老师给打个分数，自己的数据库课程大作业就算完成了。但是数据库到底是个什么样的存在，即便毕业当程序员多年后也是一头雾水。 后来进入甲骨文，专门从事Oracle数据库技术支持，对于数据库才有了清晰
Oracle 11g 新特性 -- 自适应游标共享(Adaptive Cursor Sharing： ACS) 说明
weixin_30802171的博客
05-16 241
一．自适应游标共享（Adaptive Cursor Sharing） 说明1.1 ACS概述绑定变量使Oracle DB 可以为多条SQL 语句共享单个游标，以减少分析SQL 语句所使用的共享内存量。然而，游标共享和SQL 优化是两个相互冲突的目标。用文字编写SQL 语句为优化程序提供了更多的信息，这无疑会导致更好的执行计划，但大量的硬分析会导致内存和CPU 开销增加。Oracle9i...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值