LOADS、INVALIDATIONS and PARSE_CALLS

d

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

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

转载于:http://blog.itpub.net/104152/viewspace-140024/