Re: Table performance problems with RAW.

From: John P. Higgins <jh33378_at_deere.com>
Date: Mon, 21 Sep 1998 16:38:23 -0500
Message-ID: <3606C74E.D7B87267@deere.com>

Since you are using OPS, you probably have multiple freelist groups. If the inserts are executed on multiple instances, each instance has an 'initial extent'. That is, instance A will not insert a row into an extent 'owned' by instance B. The HWM is the last rowid ever inserted into the highest extent.

john wrote:

Gary Eckhardt wrote in message <6trml6$lps$1@client3.news.psi.net>...
SNIPPED
>
>We have come across an interesting problem that I thought I would ask the
>rest of the world if they've seen the same thing.
>
>Our current setup is Oracle 7.3.4 Parallel Server running on AIX 4.2.  The
>performance problem comes when we have tables with a "medium to large"
>initial extent (50M) and are performing unindexed full table scans of the
>tables, such as in a SELECT COUNT(*) FROM EMP.  These selects are taking a
>very long time on EMPTY TABLES.  I have not seen this behavior in the
>"cooked" file system world, and about the only thing I can attribute it to
>is running on RAW file systems.  The larger the initial extent, the longer
>the select time and vice-versa.
>
>Currently we've gone through our code and tried to find all the full table
>scans and make them indexed reads, and have shrunk down the size of some
>tables where we cannot force an indexed scan to get around the situation.
>However, I believe there must be some type of RAW tuning parameter
somewhere
>that will alleviate some of our problems.
>
>Any suggestions/comments about this?
>

Hi,
it is nothing to with their raw-ness, it is probably because the tables were
originally large and have been shrunk by data deletion, but the 'high-level
water mark' has not been changed.
Oracle, when doing a full table scan, will always read to the high-level water
mark.

Use dbms_space.unused_space to get info about the table.

You can use: alter table <tab> deallocate unused, to free unused space.
or: Truncate table drop storage (which will delete everything and deallocate
storage).

Regards,
John.

--
Regards,
John.

  Received on Mon Sep 21 1998 - 16:38:23 CDT