關於物件快取大小的檢視有多個,介紹如下.

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 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 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.

關於v$db_object_cache

相關文章