Large CLOBs and attention to detail needed while reading Oracle Support notes.
Posted by John Hallas on October 3, 2013
This post was forwarded to me by Vitaly Kaminsky who did work with me but has now bettered himself elsewhere. He writes :-
I have recently been involved with performance tuning of a database layer for the major Dutch website which was preparing for the “crazy days” of sales on the 2nd to 5th of October.
The general setup was as follows:
2-node physical RAC cluster with fast storage layer running ORACLE 11gR2 SE and ASM.
The important bit above is the “SE” which stands for Standard Edition and implies no performance packs, no AWR, no SQL tuning and no partitioning.
The shop application makes heavy use of Oracle Text functionality and the purpose of the tuning was to ensure we can get 12000 orders per hour through the system. Each of the orders would create a single insert and numerous updates of a CLOB in XML format. This is actually the total info we managed to get from the vendor on what the application performance tuning should be focused on.
As expected, after the first test runs, it became apparent that the stumbling block was literally a block of CLOB storage. When there was enough padding space, the application ran fine and then, suddenly, the database would grind to a halt with “enq: HW – contention” waits.
The waiting time per transaction on the application server side started to increase and the application terminated the test run when transactions begun to time out.
Simple statspack report confirmed what was happening:
|Event||Waits||Time (s)||Avg Wait Time (ms)||% Total Call Time|
|enq: HW – contention||61,236||118,515||1935||89.6|
|db file sequential read||152,792||1,729||11||1.3|
|PX Nsq: PQ load info query||2,337||468||200||.4|
|gc current block 2-way||722,660||323||0||.2|
So, how many calls resulted in waits?
select ksqstreq, ksqstwat from x$ksqst where ksqsttyp='HW' KSQSTREQ KSQSTWAT 100536 21519 Considerable, 21% - every fifth request was waiting for space to be allocated.
Now we come to the basic Russian questions of “Who’s fault it is” and “What to do”.
The answer to the first one will be the default “Application vendor” and I’ve decided to throw the following response to the second one because the obvious solution of hash partitioning was not available:
- Move the offending table and CLOB to a separate tablespace. (If you decide to implement this, don’t forget that CLOBS are not moved when you issue “alter table .. move tablespace” – you need to move them separately with “alter table… move LOB… store as”).
- Set new storage parameters for the new tablespace to avoid any “small chunk increases” and to set PCTVERSION to 0 to avoid any versioning of CLOBS:
- CREATE BIGFILE TABLESPACE “PERFORMANCE” DATAFILE ‘+DATA’ SIZE 20G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 200M SEGMENT SPACE MANAGEMENT AUTO
- ALTER TABLE BASKET MOVE LOB ITEMS STORE AS (TABLESPACE PERFORMANCE PCTVERSION 0 DISABLE STORAGE IN ROW CACHE LOGGING)
Now, it’s time to test the new setup:
As you can see, everything went quite well for a while until we hit the same problem, which looks a bit intermittent and occurring, roughly, every four minutes.
After some basic calculation, the 4 minutes interval was proven to be the time taken for the application to insert about 200MB of CLOBS and the datafile then needs additional extents.
OK, we found the cause of the slowdown for inserts, but why Oracle does not reuse some space of updated CLOBS?
Here we come to the interesting bit – Bug 6376915. Oracle support says:
I have to admit that I came across this but earlier, while looking for a solution and the above confirmation was all I needed to know and I did not examine the page any further, missing one little bit at the bottom:
After setting the events with
ALTER SYSTEM SET EVENT=’44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024′ scope=spfile
- and restarting the database, the problem has gone away and does not show its ugly head any more.
The morale of the story: the lack of an easy fix like partitioning forces you to go back to basics and to examine the problem in-depth which may lead you to better and more elegant solution and the question is – are we losing some skills by relying on the newer and newer technologies to solve our problems?