ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【VIEW】创建视图快速获取Library Cache中超过50KB大小的数据库对象

【VIEW】创建视图快速获取Library Cache中超过50KB大小的数据库对象

原创 Linux操作系统 作者:secooler 时间:2011-08-18 23:28:47 0 删除 编辑
  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官方文档参考链接: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 --




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

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    8108187