V$DB_OBJECT_CACHE檢視記錄了那些快取在Shared Pool的Library Cache中的資料庫物件，這些物件包括：tables、indexes、clusters、synonym definitions、PL/SQL procedures、packages和triggers等。該檢視的SHARABLE_MEM欄位記錄了佔用Shared Pool的大小。因此我們便可以使用這個檢視輕鬆的構造出查詢被快取的大資料物件的語句，進而構造一個檢視完成這個貌似複雜的任務。

1.檢視構造需求
建立檢視，這個檢視能夠獲得所有快取到Library Cache中大小在50KB以上的PACKAGE、PROCEDURE、TRIGGER、FUNCTION。

2.具體檢視構造如下
CREATE VIEW v_db_object_cache
AS
SELECT name,
       TYPE,
       sharable_mem
FROM V$DB_OBJECT_CACHE
WHERE sharable_mem > 51200
       AND type IN ('PACKAGE',
                    'PACKAGE BODY',
                    'PROCEDURE',
                    'TRIGGER',
                    'FUNCTION');

我們使用sharable_mem欄位來指定大小，使用type欄位來限制資料庫物件型別。

3.建立檢視
sys@ora10g> CREATE VIEW v_db_object_cache
2 AS
3 SELECT name,
4         TYPE,
5         sharable_mem
6    FROM V$DB_OBJECT_CACHE
7   WHERE sharable_mem > 51200
8         AND type IN ('PACKAGE',
9                      'PACKAGE BODY',
10                      'PROCEDURE',
11                      'TRIGGER',
12                      'FUNCTION');

View created.

4.查詢檢視返回結果
sys@ora10g> select * from v_db_object_cache;

NAME                           TYPE                 SHARABLE_MEM
------------------------------ -------------------- ------------
DBMS_SCHEDULER                 PACKAGE BODY                57978
STATSPACK                      PACKAGE BODY                63606
STATSPACK                      PACKAGE                     53602
DBMS_BACKUP_RESTORE            PACKAGE BODY                95547
DBMS_STATS                     PACKAGE BODY               447342
PRVT_ADVISOR                   PACKAGE                     74080
DBMS_STATS_INTERNAL            PACKAGE BODY                77487
PRVT_ADVISOR                   PACKAGE BODY                66752
DBMS_ISCHED                    PACKAGE BODY               145191
STANDARD                       PACKAGE                    438620
DBMS_RCVMAN                    PACKAGE BODY               375743
DBMS_SCHEDULER                 PACKAGE                     86422

12 rows selected.

我們需要的資訊盡收眼底。

5.有關V$DB_OBJECT_CACHE檢視的官方參考資訊
10gR2官方文件參考連結：

V$DB_OBJECT_CACHE

This view displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers.

Column	Datatype	Description
OWNER	VARCHAR2(64)	Owner of the object
NAME	VARCHAR2(1000)	Name of the object
DB_LINK	VARCHAR2(64)	Database link name, if any
NAMESPACE	VARCHAR2(28)	Library cache namespace of the object: TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT
TYPE	VARCHAR2(28)	Type of the object: INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK
SHARABLE_MEM	NUMBER	Amount of sharable memory in the shared pool consumed by the object
LOADS	NUMBER	Number of times the object has been loaded. This count also increases when an object has been invalidated.
EXECUTIONS	NUMBER	Not used See Also: to see actual execution counts
LOCKS	NUMBER	Number of users currently locking this object
PINS	NUMBER	Number of users currently pinning this object
KEPT	VARCHAR2(3)	(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	Child latch number that is protecting the object
INVALIDATIONS	NUMBER	Total number of times objects in the namespace were marked invalid because a dependent object was modified

重點關注一下V$DB_OBJECT_CACHE檢視的TYPE欄位取值範圍：INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK。

6.小結
定期檢查Library Cache的使用情況有助於我們及時發現系統存在的效能問題，做到早發現，早處理。

Good luck.

secooler
11.08.18

-- The End --

【VIEW】建立檢視快速獲取Library Cache中超過50KB大小的資料庫物件

V$DB_OBJECT_CACHE

相關文章