语言
<< 返回文章列表

Oracle 性能优化:使用 oradebug 转储 library_cache 内存信息

2018年8月3日
eygle
1523

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@enmotech ~]$ 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@enmotech ~]$ 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

其他级别,不做详细列举。

数据驱动,成就未来,云和恩墨,不负所托!