Jump to content
p_sony_2000@yahoo.com

Deadlock and need help to understand the Oracle Trace File

Recommended Posts

Hi,

There is a deadlock occurred in our application and the DBA emailed the Oracle Trace file which contains information on the Deadlock. I am unable to identify how the 2 SQL's in the trace file can cause the deadlock. I could not find the data for the rowids mentioned in the trace file. Need help to resolve the deadlock issue after analyzing the trace file

DEADLOCK DETECTED ( ORA-00060 )

 

[Transaction Deadlock]

 

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

 

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-00130017-00e3e88b        75     354     X             83     618           X

TX-00160010-01fc51aa        83     618     X             75     354           X

 

session 354: DID 0001-004B-00A57146     session 618: DID 0001-0053-00F60097

session 618: DID 0001-0053-00F60097     session 354: DID 0001-004B-00A57146

 

Rows waited on:

  Session 354: obj - rowid = 0002C8A3 - AAAsijAAMAAPdyCAAC

  (dictionary objn - 182435, file - 12, block - 4054146, slot - 2)

  Session 618: obj - rowid = 0002C8A3 - AAAsijAANAAD6igAAA

  (dictionary objn - 182435, file - 13, block - 1026208, slot - 0)

 

----- Information for the OTHER waiting sessions -----

Session 618:

  sid: 618 ser: 15833 audsid: 1467298090 user: 127/APPOWNER

    flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

    flags2: (0x40009) -/-/INC

  pid: 83 O/S info: user: grid, term: UNKNOWN, ospid: 705

    image: oracle@usplsabdb001

  client details:

    O/S info: user: ecowner, term: , ospid: 702

    machine: usplsabdb001 program: runOneProc@usplsabdb001 (TNS V1-V3)

    application name: runOneProc@usplsabdb001 (TNS V1-V3), hash value=11319988

  current SQL:

  UPDATE PTR_DATA SET PRL_PROD_UPC_MATCH = 'Y' WHERE PL_RTLR_CO_ID = :B2 AND PRL_PROD_UPC_MATCH IS NULL AND SAR_SEQ_NUM = :B1 AND PL_SEQ_NUM IN ( SELECT DISTINCT PL_SEQ_NUM FROM PROMO_LIST, PTR_DATA WHERE SAR_SEQ_NUM = :B1 AND PRL_PROMO_END_DATE >= TRUNC (SYSDATE) AND ( ( (PRL_PROMO_END_DATE >= TO_DATE (PL_START_DATE, 'MM/DD/YYYY' ) ) AND (PRL_PROMO_END_DATE <= TO_DATE (PL_END_DATE, 'MM/DD/YYYY' ) ) ) OR ( (PRL_PROMO_START_DATE >= TO_DATE (PL_START_DATE, 'MM/DD/YYYY' ) ) AND (PRL_PROMO_START_DATE <= TO_DATE (PL_END_DATE, 'MM/DD/YYYY' ) ) ) OR ( (PRL_PROMO_END_DATE >= TO_DATE (PL_END_DATE, 'MM/DD/YYYY' ) ) AND (PRL_PROMO_START_DATE < TO_DATE (PL_END_DATE, 'MM/DD/YYYY' ) ) ) ) AND PRL_PH1_WSLR_CO_ID = PL_WSLR_CO_ID AND PRL_PH1_RTLR_CO_ID = PL_RTLR_CO_ID AND ( ( (TRIM (PRL_PH4_MKT_QUAL) = TRIM

 

----- End of information for the OTHER waiting sessions -----

 

Information for THIS session:

 

----- Current SQL Statement for this session (sql_id=0qmf3j8nk7f29) -----

UPDATE PTR_DATA A SET (A.SPL_CATEGORY, A.SPL_FAMILY, A.SPL_NAME, A.SPL_BRAND, A.SPL_PACKAGE, A.SPL_CASE_TOTAL_NUM, A.SPL_PACKAGE_GROUP) = (SELECT DISTINCT B.SPL_CATEGORY, B.SPL_FAMILY, B.SPL_NAME, B.SPL_BRAND, B.SPL_PACKAGE, B.SPL_CASE_TOTAL_NUM, B.SPL_PACKAGE_GROUP FROM SA_PRODUCT_LIBRARY B WHERE B.SPL_PROD_UPC = A.PL_PROD_UPC) WHERE A.PL_PROD_UPC IS NOT NULL

----- PL/SQL Stack -----

----- PL/SQL Call Stack -----

  object      line  object

  handle    number  name

0x2adccfb00     15408  package body APPOWNER.PRICING

0x2b14c5770         1  anonymous block

===================================================

PROCESS STATE

-------------

Process global information:

     process: 0x2a64284a8, call: 0x290c632a8, xact: 0x29f6e8b68, curses: 0x2a65230b0, usrses: 0x2a65230b0

     in_exception_handler: no

  ----------------------------------------

  SO: 0x2a64284a8, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3

   proc=0x2a64284a8, name=process, file=ksu.h LINE:12721, pg=0

  (process) Oracle pid:75, ser:98, calls cur/top: 0x290c632a8/0x290c5ef18

            flags : (0x0) -

            flags2: (0x0),  flags3: (0x10)

            intr error: 0, call error: 0, sess error: 0, txn error 0

            intr queue: empty

    ksudlp FALSE at location: 0

  (post info) last post received: 0 0 264

              last post received-location: kgsk2.h LINE:669 ID:kgskrunnextint: posting new vt to run

              last process to post me: 0x2b839de20 108 0

              last post sent: 0 0 26

              last post sent-location: ksa2.h LINE:285 ID:ksasnd

              last process posted by me: 0x29e390508 2 6

    (latch info) wait_event=0 bits=0x0

    Process Group: DEFAULT, pseudo proc: 0x29e3ef680

    O/S info: user: grid, term: UNKNOWN, ospid: 64901

    OSD pid info: Unix process pid: 64901, image: oracle@usplsabdb001

    ----------------------------------------

    SO: 0x2a777a570, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

 

    (FOB) 0x2a777a570 flags=2560 fib=0x2a35e4510 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/undotbs_1.292.861270615

     fno=36 lblksz=8192 fsiz=4194176

    ----------------------------------------

    SO: 0x2a777a440, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a777a440 flags=2560 fib=0x2a35d7970 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/undotbs_1.263.863072479

     fno=3 lblksz=8192 fsiz=2560000

    ----------------------------------------

    SO: 0x2a777a310, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a777a310 flags=2560 fib=0x2a35ed310 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/undotbs_1.319.938781973

     fno=59 lblksz=8192 fsiz=1572864

    ----------------------------------------

    SO: 0x2a777a1e0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a777a1e0 flags=2560 fib=0x2a35eccd8 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/undotbs_1.318.938781535

     fno=58 lblksz=8192 fsiz=1572864

    ----------------------------------------

    SO: 0x2a775f0e0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a775f0e0 flags=2560 fib=0x2a35db728 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.274.860481367

     fno=13 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a776e590, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a776e590 flags=2560 fib=0x2a35dfb00 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.285.860482879

     fno=24 lblksz=8192 fsiz=4185600

    ----------------------------------------

    SO: 0x2a776bcd0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a776bcd0 flags=2560 fib=0x2a35e38b8 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.294.897330327

     fno=34 lblksz=8192 fsiz=3840000

    ----------------------------------------

 SO: 0x2a776b208, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a776b208 flags=2560 fib=0x2a35dc9a0 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.277.860481647

     fno=16 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a776b0c0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a776b0c0 flags=2560 fib=0x2a35d9840 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.269.860480905

     fno=8 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a776a728, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a776a728 flags=2560 fib=0x2a35e2008 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.291.885268403

     fno=30 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a7769c60, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7769c60 flags=2560 fib=0x2a35de250 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.281.860482349

     fno=20 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a77650d0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a77650d0 flags=2560 fib=0x2a35dd5f8 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.279.860481913

     fno=18 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a7764fa0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7764fa0 flags=2560 fib=0x2a35dcfd8 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.278.860481845

     fno=17 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a7764c10, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7764c10 flags=2560 fib=0x2a35dc380 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.276.860481485

     fno=15 lblksz=8192 fsiz=3200000

    ----------------------------------------

SO: 0x2a775ff50, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a775ff50 flags=2560 fib=0x2a35e0758 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.287.860483073

     fno=26 lblksz=8192 fsiz=4185600

    ----------------------------------------

    SO: 0x2a7756bd8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7756bd8 flags=2560 fib=0x2a35ea7d0 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.313.915868563

     fno=52 lblksz=8192 fsiz=2432000

    ----------------------------------------

    SO: 0x2a7756370, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7756370 flags=2560 fib=0x2a35ea1b0 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.312.915868355

     fno=51 lblksz=8192 fsiz=2432000

    ----------------------------------------

    SO: 0x2a7756240, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7756240 flags=2560 fib=0x2a35e8f20 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.309.913366385

     fno=48 lblksz=8192 fsiz=3456000

    ----------------------------------------

    SO: 0x2a77552a0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a77552a0 flags=2560 fib=0x2a35ddc30 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.280.860481981

     fno=19 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a7754b68, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7754b68 flags=2560 fib=0x2a35dbd48 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.275.860481423

     fno=14 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a7753248, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7753248 flags=2560 fib=0x2a35db0f0 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.273.860481293

     fno=12 lblksz=8192 fsiz=4096000

    ----------------------------------------

SO: 0x2a77529e0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a77529e0 flags=2560 fib=0x2a35daad0 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.272.860481231

     fno=11 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a77528b0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a77528b0 flags=2560 fib=0x2a35d9e78 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.270.860480995

     fno=9 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a77431a0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a77431a0 flags=2560 fib=0x2a35d8be8 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.267.860480625

     fno=6 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a7740da0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7740da0 flags=2560 fib=0x2a35e13b0 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.289.860483209

     fno=28 lblksz=8192 fsiz=4185600

    ----------------------------------------

    SO: 0x2a77523d8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a77523d8 flags=2560 fib=0x2a35e19e8 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.261.884958689

     fno=29 lblksz=8192 fsiz=4194176

    ----------------------------------------

    SO: 0x2a77522a8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a77522a8 flags=2560 fib=0x2a35e0d90 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.288.860483181

     fno=27 lblksz=8192 fsiz=4185600

 SO: 0x2a774edd8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a774edd8 flags=2560 fib=0x2a35eba60 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.320.966325123

     fno=55 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a774e588, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a774e588 flags=2560 fib=0x2a35de888 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.282.860482445

     fno=21 lblksz=8192 fsiz=3840000

    ----------------------------------------

    SO: 0x2a774dd20, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a774dd20 flags=2560 fib=0x2a35df4e0 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.284.860482773

     fno=23 lblksz=8192 fsiz=4185600

    ----------------------------------------

    SO: 0x2a7743790, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7743790 flags=2560 fib=0x2a35eb428 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.315.915869027

     fno=54 lblksz=8192 fsiz=4185600

    ----------------------------------------

    SO: 0x2a7743530, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7743530 flags=2560 fib=0x2a35e82c8 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.307.912580283

     fno=46 lblksz=8192 fsiz=4185600

    ----------------------------------------

    SO: 0x2a77408e0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a77408e0 flags=2560 fib=0x2a35e3298 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.293.897330201

     fno=33 lblksz=8192 fsiz=4185600

    ----------------------------------------

    SO: 0x2a7761c18, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7761c18 flags=2560 fib=0x2a35e9b78 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.311.913366471

     fno=50 lblksz=8192 fsiz=3456000

 SO: 0x2a77614e0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a77614e0 flags=2560 fib=0x2a35e9558 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.310.913366421

     fno=49 lblksz=8192 fsiz=3456000

    ----------------------------------------

    SO: 0x2a77613b0, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a77613b0 flags=2560 fib=0x2a35e7ca8 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.306.912396193

     fno=45 lblksz=8192 fsiz=3328000

    ----------------------------------------

    SO: 0x2a7760688, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7760688 flags=2560 fib=0x2a35e57a0 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.300.902202953

     fno=39 lblksz=8192 fsiz=3456000

    ----------------------------------------

    SO: 0x2a775f818, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a775f818 flags=2560 fib=0x2a35e5168 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.297.897330475

     fno=38 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a775d418, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a775d418 flags=2560 fib=0x2a35eae08 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.314.915868959

     fno=53 lblksz=8192 fsiz=4185600

    ----------------------------------------

    SO: 0x2a775d1b8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a775d1b8 flags=2560 fib=0x2a35e2640 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.268.885268515

     fno=31 lblksz=8192 fsiz=4194176

 ----------------------------------------

    SO: 0x2a775ccf8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a775ccf8 flags=2560 fib=0x2a35e4b48 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.296.897330425

     fno=37 lblksz=8192 fsiz=3840000

    ----------------------------------------

    SO: 0x2a775c490, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a775c490 flags=2560 fib=0x2a35e63f8 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.302.904197903

     fno=41 lblksz=8192 fsiz=4185600

    ----------------------------------------

    SO: 0x2a775b750, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a775b750 flags=2560 fib=0x2a35e7670 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.305.912141273

     fno=44 lblksz=8192 fsiz=4185600

    ----------------------------------------

    SO: 0x2a7759828, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7759828 flags=2560 fib=0x2a35e3ef0 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.295.897330381

     fno=35 lblksz=8192 fsiz=3840000

    ----------------------------------------

    SO: 0x2a7758c30, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7758c30 flags=2560 fib=0x2a35da498 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_data.271.860481087

     fno=10 lblksz=8192 fsiz=4096000

    ----------------------------------------

    SO: 0x2a77583c8, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a77583c8 flags=2560 fib=0x2a35e2c60 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.258.897330139

     fno=32 lblksz=8192 fsiz=4185600

    ----------------------------------------

    SO: 0x2a7758038, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7758038 flags=2560 fib=0x2a35e8900 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.308.912580345

     fno=47 lblksz=8192 fsiz=4185600

    ----------------------------------------

 SO: 0x2a7757a30, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7757a30 flags=2560 fib=0x2a35e0138 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/ab_index.286.860482995

     fno=25 lblksz=8192 fsiz=4185600

    ----------------------------------------

    SO: 0x2a7757900, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a7757900 flags=2560 fib=0x2a35e7050 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/system.304.911263809

     fno=43 lblksz=8192 fsiz=1920000

    ----------------------------------------

    SO: 0x29d7a4fb8, type: 124, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=ASM map operations, file=kffm2.h LINE:418, pg=0

    transition:(nil)

    busylist:[29d7a5020,29d7a5020]

    freelist:[2b59bf1a0,2b4d030b8]

      KFFMOP: hash link:[2b59bf190,2b59bf190] sobj link:[2b4f89cf8,29d7a5010]

        map kggrp:[0x2b5fab848, 0, valid]  map id:44

        group:[2,1412979558] file:[302,904197903] extent:19408

        flags:0x0000 disk:12 au:25307 lock:0 proc:0x2a64284a8

      KFFMOP: hash link:[2b4f89ce8,2b4f89ce8] sobj link:[2b4d030b8,2b59bf1a0]

        map kggrp:[0x2b5fab848, 0, valid]  map id:44

        group:[2,1412979558] file:[302,904197903] extent:14252

        flags:0x0000 disk:19 au:60250 lock:0 proc:0x2a64284a8

      KFFMOP: hash link:[2b4d030a8,2b4d030a8] sobj link:[29d7a5010,2b4f89cf8]

        map kggrp:[0x2b5fab848, 0, valid]  map id:44

        group:[2,1412979558] file:[302,904197903] extent:19408

        flags:0x0000 disk:12 au:25307 lock:0 proc:0x2a64284a8

    ----------------------------------------

    SO: 0x2a776de58, type: 10, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x1 c: 0x1

     proc=0x2a64284a8, name=FileOpenBlock, file=ksfd.h LINE:6688, pg=0

    (FOB) 0x2a776de58 flags=2560 fib=0x2a35d6d18 incno=0 pending i/o cnt=0

     fname=+DATA_GRP/prod/datafile/system.256.860473317

     fno=1 lblksz=8192 fsiz=4192000

    ----------------------------------------

    SO: 0x2a65230b0, type: 4, owner: 0x2a64284a8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3

     proc=0x2a64284a8, name=session, file=ksu.h LINE:12729, pg=0

    (session) sid: 354 ser: 14153 trans: 0x29f6e8b68, creator: 0x2a64284a8

              flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

              flags2: (0x40009) -/-/INC

              DID: , short-term DID:

            txn branch: (nil)

              edition#: 100              oct: 6, prv: 0, sql: 0x2b56b2f78, psql: 0x2b53d55b0, user: 127/APPOWNER

    ksuxds FALSE at location: 0

    service name: PROD

    client details:

      O/S info: user: ecowner, term: , ospid: 64898

      machine: usplsabdb001 program: runOneProc@usplsabdb001 (TNS V1-V3)

      application name: runOneProc@usplsabdb001 (TNS V1-V3), hash value=11319988

    Current Wait Stack:

     0: waiting for 'enq: TX - row lock contention'

        name|mode=0x54580006, usn<<16 | slot=0x160010, sequence=0x1fc51aa

        wait_id=24015 seq_num=24016 snap_id=1

        wait times: snap=6.303755 sec, exc=6.303755 sec, total=6.303755 sec

        wait times: max=infinite, heur=6.303755 sec

        wait counts: calls=3 os=3

        in_wait=1 iflags=0x15a0

    There is at least one session blocking this session.

      Dumping 1 direct blocker(s):

        inst: 1, sid: 618, ser: 15833

      Dumping final blocker:

        inst: 1, sid: 618, ser: 15833

    Wait State:

      fixed_waits=0 flags=0x22 boundary=(nil)/-1

    Session Wait History:

        elapsed time of 0.036797 sec since current wait

     0: waited for 'db file sequential read'

        file#=0x29, block#=0x25e825, blocks=0x1

        wait_id=24014 seq_num=24015 snap_id=1

        wait times: snap=0.000129 sec, exc=0.000129 sec, total=0.000129 sec

        wait times: max=infinite

        wait counts: calls=0 os=0

        occurred after 0.000447 sec of elapsed time

     1: waited for 'db file sequential read'

        file#=0x29, block#=0x1bd666, blocks=0x1

        wait_id=24013 seq_num=24014 snap_id=1

        wait times: snap=0.000137 sec, exc=0.000137 sec, total=0.000137 sec

        wait times: max=infinite

        wait counts: calls=0 os=0

        occurred after 0.000969 sec of elapsed time

     2: waited for 'db file sequential read'

        file#=0x29, block#=0x25e819, blocks=0x1

        wait_id=24012 seq_num=24013 snap_id=1

        wait times: snap=0.000128 sec, exc=0.000128 sec, total=0.000128 sec

                                                                                  wait times: max=infinite

        wait counts: calls=0 os=0

        occurred after 0.000815 sec of elapsed time

     3: waited for 'db file sequential read'

        file#=0x20, block#=0x3d0ef6, blocks=0x1

        wait_id=24011 seq_num=24012 snap_id=1

        wait times: snap=0.000132 sec, exc=0.000132 sec, total=0.000132 sec

        wait times: max=infinite

        wait counts: calls=0 os=0

        occurred after 0.001581 sec of elapsed time

     4: waited for 'db file sequential read'

        file#=0x29, block#=0x25e821, blocks=0x1

        wait_id=24010 seq_num=24011 snap_id=1

        wait times: snap=0.000127 sec, exc=0.000127 sec, total=0.000127 sec

        wait times: max=infinite

        wait counts: calls=0 os=0

        occurred after 0.000073 sec of elapsed time

     5: waited for 'db file sequential read'

        file#=0x29, block#=0x1bd662, blocks=0x1

        wait_id=24009 seq_num=24010 snap_id=1

        wait times: snap=0.000283 sec, exc=0.000283 sec, total=0.000283 sec

        wait times: max=infinite

        wait counts: calls=0 os=0

        occurred after 0.000869 sec of elapsed time

     6: waited for 'db file sequential read'

        file#=0x20, block#=0x3d0eee, blocks=0x1

        wait_id=24008 seq_num=24009 snap_id=1

        wait times: snap=0.000136 sec, exc=0.000136 sec, total=0.000136 sec

        wait times: max=infinite

        wait counts: calls=0 os=0

        occurred after 0.001619 sec of elapsed time

     7: waited for 'db file sequential read'

        file#=0x29, block#=0x25e81d, blocks=0x1

        wait_id=24007 seq_num=24008 snap_id=1

        wait times: snap=0.000141 sec, exc=0.000141 sec, total=0.000141 sec

        wait times: max=infinite

        wait counts: calls=0 os=0

        occurred after 0.000077 sec of elapsed time

     8: waited for 'db file sequential read'

        file#=0x29, block#=0x1bd60b, blocks=0x1

        wait_id=24006 seq_num=24007 snap_id=1

 wait times: snap=0.000145 sec, exc=0.000145 sec, total=0.000145 sec

        wait times: max=infinite

        wait counts: calls=0 os=0

        occurred after 0.000102 sec of elapsed time

     9: waited for 'db file sequential read'

        file#=0x27, block#=0x13b300, blocks=0x1

        wait_id=24005 seq_num=24006 snap_id=1

        wait times: snap=0.008147 sec, exc=0.008147 sec, total=0.008147 sec

        wait times: max=infinite

        wait counts: calls=0 os=0

        occurred after 0.000448 sec of elapsed time

    Sampled Session History of session 354 serial 14153

    ---------------------------------------------------

    The sampled session history is constructed by sampling

    the target session every 1 second. The sampling process

    captures at each sample if the session is in a non-idle wait,

    an idle wait, or not in a wait. If the session is in a

    non-idle wait then one interval is shown for all the samples

    the session was in the same non-idle wait. If the

    session is in an idle wait or not in a wait for

    consecutive samples then one interval is shown for all

    the consecutive samples. Though we display these consecutive

    samples  in a single interval the session may NOT be continuously

    idle or not in a wait (the sampling process does not know).

 

    The history is displayed in reverse chronological order.

 

    sample interval: 1 sec, max history 120 sec

    ---------------------------------------------------

    temporary object counter: 0

      ----------------------------------------

      Virtual Thread:

      kgskvt: 0x2b8719d90, sess: 0x2a65230b0 sid: 354 ser: 14153

      vc: (nil), proc: 0x2a64284a8, id: 354

      consumer group cur: OTHER_GROUPS (upd? 0), mapped: DEFAULT_CONSUMER_GROUP, orig:

      vt_state: 0x8, vt_flags: 0xE030, blkrun: 1, numa: 1

      inwait: 1, short wait event: 241 posted_run: 0

      location where insched last set: kgskbwt

      location where insched last cleared: kgskbwt

      location where inwait last set: kgskbwt

      location where inwait last cleared: NULL

      is_assigned: 1, in_sched: 0 (0)

      qcls: 0x2b3aa0f50, qlink: TRUE

                                                       vt_active: 0 (pending: 1)

      vt_pq_active: 0, dop: 0

      used quanta (usecs):

      stmt: 5186860, accum: 0, mapped: 0, tot: 5186860

      cpu start time: 0

      idle time: 0, active time: 4184715 (cg: 4184715)

      cpu yields:

      stmt: 0, accum: 0, mapped: 0, tot: 0

      cpu waits:

      stmt: 0, accum: 0, mapped: 0, tot: 0

      cpu wait time (usecs):

      stmt: 288, accum: 0, mapped: 0, tot: 288

      io waits:

      stmt: 0, accum: 0, mapped: 0, tot: 0

      io wait time:

      stmt: 0, accum: 0, mapped: 0, tot: 0

      ASL queued time outs: 0, time: 0 (cur 0, cg 0)

      PQQ queued time outs: 0, time: 0 (cur 0, cg 0)

      Queue timeout violation: 0

      calls aborted: 0, num est exec limit hit: 0

      undo current: 0k max: 0k

      I/O credits acquired:small=0 large=0

      I/O credits waiting for:small=0 large=0

      KTU Session Commit Cache Dump for IDLs:

      KTU Session Commit Cache Dump for Non-IDLs:

      ----------------------------------------

      KKS-UOL used : 0 locks(used=8, free=2)

      KGX Atomic Operation Log 0x2b541d988

       Mutex (nil)(0, 0) idn 0 oper NONE(0)

       hash table uid 354 efd 15 whr 1 slp 0

       oper=NONE pt1=(nil) pt2=(nil) pt3=(nil)

       pt4=(nil) u41=0 stt=0

      KGX Atomic Operation Log 0x2b541d9d8

       Mutex (nil)(0, 0) idn 0 oper NONE(0)

       hash table uid 354 efd 16 whr 3 slp 0

       oper=NONE pt1=0x7f1192d8 pt2=0x7f119680 pt3=0x7f1192c8

       pt4=(nil) u41=0 stt=0

      KGX Atomic Operation Log 0x2b541da28

       Mutex (nil)(0, 0) idn 0 oper NONE(0)

       FSO mutex uid 354 efd 0 whr 0 slp 0

      KGX Atomic Operation Log 0x2b541da78

       Mutex (nil)(0, 0) idn 0 oper NONE(0)

       FSO mutex uid 354 efd 0 whr 0 slp 0

                                               KGL-UOL SO Cache(total=111, free=18)

      KGX Atomic Operation Log 0x2b3b2d8f8

       Mutex (nil)(0, 0) idn 0 oper NONE(0)

       Library Cache uid 354 efd 23 whr 85 slp 0

       oper=0 pt1=0x2ab3dab00 pt2=0x2b3768150 pt3=(nil)

       pt4=(nil) pt5=(nil) ub4=0

      KGX Atomic Operation Log 0x2b3b2d950

       Mutex (nil)(0, 0) idn 0 oper NONE(0)

       Library Cache uid 354 efd 23 whr 64 slp 0

       oper=0 pt1=(nil) pt2=0x2b3768150 pt3=(nil)

       pt4=(nil) pt5=(nil) ub4=126172

      KGX Atomic Operation Log 0x2b3b2d9a8

       Mutex 0x2b5601a48(0, 0) idn b349788 oper NONE(0)

       Library Cache uid 354 efd 21 whr 70 slp 0

       oper=0 pt1=0x2b5601908 pt2=(nil) pt3=(nil)

       pt4=(nil) pt5=(nil) ub4=4

      KGX Atomic Operation Log 0x2b3b2da00

       Mutex 0x2b5601a48(0, 0) idn b349788 oper NONE(0)

       Library Cache uid 354 efd 21 whr 70 slp 0

       oper=0 pt1=0x2b5601908 pt2=(nil) pt3=(nil)

       pt4=(nil) pt5=(nil) ub4=0

      KGX Atomic Operation Log 0x2b3b2da58

       Mutex (nil)(0, 0) idn 0 oper NONE(0)

       Library Cache uid 354 efd 0 whr 0 slp 0

       oper=0 pt1=(nil) pt2=(nil) pt3=(nil)

       pt4=(nil) pt5=(nil) ub4=0

      KGX Atomic Operation Log 0x2b3b2dab0

       Mutex (nil)(0, 0) idn 0 oper NONE(0)

       Library Cache uid 354 efd 0 whr 0 slp 0

       oper=0 pt1=(nil) pt2=(nil) pt3=(nil)

       pt4=(nil) pt5=(nil) ub4=0

      KGX Atomic Operation Log 0x2b3b2db08

       Mutex (nil)(0, 0) idn 0 oper NONE(0)

       Library Cache uid 354 efd 0 whr 0 slp 0

       oper=0 pt1=(nil) pt2=(nil) pt3=(nil)

       pt4=(nil) pt5=(nil) ub4=0

      KGL SO cache

      SO=0x2b3768150 Link=0x2b37681c0[0x2b3768028,0x2b3b2d8e0]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b3767fb8 Link=0x2b3768028[0x2b3767e28,0x2b37681c0]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b3767db8 Link=0x2b3767e28[0x2b390efe0,0x2b3768028]

 flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b390ef70 Link=0x2b390efe0[0x2b3828010,0x2b3767e28]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b3827fa0 Link=0x2b3828010[0x2b3827f10,0x2b390efe0]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b3827ea0 Link=0x2b3827f10[0x2b3827e10,0x2b3828010]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b3827da0 Link=0x2b3827e10[0x2b3827d10,0x2b3827f10]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b3827ca0 Link=0x2b3827d10[0x2b3827c10,0x2b3827e10]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b3827ba0 Link=0x2b3827c10[0x2b3827b10,0x2b3827d10]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b3827aa0 Link=0x2b3827b10[0x2b3441b00,0x2b3827c10]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b3441a90 Link=0x2b3441b00[0x2b3441a00,0x2b3827b10]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b3441990 Link=0x2b3441a00[0x2b331d5d8,0x2b3441b00]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b331d568 Link=0x2b331d5d8[0x2b331d4d8,0x2b3441a00]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b331d468 Link=0x2b331d4d8[0x2b331d3d8,0x2b331d5d8]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b331d368 Link=0x2b331d3d8[0x2b331d2d8,0x2b331d4d8]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b331d268 Link=0x2b331d2d8[0x2b39ab258,0x2b331d3d8]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b39ab1e8 Link=0x2b39ab258[0x2b39ab158,0x2b331d2d8]

      flg=8000 use=0x2a65230b0 ses=0x2a65230b0

      SO=0x2b39ab0e8 Link=0x2b39ab158[0x2b3b2d8e0,0x2b39ab258]

      flg=8000 use=0x2a65230b0 ses=0x2a651ffd0 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×