library cache: mutex X

来源：互联网发布：网络用语丫丫编辑：程序博客网时间：2024/05/22 09:05

发现数据库存在 ;library cache: mutex X 等待事件（ID 1357946.1）

Top 5 Timed Foreground Events

EventWaitsTime(s)Avg wait (ms)% DB timeWait Classlibrary cache: mutex X41,54229,45870921.04Concurrencylatch: shared pool2,5557,56829625.40Concurrencyenq: TX - index contention3,1163,68611832.63ConcurrencyDB CPU 2,672 1.91 SQL*Net message from dblink4,402,4151,22700.88Network

下面我们通过awr 报告来进行诊断：

1、First look for high parsing and high version counts from AWR.

SQL ordered by Parse Calls

Total Parse Calls: 171,846
Captured SQL account for 26.8% of Total

Parse CallsExecutions% Total ParsesSQL IdSQL ModuleSQL Text3,5327,8532.06cm5vu20fhtnq1 select /*+ connect_by_filterin...3,5273,5312.050k8522rmdzg4k select privilege# from sysauth...3,5163,5172.05459f3z9u4fb3u select value$ from props$ wher...3,4953,4952.030ws7ahf1d78qa select SYS_CONTEXT('USERENV', ...3,4873,4872.035ur69atw3vfhj select decode(failover_method,...3,4853,4872.034vs91dcv7u1p6w3wp.exeinsert into sys.aud$( sessioni...2,9672,9691.73f711myt0q6cmaBest.HandSet.Q9Exchange.ImgUploader.exeinsert into sys.aud$( sessioni...1,770511,3431.030dx7vzvyb0g8ww3wp.exeSELECT GLOBALSEQ.NEXTVAL as v...1,651255,7720.968pmn3c452zumqw3wp.exeInsert into TAB_LOCATION( ID,...1,646255,7710.96dch30czvp4wtfw3wp.exeInsert into TAB_SCAN_LOCATION...

可以看出，没有parse 异常的sql,下面是正常情况下的parse call 情况：

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

也不存在多个版本的sql

2、进入“ load profile” ，查看hard parsing的值，他可能造成reload 在sql area。

Load Profile

Per SecondPer TransactionPer ExecPer CallDB Time(s):79.60.10.060.04DB CPU(s):1.50.00.000.00Redo size:1,804,223.72,425.6 Logical reads:36,851.049.5 Block changes:10,298.313.9 Physical reads:202.10.3 Physical writes:337.20.5 User calls:1,798.52.4 Parses:97.70.1

Hard parses:53.80.1 W/A MB processed:3.30.0 Logons:2.30.0 Executes:1,293.61.7 Rollbacks:27.20.0 Transactions:743.8

每秒的硬解析多达53，说明硬解析很严重，

Library Cache Activity

"Pct Misses" should be very low

NamespaceGet RequestsPct MissPin RequestsPct MissReloadsInvali- dationsACCOUNT_STATUS6,9640.000 00BODY6360.6323,7690.0320CLUSTER110.00110.0000DBLINK2,210,0760.000 00EDITION3,5440.007,0600.0000INDEX32314.2427931.9000OBJECT ID1100.000 00SCHEMA3,6950.000 00SECURITY CLASS160.00160.0000SQL AREA171,62454.932,511,54411.13503109SQL AREA BUILD94,49499.890 00SQL AREA STATS94,53099.7294,59599.7200TABLE/PROCEDURE103,3730.081,292,7760.001550TRIGGER1560.001770.0000XDB ACL40.0040.0000XDB CONFIG20.0020.0000XML SCHEMA100.00180.0000

可以看到reload 也挺高的。

问题的原因找到了，下面就是找出为什么hard parse 超高的原因：

Avoid Invalidations

Some specific orders will change the state of cursors to INVALIDATE. These orders modify directly the context of related objects associated with cursors. That's orders are TRUNCATE, ANALYZE or DBMS_STATS.GATHER_XXX on tables or indexes, grants changes on underlying objects. The associated cursors will stay in the SQLAREA but when it will be reference next time, it should be reloaded and reparsed fully, so the global performance will be impacted.

The following query could help us to better identify the concerned cursors:

记得当天有几个表重建了

SELECT SUBSTR(sql_text, 1, 40) "SQL",invalidationsFROM v$sqlareaORDER BY invalidations DESC;

0 0