Shared_Pool 是 Oracle SGA中最复杂的一部分,在分析很多 library cache 问题时,经常会用到转储命令。

oradebug dump library_cache 的常用级别包括 (注意,一定要测试之后再采用,要先看看 library cache 大小,如果库缓存非常大,这个转储的日志可能会是 Huge 的):

等级1:关键结构的统计汇总信息

等级2:HASH CHAIN 信息

等级4:持有对象结构 Bucket 信息,可以看到一个对象的lock,pin,mutex信息。

等级8:Level 4 + 相关数据块

等级16:Level 8 + 每个对象的 Heap 信息

等级32:Level 16 + 完整的 Heap DUMP

使用 oradebug 可以很方便的转储 library_cache 信息,示范如下:

[oracle12c@cat1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 31 10:17:00 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> oradebug setmypid

Statement processed.

SQL> oradebug dump library_cache 1;

Statement processed.

SQL> select value from v$diag_info where name like 'Defa%';

VALUE

--------------------------------------------------------------------------------

/oracle12c/db/diag/rdbms/enmo12c/enmo12c/trace/enmo12c_ora_7277.trc

SQL> !

[oracle12c@cat1 ~]$ cat /oracle12c/db/diag/rdbms/enmo12c/enmo12c/trace/enmo12c_ora_7277.trc

Trace file /oracle12c/db/diag/rdbms/enmo12c/enmo12c/trace/enmo12c_ora_7277.trc

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125

ORACLE_HOME: /u01/oracle12c/db/product/12.2.0/dbhome_1

System name: Linux

Node name: enmotech

Release: 3.10.0-514.6.2.el7.x86_64

Version: #1 SMP Thu Feb 23 03:04:39 UTC 2017

Machine: x86_64

Instance name: enmo12c

Redo thread mounted by this instance: 1

Oracle process number: 8

Unix process pid: 7277, image: oracle@enmotech (TNS V1-V3)

*** 2018-07-31T10:17:07.023214+08:00 (CDB$ROOT(1))

*** SESSION ID:(982.48265) 2018-07-31T10:17:07.023262+08:00

*** CLIENT ID:() 2018-07-31T10:17:07.023270+08:00

*** SERVICE NAME:(SYS$USERS) 2018-07-31T10:17:07.023277+08:00

*** MODULE NAME:(sqlplus@enmotech (TNS V1-V3)) 2018-07-31T10:17:07.023285+08:00

*** ACTION NAME:() 2018-07-31T10:17:07.023292+08:00

*** CLIENT DRIVER:(SQL*PLUS) 2018-07-31T10:17:07.023298+08:00

*** CONTAINER ID:(1) 2018-07-31T10:17:07.023305+08:00

Processing Oradebug command 'setmypid'

*** 2018-07-31T10:17:07.023346+08:00 (CDB$ROOT(1))

Oradebug command 'setmypid' console output: <none>

*** 2018-07-31T10:17:13.956173+08:00 (CDB$ROOT(1))

Processing Oradebug command 'dump library_cache 1'

LIBRARY CACHE DUMP

LIBRARY CACHE STATISTICS:

namespace gets hit ratio pins hit ratio reloads invalids

-------------- --------- --------- --------- --------- ---------- ----------

SQL AREA 1217773 0.891 21268193 0.985 55850 35229

TABLE/PROCEDURE 881146 0.961 2039858 0.909 76152 0

BODY 192474 0.993 289193 0.994 348 0

TRIGGER 61079 0.986 61077 0.986 2 1

INDEX 152995 0.979 139263 0.816 12489 0

CLUSTER 20622 0.992 20963 0.992 0 0

Related Posts