资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/有关11g关闭自适应游标需要注意的问题整理

有关11g关闭自适应游标需要注意的问题整理

IT那活儿 / 1425人阅读
有关11g关闭自适应游标需要注意的问题整理
一. 为什么要关闭自适应游标


  • 可能会导致一定数量的硬解析

  • 可能会导致一定数量的额外的child cursor挂在同一个父游标下，这会增加软解析、软软解析时查找匹配子游标的工作量

  • 为了存储这些额外的子游标，shared pool在空间方面也会承受额外的压力，需要额外的调整shared pool的大小


二. 如何关闭

如果因为开启了自适应游标共享而导致系统产生了过多的子游标，进而导致shared pool空间紧张或者过多的mutex等待，则可以通过如下任意的一种方式来关闭自适应游标共享

  • 将隐含参数_optimizer_extended_cursor_sharing和_optimizer_extended_cursor_sharing_rel的值设为none，这样就关闭了可扩展性游标共享。一旦可扩展性游标共享被禁，所有的子游标不能再被标记为绑定敏感，而绑定敏感是绑定感知的前提条件，一旦不能被标记为绑定敏感，则后续的绑定感知就无法进行

  • 将隐含参数_optimizer_adaptive_cursor_sharing的值设置为false，一旦这个值被设置的false，子游标就不能被标记为绑定感知，也就是说自适应游标被关闭了

当启用绑定变量窥探之后，每次Oracle以硬解析的方式解析了使用绑定变量的目标SQL时，Oracle都会实际窥探（Peeking）一下对应绑定变量变量的具体输入值，并以这些值为标准，来决定这些使用了绑定变量的目标SQL的where条件中的selectivity和Cardinality的值，并根据此信息来选择该SQL的执行计划，当绑定变量的目标SQL再次执行时（软解析或者软软解析），即便此时对应绑定变量的具体输入值和之前硬解析时对应的值不同，Oracle也会沿用之前硬解析所产生的解析数和执行计划，而不会再次进行窥探的动作。


三. 案例演示

1.关闭参数

alter system set "_optimizer_adaptive_cursor_sharing"=none sid=*;
alter system set "_optimizer_extended_cursor_sharing"=none sid=*;
或者
alter system set "_optimizer_extended_cursor_sharing_rel"=false sid=*;

2.测试验证

1) 创建测试表

create table t as select * from dba_objects;

2) 创建索引

create index t_id on t(status);

3) 执行以下SQL，收集统计信息

select count(*) from t where status=INVALID;
exec dbms_stats.gather_table_stats(dbmon,t,cascade=>true,no_invalidate=>false);

4）查看表status列是否已经收集统计信息，从下面查询可以看到status已经有统计信息

SQL> select COLUMN_NAME,HISTOGRAM from dba_tab_col_statistics where owner=DBMON and table_name=T;

COLUMN_NAME                    HISTOGRAM
-------------------------------------   -------------------------
OWNER                                NONE
OBJECT_NAME                     NONE
SUBOBJECT_NAME              NONE
OBJECT_ID                            NONE
DATA_OBJECT_ID                 NONE
OBJECT_TYPE                      NONE
CREATED                               NONE
LAST_DDL_TIME                    NONE
TIMESTAMP                            NONE
STATUS                                   FREQUENCY
TEMPORARY                          NONE


COLUMN_NAME                    HISTOGRAM
--------------------------------------  --------------------------
GENERATED                           NONE
SECONDARY                           NONE
NAMESPACE                           NONE
EDITION_NAME                       NONE

5）使用绑定变量进行验证测试

var b varchar2(20);

--当我们使用值INVALID，我们肯定是认为走索引

exec :b:=INVALID;

select count(distinct object_name) from t where status=:b;

--通过SQL执行计划，确是和我们的想法是一样的

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |       |    14 (100)|          |      1 |00:00:00.01 |      96 |
|   1 |  SORT AGGREGATE                |          |      1 |      1 |    66 |            |          |      1 |00:00:00.01 |      96 |
|   2 |   VIEW                         | VW_DAG_0 |      1 |    693 | 45738 |    14   (8)| 00:00:01 |    440 |00:00:00.01 |      96 |
|   3 |    HASH GROUP BY               |          |      1 |    693 | 22176 |    14   (8)| 00:00:01 |    440 |00:00:00.01 |      96 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T        |      1 |    695 | 22240 |    13   (0)| 00:00:01 |    659 |00:00:00.01 |      96 |
|*  5 |      INDEX RANGE SCAN          | T_ID     |      1 |    695 |       |     2   (0)| 00:00:01 |    659 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------------------------------

--多次执行SQL，使该SQL为软软解析状态

select count(distinct object_name) from t where status=:b;
select count(distinct object_name) from t where status=:b;

--替换绑定变量值VALID，根据数据的分布，我们肯定认为该SQL是走全表扫描的

exec :b:=VALID;
select count(distinct object_name) from t where status=:b;

--通过SQL执行计划，我们发现和我们的判断是相反的，SQL还是走的索引。从buffers可以看到数据很大，应该走全表扫描

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |       |    14 (100)|          |      1 |00:00:00.11 |    1443 |
|   1 |  SORT AGGREGATE                |          |      1 |      1 |    66 |            |          |      1 |00:00:00.11 |    1443 |
|   2 |   VIEW                         | VW_DAG_0 |      1 |    693 | 45738 |    14   (8)| 00:00:01 |  51967 |00:00:00.10 |    1443 |
|   3 |    HASH GROUP BY               |          |      1 |    693 | 22176 |    14   (8)| 00:00:01 |  51967 |00:00:00.09 |    1443 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T        |      1 |    695 | 22240 |    13   (0)| 00:00:01 |  85956 |00:00:00.05 |    1443 |
|*  5 |      INDEX RANGE SCAN          | T_ID     |      1 |    695 |       |     2   (0)| 00:00:01 |  85956 |00:00:00.02 |     206 |
-------------------------------------------------------------------------------------------------------------------------------------

--多次执行该SQL，执行计划并没有发生改变。

select count(distinct object_name) from t where status=:b;

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |       |    14 (100)|          |      1 |00:00:00.09 |    1443 |
|   1 |  SORT AGGREGATE                |          |      1 |      1 |    66 |            |          |      1 |00:00:00.09 |    1443 |
|   2 |   VIEW                         | VW_DAG_0 |      1 |    693 | 45738 |    14   (8)| 00:00:01 |  51967 |00:00:00.09 |    1443 |
|   3 |    HASH GROUP BY               |          |      1 |    693 | 22176 |    14   (8)| 00:00:01 |  51967 |00:00:00.07 |    1443 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T        |      1 |    695 | 22240 |    13   (0)| 00:00:01 |  85956 |00:00:00.04 |    1443 |
|*  5 |      INDEX RANGE SCAN          | T_ID     |      1 |    695 |       |     2   (0)| 00:00:01 |  85956 |00:00:00.01 |     206 |
-------------------------------------------------------------------------------------------------------------------------------------

select count(distinct object_name) from t where status=:b;
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |       |    14 (100)|          |      1 |00:00:00.09 |    1443 |
|   1 |  SORT AGGREGATE                |          |      1 |      1 |    66 |            |          |      1 |00:00:00.09 |    1443 |
|   2 |   VIEW                         | VW_DAG_0 |      1 |    693 | 45738 |    14   (8)| 00:00:01 |  51967 |00:00:00.09 |    1443 |
|   3 |    HASH GROUP BY               |          |      1 |    693 | 22176 |    14   (8)| 00:00:01 |  51967 |00:00:00.07 |    1443 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T        |      1 |    695 | 22240 |    13   (0)| 00:00:01 |  85956 |00:00:00.04 |    1443 |
|*  5 |      INDEX RANGE SCAN          | T_ID     |      1 |    695 |       |     2   (0)| 00:00:01 |  85956 |00:00:00.01 |     206 |
-------------------------------------------------------------------------------------------------------------------------------------

3. 总结

简单描述：如果关闭自适应游标，含有直方图信息的列使用绑定变量，目标SQL执行多次后（该SQL为软解析或者软软解析状态），后续绑定变量无论传入任何值，都会沿用第一次执行SQL的执行计划，即使这个执行计划是错误的。和丁伟大哥观察，移动业务都是关闭的，含有直方图信息的列使用绑定变量需要注意是否存在执行计划不准确的问题。


END



更多精彩干货分享

点击下方名片关注

IT那活儿

云服务器 GPU云服务器 看apue遇到的有关问题 自适应阈值的流量算法 html的宽度自适应 购买域名应注意的问题

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129783.html

相关文章

  • Oracle Linux And Oracle Database 11g R2 Intsallati

    摘要：最近工作中用到了作为测试的数据库，在装和折腾了很久，这篇文章就这两点对大家分享一些我安装过程中遇到的问题和注意点，暂时还未配置，稍后找时间补上现在网上找一篇好的文章好难，往往要在一个话题或技术上找多方资源东拼西凑，互相借鉴才能达成目的，原因 最近工作中用到了Oracle作为Intel测试的数据库，在装Oracle Linux和Oracle Database 11g R2折腾了很久，这...

    Flands 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<