v$db_object_cache

最新推荐文章于 2024-08-26 21:11:57 发布

weixin_30519071

最新推荐文章于 2024-08-26 21:11:57 发布

阅读量92

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/hehe520/archive/2009/12/17/6330641.html

版权

官方文档上面说 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里面，这里不再贴出。