Performance-Question

Hello,

I have a problem with a Oracle-Database on a HP 9000/K200 UNIX-System.

We did a number of optimization at the Unix-Level (Striping the Disks, distributing the database-tablespaces over different groups of disks and so on) but the effect is very strange:

if we loaded the data using SQL-loader with "direct path", our response-time for a query like

SELECT COUNT(*) FROM TABLE; is about 5 Minutes. (Yes, I know, oracle does a full table-scan)

If we are loading the data with a program, that does an insert for each row of the data, we have response-times for that query of 2 hours and above.

The query was executed immediately after the load had finished.

What the hell is going on? IMHO both cases require a full table-scan. Why is the second one so much slower than the first one? In both cases the Space occupied on disks is nearly the same.

I checked the wait-statistics at OS-Level for those processes and discovered, that in the second case, the process spends most time waiting for a semaphore, but I dont know which one.

OK and now the technical stuff:

The Unix-Box is a HP 9000/K200 with HP-UX 10.0. We have 15 Disks, 2 Gigabytes each and 12 of them are organized in 4 volume-groups. Every volume-group is dividen into 3 logical volumes, every volume ist striped over all three disks. Combined together, we have 12 filesystems each filesystem with a size of approximately 2 gigabytes.

Oracle version 7.1.4 is used.

The tablespace consists of 4 data-files, eah with a size of 1.75 gigabyte. The primary extent is 1749 MB, other extents are 874 MB megabytes. PCTINCREASE is 0.

PCTFREE is 10 % and PCTUSED is 80 %.

The data is 7.500.000 rows with approx. 200 bytes of size for each row. The tablespace holds only the first extent.

If more informations are necessary, please e-mail.

Thank you very much

H. Becker

-- 
----------------------------------------------------------------------
  Harald (Harry) Becker                   harry_at_merlin.unterland.de   
     74080 Heilbronn                                                  
        Want to turn your Pentium-PC into a gameboy?   type WIN