Re: Table performance problems with RAW.

Gary Eckhardt wrote in message <6trml6$lps$1_at_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 - 06:17:54 CDT