| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance tuning
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 <ORACLE-L_at_fatcity.com>
<BoivinP_at_mar.dfo- cc:
mpo.gc.ca> Subject: RE: Performance tuning
Sent by:
root_at_fatcity.com
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: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>
>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.
>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: azhar_at_mathtech-pk.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858)
538-5051
>San Diego, California -- Public Internet access /
Mailing
Lists
>--------------------------------------------------------------------subscribing).
>To REMOVE yourself from this mailing list, send an E-Mail
message
>to: ListGuru_at_fatcity.com (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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Peter McLarty
INET: peter.mclarty_at_incts.com
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: ListGuru_at_fatcity.com (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: azhar_at_mathtech-pk.com
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: ListGuru_at_fatcity.com (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: cspence_at_FuelSpot.com
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: ListGuru_at_fatcity.com (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: BoivinP_at_mar.dfo-mpo.gc.ca 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: ListGuru_at_fatcity.com (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: azhar_at_mathtech-pk.com 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: ListGuru_at_fatcity.com (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).Received on Thu May 31 2001 - 05:53:05 CDT
 |
 |