1OG数据库之library cache lock、pin模拟结合hanganalyze定位及systemdump定位

2024-04-02 19:04:59 713人浏览独家记忆

摘要

session 1: sql> select sid from v$mystat where rownum=1; &

session 1:

sql> select sid from v$mystat where rownum=1;

       SID
----------
       148

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
----------
       158
SQL> alter procedure pining compile;
hang.................

session 3:

SQL> select sid from v$mystat where rownum=1;

       SID
----------
       149

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
---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------
       146 SQL*Net message to client                                        Network                                                                        0
       149 library cache lock                                               Concurrency                                                                    3
       158 library cache pin                                                Concurrency                                                                    6

SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 3;
Hang Analysis in /u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc
SQL> oradebug close_trace;
Statement processed.

session 5:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_17000.trc
SQL> oradebug close_trace;
Statement processed.
SQL>

--查看hang的trace文件:
[root@rhel ~]# cat /u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc
/u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0.1/db_1
System name:    linux
node name:      rhel
Release:        2.6.18-238.el5
Version:        #1 SMP Sun Dec 19 14:22:44 EST 2010
Machine:        x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 16916, image: oracle@rhel (TNS V1-V3)

*** SERVICE NAME:(SYS$USERS) 2017-11-23 15:08:24.320
*** SESSION ID:(146.17) 2017-11-23 15:08:24.320
*** 2017-11-23 15:08:24.320
==============
HANG ANALYSIS:
==============
Open chains found:
--从这里开始以下的session都是被前面的session阻塞
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/148/102/0x83a5dcc0/16769/PL/SQL lock timer>
-- <0/158/250/0x83a5e4a8/16912/library cache pin>
-- <0/149/135/0x83a5ec90/16914/library cache lock>

Other chains found:
--下面的session也是被前面所阻塞,被间接阻塞
Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/140/1/0x83a62bd0/2825/Streams AQ: qmn slave idle wait>
Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/146/17/0x83a5f478/16916/No Wait>
Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/147/2/0x83a623e8/2823/Streams AQ: waiting for time man>
Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/151/987/0x83a5ccf0/17003/jobq slave wait>
Chain 6 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/154/1/0x83a5d4d8/2797/Streams AQ: qmn coordinator idle>
Extra infORMation that will be dumped at higher levels:
[level 4] :   1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW]
[level 5] :   5 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level 6] :   2 node dumps -- [NLEAF]
[level 10] : 12 node dumps -- [IGN]

State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[139]/0/140/1/0x83b58c18/2825/SINGLE_NODE/1/2//none
[144]/0/145/12/0x83b5f130/17000/IGN/3/4//none
[145]/0/146/17/0x83b60568/16916/SINGLE_NODE_NW/5/6//none
[146]/0/147/2/0x83b619a0/2823/SINGLE_NODE/7/8//none
[147]/0/148/102/0x83b62dd8/16769/LEAF/9/10//157
[148]/0/149/135/0x83b64210/16914/NLEAF/11/14/[157]/none
[150]/0/151/987/0x83b66a80/17003/SINGLE_NODE/15/16//none
[153]/0/154/1/0x83b6a728/2797/SINGLE_NODE/17/18//none
[157]/0/158/250/0x83b6f808/16912/NLEAF/12/13/[147]/148
[159]/0/160/1/0x83b72078/2789/IGN/19/20//none
[160]/0/161/1/0x83b734b0/2787/IGN/21/22//none
[161]/0/162/1/0x83b748e8/2785/IGN/23/24//none
[162]/0/163/1/0x83b75d20/2783/IGN/25/26//none
[163]/0/164/1/0x83b77158/2781/IGN/27/28//none
[164]/0/165/1/0x83b78590/2779/IGN/29/30//none
[165]/0/166/1/0x83b799c8/2777/IGN/31/32//none
[166]/0/167/1/0x83b7ae00/2775/IGN/33/34//none
[167]/0/168/1/0x83b7c238/2773/IGN/35/36//none
[168]/0/169/1/0x83b7d670/2771/IGN/37/38//none
[169]/0/170/1/0x83b7eaa8/2769/IGN/39/40//none
====================
END OF HANG ANALYSIS
====================
/u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0.1/db_1
System name:    Linux
Node name:      rhel
Release:        2.6.18-238.el5
Version:        #1 SMP Sun Dec 19 14:22:44 EST 2010
Machine:        x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 16916, image: oracle@rhel (TNS V1-V3)

*** 2017-11-23 15:08:44.057
Cannot find symbol
Cannot find symbol
Cannot find symbol
[root@rhel ~]#

1OG数据库之library cache lock、pin模拟结合hanganalyze定位及systemdump定位