官方文档上面说 V$DB_OBJECT_CACHE displays database objects that are cached in the library cache.
下面做个试验:
session a:
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 104857600 bytes
Fixed Size 1289172 bytes
Variable Size 71304236 bytes
Database Buffers 25165824 bytes
Redo Buffers 7098368 bytes
数据库装载完毕。
数据库已经打开。
session b:
SQL> select count(*) from test2;
COUNT(*)
----------
0
session a:
SQL> col name format a30
SQL> select name,executions,pins,kept,sharable_mem from v$db_object_cache where name='select count(*) from test2';
NAME EXECUTIONS PINS KEP SHARABLE_MEM
------------------------------ ---------- ---------- --- ------------
select count(*) from test2 1 0 NO 7480
select count(*) from test2 0 0 NO 1436
session b:
SQL> /
COUNT(*)
----------
0
session a:
SQL> select name,executions,pins,kept,sharable_mem from v$db_object_cache where name='select count(*) from test2';
NAME EXECUTIONS PINS KEP SHARABLE_MEM
------------------------------ ---------- ---------- --- ------------
select count(*) from test2 2 0 NO 7480
select count(*) from test2 0 0 NO 1436
SQL> select sql_text,executions,version_count from v$sqlarea where sql_text='select count(*) from test2';
SQL_TEXT EXECUTIONS VERSION_COUNT
------------------------------ ---------- -------------
select count(*) from test2 2 1
session b:
SQL> alter session set optimizer_mode=rule;
会话已更改。
SQL> select count(*) from test2;
COUNT(*)
----------
0
session a:
SQL> select name,executions,pins,kept,sharable_mem from v$db_object_cache where name='select count(*) from test2';
NAME EXECUTIONS PINS KEP SHARABLE_MEM
------------------------------ ---------- ---------- --- ------------
select count(*) from test2 2 0 NO 7480
select count(*) from test2 1 0 NO 7480
select count(*) from test2 0 0 NO 1436
SQL> select sql_text,executions,version_count from v$sqlarea where sql_text='select count(*) from test2';
SQL_TEXT EXECUTIONS VERSION_COUNT
------------------------------ ---------- -------------
select count(*) from test2 3 2
注意观察红色部分，为什么一条SQL执行了会在v$db_object_cache中记录从来不执行(executions=0)的信息呢?在pub上面看见有人说这个是一个cusor的parent,
我也赞同此观点。另外从上面的实验可以看出，一条SQL的child cursor 越多，越耗资源，还可以看出version_count=所有child cursor之和。
关于v$db_object_cache的脚本放在 userful script里面，这里不再贴出。