Re: Optimizing Query

Hi,

I think the problem could be indirectly caused by the update. The way the
program works is this :

1. Get a batch of records (say 1000) - based on SQL given previously.
2. Iterate through the batch one record at a time. After processing a record update its processed flag.
3. Once finished with the batch go back to 1 and repeat until all records processed.

So,
>
> ** First find out where the problem is. You say that you are updating
> some rows. Remove the update and just run the select and see how long
> it takes.
>

The update does not directly affect the query. It still takes the same length
of time.

> **The problem could also be in the update query. Post the update query
> and see the indexes it uses. Also check the PCTFREE value is not very
> low. Increase the PCTFREE and try. It matters while updating (Please
> refer the manual for this , as i am not very sure about this ). Note
> if the PCTFREE for your table which is being updated is 0 , set it to
> atleast 10.
>

The update query uses the primary key of the clients table to update the
processed flag - i have checked the explain plan for this. The PCTFREE value for the table is set at 40.

> ** Place the most restrictive AND first and try. I read this in Celkos
> books "SQl for smarties". If the optimizer mode is choose, then it
> could help.
> Rearrange
> ((c.processed = 0 > and o.ordertype = 1 ))
> to
> ((o.ordertype = 1 and c.processed = 0 ))
>
> ** Another important pointer. Check if you have too many indexes in
> the base tables. They could be slowing your updates if the indexed
> columns are being updated. If you find that the update is causing the
> problem, see whether you could drop some unused indexes. See whether
> dropping the bitmapped index helps, assuming you arent using it
> anywhere
>

I believe there are too many indexes on the tables. What I'm really trying to establish, I guess, is any ideas as to the best combination of indexes.

> ** Some other things-like network speed etc. could also be the
> problem. Read the chapter on Oracle Tuning in the docs. Since yours
> seems to be a simple query, the chances are bright that some other
> problem could be there. Also the Rollback segment may need to be tuned
>

Network speed isn't the problem as the query takes the same amount of time to
run on the server as it does on a PC over the network.

> ** Also, you say you are updating some rows. See whether bind
> variables are being used in that update. Bind variable SQL statements
> are cached. Post your update statement.
>
> Instead of
>
> UPDATE x set m =12
> and
> UPDATE x set m =12
>
> Try
> UPDATE x set m = :1. Before the statement executes, set the value of
> the bind
>
> ** If nothing works, Rearrange the columns in the From Clause and give
> a shot.
>

The update does use bind variables. And rearranging the columns does not really
improve matters.

>
> As i said, your query seems to be simple. The chances are high that
> the optimizer has not goofed up :-)
>

Indeed. The query does seem to be simple. The problem is (I think) that the
updates are causing the statistics generated to become out of date quickly.
This in turn causes the optimizer to become a bit unsure of the best execution
path. Either that or the indexes are becoming very fragmented very quickly.
If I can establish what an ideal set of indexes are for this query (and get rid
of the rest), I can keep the indexes and statistics more up to date.

Cheers
Colin Received on Tue Oct 23 2001 - 09:11:50 CDT