Re: Performace question

Sybrand,

Thanks for your answer! Your assumption is correct. The table performs like a temporary table. Many processes insert record into it, and one process read and delete record from it. So I believe that high water mark problem. Actually, I check total bytes of the table is 42188800 and unused bytes 3317760, so that high water mark is very high.

For the three solution you suggested, the first one is currently not doable, we are using Oracle 8i, there are many problems for upgrading to 10g, not only technical but also business issues. For the second and third solution, would you please make them more clear? Is there any other way to reduce high water mark?

Thanks again,
Evan

"Sybrand Bakker" <gooiditweg_at_sybrandb.verwijderdit.demon.nl> wrote in message news:gs3gu0lfdj5l4c3pjphd63q66nmq08pkgh_at_4ax.com...
> On Fri, 14 Jan 2005 22:11:14 +0800, "music4" <music4_at_163.net> wrote:
>
>
> assumption 1: the number of records in the affected table widely
> varies. It is an intermediate table only.
> assumption 2: the affected select performs a full table scan (fts).
> A fts will always traverse the table until the high water mark.
> The high water is not reset, when records are deleted.
> Truncate table empties a table by resetting the high water mark.
> (Actually that is the only thing it does).
> Got the picture?
> One solution is to upgrade to 10g, in 10g you can coalesce a table
> without truncate and at the same time reset the high water mark.
> The other solution is to redesign the process
> The third solution is to implement periodic rebuilds of the affected
> table.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Mon Jan 17 2005 - 02:58:31 CST