| 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
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.
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: 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: 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).Received on Wed May 30 2001 - 13:50:07 CDT
 |
 |