Database parameters were slightly changed for the second run and SGA was
made only 140 MB.
Loading was done without indexes and SQL loader parameters were changed to
Readsize=10m, Bindsize=10m and Rows=5000.
It took almost same time for about 21 hours but again it committed 8-10
times ( first 50000) very quickly.
Real bottleneck is RAM (as pointed out by all) since commit charge in NT
task manager was almost double than
the physical memory.
But since the data is loaded , we are through. Thanks to every one who
responded
Azhar
"Boivin, Patrice
J" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc:
mpo.gc.ca> Subject: RE: Performance tuning
Sent by:
[EMAIL PROTECTED]
05/31/2001 12:56
AM
Please respond to
ORACLE-L
I don't know what is causing this, but I would keep an eye on physical
memory available vs. commit charge in Task Manager, I strongly recommend
your commit charge never exceed your physical memory.
I found that Oracle sometimes refuses to even start services when it runs
out of physical memory, it doesn't like virtual memory very much.
Shrinking
the size of your SGA is preferable to exceeding the available physical
memory, in my opinion. Keep in mind session memory space in your
calculations, and the other programs you may be running on your machine.
I recommend you stop all the services (in Services applet) that you dare
stop, to lighten the load.
In the virtual memory settings, allocate the amount you want, but try to
avoid a range of values - when initial and max size values are different NT
keeps polling the pagefile and memory statistics to figure out if the
pagefile should grow or shrink. Better to do that manually up front,
allocate at set amount. NT then stops doing extra work regarding memory
allocation.
Every little bit helps.
I haven't used SQL*Loader, so I can't say much about that, except... when
you start loading those first few rows, what does Commit Charge look like
in
Task Manager? Is it growing? If it is, notice how slowly NT does this.
It
can't be helped. If commit charge approaches physical memory available,
you
will hit a ceiling I think. Then things may well slow down to a crawl.
Is there a buffer size you can set for SQL*Loader? I always set my exp
buffer size to 1000000 to speed it up. Maybe you can do the same for
SQL*Loader. That must use more memory though.
Can you commit every few records? Could it be every 10 rows? Would it be
better not to commit too often?
As mentioned before, RAID 5 will prove slower than simple disk or RAID 01
or
RAID 10.
Do you have many indexes on the tables you are filling up? You may want to
drop them and re-create them once the load is finished.
This may sound silly but... do you have a virus checking program running on
your server? Try to exclude the oradata directories from it.
Just some ideas. That's what I would check first.
If your RBS segments filled up, you would see an error somewhere, it seems
to me. Is TEMP filling up? You never know.
Oh another probably silly thing - if you are not using SCSI, download
DMACheck from microsoft and test whether your DMA is turned on. That would
speed up your disk by 40% or more. Servers use SCSI disks normally though,
but sometimes people install Oracle on PCs or workstations with IDE drives.
Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)
Systems Admin & Operations | Admin. et Exploit. des systèmes
Technology Services | Services technologiques
Informatics Branch | Direction de l'informatique
Maritimes Region, DFO | Région des Maritimes, MPO
E-Mail: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>HI ALL,
>We have to load almost 3 millions records of average row size
of
150
bytes.
>We are importing data using sqloader with ROWS=4000 and
bindsize=8450000 .
>We have adjusted the rollback segment to almost 10 m with 8
extents
enough
>for single transaction size and considering 30% rollback
overhead.
We
>adjusted the OPTIMAL TO 10 M to have avoid rollback extension
>Rollback segment, databuffer cache have hit ratio of 100%.
>
>The loading was fast only for first 10 commits but then it
slowed
like
>snail. LOADING TOOK 22 hours in the first run on ORACLE8i NT4
128
megs RAM
>.
>SGA figures in M :
>NAME VALUE
>-------------------- ---------
>Fixed Size .0676384
>Variable Size 239.02734
>Database Buffers 39.0625
>Redo Buffers 7.8203125
> ---------
>sum 285.97779
>( we can't use direct path due to functions in sqlldr
controlfile).
.
>Couldn't figure out the bottleneck yet.
>Any ideas.
>TIA
>Azhar Siddiq,
>DBA
>LMK Resources
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author:
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- (858) 538-5051 FAX: (858)
538-5051
>San Diego, California -- Public Internet access /
Mailing
Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail
message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You
may
>also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Peter McLarty
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858)
538-5051
San Diego, California -- Public Internet access / Mailing
Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail
message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
may
also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858)
538-5051
San Diego, California -- Public Internet access / Mailing
Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail
message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
may
also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858)
538-5051
San Diego, California -- Public Internet access / Mailing
Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail
message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
may
also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Boivin, Patrice J
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
