Article Original Creation Date: 2012-02-23

Overview

After upgrading to SG4.1.4, high database load average (>53%) may be observed.

 

Environment

Solaris 10

JBoss 4.0.4GA

Oracle 10

Apache-Tomcat 5.5.25  

Root Cause

Mainly the following 2 queries cause this behavior:

• UPDATE sprt_ec_device SET ip_address = null WHERE ip_address = :1 AND unique_id_string != :2;  

• SELECT 1 FROM sprt_ec_device WHERE ip_address = :1 AND id != :2;

Both queries were not optimized when checking via Explain plan.
The first query problem was solved by adding an index called IDXJES.

Resolution

To correct this issue add the following index:  

CREATE INDEX "XXX"."SPRT_EC_DEVICE_IDXJES" ON "XXX"."SPRT_EC_DEVICE" "IP_ADDRESS", "UNIQUE_ID_STRING" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT TABLESPACE "XXX" ;