| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> strange library cache lock/ping wait event
Hi, friends:
Generally speaking, we hit library cache lock/pin when we do DDL on object or compile package. I have a env with rac 9.2.0.3 on windows, sometimes system is hang , only bounce the oracle instance work.
when there is performance problems, the statspack report look like:
I checked dba_objects, no ddl in the this day and last. No package recompile in this day.And no snapshot/maverialized view.
I tried to check v$session_event ,find the session with most "library cache pin/library cache lock" and did a event 10046 and tkprof that trace file with waits=Yes. In the tracefile, two sql generate the most wait event of library cache lock/pin, it is a procedure in a package. The common point is that they used dbms_rls package heavily.
question 1: Can we use tracefile of 10046 to diag the library cache lock/pin wait event according to the p1,p1raw, p2 etc?
Question 2: Besides ddl/compile, what can cause library cache contention according to your experience? Can row level security cause high library cache contention?
Thanks for your time.
---statspack report.
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- ------------------
-
Begin Snap: 189 06-Jul-03 16:57:42 226 7.5
End Snap: 190 06-Jul-03 17:27:47 104 27.1
Elapsed: 30.08 (mins)
Cache Sizes (end)
Buffer Cache: 416M Std Block Size: 8K
Shared Pool Size: 128M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 8,613.48 17,010.21
Logical reads: 839.76 1,658.39
Block changes: 68.89 136.04
Physical reads: 0.51 1.00
Physical writes: 0.15 0.29
User calls: 7.72 15.25
Parses: 51.08 100.88
Hard parses: 1.46 2.88
Sorts: 19.57 38.65
Logons: 0.04 0.08
Executes: 104.41 206.20
Transactions: 0.51
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ %
Total
Event Waits Time (s) Ela
Time
library cache lock 310,416 145,265 74.87 library cache pin 58,915 41,909 21.60 latch free 266,655 4,189 2.16 CPU time 1,262 .65 library cache load lock 7,017 709.37
I checked dba_objets, no ddl since
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.cnoug.org
SQL> SELECT OWNER,OBJECT_NAME,STATUS,LAST_DDL_TIME FROM DBA_OBJECTS WHERE
LAST_DDL_TIME > S
YSDATE -5 AND OWNER<>'PERFSTAT' AND OWNER<>'PUBLIC';
OWNER OBJECT_NAME STATUS LAST_DDL_TIME
-------------------- -------------------- -------------- -----------------
SYS DBA_KEEPSIZES VALID 20030704 16:36:13
SYS DBMS_JOB VALID 20030704 16:36:26
SYS DBMS_JOB VALID 20030704 16:38:39
SYS DBMS_RLS VALID 20030707 10:45:17
SYS DBMS_SHARED_POOL VALID 20030704 16:36:26
SYS DBMS_SHARED_POOL VALID 20030704 16:36:14
SYS DBMS_STATS VALID 20030704 18:14:02
SYS DBMS_UTILITY VALID 20030704 16:37:51
SYS PLAN_TABLE VALID 20030703 21:37:52
SYS STATS$V_$FILESTATXS VALID 20030704 16:36:24
SYS STATS$V_$SQLXS VALID 20030704 16:36:24
SYS STATS$V_$TEMPSTATXS VALID 20030704 16:36:24
SYS STATS$X_$KCBFWAIT VALID 20030704 16:36:24
SYS STATS$X_$KSPPI VALID 20030704 16:36:24
SYS STATS$X_$KSPPSV VALID 20030704 16:36:24
SYS V_$BUFFER_POOL VALID 20030704 16:36:25
SYS V_$BUFFER_POOL_STATI VALID 20030704 16:36:25
STICS
SYS V_$DATABASE VALID 20030704 16:36:24
SYS V_$DB_CACHE_ADVICE VALID 20030704 16:36:25
SYS V_$DLM_MISC VALID 20030704 16:36:25
SYS V_$ENQUEUE_STAT VALID 20030704 16:36:25
SYS V_$INSTANCE VALID 20030704 16:36:24
SYS V_$INSTANCE_RECOVERY VALID 20030704 16:36:25
SYS V_$LATCH VALID 20030704 16:36:25
SYS V_$LATCH_CHILDREN VALID 20030704 16:36:25
SYS V_$LATCH_MISSES VALID 20030704 16:36:25
SYS V_$LATCH_PARENT VALID 20030704 16:36:25
SYS V_$LIBRARYCACHE VALID 20030704 16:36:25
SYS V_$PARAMETER VALID 20030704 16:36:24
SYS V_$PGASTAT VALID 20030704 16:36:25
SYS V_$PGA_TARGET_ADVICE VALID 20030704 16:36:25
SYS V_$RESOURCE_LIMIT VALID 20030704 16:36:25
SYS V_$ROLLSTAT VALID 20030704 16:36:25
SYS V_$ROWCACHE VALID 20030704 16:36:25
SYS V_$SEGMENT_STATISTIC VALID 20030704 16:36:26
S
SYS V_$SEGSTAT VALID 20030704 16:36:25
SYS V_$SEGSTAT_NAME VALID 20030704 16:36:26
SYS V_$SESSION VALID 20030704 16:36:25
SYS V_$SESSION_EVENT VALID 20030704 16:36:25
SYS V_$SESSTAT VALID 20030704 16:36:25
SYS V_$SGA VALID 20030704 16:36:25
SYS V_$SGASTAT VALID 20030704 16:36:25
SYS V_$SHARED_POOL_ADVIC VALID 20030704 16:36:25
E
SYS V_$SQL VALID 20030704 16:36:25
SYS V_$SQLAREA VALID 20030704 16:36:25
SYS V_$SQLTEXT VALID 20030704 16:36:25
SYS V_$SQL_PLAN VALID 20030704 16:36:25
SYS V_$SQL_WORKAREA_HIST VALID 20030704 16:36:25
OGRAM
SYS V_$SYSSTAT VALID 20030704 16:36:25
SYS V_$SYSTEM_EVENT VALID 20030704 16:36:25
SYS V_$SYSTEM_PARAMETER VALID 20030704 16:36:24
SYS V_$UNDOSTAT VALID 20030704 16:36:25
SYS V_$WAITSTAT VALID 20030704 16:36:25
DEV BF$ACCOUNT_BALANCE VALID 20030703 14:00:21
OWNER OBJECT_NAME STATUS LAST_DDL_TIME
-------------------- -------------------- -------------- -----------------
DEV BF$AUDITED_EVENT VALID 20030703 14:00:17
DEV BF$AUDITED_EVENT VALID 20030703 14:00:19
DEV BF$BILL_SERVER_SOAP VALID 20030703 14:46:27
DEV BF$BILL_SERVER_SOAP1 VALID 20030703 14:00:18
DEV BF$BP_PROCESS VALID 20030703 14:46:31
DEV BF$CD_KEY VALID 20030703 14:09:49
DEV BF$GEN_BP_PROCESS VALID 20030703 14:46:31
DEV BF$TRANSACTION VALID 20030703 14:09:46
DEV BF$TRANSACTION VALID 20030703 14:09:51
63 rows selected.
SQL> select sid,event,total_waits,time_waited from v$session_event 2 where event like 'library cache %';
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
12 library cache pin 18 1241
32 library cache pin 4520 140
26 library cache pin 77 4
22 library cache pin 1552 78
13 library cache pin 1 0
17 library cache pin 26 2
14 library cache pin 1 0
12 library cache lock 10 1
17 library cache lock 125 5
26 library cache lock 367 11
32 library cache lock 2109 118
22 library cache lock 952 61
SQL> select sid,serial#,username,program,machine,status from v$session where
sid=32;
SID SERIAL# USERNAME
---------- ---------- ---------------
PROGRAM
MACHINE STATUS
----------------------------------------------------------------------------
-----------------
----------------------------------- ---------- ----------------
32 23698 DISTRIBUTORMATR
IX
aspnet_wp.exe
WORKGROUP\ INACTIVE
KK97M6D
SQL> exec dbms_system.set_ev(32,23968,10046,8,'')
PL/SQL procedure successfully completed.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: zhu chao
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Jul 07 2003 - 13:14:22 CDT
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |