Re: oracle performance question

In article <Cqztu1.J0n_at_world.std.com>, BThompso_at_world.std.com (Bill Thompson) writes:
> 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'.

This query will do a FULL table scan. No indexes will be used because you have no WHERE clause.

> 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

Are you running UNIX or VMS ? All my experience is on VMS so I will assume a VMS O/S.

> 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

How much free memory do you have?

What size SGA have you linked the kernel at?

It sounds like your SGA is too big for the available memory. What process is getting the paging problem? The one you are running or one of the background processes?

> 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.

You can't tune a full table scan; assuming you MUST do a full table scan.

Do you have a large array size setting in your SQL script? This could cause it to page excessively but you would have to have extremely high setting.

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

I/O doesn't appear to be your bottleneck. I know the paging is I/O but your problem is something to do with the way you are retrieving the data. It sounds like it is trying to fill a structure in memory but you don't have enough memory to hold the entire structure so its paging parts in and out.

The script you describe above should drive your table's disk to maximum I/O and your spool disk to maximum I/O and use very little cpu at all.

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras