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" ;