在Oracle 10g中DBMS_STATS包针对GATHER_TABLE/INDEX_STATS和DELETE_TABLE/INDEX_STATS等收集统计信息的存储过程提供了AUTO_INVALIDATE选项； 该参数允许用户指定是否让那些对统计信息有依存关系的游标失效，举例来说如果SQL游标涉及到的表，索引，列或固有对象的统计信息收到以上存储过程修改时，使用NO_INVALIDATE选项可以指定是否让这些受到影响的游标失效，何时失效。 NO_INVALIDATE选项可以有以下三种值:
当统计信息为DBMS_STATS包所修改，新的尚未在共享池中缓存的游标将直接使用这些统计信息； 对于已经存在的共享池中游标缓存，我们无法在原始子游标的基础上更新它们的执行计划；这些旧的子游标将被新的参考最新统计信息的子游标替代，这个过程包含一次硬解析以便获得新的优化树和执行计划；换而言之传统的立即游标失效(Immediate Cursor Invalidation)就是在统计信息更新后立即导致原始子游标的失效，而我们所说的滚动游标失效(Rolling Cursor Invalidations)是在统计信息成功更新的前提下保证原始子游标不立即失效；设想如果系统中有一张业务相关表，一旦我们更新了该表的统计信息可能导致大量共享失效，短期内硬解析将十分频繁并占用大量cpu，而且很多时候我们并不期望执行计划有显著变化；为了防止dbms_stats包统计信息时不要越帮越忙，就可以考虑到使用NO_INVALIDATE选项。 我们来看看RCI的具体表现:
- TRUE : 不让相关游标失效
- FALSE: 立即让相关游标失效
- AUTO_INVALIDATE(default):让Oracle自己决定何时让游标失效。
|
1
2
3
4
5
6
|
-- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
-- The procedure invalidates the dependent cursors immediately
-- if set to FALSE.
-- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
-- invalidate dependend cursors. This is the default. The default
-- can be changed using set_param procedure.
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
SQL>
select
*
from
v$version;
BANNER
----------------------------------------------------------------
Oracle
Database
10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS
for
Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
/* 测试使用版本10.2.0.4 */
SQL>
create
table
MACLEAN (t1
int
);
Table
created.
SQL>
select
/* cache_me */ 1
from
MACLEAN;
no
rows
selected
SQL>
select
sql_text,sql_id,invalidations,parse_calls
from
v$sql
where
sql_text
like
'%cache_me%'
and
sql_text
not
like
'%v$sql%'
;
SQL_TEXT SQL_ID INVALIDATIONS PARSE_CALLS
-------------------------------------------------------------------------------- ------------- ------------- -----------
select
/* cache_me */ 1
from
MACLEAN 0728m2fz7yw9f 0 1
SQL>
exec
dbms_stats.gather_table_stats(
NULL
,
'MACLEAN'
,no_invalidate =>
FALSE
);
PL/SQL
procedure
successfully completed
/* 以
FORCE
VALIDATE形式强制让游标失效 */
SQL>
select
sql_text,sql_id,invalidations,parse_calls
from
v$sql
where
sql_text
like
'%cache_me%'
and
sql_text
not
like
'%v$sql%'
;
no
rows
selected
SQL>
select
/* cache_me */ 1
from
MACLEAN;
1
----------
SQL>
select
sql_text,sql_id,invalidations,parse_calls
from
v$sql
where
sql_text
like
'%cache_me%'
and
sql_text
not
like
'%v$sql%'
;
SQL_TEXT SQL_ID INVALIDATIONS PARSE_CALLS
-------------------------------------------------------------------------------- ------------- ------------- -----------
select
/* cache_me */ 1
from
MACLEAN 1q2vdmcu1pr6j 1 1
/* 可以看到这里INVALIDATIONS次数上升到1 */
SQL>
exec
dbms_stats.gather_table_stats(
NULL
,
'MACLEAN'
,no_invalidate =>
TRUE
);
PL/SQL
procedure
successfully completed
/* 当我们选择NO_INVALIDATE为
TRUE
时，游标不会因其依赖的统计信息过期而失效 */
SQL>
select
sql_text,sql_id,invalidations,parse_calls
from
v$sql
where
sql_text
like
'%cache_me%'
and
sql_text
not
like
'%v$sql%'
;
SQL_TEXT SQL_ID INVALIDATIONS PARSE_CALLS
-------------------------------------------------------------------------------- ------------- ------------- -----------
select
/* cache_me */ 1
from
MACLEAN 1q2vdmcu1pr6j 1 1
/* 当我们使用默认值又会如何呢? * /
SQL>
alter
system flush shared_pool;
System altered
SQL> col
name
for
a35;
SQL> col value
for
a20;
SQL> col describ
for
a60;
SQL>
SELECT
x.ksppinm
NAME
, y.ksppstvl VALUE, x.ksppdesc describ
2
FROM
SYS.x$ksppi x, SYS.x$ksppcv y
3
WHERE
x.inst_id = USERENV (
'Instance'
)
4
AND
y.inst_id = USERENV (
'Instance'
)
5
AND
x.indx = y.indx
6
AND
x.ksppinm
LIKE
'%_optimizer%invalid%'
;
NAME
VALUE DESCRIB
----------------------------------- -------------------- ------------------------------------------------------------
_optimizer_invalidation_period 18000
time
window
for
invalidation
of
cursors
of
analyzed objects
/* 当使用默认选项AUTO_INVALIDATE时，Oracle会在由隐式参数_optimizer_invalidation_period所指定的时间后让游标失效 */
SQL>
alter
system
set
"_optimizer_invalidation_period"
=1;
System altered.
SQL>
drop
table
MACLEAN;
Table
dropped.
SQL>
create
table
MACLEAN
AS
select
*
from
dba_objects;
Table
created.
SQL>
alter
system flush shared_pool;
System altered.
SQL>
select
count
(*)
from
MACLEAN;
COUNT
(*)
----------
50776
SQL>
select
sql_id,invalidations,parse_calls
from
v$sql
where
sql_text=
'select count(*) from MACLEAN'
;
SQL_ID INVALIDATIONS PARSE_CALLS
------------- ------------- -----------
4n922u3xbqp8d 0 1
SQL>
exec
dbms_stats.gather_table_stats(
'MACLEAN'
,
'MACLEAN'
,no_invalidate => dbms_stats.AUTO_INVALIDATE);
PL/SQL
procedure
successfully completed.
/* 实际测试中可以发现并不像预期的那样如_optimizer_invalidation_period为1则1s内生效，
大约有一个最短period在60s左右，这很令人不愉悦！
*/
SQL> !sleep 30
/* 休眠30秒 */
SQL>
select
count
(*)
from
MACLEAN;
COUNT
(*)
----------
50776
SQL>
select
sql_id,invalidations,parse_calls
from
v$sql
where
sql_text=
'select count(*) from MACLEAN'
;
SQL_ID INVALIDATIONS PARSE_CALLS
------------- ------------- -----------
4n922u3xbqp8d 0 2
4n922u3xbqp8d 0 1
SQL>
select
child_number,roll_invalid_mismatch
from
V$SQL_SHARED_CURSOR
where
sql_id=
'4n922u3xbqp8d'
;
CHILD_NUMBER R
------------ -
0 N
1 Y
/* 可以看到多出了一个子游标，child_number为1的子游标因为roll_invalid_mismatch而无法共享 */
SQL>
alter
system
set
"_optimizer_invalidation_period"
=18000;
System altered.
|
默认的AUTO_INVALIDATE选项真的能够帮到我们吗？理论上相关的游标缓存仍会在_optimizer_invalidation_period指定的时间后同时失效进而引发大规模的硬解析，似乎这一特性并不能帮助我们完全避免因游标集体失效而可能造成的性能问题，当然它仍会是有益的！
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277561
396
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
