Poor SQL Loader performance

From: Aaron Kushner <kush_at_netcom.com>
Date: Mon, 18 May 1998 23:43:43 GMT
Message-ID: <kushEt6F8v.2F0@netcom.com>

I am using SQL*Loader to populate a datawarehouse and am surprised by what slow times I am getting on the loads.

System: HP K460 with two 180Mhz processors 384MB RAM Software: Oracle 7.3.2.2 HP-UX 10.20
Config: The index, temp, and the datafile are on separate drives

I am using direct path load and unrecoverable option

What would reasonable times be for the number of rows/sec? Also, the loads get progressively slower. I assume the degredation is due to the index since the loads take constant time when I disable the primary key. Rebuilding the index takes more than 70 minutes, so disabling the index on loads is not really an option.

Thanks for any help with this problem
Aaron Kushner
akushner_at_cup.hp.com

Times from the SQL*Loader logs:

FILE                           ROWS        ELAPSED TIME    ROWS/SEC
------------------------  ----------- ----------------     --------
processed/roseville.9611 rows: 360788 time 00:02:11.81 r/s 2754.11
processed/roseville.9612 rows: 546724 time 00:03:50.26 r/s 2377.06
processed/roseville.9701 rows: 569024 time 00:04:19.01 r/s 2197.00
processed/roseville.9702 rows: 526663 time 00:04:45.02 r/s 1847.94
processed/roseville.9703 rows: 555104 time 00:05:20.29 r/s 1734.70
processed/roseville.9704 rows: 525150 time 00:05:16.53 r/s 1661.87
processed/roseville.9705 rows: 483442 time 00:05:59.76 r/s 1346.64
processed/roseville.9706 rows: 480995 time 00:06:30.79 r/s 1233.32
processed/roseville.9707 rows: 497256 time 00:06:48.71 r/s 1218.76
processed/roseville.9708 rows: 540445 time 00:07:31.20 r/s 1198.33
processed/roseville.9709 rows: 344140 time 00:06:50.04 r/s 839.37
processed/roseville.9710 rows: 597146 time 00:09:04.75 r/s 1097.69
processed/roseville.9711 rows: 413584 time 00:08:31.15 r/s 809.36
processed/roseville.9712 rows: 459957 time 00:09:04.71 r/s 845.51
processed/roseville.9801 rows: 491233 time 00:09:34.62 r/s 855.81
processed/roseville.9802 rows: 491966 time 00:10:05.59 r/s 813.17

processed/roseville.9803 rows: 455662 time 00:10:29.90 r/s 724.42 processed/roseville.9804 rows: 423421 time 00:10:34.99 r/s 667.86 Total time (hours:mins): 02:06.67
Total rows 8762700 average rows/sec 1152.99 Received on Mon May 18 1998 - 18:43:43 CDT