Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Suggestions please - Query loading 98%

Re: Suggestions please - Query loading 98%

From: <kennethkoenraadt_at_no-spam.hotmail.com>
Date: Fri, 11 Oct 2002 08:23:04 GMT
Message-ID: <3da6874c.3453706@news.mobilixnet.dk>


Hi Jonathan,

You are probably right all over the line. Especially the part about redesigning the application. The first thing I learned about tuning : "90% of performance gain comes from the application" apparently still seems to be true.

I just have one question, that's your part about bitmap indexes :

"Single row updates are likely (though not guaranteed) to make bitmap indexes explode if you are updating the indexed column"

I noticed that the MSG_STATUS column is only updated within the existing range of values (WAITING,SUCCEEDED, FAILED). Updating the column from one of these values to another should only consist of changing a bitmap from '101' to '100' or so.

I do not understand how that can make the index "explode" ?

 If new values of MSG_STATUS were introduced continously, I clearly understand the problem. But that is not the case here.

>
>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
>>>
>
>
>
Received on Fri Oct 11 2002 - 03:23:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US