oracle performance question

We are testing a database with a single table containing 110,000 records of

 82-bytes each (distributed over 9 columns). An index is defined on two of

the columns. The query is the simplest possible: 'select * from TheTable'.

Hardware is a VAX with 32M memory. The 'working set' was initially 2K as

suggested in the Installation Guide; the free list size is 49K and the

modified list size is 2.5K. The database files are distributed over 5 disks,

with data and index files on different disks. Output is being spooled. When

the query runs, CPU utilization is 100%. For the first hour or so, there are

no page faults, then the page fault rate starts to rise until the VAX is

spending all its time page faulting and very little output is actually being

spooled. If we increase the size of the working area, up to the maximum

available on this machine, 8K blocks, the amount of time it takes to reach

the high-page-faulting state is increased up to about 5 hours, but this state

is nonetheless reached. At this point only about 25% of the query results,

that is, 25% of the table, has been output.

Any suggestions on where else we should be looking for performance tuning?

thanks Received on Mon Jun 06 1994 - 22:49:12 CEST