_optimizer_invalidation_periond导致收集统计信息后执行计划没有改变

最新推荐文章于 2024-05-22 22:19:13 发布
最新推荐文章于 2024-05-22 22:19:13 发布
阅读量135 收藏
点赞数
文章标签： 数据库

一生产库在早上9：30的时候，出现大量的read by other session 等待事件，经查原因是一条sql统计信息不准走全表扫描导致，对sql进行收集统计信息后，执行计划没有立即改变，最后通过重建 索引使执行计划发生改变，后续分析是由于当时脚本里面忘记加no_invalidate=>false。在不加这参数时CBO默认是按照_optimizer_invalidation_periond指定的时间去生成新的执行计划。

-------采样时间


 

-------top event


 

 

------top sql

 


 

 

------分析当时数据库占用资源较多的sql语是sql_id: b0qf7znnrz64c

 

------执行计划走的是动态采样

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  b0qf7znnrz64c, child number 0

-------------------------------------

SELECT NVL(SUM(NVL("A1"."CHARGE",0)),0) FROM "ACCT_ITEM_AGGR_11309" "A1" WHERE

"A1"."ACCT_ID"=:LACCTID

 

Plan hash value: 1549402607

 

-------------------------------------------------------------------------------------------

| Id  | Operation          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                      |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |                      |     1 |    26 |            |          |

|*  2 |   TABLE ACCESS FULL| ACCT_ITEM_AGGR_11309 |     1 |    26 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

 

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

 

   1 - SEL$1

   2 - SEL$1 / A1@SEL$1

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("A1"."ACCT_ID"=:LACCTID)

 

Column Projection Information (identified by operation id):

-----------------------------------------------------------

 

   1 - (#keys=0) SUM(NVL("A1"."CHARGE",0))[22]

   2 - "A1"."CHARGE"[NUMBER,22]

 

Note

-----

-          dynamic sampling used for this statement

 

 

--------查看执行计划生成的日期

 

SQL> select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),PLAN_HASH_VALUE FROM DBA_HIST_SQL_PLAN WHERE sql_id='b0qf7znnrz64c';

 

TO_CHAR(TIMESTAMP,' PLAN_HASH_VALUE

------------------- ---------------

2013-09-01 00:00:12      1549402607

2013-09-01 00:00:12      1549402607

2013-09-01 00:00:12      1549402607                             =====è说明在2013-09-01 00:00:12 时候就已经保持着错误的执行计划。

2013-09-02 10:50:04      2969810046                             =====》收集完统计信息，重建索引后，生成的执行计划。

2013-09-02 10:50:04      2969810046

2013-09-02 10:50:04      2969810046

2013-09-02 10:50:04      2969810046

 

7 rows selected.

 

SQL> select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),plan_hash_value from v$sql_plan where sql_id='b0qf7znnrz64c';

 

TO_CHAR(TIMESTAMP,' PLAN_HASH_VALUE

------------------- ---------------

2013-09-02 10:50:04      2969810046                              =====》目前正在使用的执行计划。

2013-09-02 10:50:04      2969810046

2013-09-02 10:50:04      2969810046

2013-09-02 10:50:04      2969810046

 

 

 

------查看sql语句的最后一次执行时间

SQL> select child_number,parse_calls,executions,first_load_time,last_load_time,last_active_time from v$sql where sql_id='b0qf7znnrz64c';

 

 

CHILD_NUMBER PARSE_CALLS EXECUTIONS    FIRST_LOAD_TIME           LAST_LOAD_TIME       LAST_ACTIVE_TIME

------------ ----------- ---------- ------------------------------ -------------------------------------- -------------------            ----------------------------

           0       21831      21831            2013-09-01/00:00:12          2013-09-02/10:50:04         2013-09-02 13:35:46

 

 

 

 

--------查看表的统计信收集情况（发现表没有收集统计信息）

SQL> select owner,table_name,LAST_ANALYZED,NUM_ROWS,SAMPLE_SIZE from dba_tables where table_name='ACCT_ITEM_AGGR_11309';

 

OWNER                          TABLE_NAME                     LAST_ANALYZED         NUM_ROWS SAMPLE_SIZE

------------------------------ ------------------------------ ------------------- ---------- -----------

BILL                           ACCT_ITEM_AGGR_11309

 

-------查看表的索引情况（表列ACCT_ID）上有索引但没有走索引）

select INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name='ACCT_ITEM_AGGR_11309'；

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------

BILL                           PK_ACCT_ITEM_AGGR_11309        BILL                           ACCT_ITEM_AGGR_11309           ACCT_ITEM_ID                                 1

BILL                           IDX_ACCT_ITEMAGGR_SERVID_11309 BILL                           ACCT_ITEM_AGGR_11309           SERV_ID                                      1

BILL                           IDX_ACCT_ITEMAGGR_ACCTID_11309 BILL                           ACCT_ITEM_AGGR_11309           ACCT_ID

 

 

-----收集表的统计信息（发现还是走以前的执行计划）

BEGIN

dbms_stats.gather_table_stats(ownname => 'BILL',

    tabname =>'ACCT_ITEM_AGGR_11309', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,

             method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

            

END;

/

 

 

Plan hash value: 1549402607

 

-------------------------------------------------------------------------------------------

| Id  | Operation          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                      |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |                      |     1 |    26 |            |          |

|*  2 |   TABLE ACCESS FULL| ACCT_ITEM_AGGR_11309 |     1 |    26 |     2   (0)| 00:00:01 |

 

---------查看参数

SQL>

SQL>

SQL> show parameter db_file_multiblock_read_count

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_file_multiblock_read_count        integer     32                                  ======》正常

SQL>

SQL>

SQL>

SQL>

SQL> show parameter optimizer_index_cost_adj

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_index_cost_adj             integer     50                                  =====》默认值100，目前值为50说明本库应该倾向走索引。

SQL>

 

 

--------查看隐含参数（

SQL>  SELECT a.ksppinm, b.ksppstvl, b.ksppstdf

  2  FROM x$ksppi a, x$ksppcv b

  3  WHERE a.indx = b.indx

  4  AND a.ksppinm like '%_optimizer_invalidation_period%'

  5  ORDER BY a.ksppinm;

 

KSPPINM

--------------------------------------------------------------------------------

KSPPSTVL

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

KSPPSTDF

---------

_optimizer_invalidation_period                ==========》收集统计信息后，重新生成新的执行计划的时间是 18000/60/60=5小时

18000

TRUE

 

Note :

 

     因此判断可以说明为什么重新对表收集统计信息后，执行计划没有改变。

     重新收集统计时候时，不会重新生成执行计划，（收集就（默认）重新生成执行计划是在10g之前的版本 ，mos的文章做为依据）

要想重新生成执行计划，必须在收集统计信息时加一个参数 no_invalidate=>false ；

 

 

--------查看绑定变量值占用整个表的百分比

 

Sql_text= SELECT NVL(SUM(NVL("A1"."CHARGE",0)),0) FROM "ACCT_ITEM_AGGR_11309" "A1" WHERE "A1"."ACCT_ID"=:LACCTID;

 

 

SQL> SELECT SQL_ID,NAME,DATATYPE,DATATYPE_STRING,LAST_CAPTURED,VALUE_STRING,VALUE_ANYDATA FROM DBA_HIST_SQLBIND WHERE SQL_ID='b0qf7znnrz64c' AND LAST_CAPTURED > to_date('2013-09-01 00:00:12','yyyy-mm-dd hh24:mi:ss');

 

SQL_ID        NAME              DATATYPE DATATYPE_STRING LAST_CAPTURED       VALUE_STRING      VALUE_ANYDATA()

------------- --------------- ---------- --------------- ------------------- ----------------------------------- -----------------------------------

b0qf7znnrz64c :LACCTID                 2 NUMBER          2013-09-02 09:48:54 240000234632                        ANYDATA()

b0qf7znnrz64c :LACCTID                 2 NUMBER          2013-09-02 08:01:18 240003213596                        ANYDATA()

b0qf7znnrz64c :LACCTID                 2 NUMBER          2013-09-02 06:57:07 240000213902                        ANYDATA()

b0qf7znnrz64c :LACCTID                 2 NUMBER          2013-09-02 08:47:41 240003460468                        ANYDATA()

b0qf7znnrz64c :LACCTID                 2 NUMBER          2013-09-02 05:56:46 240000210185                        ANYDATA()

b0qf7znnrz64c :LACCTID                 2 NUMBER          2013-09-02 10:38:46 240002501392                        ANYDATA()

 

6 rows selected.

 

从中无法确定在第一次生成执行计划（'2013-09-01 00:00:12）时的变量值。

 

SQL> col acct_id for 999999999999999

SQL> select * from (select acct_id,count(acct_id) from bill.ACCT_ITEM_AGGR_11309 group by acct_id order by 2 desc) where rownum<20 ;

 

         ACCT_ID COUNT(ACCT_ID)

---------------- --------------

    240000191649           5760

    240002326438           5473

    240002253886           5349

    240002135954           5343

    240003435040           4733

    240002207037           4503

    240002212124           3750

    240002139158           3682

    240002144620           3619

    240002326542           3194

    240002189931           3152

    240002188438           3084

    240001085504           2773

    240002392050           2610

    240002211354           2150

    240000203644           2030

    240001904525           1952

    240002212358           1701

    240001245355           1532

 

19 rows selected.

 

SQL> select (5760/count(1))*100||'%'  from bill.ACCT_ITEM_AGGR_11309;

 

(5760/COUNT(1))*100||'%'

-----------------------------------------

.073427410503332151186018401215019678801%                  ==========è字段的最大值占整个字段的0.073%

 

 

---------最大值生成执行计划

SQL> explain plan for SELECT NVL(SUM(NVL(A1.CHARGE,0)),0) FROM BILL.ACCT_ITEM_AGGR_11309 A1 WHERE A1.ACCT_ID='240000191649';

 

Explained.

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2969810046

 

---------------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                                |     1 |    11 |     4   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |                                |     1 |    11 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| ACCT_ITEM_AGGR_11309           |     7 |    77 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | IDX_ACCT_ITEMAGGR_ACCTID_11309 |     7 |       |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("A1"."ACCT_ID"=240000191649)

 

15 rows selected.

 

 

SQL> explain plan for SELECT /*+ dynamic_sampling(A1) */ NVL(SUM(NVL(A1.CHARGE,0)),0) FROM BILL.ACCT_ITEM_AGGR_11309 A1 WHERE A1.ACCT_ID='240000191649';

 

Explained.

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2969810046

 

---------------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                                |     1 |    11 |     4   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |                                |     1 |    11 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| ACCT_ITEM_AGGR_11309           |     7 |    77 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | IDX_ACCT_ITEMAGGR_ACCTID_11309 |     7 |       |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("A1"."ACCT_ID"=240000191649)

 

15 rows selected

 

 

 

Note:

   采样分析和收集统信息分析的结果都是使用索引。说明当时应该跟统计信息没有关系。

 

 

 

---------重建表索引

执行计划发生改变

SQL_ID  b0qf7znnrz64c, child number 0

-------------------------------------

SELECT NVL(SUM(NVL("A1"."CHARGE",0)),0) FROM "ACCT_ITEM_AGGR_11309" "A1" WHERE "A1"."ACCT_ID"=:LACCTID

 

Plan hash value: 2969810046

 

---------------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                                |       |       |     4 (100)|          |

|   1 |  SORT AGGREGATE              |                                |     1 |    11 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| ACCT_ITEM_AGGR_11309           |     7 |    77 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | IDX_ACCT_ITEMAGGR_ACCTID_11309 |     7 |       |     2   (0)| 00:00:01 |

 

 

SELECT NVL(SUM(NVL("A1"."CHARGE",0)),0) FROM "ACCT_ITEM_AGGR_11309" "A1" WHERE "A1"."ACCT_ID"=:LACCTID

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29446986/viewspace-1173559/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29446986/viewspace-1173559/

cuiwuzha3492
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
pso.zip_PSO_PSo- optimizer_in_swarm
09-24
Particle swarm optimizer, in matlab
_optimizer_invalidation_period Rolling Cursor Invalidations
rose的专栏
09-27 1016
dbms_stats包分析表，no_invalidate参数有3个选项： TRUE : 不让相关游标失效 FALSE: 立即让相关游标失效 AUTO_INVALIDATE(default):让Oracle自己决定何时让游标失效。 10G后如果不指
_optimizer_invalidation_period参数的作用
cotchte0421的博客
05-05 216
no_invalidate Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent c...
dbms_stats与执行计划的合理性plan__optimizer_invalidation_period
03-26 127
老外关于dbms_stats统计收集与执行计划变动问题http://gavinsoorma.com/2012/09/why-do-my-execution-plans-not-change-after-gathering-sta...
_optimizer_invalidation_period参数,11G以后不起作用了？
cotchte0421的博客
09-22 251
select count(*) from foo; alter system set "_optimizer_invalidation_period" = 100 scope=memory; exec dbms_stats....
重新收集统计信息之后 执行计划不改变
ruilinruirui
08-03 418
ETL coder发信来说 某个ETL job 跑了450分钟还没完，叫我check一下 SQL&gt; select * from v$version where rownum=1; BANNER ------------------------------------------------------------------------------ Oracle Database 1...
path_optimizer:车辆的实时路径规划
05-07
path_optimizer 该ROS软件包为非完整车辆生成了可行的路径。模拟视频（1）（2）运行演示0.安装依赖项并进行构建Ubuntu 16.04上的ROS动力学OpenCV 3 mkdir -p workspace/src && cd workspace/srcgit clone git@github...
glsl-optimizer-master.zip_glsl 算法_glsl 算法优化_glsl_optimizer js_g
09-23
GLSL优化算法，用c++语言编写，适合学习计算数学的和工科类的运用
New-folder.rar_Ant Lion_Ant lion optimizer_lion_lion algorithm
07-14
ROULETTE WHEEL SELECTION TO SELECT ANT-LION IN ANT-LION OPTIMIZER ALGORITHM
PyTorch中model.zero_grad()和optimizer.zero_grad()用法
09-16
主要介绍了PyTorch中model.zero_grad()和optimizer.zero_grad()用法，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧
mysql事务(原理)
m0_74347016的博客
05-19 585
mysql事务原理
实战项目技术点（1）
qq_60396437的博客
05-15 1156
分页插件PageHelper 是 Mybatis 的一款功能强大、方便易用的分页插件，支持任何形式的单标、多表的分页查询。官网：https://pagehelper.github.io/。文件上传，是指将本地图片、视频、音频等文件上传到服务器，供其他用户浏览或下载的过程。
Oracle表空间管理
weixin_46593978的博客
05-19 684
假设某张表的PCTFREE为20，PCTUSED为40，这就表示当一个BLOCK的空间使用率达到80%（100-PCTFREE）时，这个BLOCK就不再被允许INSERT新的数据了，而保留下来的这20%（PCTFREE）空间将会被预留为UPDATE可能发生的空间扩展，同时该BLOCK从FREE LIST中移除。需要注意的是，Oracle 9i创建表空间时默认使用手动段管理方式，Oracle 9i及之前的低版本中自动段管理方式还有较多BUG，所以不推荐使用。此外只有本地管理的表空间才能使用自动段管理方式。
Unity 编辑器AssetDatabase用法详解
明明明的博客
05-16 387
Unity 编辑器AssetDatabase用法详解
面试???
gailun66的博客
05-15 194
事实表可以存储完整的业务信息,但是查询慢(数据量越来越大),更新效率低(如果某个网站的属性发生了变更,需要对历史的全量数据更新)当你在网站中点击鼠标时,会产生一个记录你当前点击产生的数据的表格,这几个字段。将事实表进行拆解,做成维度表,缓解上述两种问题,并对事实表进行补充说明,就可以将事实表拆分成多个维度表和一个事实表。观察事实表可以看出某些字段的。
生产报表工具PI DataLInk 与 行列视之间的区别
2401_83701843的博客
05-17 255
行列视，全名“行列视生产数据应用系统”，行列视品牌所面对的业务范围远远超越了PIDatalink报表工具，他首先是一款本地部署的在线Excel文档管理系统，解决个人报表在线管理问题，其次，她和PIDataLInk一样，也是面向实时数据库报表需求的一套工具，其区别在于行列视品牌兼容国内外大多数实时数据库，也可以同时连接多个数据源，也就是她完全可以替代PI-DataLINk，同时也适用于其他品牌下的实时数据库系统；
MySQL存储过程添加二十万条数据日常练习
mi_mx的博客
05-17 311
【代码】MySQL日常练习。
【微服务全家桶】-实用篇-2-Docker
bblb的博客
05-16 1129
【微服务全家桶】-实用篇-2-Docker
《Python编程从入门到实践》day36
最新发布
m0_37565294的博客
05-22 784
我们要编写一个名为“学习笔记”的Web应用程序，让用户能够记录感兴趣的主题，并在学习每个主题的过程中添加日志条目。django-admin startproject learning_log . # 新建一个名为learning_log的项目，末尾的句点让新项目使用合适的目录结构。运行命令ls，其输出表明Django有创建一个db.sqlite3.SQLite使用一种使用单个文件的数据库。# 包含4个文件（settings.py、urls.py、wsgi.py、__init__.py）
from timm.optim import create_optimizer_v2, create_optimizer
05-30
这行代码导入了timm库中的 `create_optimizer_v2` 和 `create_optimizer` 方法，这两个方法是用来创建优化器的。在深度学习中，优化器是训练模型时必不可少的工具，它可以帮助我们自动调整模型中的参数，使得模型在训练过程中能够更快地收敛并获得更好的性能。 其中，`create_optimizer_v2` 方法是 timm 0.4.12 版本引入的，是一个更新的版本，它支持更多的优化器和超参数，并且可以更加灵活地进行自定义。而 `create_optimizer` 方法是旧版本的方法，它支持的优化器和超参数比较有限，但是对于一些简单的场景也可以使用。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值