Re: Performens - empty blocks, 7.1 Unix

petlars_at_ibm.net (Peter H. Larsen) writes:

(Article included after comments)

Peter,

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: http://homepage.interaccess.com/~akaplan   <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
 
-----------------------------------------------------------------------------

>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: Petlars_at_ibm.net
>Complete Data Service Fidonet: 2:235/134.0
 

>Happy Computing
Received on Tue Jun 18 1996 - 00:00:00 CEST