Subject: Re: Performens - empty blocks, 7.1 Unix
References: <4q4q0p$>   (Peter H. Larsen) writes:
>I've got a "small" problem with a 7.1.5 RDBMS version. It seems to have problems
>reusing totally empty blocks after a rollback or delete. It often occurs after 
>deleting/rollback more than 35000 records.

>The symptom is: the rollback/delete completes, and everything seems okay, but 
>subsequent inserts into the table, is about 10-20 times slower than normal (even
>without any load, processing ONE record in ONE insert takes more than 1 second).
>This does not happen when the table is FRESH, or if I truncate the table after a
>rollback, or instead of deleting a lot of rows. All inserts are done from PL/SQL -
>either directly in a PL/SQL block or via a package.
>SELECTS also takes forever (Oracle somehow scans all empty blocks??).

>Does anyone know of this problem?  Is it a 7.1 bug, fixed in 7.2 or is it
>possible to be a platform specific problem?  It seems to me, to be somekind of
>"freepointer" problem,
>when PCTUSED gets below default settings ????

>Peter H. Larsen             Email:
>Complete Data Service       Fidonet: 2:235/134.0
>Happy Computing

(Article included after comments)


I have had the same symptoms myself (both Oracle 7.0.16 and 7.1.3). You are
correct in that the internal pointers indicate a table at its largest
state in its history. So, if you load and delete records, the internal
pointer will indicate the largest size of the table.
Look at the number of extents on the table. Whenever a new extent is 
created, it is never de-allocated, unless you TRUNCATE or EXPORT/IMPORT 
the table.

In your INSERT statement, if you have a constraint that needs to check the table
for integrity reasons, it might be scanning the empty blocks, explaining the
longer times you have been getting.

Oracle support confirmed this to me a while ago.

Best of luck!
-Ari Kaplan
Independent Oracle DBA Consultant

<-> Visit my Web Page:                          <->
<->             email:                    <->


Back to Ari Kaplan's Home Page F="../index.htm">Back to Ari Kaplan's Home Page