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

[stefan_koehler · Accepted solution]

Hello Prince,

We are on oracle 11.2.0.1 and advanced compression is turned on for the huge tables to save space

Well at first this environment (11.2.0.1) is not supported with advanced compression - you need at least 11.2.0.2 - check sapnote #1436352 for details.

Does anyone has faced similar kind of issues ?

No. We already discussed a topic () about decreasing performance and CPU usage on compressed tables with LONG RAW columns, but this does not fit in here because the table has not such columns.

Do high concurrency activity against a compresses table will block the INSERTs?

No usually not.

Well the provided information is not enough all - you post the SQL statement but not the corresponding wait event of it. This would be the first indicator to look at. Maybe the inserts were blocked by some huge index split (have seen something like this on compressed EDI indexes), but that is looking like in a crystal ball without any further information.

If the AWR retention time has not been reached you can get all the needed data out of AWR repository.

Regards

Stefan

[Former Member · Accepted solution]

Stefen,

Well at first this environment (11.2.0.1) is not supported with advanced
compression - you need at least 11.2.0.2 - check sapnote #1436352 for details

==> When we start compression , 11.2.0.1 was the prerequisite.

Do you think increasing INITRANS , FREELIST, FREELIST GROUPS will help in anyways on ASSM tables?

[stefan_koehler · Accepted solution]

Hello Prince,

When we start compression , 11.2.0.1 was the prerequisite.

Yes we were one of these early adopters too and started with 11.2.0.1 when it was allowed by SAP. SAP states about some performance issues with 11.2.0.1 only, but i also talked to several guys from Oracle development and there also some serious data corruption issues. So i strongly suggest to upgrade to 11.2.0.2 (which really should not be that big task in my opinion).

Do you think increasing INITRANS , FREELIST, FREELIST GROUPS will help in anyways on ASSM tables?

If you are using an ASSM tablespace (tables are only stored in it, but there is no ASSM table per definition), the parameters FREELISTS and FREELIST GROUPS are useless / irrelevant. The only parameters that are useful / relevant in a LMT and ASSM tablespace are PCTFREE and INITRANS.

For further information check the storage and physical attribute clause:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/clauses009.htm#SQLRF30013

http://docs.oracle.com/cd/E11882_01/server.112/e26088/clauses007.htm#SQLRF30011

But your problem with the wait event "enq: TX - row lock contention" could not be fixed with any of these parameters. If this would be an issue with the ITL slots (INITRANS), then you would see the wait event "enq: TX - allocate ITL entry".

For further information about the wait events - check this:

http://docs.oracle.com/cd/E11882_01/server.112/e16638/instance_tune.htm#PFGRF94503

Regards

Stefan

[Former Member · Accepted solution]

Thanks Stefan for your detail email. I heard that the upgrade 11.2.0.2 is more work since we have to upgrade database, CRS and SAPCTL.

I know my issue cannot be fixed by increasing INITRANS , but got recommendation from DBAs that increasing INITRANS from 1 to 20 would improve performance on compressed tables. Usually during month end we have heavy parallel inserts/updates to some of the compressed tables.

Thanks

Prince kudilil

[stefan_koehler · Accepted solution]

Hello Prince,

yes if you are using RAC (i overlooked that detail in my last reply) - the upgrade procedure is more effort but still worth.

got recommendation from DBAs that increasing INITRANS from 1 to 20 would improve performance on compressed tables. Usually during month end we have heavy parallel inserts/updates to some of the compressed tables.

Well that would be the case if you see high wait times on "enq: TX - allocate ITL entry" during the heavy parallel insert / update phase, but from the posted statistics there is no indicator for this.

By the way Oracle automatically allocates two ITL slots for each block, even if you specify "1". Check the comment section of Michael's blog - i published a test case there:

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

After reading INITRANS & MAXTRANS , I am kind of confused about how it works internally. Hope you can help me to understand it better ..

If INITRANS is set 1 and MAXTRANS is set to 255, imply only one transaction can modify the data block and if there are concurrent activity against the same data block , then the other transactions have to wait until the fist one finish OR oracle can increase the ITL dynamically up to maxtrans if there is space available in the data block ?

Thanks in advance

Prince Kudilil

[stefan_koehler · Accepted solution]

Hello Prince,

If INITRANS is set 1 and MAXTRANS is set to 255, imply only one transaction can modify the data block and if there are concurrent activity against the same data block , then the other transactions have to wait until the fist one finish OR oracle can increase the ITL dynamically up to maxtrans if there is space available in the data block ?

At first forget about MAXTRANS - this parameter has been deprecated. You will always have the maximum limit of 255 ITL slots per block. If you set INITRANS to "1" - Oracle automatically creates two ITL slots per block - so you always have two slots that can be used by transactions. INITRANS should only bet set to a higher value in very rare cases.

If a transaction wants to modify some data in a block - it needs to allocate an ITL slot (which is also used for read consistency) in the block before modifying the data (disregarding the previous undo stuff). If an ITL slot is free - you can go on instantly. If there is no free ITL slot available - Oracle tries to create a new ITL slot in the block up to PCTFREE or MAXTRANS (which is the maximum of 255 by default) - if you have already reached one of both limits - you will see the wait event "enq: TX - allocate ITL entry" and have to wait until a slot gets free again. That is the basic idea

Regards

Stefan

[Former Member · Accepted solution]

Thanks again Stefan for the explanation. We got a recommendation to increase INITRANS to 20, I was wondering how that going to help in performance. By increasing INITRANS to 20 explicitly , is Oracle going to create 20 ITL slots and 20 is always there even PCTFREE linit is reached ?

Thanks

Prince Kudilil

[stefan_koehler · Accepted solution]

Hello Prince,

By increasing INITRANS to 20 explicitly , is Oracle going to create 20 ITL slots and 20 is always there even PCTFREE linit is reached ?

That's the way it works. If you set INITRANS to 20 - Oracle will initially create 20 ITL slots in each (new) formatted block. This amount of ITL slots will be there forever, but if you would need additional ITL slots furthermore Oracle is able to create up top 255 ITL slots (if PCTFREE is not already reached).

Please keep in mind if you set INITRANS to a higher value (and you don't need that amount of ITL slots) you will lose a lot of space in the block - as far as i can rember 24 bytes for each ITL slot. In your example (20 ITL slots) makes a space reservation for ITL slots of round about 6 % for each block. By the way you can also do several block dumps to verify how many ITL slots are currently allocated - so you can get an idea about the usage.

We got a recommendation to increase INITRANS to 20, I was wondering how that going to help in performance.

That's a great attitude. I also tell my customers to question the recommendations - if a consultant can explain (or even better demonstrate with a short demo) how he/she get to a recommendation - you can be pretty sure that he/she knows what he/she is doing.

From my point of view - there is no valuable reason for increasing the INITRANS in your case. The posted statistics do not show any indication for that.

Regards

Stefan

[Former Member · Accepted solution]

Thats right Stephan. I couldn't see any wait on ITL on the table, However there is ITL wait on indexes on that table .I think it showed 12 for the index for that table. Not sure 12 is a big deal or not.

Thanks

Prince Kudilil

[stefan_koehler · Accepted solution]

Hello Prince,

well for this wait event the average / sum wait time is more important than the amount of wait events. Focused on the amount of occured events only it is like nothing.

If this issue still exists (it is the end of the month again) and if it is still critical for you / your company - you maybe want to check out [my consulting services] - if you are interested just write me an e-mail and we can talk about the details.

Regards

Stefan

[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