Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Poor SQL Loader performance

Re: Poor SQL Loader performance

From: Terry Engelstad <terry_at_interaccess.com>
Date: Tue, 19 May 1998 11:47:23 -0500
Message-ID: <6jsd8q$4hu$1@nntp3.interaccess.com>


One possibility is that your database is not sized properly prior to loading your tables. If Oracle has to constantly expand, then it will take a long time. Second, how much of your 384MB RAM is Oracle using. It sounds like your not getting enough.

Aaron Kushner wrote in message ...
>
>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 Tue May 19 1998 - 11:47:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US