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

您可以按照以下步骤引用本文.本站收到您的引用通知后, 将自动链接您的文章, 以方便别人阅览 .

1. 启动您自己的博客管理页面, 并进入发表新文章的画面, 输入文章的内容. (如果您是ITPUB的博客请点这里.)

2. 复制下面虚线框里的连接字串, 把它们粘贴到您的文章中, 按照您的喜好修改一下表示文字.

3. 确认您选择了"发送引用通知"的选项.

4. 发表您的文章.

好啦, 您的文章就可以被自动链接到本站啦.