LOADS、INVALIDATIONS and PARSE_CALLS
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
session1:
SQL> create table test(x varchar2(10));
Table created.
SQL> insert into test values('a');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
X
----------
a
SQL>
session 2:
SQL> conn / as sysdba
Connected.
SQL> select sql_text,version_count,loads,invalidations,parse_calls
2 from v$sqlarea where sql_text like 'select * from test';
SQL_TEXT ERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS
------------------------------ ------------- - --------- ------------- -----------
select * from test 1 5 4 1
session 1:
SQL> select * from test;
X
--------------------------------------------------------------------------------
a
session 2:
SQL> /
SQL_TEXT ERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS
------------------------------ ------------- - --------- ------------- -----------
select * from test 1 5 4 2
session 1:
SQL> select * from test;
X
--------------------------------------------------------------------------------
a
session 2:
SQL> /
SQL_TEXT ERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS
------------------------------ ------------- - --------- ------------- -----------
select * from test 1 5 4 3
我们在session 1将表truncate掉,并作select
SQL> truncate table test;
Table truncated.
SQL> select * from test;
no rows selected
再回到session 2观察:
SQL> /
SQL_TEXT ERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS
------------------------------ ------------- - --------- ------------- -----------
select * from test 1 6 5 1
在这里可以看到在truncate之前,只有parse_calls在增加,
但是在truncate之后. parse_calls反而还原成1了.
而loads和invalidations却各自增加了1,而loads和invalidations在truncate之前是并没有变化的.
其实这里的原因很明了.因为truncate是ddl,所以当你对一个对象作这样的操作时,
那对于该对象所有的引用都将失效,因此当truncate之后,相应的需要reloads,
以及作validate,而如果这种情况是delete,那就不一样了.来看这个例子:
session 1:
SQL> insert into test values('a');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
X
----------
a
session 2:
SQL> /
SQL_TEXT VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS
---------------------------------------- ------------- ---------- ------------- -----------
select * from test 1 7 5 7
session 1:
SQL> select * from test;
X
----------
a
session 2:
SQL> /
SQL_TEXT VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS
---------------------------------------- ------------- ---------- ------------- -----------
select * from test 1 7 5 8
session 1:
SQL> delete test;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from test;
no rows selected
SQL>
session 1:
SQL> /
SQL_TEXT VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS
---------------------------------------- ------------- ---------- ------------- -----------
select * from test 1 7 5 9
可以看到,dml是不对其有任何的影响.
再来看对表的mov操作.
session 1:
SQL> alter table test move;
Table altered.
SQL> select * from test;
no rows selected
session 2:
SQL> /
SQL_TEXT VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS
---------------------------------------- ------------- ---------- ------------- -----------
select * from test 1 8 6 1
SQL>
同样的,loads和invalidations以及parse_calls都出现了变化.
可见,在高并发的生产系统中,这样的ddl操作还是慎重为见.通常这将导致比较严重的library cache