11.oracle的dump理解十一 dump library_cache
欢迎转载,转载请标明出处:http://blog.csdn.net/notbaron/article/details/51284573
DUMP 库缓存相关信息。这个DUMP使用位图策略。
命令如下:
SQL> oradebug dump library_cache 1
支持多个等级DUMP
等级1:关键结构的性能汇总
等级2:HASH CHAIN统计
等级4:持有对象结构的哈希桶链,可以看到一个对象的lock,pin,mutex信息。
等级8:4+相关数据块
等级16:8+每个对象的堆
等级32:16+完成的堆DUMP,非常大
等级1的内容如下,非常吸引人,列出了库缓存中各个内容的命令中,重新加载次数,失效次数等等。
Processing Oradebug command 'dumplibrary_cache 1'
LIBRARY CACHE DUMP
LIBRARY CACHE STATISTICS:
namespace gets hit ratio pins hit ratio reloads invalids
-------------- --------- ------------------ --------- ---------- ----------
SQL AREA 229256 0.844 1549575 0.959 9893 3554
TABLE/PROCEDURE 150852 0.927 329621 0.879 15193 0
BODY 49388 0.988 64600 0.989 93 0
TRIGGER 3884 0.924 3884 0.924 0 0
INDEX 17302 0.926 15772 0.794 889 0
CLUSTER 4244 0.984 4403 0.985 0 0
KGL TESTING 0 0.000 0 0.000 0 0
PIPE 0 0.000 0 0.000 0 0
LOB 0 0.000 0 0.000 0 0
DIRECTORY 9 0.667 9 0.667 0 0
QUEUE 6 0.667 12 0.500 1 0
REPLICATION OBJECT GROUP 0 0.000 0 0.000 0 0
REPLICATION PROPAGATOR 0 0.000 0 0.000 0 0
JAVA SOURCE 0 0.000 0 0.000 0 0
JAVA RESOURCE 0 0.000 0 0.000 0 0
REPLICATED TABLE OBJECT 0 0.000 0 0.000 0 0
REPLICATION INTERNAL PACKAGE 0 0.000 0 0.000 0 0
CONTEXT POLICY 0 0.000 0 0.000 0 0
PUB SUB INTERNAL INFORMATION 5101 0.995 6686 0.994 18 0
SUMMARY 0 0.000 0 0.000 0 0
DIMENSION 0 0.000 0 0.000 0 0
APP CONTEXT 0 0.000 0 0.000 0 0
STORED OUTLINE 0 0.000 0 0.000 0 0
RULESET 1 0.000 3 0.667 0 0
RESOURCE MANAGER 7633 0.996 15249 0.996 0 0
XML SCHEMA 0 0.000 0 0.000 0 0
PENDING RESOURCE MANAGER PLAN 0 0.000 0 0.000 0 0
PENDING RESOURCE MANAGER CONSUMERGROUP 0 0.000 0 0.000 0 0
SUBSCRIPTION 0 0.000 0 0.000 0 0
LOCATION 0 0.000 0 0.000 0 0
REMOTE OBJECT INFORMATION 0 0.000 0 0.000 0 0
SNAPSHOT META DATA 0 0.000 0 0.000 0 0
JAVA DATA 0 0.000 0 0.000 0 0
SECURITY PROFILE 0 0.000 0 0.000 0 0
TRANSFORMATION 0 0.000 0 0.000 0 0
REPLICATION - LOG BASED CHILD 0 0.000 0 0.000 0 0
RULE 0 0.000 0 0.000 0 0
STREAM 0 0.000 0 0.000 0 0
RULE EVALUATION CONTEXT 1 0.000 0 0.000 0 0
STREAMS APPLY PROCESS 0 0.000 0 0.000 0 0
REPLICATION SOURCE 0 0.000 0 0.000 0 0
REPLICATION DESTINATION 0 0.000 0 0.000 0 0
IFS SCHEMA 0 0.000 0 0.000 0 0
XDB CONFIG 0 0.000 0 0.000 0 0
USER AGENT 0 0.000 0 0.000 0 0
MULTI-VERSION OBJECT FOR TABLE 10830 0.964 10707 0.964 20 0
SCHEDULER EVENT QUEUE INFORMATION 0 0.000 0 0.000 0 0
CHANGE SET 0 0.000 0 0.000 0 0
MULTI-VERSION OBJECT FOR INDEX 4635 0.911 4499 0.911 16 0
SQL TUNING BASE OBJECT 0 0.000 0 0.000 0 0
HINTSET OBJECT 0 0.000 0 0.000 0 0
SCHEDULER GLOBAL ATTRIBUTE 254761 1.000 254761 1.000 1 0
RESOURCE MANAGER CDB 0 0.000 0 0.000 0 0
TEMPORARY TABLE 4054 0.760 4054 0.000 3082 0
TEMPORARY CLUSTER 0 0.000 0 0.000 0 0
TEMPORARY INDEX 1533 0.207 1533 0.000 317 0
SCRATCH PAD 0 0.000 0 0.000 0 0
SCHEDULER JOB SLAVE 0 0.000 0 0.000 0 0
MINING MODELS 0 0.000 0 0.000 0 0
SYNC STREAMS CAPTURE 0 0.000 0 0.000 0 0
LIGHT WEIGHT SESSION 0 0.000 0 0.000 0 0
DATA SECURITY DOCUMENT 0 0.000 0 0.000 0 0
SECURITY CLASS 0 0.000 0 0.000 0 0
XDB ACL 0 0.000 0 0.000 0 0
EDITION 19160 0.999 36626 0.999 0 0
SCHEDULER LIGHTWEIGHT JOB 0 0.000 0 0.000 0 0
REMOTE OBJECT LOCAL SUMMARY REFERENCE 0 0.000 0 0.000 0 0
STREAMS PROPOGATION 0 0.000 0 0.000 0 0
FUSION XS PARAMETERS 0 0.000 0 0.000 0 0
DBLINK 276 0.924 0 0.000 0 0
XDB REPOSITORY 0 0.000 0 0.000 0 0
EV BASE TABLE 0 0.000 0 0.000 0 0
OBJECT ID 530 0.000 0 0.000 0 0
SCHEMA 19537 0.995 0 0.000 0 1
DBINSTANCE 1 0.000 0 0.000 0 0
SQL AREA STATS 22674 0.203 22674 0.203 0 0
CONTEXT MVDATA 0 0.000 0 0.000 0 0
CONTEXT MTABLE 0 0.000 0 0.000 0 0
CONTEXT FTABLE 0 0.000 0 0.000 0 0
ACCOUNT_STATUS 37 0.838 0 0.000 0 0
PCTABL 0 0.000 0 0.000 0 0
PCINDX 0 0.000 0 0.000 0 0
SQL AREA BUILD 23744 0.065 0 0.000 0 0
KGLNEDSYNC 0 0.000 0 0.000 0 0
SDO Index MetaData 0 0.000 0 0.000 0 0
FUSION NAMESPACE TEMPLATES 0 0.000 0 0.000 0 0
SECURITY CLASS 0 0.000 0 0.000 0 0
ACL 0 0.000 0 0.000 0 0
PDB 2 0.500 0 0.000 0 0
SDO Geom MetaData 0 0.000 0 0.000 0 0
XDBTM ID2PATH 0 0.000 0 0.000 0 0
SDO SRID MetaData 0 0.000 0 0.000 0 0
SQL TRANSLATION PROFILE 0 0.000 0 0.000 0 0
AUDIT POLICY 44 0.591 44 0.591 0 0
SDO Product Usage 0 0.000 0 0.000 0 0
SDO SRID CRS DIM 0 0.000 0 0.000 0 0
LABEL SECURITY ILABELS 0 0.000 0 0.000 0 0
LABEL SECURITY GROUP NO->SHORT NAME 0 0.000 0 0.000 0 0
LABEL SECURITY GROUP SHORT NAME->NO 0 0.000 0 0.000 0 0
LABEL SECURITY COMPARTMENT NO->SHORTNAME 0 0.000 0 0.000 0 0
LABEL SECURITY COMPARTMENT SHORTNAME->NO 0 0.000 0 0.000 0 0
LABEL SECURITY LEVEL NO->SHORT NAME 0 0.000 0 0.000 0 0
LABEL SECURITY LEVEL SHORT NAME->NO 0 0.000 0 0.000 0 0
OPTIMIZER FINDING 21450 0.934 21450 0.934 0 0
OPTIMIZER DIRECTIVE OWNER 15941 0.801 15941 0.801 1 0
LABEL SECURITY USERS 0 0.000 0 0.000 0 0
Database Vault Rule 0 0.000 0 0.000 0 0
Database Vault Ruleset 0 0.000 0 0.000 0 0
ZLL policy-table 0 0.000 0 0.000 0 0
ZLL nlil 0 0.000 0 0.000 0 0
ZLLR pol# to table-list 0 0.000 0 0.000 0 0
LABEL SECURITY OLS$AUDIT 0 0.000 0 0.000 0 0
USER PRIVILEGE 1 0.000 1 0.000 0 0
GTT SESSION PRIVATE STATS 0 0.000 0 0.000 0 0
QUEUE SUBSCRIBER 0 0.000 0 0.000 0 0
KZP Exempt Access Policy list 0 0.000 0 0.000 0 0
SDO Row Cache 0 0.000 0 0.000 0 0
LABEL SECURITY PROFILE DATALABELS 0 0.000 0 0.000 0 0
XDBTM QNAME2ID 0 0.000 0 0.000 0 0
XDBTM ID2QNAME 0 0.000 0 0.000 0 0
XDBTM NS2ID 0 0.000 0 0.000 0 0
XDBTM ID2NS 0 0.000 0 0.000 0 0
XDBTM PATH2ID 0 0.000 0 0.000 0 0
Database Vault Realm Participant 0 0.000 0 0.000 0 0
Database Vault Factor 0 0.000 0 0.000 0 0
PDBOPER 0 0.000 0 0.000 0 0
MULTI-VERSION OBJECT FOR CLUSTER 0 0.000 0 0.000 0 0
Last_Successful_Logon_Time 18 0.667 0 0.000 0 0
SQL TRANSLATION CACHE 0 0.000 0 0.000 0 0
CUMULATIVE 866905 0.887 2362104 0.942 29524 3555
SGA:0x000007FF28A415D0 Flags=ac3
DebugContext: DebugNameSpace=0 DebugType=0 DebugLevel=0x0END OF LIBRARY CACHE DUMP