Re: Performance decrease while deletin a table

Not sure quiet why such an operation should be needed, however --

Is the select on an index ?

Does the explain plan suggest it uses that index?

IF not does the table in the EMPTY state have a high, High Water Mark? or is it truncated giving a low High Water Mark and allowing a full table scan to complete quickly.

In the case of the IOs check the file IO positions before and after the query and see from the differences which files are being hit during the query.

Some questions and some ideas for you.

Regards

--
Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)

                        <Kevin.Lewis_at_BOCMPAULS.co.uk>

The views expressed herein by the author of this document are not necessarily those of BOCM PAULS Ltd. Grzegorz Gizinski <grzesiek_at_no.spam> wrote in message news:7g97ls$m28$1_at_euler.softax.com.pl...
> Hi,
> I'm using Oracle 8.0.3 running on VMS 7.1-1H2.
> Can anybody explain me behaviour of Oracle while processing following
> statments in one transaction:
> 1. deleting whole table (about 5000 records)
>
> 2. selecting a record from the same table
> 3. when select has not returned any record - inserting new,
> in other case - rollback (transaction ends).
> 4. commit
>
> Steps 2 and 3 repeat about 5000 times.
> When processing only steps 2, 3 and possibly 4 (on empty table) everything
> goes OK.
> But with the first step - the second takes signifficient elapse of time.
> Explain says that fetch operation is producing tousands of IOs.
> Why? Does Oracle searching the rollback segment?
>
> TIA
> Grzesiek.
>
>
> Grzegorz Gizinski
> Reply for spam. Real e-mail:
> Grzegorz.Gizinski_at_softax.com.pl
>
>
Received on Thu Apr 29 1999 - 05:11:54 CDT