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官方文档参考链接：http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1083.htm
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: "V$SQLAREA" 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 --
