Shulou(Shulou.com)06/01 Report--

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'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.