>
> Hello group,
>
> I am involved in a large data warehouse project using Oracle 8.0.4,
> Solaris 2.6 and a large EMC disk array.
> We have 16 processors in the production domain, 8 GB of ram, and I have
> no idea how much disk.  There are
> a number of large tables processed for each month, the biggest being
> between 60 and 80 million rows (additional
> ones each month).  There are two problems.
>
> 1.  When processing a large number of updates (>100K) the rate starts
> out O.K. but then gradually drops to nothing.
>      The ETL tool is DataStage which connects via ODBC.  This is also on
> the Solaris machine.  The application does
>      and initial PREPARE then calls the UPDATE for each row.  By the
> time 150K rows are processed, each update
>      is taking 2.5 to 4 seconds !!!  Has anyone else seen this problem
> before?  Is it a known bug?  BTW, it does not
>      matter if the application commits at various intervals or runs it
> as one huge transaction.  The work around I have
>      been using is to insert the changed data and primary key into a
> working table and do the update as a single,
>      corelated query.  This works quite nicely but is obviously not
> desireable as a project-wide solution.
>
> 2.   When the demand on the system is high enough to force even a very
> modest amount of I/O, the performance is
>       absolutely awful.  Even simple SELECT statements returning large
> numbers of rows take forever.  Looking at
>       things from Solaris, we can see that the majority of the CPU
> utilization is dominated by IOWAIT.  By dominated
>       I mean 65 to 69 percent with the balance split between USER and
> KERNAL time.  The box is not heavily loaded,
>       especially given the size of the beast.
>
>       Is it the EMC array?
>       Is it a controller and/or driver problem?
>       Is it a configuration issue?   The project team does not have
> control of the box so we can not get to all of the details
>                                                 so I can't determine if
> the I/O subsystems are configured for asynchronous I/O or not.
>
> Thanks in advance for any help or suggestions on either of these
> problems.
>
> Bill Grant
> Nautilus Consulting Group, Inc.
> Westlake, Ohio

HI
What's the network activity like? using ODBC can cause massive amount of
network activity and many times I've heard of large selects being down
to network issues....but this doesn't sound like the case.

What do the Oracle stats show? How big are your DB block buffers? Is the
machine paging alot when you see the slow down?

How are your redo log buffers doing? check with
select name, gets, misses, immediate_gets, immediate_misses from v$latch
where name in('redo allocation','redo copy')

if the ratio of gets to misses (or immediate gets/misses) exceeds 1%
consider tuning the redo buffer.

get the O'reilly book on Oracle Performance Tuning - its invaluable as
with most O'Reilly books.

Martin