Re: Suggestions please - Query loading 98%

I beg to differ -

wrt (3) - the column is not begging for a bitmap index. On the statistics given, the target rows account for about one row in twelve - the typical cost of a query is hugely controlled by the number of blocks to be visited in the table - this won't change just because you change from a b-tree to a bitmap index. The fact that there are only three different values for the column is irrelevant.

See also your comment in (4) "since your table is highly volatile ...." Bitmap indexes are not a good idea on volatile data. This is particularly relevant when you look at the query - guessing intent from code:

    The user finds the (ONE) oldest WAITING row,     does something to it, then changes its status     from WAITING to SUCCEEDED or FAILED. Single row updates are likely (though not guaranteed) to make bitmap indexes explode if you are updating the indexed column.

The main problem is a design issue - the process identifies and modifies one row at a time. Could it not fetch all the waiting rows, and process them all in order ? The overhead of the fetch would then occur once per large number of rows ?

If this is not currently feasible, then the next "problem" is the version of Oracle

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

Apart from the fact that this is not a supported version, it is also so old that there are optimizer options (such as index range (min/max)) that are not available to make this query run efficiently without 'fixing'.

It is also so old that 'dirty tricks' that could safely be employed on newer versions of Oracle may be subject to bugs, and therefore give unpredictable results.

In this case, for example, one 'high-risk' strategy is to create an index on (msg_status, last_update_time) and then simply the query - with an order by clause - hinting the use of index:

select /*+ first_rows index(store_message, two_col_index)

        *
FROM STORE_MESSAGE
WHERE MSG_STATUS = 'WAITING'
order by

    msg_status, last_UPDATE_TIME
;

Then have the calling program stop after one row.

This should be sufficient (in this version, I think) to make Oracle use the index to drive the ORDER BY, and therefore to find the one row required in the shortest possible time.

(If the execution doesn't show something like SORT ORDER BY (NOSORT) then it hasn't worked.

The reason I don't like this solution is that it depends on a complete absence of optimizer bugs - and a rule embedded FIRST_ROWS
optimization that changed somewhere along the line)

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





kennethkoenraadt_at_no-spam.hotmail.com wrote in message
<3da5c067.1321740_at_news.mobilixnet.dk>...


>

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

>

>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 ?

>






>>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)


>>

>>

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

>>