Hi All,
DB version is 10.2.0.4 on Red Hat Enterprise Linux AS release 4 (Nahant Update 6)
Yesterday system got completely hanged and programs were taking lot of time and when i ran the below script
1 select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
2 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
3 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
4 11, 'PACKAGE BODY', 12, 'TRIGGER',
5 13, 'TYPE', 14, 'TYPE BODY',
6 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
7 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
8 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
9 32, 'INDEXTYPE', 33, 'OPERATOR',
10 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
11 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
12 42, 'MATERIALIZED VIEW',
13 43, 'DIMENSION',
14 44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
15 48, 'CONSUMER GROUP',
16 51, 'SUBSCRIPTION', 52, 'LOCATION',
17 55, 'XML SCHEMA', 56, 'JAVA DATA',
18 57, 'SECURITY PROFILE', 59, 'RULE',
19 62, 'EVALUATION CONTEXT',
20 'UNDEFINED') object_type,
21 lob.KGLNAOBJ object_name,
22 lk.KGLLKMOD lock_mode_held,
23 lk.KGLLKREQ lock_mode_requested,
24 ses.sid,
25 ses.serial#,
26 ses.username
27 FROM
28 x$kgllk lk,
29 v$session ses,
30 x$kglob lob,
31 v$session_wait vsw
32 WHERE
33 lk.KGLLKUSE = ses.saddr and
34 lk.KGLLKHDL = lob.KGLHDADR
35 and lob.kglhdadr = vsw.p1raw
36 and vsw.event = 'library cache lock'
37* order by lock_mode_held desc
It returned 700 rows of packages and procedure which went to invalid state and got locked.
SQL> @libcaclock.sql
OBJECT_TYPE OBJECT_NAME LOCK_MODE_HELD LOCK_MODE_REQUESTED SID SNO USERNAME
------------------ ------------------------------ -------------- ------------------- ----- ------ ---------------
PACKAGE BODY OE_SCHEDULE_UTIL 3 0 808 27974 APPS
PACKAGE BODY OE_SCHEDULE_UTIL 3 0 808 27974 APPS
PACKAGE BODY OE_SCHEDULE_UTIL 3 0 808 27974 APPS
PACKAGE GMICCAL 3 0 980 29584 APPS
PACKAGE GMICCAL 3 0 980 29584 APPS
PACKAGE BODY OE_ORDER_SCH_UTIL 3 0 993 25910 APPS
PACKAGE BODY OE_ORDER_SCH_UTIL 3 0 993 25910 APPS
PACKAGE BODY OE_ORDER_SCH_UTIL 3 0 993 25910 APPS
PACKAGE BODY OE_ORDER_SCH_UTIL 3 0 993 25910 APPS
PACKAGE BODY OE_ORDER_SCH_UTIL 3 0 993 25910 APPS
......
awr report shows
cache buffers chains 2,051,520,074 0.0 0.1 11 30,055,114 0.3
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
library cache 85,050,458 0.4 0.0 1 94,762 0.2
library cache load lock 25,484 0.0 N/A 0 0 N/A
LLatch Miss Sources DB/Inst: STEELP/STEELP1 Snaps: 14146-14147
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
ges resource table freel kjchc: clean resource cach 0 2 0
ges resource table freel kjralc: resource allocatio 0 1 0
lgwr LWN SCN kcs023 0 100 0
library cache kglpnp: child 0 1,131 1,461
library cache kglpndl: child: after proc 0 1,018 49
library cache kglpndl: child: before pro 0 212 1,021
library cache kglobpn: child: 0 76 85
library cache kglScanDependency 0 49 0
library cache kglLockCursor 0 41 44
library cache kgldti: 2child 0 39 10
library cache kglhdgn: child: 0 30 28
library cache kglpin 0 30 25
library cache kgldte: child 0 0 16 20
library cache kglic 0 13 0
library cache kglrtl 0 7 1
library cache kglnti 0 6 0
library cache kglhdgc: child: 0 4 0
library cache kglati 0 2 0
library cache kglhdiv: child 0 2 0
library cache kglobld 0 2 0
library cache kgldtld: 2child 0 1 1
library cache lock kgllkdl: child: no lock ha 0 33 15
library cache lock kgllkal: child: multiinsta 0 7 2
library cache lock kgllkdl: child: cleanup 0 2 7
library cache lock alloc kgllkget 0 1 1
library cache pin kglpndl 0 971 841
library cache pin kglpnp: child 0 964 1,088
After bouncing the database and compiling invalid system returned to normal. Dont know why those package went in to invalid state that time.?
thanks,
baskar.l