dbms_stats与执行计划的合理性plan__optimizer_invalidation_period

最新推荐文章于 2024-11-15 18:38:47 发布
最新推荐文章于 2024-11-15 18:38:47 发布
阅读量143 收藏
点赞数
文章标签： 数据库

老外关于dbms_stats统计收集与执行计划变动问题
http://gavinsoorma.com/2012/09/why-do-my-execution-plans-not-change-after-gathering-statistics-a-look-at-rolling-cursor-invalidations/

SQL> select dbms_stats.get_prefs('NO_INVALIDATE','SYSTEM','MYOBJECTS') from dual;
 
DBMS_STATS.GET_PREFS('NO_INVAL
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

/***构建测试表并插入重复值很高的记录*******/
SQL> create table t_object(object_id int,object_type varchar2(100));
 
Table created
 
SQL> insert into t_object select level,'table' from dual connect by level<=100000;
 
100000 rows inserted
 
SQL> insert into t_object select level,'index' from dual connect by level<=3;
 
3 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> create index idx_t_object on t_object(object_type);
 
Index created
 
SQL> select object_type,count(object_id) from t_object group by object_type;
 
OBJECT_TYPE                                                                      COUNT(OBJECT_ID)
-------------------------------------------------------------------------------- ----------------
table                                                                                      100000
index                                                                                           3
 
SQL> select /*test2*/ distinct object_type from t_object where object_type='index';
 
OBJECT_TYPE
--------------------------------------------------------------------------------
index
 
SQL> select sql_text,plan_hash_value,sql_id,parse_calls,executions,address from v$sqlarea where sql_text like '%/*test2*/%';
 
SQL_TEXT                                                                         PLAN_HASH_VALUE SQL_ID        PARSE_CALLS EXECUTIONS ADDRESS
-------------------------------------------------------------------------------- --------------- ------------- ----------- ---------- ----------------
 select sql_text,plan_hash_value,sql_id,parse_calls,executions,address from v$sq       232555890 28mq12bavybz2           5          5 000007FF06134BD8
 select /*test2*/ distinct object_type from t_object where object_type='index'        3554248643 fy7xurbuv7qa2           1          1 000007FF06E96270
 
SQL> select * from table(dbms_xplan.display_cursor('fy7xurbuv7qa2',null,null));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fy7xurbuv7qa2, child number 0
-------------------------------------
 select /*test2*/ distinct object_type from t_object where
object_type='index'
Plan hash value: 3554248643
--------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |       |       |     2 (100)|
|   1 |  SORT UNIQUE NOSORT|              |     3 |   156 |     2  (50)| 00:00:0
|*  2 |   INDEX RANGE SCAN | IDX_T_OBJECT |     3 |   156 |     1   (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='index')
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
 
24 rows selected
 
SQL>
SQL>
SQL>
SQL>
SQL> update t_object set object_type='index' where object_type='table' and rownum<=90000;
 
90000 rows updated
 
SQL> commit;
 
Commit complete
 
SQL> exec dbms_stats.gather_table_stats(user,'t_object',cascade=>true);
 
PL/SQL procedure successfully completed
 
SQL> select /*test2*/ distinct object_type from t_object where object_type='index';
 
OBJECT_TYPE
--------------------------------------------------------------------------------
index
 
SQL> select sql_text,plan_hash_value,sql_id,parse_calls,executions,address from v$sqlarea where sql_text like '%/*test2*/%';
 
SQL_TEXT                                                                         PLAN_HASH_VALUE SQL_ID        PARSE_CALLS EXECUTIONS ADDRESS
-------------------------------------------------------------------------------- --------------- ------------- ----------- ---------- ----------------
 select sql_text,plan_hash_value,sql_id,parse_calls,executions,address from v$sq       232555890 28mq12bavybz2           6          6 000007FF06134BD8
 select /*test2*/ distinct object_type from t_object where object_type='index'        3554248643 fy7xurbuv7qa2           2          2 000007FF06E96270
 
SQL> select * from table(dbms_xplan.display_cursor('fy7xurbuv7qa2',null,null));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fy7xurbuv7qa2, child number 0
-------------------------------------
 select /*test2*/ distinct object_type from t_object where
object_type='index'
Plan hash value: 3554248643
--------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |       |       |     2 (100)|
|   1 |  SORT UNIQUE NOSORT|              |     3 |   156 |     2  (50)| 00:00:0
|*  2 |   INDEX RANGE SCAN | IDX_T_OBJECT |     3 |   156 |     1   (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='index')
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
 
24 rows selected

 

SQL> alter system set "_optimizer_invalidation_period" = 60 scope=memory;
 
System altered
 
SQL> exec dbms_stats.gather_table_stats(user,'t_object',cascade=>true);
 
PL/SQL procedure successfully completed
 
SQL> select /*test2*/ distinct object_type from t_object where object_type='index';
 
OBJECT_TYPE
--------------------------------------------------------------------------------
index
 
SQL> select sql_text,plan_hash_value,sql_id,parse_calls,executions,address from v$sqlarea where sql_text like '%/*test2*/%';
 
SQL_TEXT                                                                         PLAN_HASH_VALUE SQL_ID        PARSE_CALLS EXECUTIONS ADDRESS
-------------------------------------------------------------------------------- --------------- ------------- ----------- ---------- ----------------
 select sql_text,plan_hash_value,sql_id,parse_calls,executions,address from v$sq       232555890 28mq12bavybz2           7          7 000007FF06134BD8
 select /*test2*/ distinct object_type from t_object where object_type='index'        2142696267 fy7xurbuv7qa2           3          3 000007FF06E96270
 
SQL> select * from table(dbms_xplan.display_cursor('fy7xurbuv7qa2',null,null));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fy7xurbuv7qa2, child number 0
-------------------------------------
 select /*test2*/ distinct object_type from t_object where
object_type='index'
Plan hash value: 3554248643
--------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |       |       |     2 (100)|
|   1 |  SORT UNIQUE NOSORT|              |     3 |   156 |     2  (50)| 00:00:0
|*  2 |   INDEX RANGE SCAN | IDX_T_OBJECT |     3 |   156 |     1   (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='index')
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
SQL_ID  fy7xurbuv7qa2, child number 1
-------------------------------------
 select /*test2*/ distinct object_type from t_object where
object_type='index'
Plan hash value: 2142696267
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |       |       |    77 (100)|          |
|   1 |  SORT UNIQUE NOSORT|          |     1 |     6 |    77  (12)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T_OBJECT | 89844 |   526K|    70   (3)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - filter("OBJECT_TYPE"='index')
 
44 rows selected

 

SQL> select * from v$sql_shared_cursor v where v.ADDRESS='000007FF06E96270';
 
SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER UNBOUND_CURSOR SQL_TYPE_MISMATCH OPTIMIZER_MISMATCH OUTLINE_MISMATCH STATS_ROW_MISMATCH LITERAL_MISMATCH FORCE_HARD_PARSE EXPLAIN_PLAN_CURSOR BUFFERED_DML_MISMATCH PDML_ENV_MISMATCH INST_DRTLD_MISMATCH SLAVE_QC_MISMATCH TYPECHECK_MISMATCH AUTH_CHECK_MISMATCH BIND_MISMATCH DESCRIBE_MISMATCH LANGUAGE_MISMATCH TRANSLATION_MISMATCH BIND_EQUIV_FAILURE INSUFF_PRIVS INSUFF_PRIVS_REM REMOTE_TRANS_MISMATCH LOGMINER_SESSION_MISMATCH INCOMP_LTRL_MISMATCH OVERLAP_TIME_MISMATCH EDITION_MISMATCH MV_QUERY_GEN_MISMATCH USER_BIND_PEEK_MISMATCH TYPCHK_DEP_MISMATCH NO_TRIGGER_MISMATCH FLASHBACK_CURSOR ANYDATA_TRANSFORMATION INCOMPLETE_CURSOR TOP_LEVEL_RPI_CURSOR DIFFERENT_LONG_LENGTH LOGICAL_STANDBY_APPLY DIFF_CALL_DURN BIND_UACS_DIFF PLSQL_CMP_SWITCHS_DIFF CURSOR_PARTS_MISMATCH STB_OBJECT_MISMATCH CROSSEDITION_TRIGGER_MISMATCH PQ_SLAVE_MISMATCH TOP_LEVEL_DDL_MISMATCH MULTI_PX_MISMATCH BIND_PEEKED_PQ_MISMATCH MV_REWRITE_MISMATCH ROLL_INVALID_MISMATCH OPTIMIZER_MODE_MISMATCH PX_MISMATCH MV_STALEOBJ_MISMATCH FLASHBACK_TABLE_MISMATCH LITREP_COMP_MISMATCH PLSQL_DEBUG LOAD_OPTIMIZER_STATS ACL_MISMATCH FLASHBACK_ARCHIVE_MISMATCH LOCK_USER_SCHEMA_FAILED REMOTE_MAPPING_MISMATCH LOAD_RUNTIME_HEAP_FAILED HASH_MATCH_FAILED PURGED_CURSOR BIND_LENGTH_UPGRADEABLE
------------- ---------------- ---------------- ------------ -------------- ----------------- ------------------ ---------------- ------------------ ---------------- ---------------- ------------------- --------------------- ----------------- ------------------- ----------------- ------------------ ------------------- ------------- ----------------- ----------------- -------------------- ------------------ ------------ ---------------- --------------------- ------------------------- -------------------- --------------------- ---------------- --------------------- ----------------------- ------------------- ------------------- ---------------- ---------------------- ----------------- -------------------- --------------------- --------------------- -------------- -------------- ---------------------- --------------------- ------------------- ----------------------------- ----------------- ---------------------- ----------------- ----------------------- ------------------- --------------------- ----------------------- ----------- -------------------- ------------------------ -------------------- ----------- -------------------- ------------ -------------------------- ----------------------- ----------------------- ------------------------ ----------------- ------------- -----------------------
fy7xurbuv7qa2 000007FF06E96270 000007FF076257D8            0 N              N                 N                  N                N                  N                N                N                   N                     N                 N                   N                 N                  N                   N             N                 N                 N                    N                  N            N                N                     N                         N                    N                     N                N                     N                       N                   N                   N                N                      N                 N                    N                     N                     N              N              N                      N                     N                   N                             N                 N                      N                 N                       N                   N                     N                       N           N                    N                        N                    N           N                    N            N                          N                       N                       N                        N                 N             N
fy7xurbuv7qa2 000007FF06E96270 000007FF07F14448            1 N              N                 N                  N                N                  N                N                N                   N                     N                 N                   N                 N                  N                   N             N                 N                 N                    N                  N            N                N                     N                         N                    N                     N                N                     N                       N                   N                   N                N                      N                 N                    N                     N                     N              N              N                      N                     N                   N                             N                 N                      N                 N                       N                   Y                     N                       N           N                    N                        N                    N           N                    N            N                          N                       N                       N                        N                 N             N
 
 
小结：1,oracle默认在采样会让cached cursor无效,导致大量的hard parse,容易出现shared pool latch的library cache latch
      2,查阅http://space.itpub.net/?uid-9240380-action-viewspace-itemid-757204,
      　可获取_optimizer_invalidation_period参数控制：即定义分析对象的游标多久会无效，这样因为重新收集统计才会产生更为合理的执行计划
      　　time window for invalidation of cursors of analyzed objects
      3,dbms_stats包的有些参数非常重要,在使用要多加测试

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

转载于:http://blog.itpub.net/9240380/viewspace-757205/

clg10051
关注
_optimizer_invalidation_period Rolling Cursor Invalidations
rose的专栏
09-27 1051
dbms_stats包分析表，no_invalidate参数有3个选项： TRUE : 不让相关游标失效 FALSE: 立即让相关游标失效 AUTO_INVALIDATE(default):让Oracle自己决定何时让游标失效。 10G后如果不指
表分析 analyze 及dbms_stats 提高执行计划准确性
zzyang的专栏
11-20 1166
 之前遇到这样一个问题，有个表有3000W多条数据，并且每月都有300W左右的数据量在增加，同时这个表的数据经常需要查询更新和删除。由于经常进行插入，删除，更新从而导致执行计划很不准，一条很简单的链接查询都要好几分钟。稍微复杂点的查询经常超时。经过表分析后，同一条查询语句，执行计划变了不少，查询速度也提高了许多。现在将用过的几个语句抄录下来，或许对有的朋友来说用的着。一.analyze
_optimizer_invalidation_periond导致收集统计信息后执行计划没有改变
cuiwuzha3492的博客
05-31 158
一生产库在早上9：30的时候，出现大量的read by other session 等待事件，经查原因是一条sql统计信息不准走全表扫描导致，对sql进行收集统计信息后，执行计划没有立即改变，最后通过重建 索引使执...
_optimizer_invalidation_period参数的作用
cotchte0421的博客
05-05 243
no_invalidate Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent c...
_optimizer_invalidation_period参数,11G以后不起作用了？
cotchte0421的博客
09-22 272
select count(*) from foo; alter system set "_optimizer_invalidation_period" = 100 scope=memory; exec dbms_stats....
使用dbms_stats包手工收集统计信息
01-03
Oracle 数据库中使用 dbms_stats 包手动收集统计信息 在 Oracle 数据库中，dbms_stats 包提供了一种手动收集统计信息的方式，包括基于表、用户和索引的统计信息。通过使用 dbms_stats 包，我们可以手动收集统计信息...
Oracle中使用DBMS_XPLAN处理执行计划详解
12-16
DBMS_XPLAN是Oracle提供的一个用于查看SQL计划，包括执行计划和解释计划的包；在以前查看SQL执行计划的时候，我都是使用set autotrace命令，不过现在看来，DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 这5...
DBMS_STATS.GATHER_TABLE_STATS详解.pdf
02-01
`DBMS_STATS.GATHER_TABLE_STATS` 是 Oracle 数据库中的一个重要过程，主要用于收集表、列和索引的统计信息，这些统计信息对于优化器选择合适的执行计划至关重要。该过程允许数据库管理员通过一系列参数来灵活控制...
DBMS_STATS常用
isunlight001的专栏
05-02 1005
作用:DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息（默认参数下是对表进行直方图信息收集，包含该表的自身-表的行数、数据块数、行长等信息；列的分析--列值的重复数、列上的空值、数据在列上的分布情况；索引的分析-索引页块的数量、索引的深度、索引聚合因子）. DBMS_STATS.GATHER_TABLE_STATS的语法如下: DBMS_STATS.GATH...
[20120206]Cursor Invalidation与分析表.txt
02-06 118
在分析表的是否有一个参数no_invalidate：缺省值是DBMS_STATS.AUTO_INVALIDATE.AUTO_INVALIDATE。 10g中默认是AUTO_INVALIDATE，就是说分析表后，游标不会马上...
rolling invalidation对子游标产生的影响
congdingfu1756的博客
08-24 166
这两天做性能测试碰见一个问题，比较有意思。 一条SQL，使用了绑定变量，查看V$SQLAREA发现version_count是2， 查看V$SQL，发现有两条记录，分别对应了0和1两个chi...
oracle使用带参数执行计划,Oracle的执行计划查看方式 - DBMS_XPLAN及参数
weixin_35431719的博客
04-06 239
在Oracle数据库中进行SQL优化的过程中，不可避免的经常需要去查看SQL的执行计划，而DBMS_XPLAN包就是最常用的一个工具，以下简要记录一下常用的方法。DBMS_XPLAN 中最常用的两个函数是 display_cursor 和 display_awr ，前者从当前内存中获得SQL执行计划，而后者ze：FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TY...
执行计划生成及查看的几种方法
almtnwd263703的博客
07-03 273
1、生成某个会话跟踪文件 方式1：SQL_trace 一、跟踪其他会话 exec dbms_system.set_SQL_trace_in_session(41,49,true); exec dbms_system.set_SQL_trace_in_session(7,284,true); 应用执行一段时间后 exec dbms_system.set_SQL_tr...
Oracle执行计划——查看执行计划的方法
热门推荐
654657008的博客
09-12 7万+
（1）什么是执行计划 SQL是一种傻瓜式语言，每一个条件就是一个需求，访问的顺序不同就形成了不同的执行计划。Oracle必须做出选择，一次只能有一种访问路径。执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述。 （2）执行计划的选择 通常一条SQL有多个执行计划，那我们如何选择？那种执行开销更低，就意味着性能更好，速度更快，我们就选哪一种，这个过程叫做Oracle的解析过程，然后...
Mybatis
最新发布
庸不易的博客
11-15 816
1、定义不同：#{} 预处理：而 ${} 是直接替换2、使用不同：#{} 适用于所有类型的参数匹配；但 ${} 只适用于数值类型。3、安全性不同：#{} 性能高，并且没有安全问题；但 $ {} 存在 SQL 注入的安全问题。4、使用场景不同：当传递的是一个 SQL 关键字 的时候，只能 使用 ${}。当传递的是一个字段，总之，就是需要获取到参数类型 与 内容，只能使用 #{}。（PS：数字类型，#{} 和 ${} 都是可以使用的！5、 ${} 不能用于 模糊匹配查询；
【2024最新】springboot网上商城购物系统lw+ppt
man2017的博客
11-12 629
开发技术：SpringBoot、php、Python、小程序、SSM、Vue、MySQL、JSP、ElementUI等，文末源码。专栏推荐：SpringBoot项目源码、Vue项目源码、SSM项目源码、微信小程序源码精品专栏：Java精选实战项目源码、Python精选实战项目源码、大数据精选实战项目源码。
MySQL 8.0特性-自增变量的持久化
myneth的博客
11-13 567
在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。下面通过案例来对比不同的版本中自增变量是否持久化。
Oracle DBMS_XPLAN深度解析：执行与解释计划
"Oracle中使用DBMS_XPLAN处理执行计划详解，通过DBMS_XPLAN包查看SQL计划，包括执行计划和解释计划，提供更简化的方法，替代set autotrace命令。DBMS_XPLAN包的5个核心函数用于不同场景，如DISPLAY、DISPLAY_AWR、...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值