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 services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [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).
