Re: Suggestions please - Query loading 98%

Hi BN,

I see at least 4 things that could explain the high load :

1. There's a (possible huge) sort in your execution plan. Is your sort_area_size high enough or do you experience large disk sorts ?
2. You delete a big part of the rows once a month. That could, under some conditions, make your indexes very inefficient. Try the following

select segment_name,bytes from dba_segments where segment_type = 'INDEX'
and segment_name in
(select index_name from dba_indexes where table_name = 'STORE_MESSAGE'); and note the result.

Then rebuild the indexes on STORE_MESSAGE and rerun the query above. If some of the indexes take much less space up now, your indexes are probably quite skewed.

The short-term solution is to rebuild the indexes regularly. The long-term to reconsider the table design (i.e. partitioning).

3) There is, AFAIK, no index on the MSG_STATUS column, though it is in the WHERE clause of your subquery. That column begs for a Bitmax index.

4) Since your table is high-volatile : Do you have recent, reliable statistics on it ?

• Kenneth Koenraadt

On 10 Oct 2002 10:22:07 -0700, vze33dv2_at_verizon.net (BN) wrote:

>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 - 13:22:36 CDT