When you generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. Oracle also invalidates any currently parsed SQL statements that access the object.

session1：

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter system flush buffer_cache;

系统已更改。

SQL> select * from tt;

ID NAME AGE
---------- ----------------------------------- ----------
3 b 0
4 b 0
6 b 0
0 a 0

SQL> select namespace,reloads,invalidations from v$librarycache;

NAMESPACE RELOADS INVALIDATIONS
--------------- ---------- -------------
SQL AREA 240 1326
TABLE/PROCEDURE 958 0
BODY 6 0
TRIGGER 0 0
INDEX 0 0
CLUSTER 0 0
OBJECT 0 0
PIPE 0 0
JAVA SOURCE 0 0
JAVA RESOURCE 0 0
JAVA DATA 0 0

已选择11行。

SQL> select a.*,b.name
2 from v$sesstat a , v$statname b
3 where a.statistic#=b.statistic#
4 and b.name like '%parse%'
5 and a.sid=(select sid from v$mystat where rownum=1);

SID STATISTIC# VALUE NAME
---------- ---------- ---------- -----------------------------------
137 430 146 parse time cpu
137 431 479 parse time elapsed
137 432 1623 parse count (total)
137 433 557 parse count (hard)
137 434 4 parse count (failures)

为了准确观察parse的次数，减少在同一个session中analyze table tt compute statistics;所引起的干扰，另开一个session执行analyze table tt compute statistics：

session2：

SQL> analyze table tt compute statistics;

表已分析。

再回到session1：

SQL> select * from tt;

ID NAME AGE
---------- ----------------------------------- ----------
3 b 0
4 b 0
6 b 0
0 a 0

SQL> select namespace,reloads,invalidations from v$librarycache;

NAMESPACE RELOADS INVALIDATIONS
--------------- ---------- -------------
SQL AREA 331 1328
TABLE/PROCEDURE 986 0
BODY 8 0
TRIGGER 0 0
INDEX 0 0
CLUSTER 0 0
OBJECT 0 0
PIPE 0 0
JAVA SOURCE 0 0
JAVA RESOURCE 0 0
JAVA DATA 0 0

已选择11行。

SQL> select a.*,b.name
2 from v$sesstat a , v$statname b
3 where a.statistic#=b.statistic#
4 and b.name like '%parse%'
5 and a.sid=(select sid from v$mystat where rownum=1);

SID STATISTIC# VALUE NAME
---------- ---------- ---------- -----------------------------------
137 430 146 parse time cpu
137 431 479 parse time elapsed
137 432 1629 parse count (total)
137 433 558 parse count (hard)
137 434 4 parse count (failures)

SQL>

通过对比发现分析tt前后的INVALIDATIONS和parse count

都增加了，尽管执行了相同的sql：select * from tt;