Suggestions please - Query loading 98%

From: BN <vze33dv2_at_verizon.net>
Date: 10 Oct 2002 10:22:07 -0700
Message-ID: <acb0c961.0210100922.45fe9662@posting.google.com>

Greetings,

Oracle Database is 8044 runing on IBM RS6000 BOX. 24X7 system OLTP

I see the following query run many times, and loading the system (cpu and io).

SELECT MSG_LEN,MSG_NUM
FROM STORE_MESSAGE WHERE MSG_STATUS = 'WAITING' AND LAST_UPDATE_TIME = (SELECT MIN(LAST_UPDATE_TIME) FROM STORE_MESSAGE WHERE MSG_STATUS = 'WAITING' ) AND ROWNUM = 1 /
Execution Plan

---

          0
SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=42)

          1                  0
  COUNT (STOPKEY)
          2                  1
    TABLE ACCESS (BY INDEX ROWID) OF 'STORE_MESSAGE' (Cost=4 Card=1 Bytes=42)
          3                  2
      INDEX (RANGE SCAN) OF 'IND_SM_STORE_MESSAGE' (NON-UNIQUE) (Cost=3 Card=1)
         4                  3
        SORT (AGGREGATE)
          5                  4
          TABLE ACCESS (FULL) OF 'STORE_MESSAGE' (Cost=1749 Card=23094 Bytes=369504)


desc STORE_MESSAGE
 Name                            Null?    Type
 ------------------------------- -------- ----
 MSG_NUM                         NOT NULL NUMBER(6)
 SERVICE_ID                      NOT NULL NUMBER(3)
 SERVICE_VERSION                 NOT NULL NUMBER(3)
 MSG_STATUS                               VARCHAR2(10)
 FAIL_COUNT                               NUMBER(2)
 LAST_UPDATE_TIME                         DATE
 DATE_CREATED                             DATE
 MSG_LEN                                  NUMBER(5)
 FCIF_MSG                                 LONG

CONSTRAINT_NAME               COLUMN_NAME                   TYPE
----------------------------- ----------------------------- ------
SYS_C001341                   MSG_NUM                       Check
CK1_STORE_MESSAGE             MSG_STATUS                    Check
SYS_C001342                   SERVICE_ID                    Check
SYS_C001343                   SERVICE_VERSION               Check
PK_STORE_MESSAGE              MSG_NUM                       PK
FK_STORE_MESSAGE              SERVICE_ID                    FK
                              SERVICE_VERSION               FK

service_id and service_version are indexed. It is insert and update intensive.
This table has 100K rows. The column msg_status is MSG_STATUS COUNT(*)
---------- ----------

FAILED            625
SUCCEEDED       63686
WAITING          5029

We delete all the succuuded once a month. I appreciate your suggestions .
Regards & Thanks
BN Received on Thu Oct 10 2002 - 12:22:07 CDT