Performance problem, Oralce 8.0.4 / Solaris 2.6 / EMC Disk

From: C. William Grant <cwgrant_at_ix.netcom.com>
Date: Sun, 15 Aug 1999 19:38:38 -0400
Message-ID: <37B74F7E.EDE07221@ix.netcom.com>

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.

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.
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 Received on Sun Aug 15 1999 - 18:38:38 CDT