History | Log In     View a printable version of the current page.  
   HOME       BROWSE PROJECT       FIND ISSUES   
    QUICK SEARCH:  
Issue Details (XML | Word | Printable)

Key: QA-39
Type: Oracle - Database Tuning Oracle - Database Tuning
Status: Closed Closed
Resolution: Answered
Priority: Major Major
Assignee: ubTools Support
Reporter: ubTools Support
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Questions & Answers

Database hangs on "cursor: pin S wait on X" wait events.

Created: 07/Jun/08 05:35 PM   Updated: 07/Jun/08 07:05 PM
Return to search
Fix Version/s: None

Product Version: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
Operating System: IBM-AIX


 Description  « Hide
The Database hangs.

The ASH report shows the activity on cursor: pin S wait on X wait event.

Top User Events
Event                        Event Class   % Activity     Avg Active Sessions
cursor: pin S wait on X      Concurrency        98.90                   13.74

An excerpt from SYSTEMSTATE (level 10) dump

PROCESS 23:
...
    waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=42033 wait_time=0 seconds since wait started=0
                idn=ec048ba, value=137000000000, where|sleeps=5007f3271
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000d44f4280
       Mutex 7000000d92bce40(4976, 0) idn ec048ba oper GET_SHRD
       Cursor Pin uid 4939 efd 0 whr 5 slp 44251
       opr=2 pso=7000000cefa5df0 flg=0
       pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
       ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
       hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
      ----------------------------------------
...
PROCESS 33:
...
    ----------------------------------------
    SO: 7000000f78e0770, type: 4, owner: 7000000fa8bbcf8, flag: INIT/-/-/0x00
    (session) sid: 4976 trans: 7000000f10ce318, creator: 7000000fa8bbcf8, flag: (8100041) USR/- BSY/-/-/-/-/-
              DID: 0001-0021-000193D1, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 3, prv: 0, sql: 7000000fc179fa8, psql: 7000000fc68f018, user: 0/SYS
    O/S info: user: orapaky0, term: , ospid: 5099938, machine: akmenkulp2
              program: sqlplus@akmenkulp2 (TNS V1-V3)
    application name: sqlplus@akmenkulp2 (TNS V1-V3), hash value=0
    waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=27479 wait_time=0 seconds since wait started=0
                idn=ec048ba, value=137000000000, where|sleeps=5007f33be
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000fc64adb8
       Mutex 7000000d92bce40(4976, 0) idn ec048ba oper GET_SHRD
       Cursor Pin uid 4976 efd 0 whr 5 slp 15527
       opr=2 pso=7000000d86f7d88 flg=0
       pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
       ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
       hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
      ----------------------------------------
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000fc64ad80
       Mutex 7000000d92bce40(4976, 0) idn ec048ba oper EXCL
       Cursor Pin uid 4976 efd 0 whr 1 slp 0
       opr=3 pso=7000000cee2b2c0 flg=0
       pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
       ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
       hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
      ----------------------------------------
...
PROCESS 34:
...
    waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=63882 wait_time=0 seconds since wait started=0
                idn=ec048ba, value=137000000000, where|sleeps=5007f348e
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000fdd75c30
       Mutex 7000000d92bce40(4976, 0) idn ec048ba oper GET_SHRD
       Cursor Pin uid 4693 efd 0 whr 5 slp 62048
       opr=2 pso=7000000e7548610 flg=0
       pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
       ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
       hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
      ----------------------------------------
...
PROCESS 39:
...
    waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=12512 wait_time=0 seconds since wait started=0
                idn=ec048ba, value=137000000000, where|sleeps=5007f36cf
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000fd857ad8
       Mutex 7000000d92bce40(4976, 0) idn ec048ba oper GET_SHRD
       Cursor Pin uid 4970 efd 0 whr 5 slp 12395
       opr=2 pso=7000000cea1c250 flg=0
       pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
       ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
       hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
      ----------------------------------------
...
PROCESS 47:
...
      waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=48746 wait_time=0 seconds since wait started=0
                  idn=16a1ebe6, value=132e00000000, where|sleeps=5002494fe
...
        ----------------------------------------
        KGX Atomic Operation Log 7000000c5ca4690
         Mutex 7000000d71fd6b0(4910, 0) idn 16a1ebe6 oper GET_SHRD
         Cursor Pin uid 4896 efd 0 whr 5 slp 48634
         opr=2 pso=7000000e24f7e30 flg=0
         pcs=7000000d71fd6b0 nxt=0 flg=35 cld=0 hd=7000000fda5fc00 par=7000000d71fdaa0
         ct=0 hsh=0 unp=0 unn=0 hvl=d71fdd78 nhv=1 ses=7000000f78b5cc0
         hep=7000000d71fd730 flg=80 ld=1 ob=7000000d76d3848 ptr=70000008a8ff478 fex=70000008a8fe788
        ----------------------------------------
...
PROCESS 53:
...
      waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=38686 wait_time=0 seconds since wait started=0
                  idn=16a1ebe6, value=132e00000000, where|sleeps=500249647
...
        ----------------------------------------
        KGX Atomic Operation Log 7000000db1dee98
         Mutex 7000000d71fd6b0(4910, 0) idn 16a1ebe6 oper GET_SHRD
         Cursor Pin uid 4828 efd 0 whr 5 slp 38647
         opr=2 pso=7000000e7f58940 flg=0
         pcs=7000000d71fd6b0 nxt=0 flg=35 cld=0 hd=7000000fda5fc00 par=7000000d71fdaa0
         ct=0 hsh=0 unp=0 unn=0 hvl=d71fdd78 nhv=1 ses=7000000f78b5cc0
         hep=7000000d71fd730 flg=80 ld=1 ob=7000000d76d3848 ptr=70000008a8ff478 fex=70000008a8fe788
        ----------------------------------------
...
PROCESS 54:
...
      (session) sid: 4910 trans: 0, creator: 7000000d81123c0, flag: (e1) USR/- BSY/-/-/-/-/-
                DID: 0001-002F-00044CCF, short-term DID: 0000-0000-00000000
                txn branch: 0
                oct: 3, prv: 0, sql: 7000000dbd12ce0, psql: 7000000dbb98da0, user: 55/SYSMAN
      O/S info: user: orapaky0, term: unknown, ospid: 1234, machine: akmenkulp2
                program: OMS
      client info: akmenkulp2_Management_Service
      application name: OEM.DefaultPool, hash value=3997945242
      action name: /database/instance/sitemap, hash value=105676648
      waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=54571 wait_time=0 seconds since wait started=0
                  idn=ec048ba, value=137000000000, where|sleeps=5007f3abc
...
        ----------------------------------------
        KGX Atomic Operation Log 7000000dcd36f50
         Mutex 7000000d92bce40(4976, 0) idn ec048ba oper GET_SHRD
         Cursor Pin uid 4910 efd 0 whr 5 slp 53641
         opr=2 pso=7000000e7c7f338 flg=0
         pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
         ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
         hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
        ----------------------------------------
...
        ----------------------------------------
        KGX Atomic Operation Log 7000000dcd36e38
         Mutex 7000000d71fd6b0(4910, 0) idn 16a1ebe6 oper EXCL
         Cursor Pin uid 4910 efd 0 whr 1 slp 0
         opr=3 pso=7000000e79b9030 flg=0
         pcs=7000000d71fd6b0 nxt=0 flg=35 cld=0 hd=7000000fda5fc00 par=7000000d71fdaa0
         ct=0 hsh=0 unp=0 unn=0 hvl=d71fdd78 nhv=1 ses=7000000f78b5cc0
         hep=7000000d71fd730 flg=80 ld=1 ob=7000000d76d3848 ptr=70000008a8ff478 fex=70000008a8fe788
        ----------------------------------------
...
PROCESS 55:
...
      waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=39147 wait_time=0 seconds since wait started=0
                  idn=16a1ebe6, value=132e00000000, where|sleeps=5002496e9
...
        ----------------------------------------
        KGX Atomic Operation Log 7000000dbb57a10
         Mutex 7000000d71fd6b0(4910, 0) idn 16a1ebe6 oper GET_SHRD
         Cursor Pin uid 4973 efd 0 whr 5 slp 39118
         opr=2 pso=7000000ce4e6fe8 flg=0
         pcs=7000000d71fd6b0 nxt=0 flg=35 cld=0 hd=7000000fda5fc00 par=7000000d71fdaa0
         ct=0 hsh=0 unp=0 unn=0 hvl=d71fdd78 nhv=1 ses=7000000f78b5cc0
         hep=7000000d71fd730 flg=80 ld=1 ob=7000000d76d3848 ptr=70000008a8ff478 fex=70000008a8fe788
        ----------------------------------------
...
PROCESS 62:
...
      (session) sid: 4805 trans: 0, creator: 7000000eab6d018, flag: (e1) USR/- BSY/-/-/-/-/-
                DID: 0001-003E-00010563, short-term DID: 0000-0000-00000000
                txn branch: 0
                oct: 3, prv: 0, sql: 7000000dce39e08, psql: 7000000dc0fc240, user: 72/MENKUL2008
      O/S info: user: akbank, term: L1058, ospid: 3468:3428, machine: AA\L1058
                program: toad.exe
      application name: TOAD 9.1.0.62, hash value=3156025525
      waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=41562 wait_time=0 seconds since wait started=0
                  idn=ec048ba, value=137000000000, where|sleeps=5007f3dc4
...
        ----------------------------------------
        KGX Atomic Operation Log 7000000db0f6b20
         Mutex 7000000d92bce40(4976, 0) idn ec048ba oper GET_SHRD
         Cursor Pin uid 4805 efd 0 whr 5 slp 41523
         opr=2 pso=7000000ce1b0070 flg=0
         pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
         ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
         hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
        ----------------------------------------
...
        ----------------------------------------
        KGX Atomic Operation Log 7000000db0f6ae8
         Mutex 7000000e51cde60(4805, 0) idn 7a99f649 oper EXCL
         Cursor Pin uid 4805 efd 0 whr 1 slp 0
         opr=3 pso=7000000d8b887e8 flg=0
         pcs=7000000e51cde60 nxt=0 flg=35 cld=0 hd=7000000dcd2b108 par=7000000d9991e20
         ct=0 hsh=0 unp=0 unn=0 hvl=d99920f8 nhv=1 ses=7000000f88942e0
         hep=7000000e51cdee0 flg=80 ld=1 ob=7000000e59bf060 ptr=7000000882ed3d8 fex=7000000882ec6e8
        ----------------------------------------
...
PROCESS 65:
...
      waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=35648 wait_time=0 seconds since wait started=0
                  idn=7a99f649, value=12c500000000, where|sleeps=500028b22
...
        ----------------------------------------
        KGX Atomic Operation Log 7000000fd34bbf8
         Mutex 7000000e51cde60(4805, 0) idn 7a99f649 oper GET_SHRD
         Cursor Pin uid 4570 efd 0 whr 5 slp 35618
         opr=2 pso=7000000d898d0c0 flg=0
         pcs=7000000e51cde60 nxt=0 flg=35 cld=0 hd=7000000dcd2b108 par=7000000d9991e20
         ct=0 hsh=0 unp=0 unn=0 hvl=d99920f8 nhv=1 ses=7000000f88942e0
         hep=7000000e51cdee0 flg=80 ld=1 ob=7000000e59bf060 ptr=7000000882ed3d8 fex=7000000882ec6e8
        ----------------------------------------
...
PROCESS 68:
...
   (session) sid: 4698 trans: 0, creator: 7000000f98a7320, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0001-0044-00000344, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 3, prv: 0, sql: 7000000c5281eb8, psql: 7000000d4d7e910, user: 72/MENKUL2008
    O/S info: user: geneks, term: AKYGM011, ospid: 3168:3148, machine: AKYATIRIM\AKYGM011
              program:
    waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=44607 wait_time=0 seconds since wait started=0
                idn=ec048ba, value=137000000000, where|sleeps=5007f3dfe
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000dc155c48
       Mutex 7000000d92bce40(4976, 0) idn ec048ba oper GET_SHRD
       Cursor Pin uid 4698 efd 0 whr 5 slp 8722
       opr=2 pso=7000000cea6fce8 flg=0
       pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
       ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
       hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
      ----------------------------------------
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000dc155c10
       Mutex 7000000e5f9cd90(4698, 0) idn 651e7adb oper EXCL
       Cursor Pin uid 4698 efd 0 whr 1 slp 0
       opr=3 pso=7000000cef62b68 flg=0
       pcs=7000000e5f9cd90 nxt=0 flg=34 cld=1 hd=7000000dcea52a8 par=7000000e5f9d708
       ct=1 hsh=0 unp=0 unn=0 hvl=e5f9d098 nhv=1 ses=7000000f782cbe0
       hep=7000000e5f9ce10 flg=80 ld=1 ob=7000000e53207c8 ptr=700000099b3ef88 fex=700000099b3e298
      ----------------------------------------
...
PROCESS 70:
...
    waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=34939 wait_time=0 seconds since wait started=0
                idn=ec048ba, value=137000000000, where|sleeps=5007f3e0f
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000fc176d20
       Mutex 7000000d92bce40(4976, 0) idn ec048ba oper GET_SHRD
       Cursor Pin uid 4784 efd 0 whr 5 slp 33075
       opr=2 pso=7000000ceb24778 flg=0
       pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
       ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
       hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
      ----------------------------------------
...
PROCESS 73:
...
    waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=24155 wait_time=0 seconds since wait started=0
                idn=651e7adb, value=125a00000000, where|sleeps=5000121e2
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000dc4eba08
       Mutex 7000000e5f9cd90(4698, 0) idn 651e7adb oper GET_SHRD
       Cursor Pin uid 4637 efd 0 whr 5 slp 8674
       opr=2 pso=7000000cef8dcb0 flg=0
       pcs=7000000e5f9cd90 nxt=0 flg=34 cld=1 hd=7000000dcea52a8 par=7000000e5f9d708
       ct=1 hsh=0 unp=0 unn=0 hvl=e5f9d098 nhv=1 ses=7000000f782cbe0
       hep=7000000e5f9ce10 flg=80 ld=1 ob=7000000e53207c8 ptr=700000099b3ef88 fex=700000099b3e298
      ----------------------------------------
...
PROCESS 76:
...
    waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=35688 wait_time=0 seconds since wait started=0
                idn=ec048ba, value=137000000000, where|sleeps=5007f3ed4
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000fc655c80
       Mutex 7000000d92bce40(4976, 0) idn ec048ba oper GET_SHRD
       Cursor Pin uid 4806 efd 0 whr 5 slp 35673
       opr=2 pso=7000000ce702c90 flg=0
       pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
       ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
       hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
      ----------------------------------------
...
PROCESS 142:
...
      waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=48159 wait_time=0 seconds since wait started=0
                  idn=16a1ebe6, value=132e00000000, where|sleeps=5002499ac
...
        ----------------------------------------
        KGX Atomic Operation Log 7000000db235510
         Mutex 7000000d71fd6b0(4910, 0) idn 16a1ebe6 oper GET_SHRD
         Cursor Pin uid 4592 efd 0 whr 5 slp 48111
         opr=2 pso=7000000e2252830 flg=0
         pcs=7000000d71fd6b0 nxt=0 flg=35 cld=0 hd=7000000fda5fc00 par=7000000d71fdaa0
         ct=0 hsh=0 unp=0 unn=0 hvl=d71fdd78 nhv=1 ses=7000000f78b5cc0
         hep=7000000d71fd730 flg=80 ld=1 ob=7000000d76d3848 ptr=70000008a8ff478 fex=70000008a8fe788
        ----------------------------------------
...


 All   Comments   Change History      Sort Order: Ascending order - Click to sort in descending order
[ Permlink | « Hide ]
ubTools Support - 07/Jun/08 05:46 PM
Mutex identifier value helps to locate address of mutex. For example: 
PROCESS 23:
...
    waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=42033 wait_time=0 seconds since wait started=0
                idn=ec048ba, value=137000000000, where|sleeps=5007f3271
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000d44f4280
       Mutex 7000000d92bce40(4976, 0) idn ec048ba oper GET_SHRD
       Cursor Pin uid 4939 efd 0 whr 5 slp 44251
       opr=2 pso=7000000cefa5df0 flg=0
       pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
       ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
       hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
      ----------------------------------------

The mutex identifier 0xec048ba at the address 0x7000000d92bce40 is requested in the shared mode (GET_SHRD)


[ Permlink | « Hide ]
ubTools Support - 07/Jun/08 06:11 PM - edited
According to SYSTEMSTATE dump, the following table shows the which process is waiting for the which process:
Waiter Process# Holder Process#
23 33
33 33
34 33
39 33
47 54
53 54
54 33
55 54
62 33
65 62
68 33
70 33
73 68
76 33
142 54

According to the table above, there is a deadlock and the root holder is the process#33. This process waits for itself. That means there is a self-deadlock problem.


[ Permlink | « Hide ]
ubTools Support - 07/Jun/08 06:25 PM - edited
Process#33 state: 
PROCESS 33:
...
    ----------------------------------------
    SO: 7000000f78e0770, type: 4, owner: 7000000fa8bbcf8, flag: INIT/-/-/0x00
    (session) sid: 4976 trans: 7000000f10ce318, creator: 7000000fa8bbcf8, flag: (8100041) USR/- BSY/-/-/-/-/-
              DID: 0001-0021-000193D1, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 3, prv: 0, sql: 7000000fc179fa8, psql: 7000000fc68f018, user: 0/SYS
    O/S info: user: orapaky0, term: , ospid: 5099938, machine: akmenkulp2
              program: sqlplus@akmenkulp2 (TNS V1-V3)
    application name: sqlplus@akmenkulp2 (TNS V1-V3), hash value=0
    waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=27479 wait_time=0 seconds since wait started=0
                idn=ec048ba, value=137000000000, where|sleeps=5007f33be
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000fc64adb8
       Mutex 7000000d92bce40(4976, 0) idn ec048ba oper GET_SHRD
       Cursor Pin uid 4976 efd 0 whr 5 slp 15527
       opr=2 pso=7000000d86f7d88 flg=0
       pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
       ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
       hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
      ----------------------------------------
...
      ----------------------------------------
      KGX Atomic Operation Log 7000000fc64ad80
       Mutex 7000000d92bce40(4976, 0) idn ec048ba oper EXCL
       Cursor Pin uid 4976 efd 0 whr 1 slp 0
       opr=3 pso=7000000cee2b2c0 flg=0
       pcs=7000000d92bce40 nxt=7000000cb6ed710 flg=35 cld=0 hd=7000000fc8611f0 par=7000000d94637a0
       ct=4 hsh=0 unp=0 unn=0 hvl=cb6eda00 nhv=1 ses=7000000f78e0770
       hep=7000000d92bcec0 flg=80 ld=1 ob=7000000d9c1b3a8 ptr=7000000991e6108 fex=7000000991e5418
      ----------------------------------------
...

Process#33 holds mutex identifier 0xec048ba in the exclusive mode. But, it requests the same identifier in the shared mode. It's self-deadlock bug.


[ Permlink | « Hide ]
ubTools Support - 07/Jun/08 06:39 PM - edited
Unfortunately, there is no stack trace of process#33 to find the kernel function in which it's was running. But, the following SQL ran by process#33 helps to narrow down the Oracle bugs: 
      LIBRARY OBJECT HANDLE: handle=7000000fc179fa8 mtx=7000000fc17a0d8(1) cdp=1
      name=
select i.obj#, i.rowcnt, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey,i.clufac, i.blevel
, i.analyzetime, i.samplesize, decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),
 i.flags, ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist
 where i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
      hash=25d75620e6d3487e18921ac30ec048ba timestamp=06-06-2008 23:01:28
...

Looks like a statistic collection SQL...


[ Permlink | « Hide ]
ubTools Support - 07/Jun/08 06:44 PM
Oracle Note:5907779.8: 
This problem is introduced in 10.2.0.3 

A process may hang with a self deadlock typically when executing 
DBMS_STATS. The hung process shows itself waiting on a
"cursor: pin S wait on X" waitevent waiting for an object
that it has pinned itself.

According to the note, this problem has been fixed in Oracle 10.2.0.4.
Powered by Atlassian JIRA the Professional Issue Tracker. (Standard Edition, Version: 3.12.3-#302) - Bug/feature request - Atlassian news - Contact Administrators