kksfbc child completion

It’s been long time that i have blogged. But here is the small issue which was encounter few weeks back

One particular job was taking long time and was waiting on following Wait.


                                                       Wait Wait
  SID Username     Event                              hsecs  is
----- ------------ -------------------------------- ------- ----
 1637 XXXXX        kksfbc child completion                5 prev

Here are some more details. As you can see this session was mostly on CPU and not moving forward.

server1:XYZ>@sw 1637

    SID USERN STATE   EVENT                                          SEQ# SEC_IN_STATE SQL_ID         CHN P1                        P2                        P3
------- ----- ------- ---------------------------------------- ---------- ------------ ------------- ---- ------------------------- ------------------------- -------------------------
   1637 XXXXX WORKING On CPU / runqueue                             29784       429481 31z2drgv36qpw      0                         0                         0

    SID USERN STATE   EVENT                                          SEQ# SEC_IN_STATE SQL_ID         CHN P1                        P2                        P3
------- ----- ------- ---------------------------------------- ---------- ------------ ------------- ---- ------------------------- ------------------------- -------------------------
   1637 XXXXX WORKING On CPU / runqueue                             29784       429481 31z2drgv36qpw      0                         0                         0

    SID USERN STATE   EVENT                                          SEQ# SEC_IN_STATE SQL_ID         CHN P1                        P2                        P3
------- ----- ------- ---------------------------------------- ---------- ------------ ------------- ---- ------------------------- ------------------------- -------------------------
   1637 XXXXX WORKING On CPU / runqueue                             29784       429482 31z2drgv36qpw      0                         0                         0

    SID USERN STATE   EVENT                                          SEQ# SEC_IN_STATE SQL_ID         CHN P1                        P2                        P3
------- ----- ------- ---------------------------------------- ---------- ------------ ------------- ---- ------------------------- ------------------------- -------------------------
   1637 XXXXX WORKING On CPU / runqueue                             29784       429483 31z2drgv36qpw      0                         0                         0

SLEEPS       GETS MUTEX_TYPE              ID GET_LOCATION                             REQ        BLK OBJECT_NAME
---------- ---------- --------------- ---------- --------------------------------- ---------- ---------- -------------------------------------
     53352 1682367421 Cursor Pin      2213265387 kksfbc [KKSCHLFSP2]                     1649       1302 SELECT * from abc where id=:B1
     53351 1682317807 Cursor Pin      2213265387 kksfbc [KKSCHLFSP2]                     1668       1676 SELECT * from abc where id=:B1
     53348 1682026882 Cursor Pin      2213265387 kksLockDelete [KKSCHLPIN6]              2018       1334 SELECT * from abc where id=:B1
     53347 1682026002 Cursor Pin      2213265387 kksfbc [KKSCHLFSP2]                     1323       1393 SELECT * from abc where id=:B1
     53346 1681937885 Cursor Pin      2213265387 kksLockDelete [KKSCHLPIN6]              1283       1408 SELECT * from abc where id=:B1
     53345 1681850426 Cursor Pin      2213265387 kksfbc [KKSCHLFSP2]                     1283       1323 SELECT * from abc where id=:B1
     53344 1681743220 Cursor Pin      2213265387 kksfbc [KKSCHLFSP2]                     2048       1679 SELECT * from abc where id=:B1
     53343 1681468292 Cursor Pin      2213265387 kksfbc [KKSCHLFSP2]                     1495       1686 SELECT * from abc where id=:B1
     53342 1681409080 Cursor Pin      2213265387 kksfbc [KKSCHLFSP2]                     2018       1669 SELECT * from abc where id=:B1
     53339 1681267876 Cursor Pin      2213265387 kksfbc [KKSCHLFSP2]                     1714       1668 SELECT * from abc where id=:B1
server1:XYZ>select * from V$mutex_sleep;

MUTEX_TYPE                       LOCATION                                     SLEEPS  WAIT_TIME
-------------------------------- ---------------------------------------- ---------- ----------
Cursor Parent                    kkspsc0 [KKSPRTLOC27]                            20         37
Cursor Parent                    kkspsc0 [KKSPRTLOC26]                          3440       4288
Cursor Parent                    kksfbc [KKSPRTLOC2]                              97       1311
Cursor Parent                    kksfbc [KKSPRTLOC1]                              36         79
Cursor Pin                       kksLockDelete [KKSCHLPIN6]                    13544      55385
Cursor Pin                       kksxsccmp [KKSCHLPIN5]                           15      48900
Cursor Pin                       kkslce [KKSCHLPIN2]                           22641  209586425
Cursor Pin                       kksfbc [KKSCHLPIN1]                              46     166792
Cursor Pin                       kksfbc [KKSCHLFSP2]                          106768  632164142

So, it seems that that particular SQL was burning CPU while in parsing phase and after that it releases runtime locks. In between that it was just burning CPU.

This Seems Like Bug 6795880 Session spins / OERI after ‘kksfbc child completion’ wait – superceded.

“A session may go into an infinite spin just after a wait for ‘kksfbc child completion’. The spin occurs with a stack including kksSearchChildList -> kkshgnc where kksSearchChildList loops forever.”

Advertisements

About Taral
I am Rookie to Oracle Technology so let's see where it goes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: