INSERTs Blocking WPs

[Former Member]

Hi Experts,

We have a strange issue in our production system. During month end we have noticed so many INSERTS against BSAS table and eventually Work processes are getting blocked. We also see locks on AGKO table during this issue. Even after killing the lock and the jobs, UPD WPs block and the oracle INSERT session is not getting cleared. To fix the issue, we usually relocate the database service to the second node (we have 2 node RAC) and restart the jobs. Interestingly the same issue repeated when running on single node, so this does not seem to be a RAC issue. Other important point is BSAS is compressed in our database. We are on oracle 11.2.0.1 and advanced compression is turned on for the huge tables to save space. Table was compressed like 5 months ago and was perfectly fine so far.

Does anyone has faced similar kind of issues ?, Do high concurrency activity against a compresses table will block the INSERTs? I think Oracle takes care of segment management Since ASSM is turned on and is a pre requisite for compression. So no adjustment to init trans, free list etc are to me made .

Insert sessions from ST04:

INSERT INTO "BSAS" VALUES( :A0 , :A1 , :A2 , :A3 , :A4 , :A5

INSERT INTO "BSAS" VALUES( :A0 , :A1 , :A2 , :A3 , :A4 , :A5

INSERT INTO "BSAS" VALUES( :A0 , :A1 , :A2 , :A3 , :A4 , :A5

INSERT INTO "BSAS" VALUES( :A0 , :A1 , :A2 , :A3 , :A4 , :A5

INSERT INTO "BSAS" VALUES( :A0 , :A1 , :A2 , :A3 , :A4 , :A5

INSERT INTO "BSAS" VALUES( :A0 , :A1 , :A2 , :A3 , :A4 , :A5

INSERT INTO "BSAS" VALUES( :A0 , :A1 , :A2 , :A3 , :A4 , :A5

INSERT INTO "BSAS" VALUES( :A0 , :A1 , :A2 , :A3 , :A4 , :A5

INSERT INTO "BSAS" VALUES( :A0 , :A1 , :A2 , :A3 , :A4 , :A5

INSERT INTO "BSAS" VALUES( :A0 , :A1 , :A2 , :A3 , :A4 , :A5

Thanks

Prince Kudilil

[Former Member]

Do the sessions totally stall, or are they just slowing down each other? Please provide the DB01 information and ST04 -> Wait Event Analysis -> Session Monitor.

If you can reproduce the issue on the test system then i suggest you uncompress the table there to verify it is related to compression.

Cheers Michael

[Former Member]

Sorry for the delay in replying.

Avg

%Time Total Wait wait Waits % DB

Event Waits -outs Time (s) (ms) /txn time

-

---

-

---

-

---

-

---

-

---

-

---

-

---

enq: TX - row lock content 9,335 0 61,316 6568 0.2 44.5

db file sequential read 7,319,576 0 20,445 3 191.7 14.8

buffer busy waits 56,615,944 0 3,922 0 1,482.9 2.8

latch: cache buffers chain 18,591,332 0 1,346 0 486.9 1.0

read by other session 433,936 0 1,191 3 11.4 .9

gc buffer busy acquire 276,305 2 1,186 4 7.2 .9

direct path read 45,326 0 707 16 1.2 .5

log file sync 38,773 0 464 12 1.0 .3

gc cr grant 2-way 305,594 0 146 0 8.0 .1

gc current grant busy 144,691 0 120 1 3.8 .1

SQL*Net more data to clien 1,301,684 0 116 0 34.1 .1

SQL*Net message to client 55,379,406 0 104 0 1,450.5 .1

db file parallel read 6,333 0 96 15 0.2 .1

db file scattered read 8,091 0 86 11 0.2 .1

gc current block 2-way 117,963 0 72 1 3.1 .1

gc remaster 95 6 60 632 0.0 .0

SQL*Net break/reset to cli 454,010 0 49 0 11.9 .0

control file sequential re 78,090 0 44 1 2.0 .0

gc current grant 2-way 47,290 0 22 0 1.2 .0

As per SAP , this issue is not a compression issue. It was a locking issue on RFBLG which prevents inserts on BSAS. So we are working with development team to see if anything can be changed at program level.

Thanks

Prince Kudilil