Library cache lock and library cache pin Simulation of 11G Database combined with hanganalyze Positioning
Session 1:
SQL > select sid from v$mystat where rownum=1
SID
-
forty-one
SQL > create or replace procedure pining
Is
Begin
Null
End
/
Procedure created
SQL >
SQL > create or replace procedure calling
Is
Begin
Pining
Dbms_lock.sleep (200)
End
/
SQL >
SQL > call calling ()
.
Session 2:
SQL > select sid from v$mystat where rownum=1
SID
-
thirty-seven
SQL > alter procedure pining compile
Hang.
Session 3:
SQL > select sid from v$mystat where rownum=1
SID
-
thirty-eight
SQL > drop procedure pining
HANG...
Session 4:
SQL > select sid, event,wait_class, seconds_in_wait from v$session_wait w where w.WAIT_CLASS 'Idle'
SID EVENT WAIT_CLASS SECONDS_IN_WAIT
--
1 SQL*Net message to client Network 0
37 library cache pin Concurrency 44
38 library cache lock Concurrency 33
SQL >
SQL > oradebug setmypid
Statement processed.
SQL > oradebug hanganalyze 3
Hang Analysis in / u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_27483.trc
SQL > oradebug close_trace
Statement processed.
-- View the trace file:
[oracle@wang ~] $more / u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_27483.trc
Trace file / u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_27483.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = / u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: wang
Release: 3.10.0-327.el7.x86_64
Version: # 1 SMP Thu Oct 29 17:29:29 EDT 2015
Machine: x86_64
Instance name: DBdb
Redo thread mounted by this instance: 1
Oracle process number: 32
Unix process pid: 27483, image: oracle@wang (TNS V1-V3)
* 2017-12-11 11 Fringe 02purl 22.201
* SESSION ID: (1.201) 2017-12-11 1115 02purl 22.201
* CLIENT ID: () 2017-12-11 1115 02purl 22.201
* SERVICE NAME: (SYS$USERS) 2017-12-11 1115 02purl 22.201
* MODULE NAME: (sqlplus@wang (TNS V1-V3)) 2017-12-11 11 purl 02purl 22.201
* ACTION NAME: () 2017-12-11 1115 02purl 22.201
Processing Oradebug command 'setmypid'
* 2017-12-11 11 Fringe 02purl 22.201
Oradebug command 'setmypid' console output:
* * 2017-12-11 11 Fringe 02purl 30.699
Processing Oradebug command 'hanganalyze 3'
* 2017-12-11 11 Fringe 02purl 30.701
=
HANG ANALYSIS:
Instances (db_name.oracle_sid): dbdb.dbdb
Oradebug_node_dump_level: 3
Analysis initiated by oradebug
Os thread scheduling delay history: (sampling every 1.000000 secs)
0.000000 secs at [11:02:29]
NOTE: scheduling delay has not been sampled for 0.879666 secs 0.000000 secs from [11:02:25-11:02:30], 5 sec avg
0.000000 secs from [11:01:31-11:02:30], 1 min avg
0.000000 secs from [10:57:31-11:02:30], 5 min avg
Vktm time drift history
=
Chains most likely to have caused the hang:
=
No chains found.
=
Extra information that will be dumped at higher levels:
State of ALL nodes
([nodenum] / cnode/sid/sess_srno/session/ospid/state/ [adjlist]):
* * 2017-12-11 11 Fringe 02purl 30.770
=
END OF HANG ANALYSIS
=
* * 2017-12-11 11 Fringe 02purl 30.770
=
HANG ANALYSIS DUMPS:
Oradebug_node_dump_level: 3
=
State of LOCAL nodes
([nodenum] / cnode/sid/sess_srno/session/ospid/state/ [adjlist]):
No processes qualify for dumping.
=
HANG ANALYSIS DUMPS: END
=
* * 2017-12-11 11 Fringe 02purl 30.771
Oradebug command 'hanganalyze 3' console output:
Hang Analysis in / u01/app/oracle/diag/rdbms/dbdb/DBdb/trace/DBdb_ora_27483.trc
* 2017-12-11 11 Fringe 03purl 41.097
Processing Oradebug command 'close_trace'
Oradebug command 'close_trace' console output:
Processing Oradebug command 'setmypid'
Oradebug command 'setmypid' console output:
Processing Oradebug command 'hanganalyze 3'
=
HANG ANALYSIS:
Instances (db_name.oracle_sid): dbdb.dbdb
Oradebug_node_dump_level: 3
Analysis initiated by oradebug
Os thread scheduling delay history: (sampling every 1.000000 secs)
0.000000 secs at [11:05:35]
NOTE: scheduling delay has not been sampled for 0.908940 secs 0.000000 secs from [11:05:31-11:05:36], 5 sec avg
0.000000 secs from [11:04:37-11:05:36], 1 min avg
0.000001 secs from [11:00:37-11:05:36], 5 min avg
Vktm time drift history
=
Chains most likely to have caused the hang:
[a] Chain 1 Signature: 'PL/SQL lock timer'
