关于v$db_object_cache

关于对象缓存大小的视图有多个,介绍如下.

1,DBA_OBJECT_SIZE
给出了各种PL/SQL对象的大小.

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
SQL>
SQL> select type,count(*) from dba_object_size
  2  group by type
  3  order by 2;
 
 
TYPE            COUNT(*)
------------- ----------
VIEW                   6
JAVA SOURCE            8
TABLE                 13
SEQUENCE             171
TYPE BODY            175
TRIGGER              181
JAVA DATA            306
FUNCTION             327
JAVA RESOURCE        770
PACKAGE BODY         940
PROCEDURE            988
PACKAGE             1003
TYPE                1875
JAVA CLASS         16425
 
14 rows selected
 
SQL> desc dba_object_size
Name        Type         Nullable Default Comments                                                                                                                                                                                                     
----------- ------------ -------- ------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OWNER       VARCHAR2(30)                  Owner of the object                                                                                                                                                                                          
NAME        VARCHAR2(30)                  Name of the object                                                                                                                                                                                           
TYPE        VARCHAR2(13) Y                Type of the object: "TYPE", "TYPE BODY", "TABLE", "VIEW", "SYNONYM",
                                             "SEQUENCE", "PROCEDURE", "FUNCTION", "PACKAGE", "PACKAGE BODY", "TRIGGER",
                                             "JAVA SOURCE", "JAVA CLASS", "JAVA RESOURCE" or "JAVA DATA"
SOURCE_SIZE NUMBER       Y                Size of the source, in bytes.  Must be in memory during compilation, or
                                             dynamic recompilation                                                                                                                 
PARSED_SIZE NUMBER       Y                Size of the parsed form. of the object, in bytes.  Must be in memory when
                                             an object is being compiled that references this object                                                                              
CODE_SIZE   NUMBER       Y                Code size, in bytes.  Must be in memory when this object is executing                                                                                                                                        
ERROR_SIZE  NUMBER       Y                Size of error messages, in bytes.  In memory during the compilation of the object when there are compilation errors                                                                                          

 

2,V$SUBCACHE
该视图列示了当前装载到库缓存(library cache)中的子缓存(subordinate caches),每个子缓存一行.

SQL> desc v$subcache
Name       Type           Nullable Default Comments
---------- -------------- -------- ------- --------
OWNER_NAME VARCHAR2(64)   Y                 Owner of object containing these cache entries       
NAME       VARCHAR2(1000) Y                 Object Name       
TYPE       NUMBER         Y                 Object Type       
HEAP_NUM   NUMBER         Y                 Heap number containing this subordinate cache       
CACHE_ID   NUMBER         Y                 Subordinate cache ID       
CACHE_CNT  NUMBER         Y                 Number of entries for this cache in this object       
HEAP_SZ    NUMBER         Y                 Amount of extent space allocated to this heap       
HEAP_ALOC  NUMBER         Y                 Amount of extent space allocated from this heap       
HEAP_USED  NUMBER         Y                 Amount of space utilized in this heap       
 
SQL>
SQL> col owner_name for a10
SQL> col name for a15
SQL> col type for a3
SQL> col heap_num for a4
SQL> col cache_id for a3
SQL> col cache_cnt for a4
SQL> col cahce_heap_sz for a6
SQL> col heap_aloc for a6
SQL> col heap_used for a6
SQL> select * from v$subcache where name='TAB$' order  by cache_id;
 
OWNER_NAME NAME            TYP HEAP CAC CACH    HEAP_SZ HEAP_A HEAP_U
---------- --------------- --- ---- --- ---- ---------- ------ ------
SYS        TAB$              2    8   0   37       6432   5280   4812
SYS        TAB$              2    9   1    1       1072    212    188
SYS        TAB$              2    9   2    0       1072    212    188
SYS        TAB$              2    8   3    1       6432   5280   4812
SYS        TAB$              2   10   4   14      32264  31028  30848
SYS        TAB$              2   10   5    0      32264  31028  30848
SYS        TAB$              2    8  35    0       6432   5280   4812
SYS        TAB$              2    8  44    0       6432   5280   4812
SYS        TAB$              2    9  46    0       1072    212    188
SYS        TAB$              2    8  50    0       6432   5280   4812
SYS        TAB$              2    8  51    0       6432   5280   4812
SYS        TAB$              2    8  53    0       6432   5280   4812
SYS        TAB$              2    9  57    0       1072    212    188
SYS        TAB$              2    8  80    0       6432   5280   4812
 
14 rows selected

3,V$DB_OBJECT_CACHE
3.1介绍
该视图给出缓存在库缓存(library cache)中的数据库对象，包括表、索引、聚簇、PL/SQL存储过程/包/触发器。

SQL> desc v$db_object_cache
Name          Type           Nullable Comments
------------- -------------- -------- --------
OWNER         VARCHAR2(64)   Y        Owner of the object        
NAME          VARCHAR2(1000) Y        Name of the object        
DB_LINK       VARCHAR2(64)   Y        Database link name, if any        
NAMESPACE     VARCHAR2(28)   Y        Library cache namespace of the object: TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT         
TYPE          VARCHAR2(28)   Y        Type of the object: INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK         
SHARABLE_MEM  NUMBER         Y        Amount of sharable memory in the shared pool consumed by the object         
LOADS         NUMBER         Y        Number of times the object has been loaded. This count also increases when an object has been invalidated.         
EXECUTIONS    NUMBER         Y        Not used.See Also: "V$SQLAREA" to see actual execution counts       
LOCKS         NUMBER         Y        Number of users currently locking this object        
PINS          NUMBER         Y        Number of users currently pinning this object        
KEPT          VARCHAR2(3)    Y        (YES | NO) Depends on whether this object has been "kept" (permanently pinned in memory) with the PL/SQL procedure DBMS_SHARED_POOL.KEEP         
CHILD_LATCH   NUMBER         Y        Child latch number that is protecting the object         
INVALIDATIONS NUMBER         Y        Total number of times objects in the namespace were marked invalid because a dependent object was modified         
 

SQL> select namespace,kept,count(*) from v$db_object_cache
  2  group by namespace,kept
  3  order by 1,2;
 
NAMESPACE            KEPT    COUNT(*)
-------------------- ----- ----------
BODY                 NO            40
CLUSTER              NO             2
CLUSTER              YES            6
CURSOR               NO          2572
CURSOR               YES          537
INDEX                NO            11
INDEX                YES            7
INVALID NAMESPACE    NO            15
INVALID NAMESPACE    YES            9
JAVA RESOURCE        NO             6
JAVA SOURCE          NO             6
PUB_SUB              NO             6
RSRC CONSUMER GROUP  NO             2
RSRC CONSUMER GROUP  YES            2
RSRC PLAN            NO             2
RULESET              NO             1
SUBSCRIPTION         NO             1
TABLE/PROCEDURE      NO           895
TABLE/PROCEDURE      YES           26
TRIGGER              NO            17
 
20 rows selected

SQL> col namespace for a20
SQL> col type for 10
SQL> col sharable_mem for a10
SQL> col loads for a5
SQL> col executions for a5
SQL> col locks for a5
SQL> col pins for a5
SQL> col kept for a5
SQL> col child_latch for a5
SQL> col invalidations for a5
SQL> select namespace,
  2         type,
  3         sharable_mem,
  4         loads,
  5         executions,
  6         locks,
  7         pins,
  8         kept,
  9         child_latch,
 10         invalidations
 11    from v$db_object_cache
 12   where wner = 'SYS'
 13     and name = 'TAB$';
 
NAMESPACE            TYPE   SHARABLE_M LOADS EXECU LOCKS  PINS KEPT  CHILD INVAL
-------------------- ------ ---------- ----- ----- ----- ----- ----- ----- -----
TABLE/PROCEDURE      TABLE         344    14     0     4     0 YES       2     0

==〉该视图给出了某个对象是否keep在内存中（KEPT列）。

3.2 测试dbms_shared_pool.keep
SQL> select namespace,
  2           type,
  3           sharable_mem,
  4           loads,
  5           executions,
  6           locks,
  7           pins,
  8           kept,
  9           child_latch,
 10           invalidations
 11      from v$db_object_cache
 12     where wner = 'SYS'
 13       and name = 'DBMS_OUTPUT';
 
NAMESPACE            TYPE          SHARABLE_M LOADS EXECU LOCKS  PINS KEPT  CHILD INVAL
-------------------- ------------- ---------- ----- ----- ----- ----- ----- ----- -----
BODY                 PACKAGE BODY        8543     7  3500    23     0 NO        4     0
TABLE/PROCEDURE      PACKAGE            16735     9     0    23     0 NO        5     0

SQL> exec dbms_shared_pool.keep('SYS.DBMS_OUTPUT');
 
begin dbms_shared_pool.keep('SYS.DBMS_OUTPUT'); end;
 
ORA-06550: line 2, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored

==>dbms_shared_pool.keep看来需要sysdba权限
 

SQL> conn / as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as mh@DEV95 AS SYSDBA
 
SQL> exec dbms_shared_pool.keep('SYS.DBMS_OUTPUT');
 
PL/SQL procedure successfully completed
 
SQL>
SQL> select namespace,
  2           type,
  3           sharable_mem,
  4           loads,
  5           executions,
  6           locks,
  7           pins,
  8           kept,
  9           child_latch,
 10           invalidations
 11      from v$db_object_cache
 12     where wner = 'SYS'
 13       and name = 'DBMS_OUTPUT';
 
NAMESPACE            TYPE          SHARABLE_M LOADS EXECU LOCKS  PINS KEPT  CHILD INVAL
-------------------- ------------- ---------- ----- ----- ----- ----- ----- ----- -----
BODY                 PACKAGE BODY        8543     7  3530    21     0 YES       4     0
TABLE/PROCEDURE      PACKAGE            16735     9     0    21     0 YES       5     0

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  1220336 bytes
Variable Size             146800912 bytes
Database Buffers          369098752 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.

SQL> conn bnet/obss
Not logged on
 
SQL>
SQL> select namespace,
  2           type,
  3           sharable_mem,
  4           loads,
  5           executions,
  6           locks,
  7           pins,
  8           kept,
  9           child_latch,
 10           invalidations
 11      from v$db_object_cache
 12     where wner = 'SYS'
 13       and name = 'DBMS_OUTPUT';
 
NAMESPACE            TYPE        SHARABLE_M LOADS EXECU LOCKS  PINS KEPT  CHILD INVAL
-------------------- ----------- ---------- ----- ----- ----- ----- ----- ----- -----
TABLE/PROCEDURE      NOT LOADED           0     0     0     0     0 NO        5     0

==〉dbms_shared_pool.keep效果在实例重起后消失

3.3创建基于该视图的视图，所有人有权限select：
SQL> create user u identified by u;
 
User created
 
SQL> grant connect to u;
 
Grant succeeded
 
SQL> create view v_v$db_object_cache
  2  as
  3  select owner,name,type,sharable_mem
  4  from v$db_object_cache
  5  where sharable_mem>10000;
 
View created
 
SQL>  create public synonym sv_v$db_object_cache for bnet.v_v$db_object_cache;
 
Synonym created
 
SQL> grant select on bnet.v_v$db_object_cache to u;
 
grant select on bnet.v_v$db_object_cache to u
 
ORA-01720: grant option does not exist for 'SYS.V_$DB_OBJECT_CACHE'
 
SQL> grant select on sv_v$db_object_cache to u;
 
grant select on sv_v$db_object_cache to u
 
ORA-01720: grant option does not exist for 'SYS.V_$DB_OBJECT_CACHE'
 
SQL> conn / as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as mh@DEV95 AS SYSDBA
 
SQL>  grant select on sv_v$db_object_cache to u;
 
Grant succeeded

SQL> conn u/u
Connected.
SQL> select * from sv_v$db_object_cache where rownum<2;

OWNER   NAME                  TYPE                 SHARABLE_MEM
------- -----------------     -------------------- -------------
        table_1_9_a3d8_0_0_0  CURSOR               ##########

SQL> select * from bnet.v_v$db_object_cache where rownum<2;

OWNER   NAME                  TYPE                 SHARABLE_MEM
------- -----------------     -------------------- -------------
        table_1_9_a3d8_0_0_0  CURSOR               ##########

==〉赋予同义词上的权限与对象上的一致。
《Oracle Database SQL Reference10g Release 2 (10.2)》 --GRANT
SYNONYM PRIVILEGES
 Synonym privileges are the same as the privileges for the base object. Granting a privilege on a synonym is equivalent to granting the privilege on the base object. Similarly, granting a privilege on a base object is equivalent to granting the privilege on all synonyms for the object. If you grant to a user a privilege on a synonym, then the user can use either the synonym name or the base object name in the SQL statement that exercises the privilege.
 

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

转载于:http://blog.itpub.net/18922393/viewspace-752753/