| 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: Temp space in 10g and data insert speed
Sort_area_size and _retained increase would definitely help.
Just set sort area size in your session before running your inserts. Set it to something in the magnitude of 30M to 50m....since you are already constrained by only having 764M, much larger would probably send you into a panic.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Igor Neyman
Sent: Tuesday, January 11, 2005 8:24 AM
To: RROGERS_at_galottery.org; oracle-l_at_freelists.org
Subject: RE: Temp space in 10g and data insert speed
Why do you think tempfile should be increasing? May be it's big enough? To check usage of tempfile look into V$TEMPSTAT.
Also, increasing sort_area_size and sort_area_retained size might help with the performance.
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ron Rogers
Sent: Tuesday, January 11, 2005 8:18 AM
To: oracle-l_at_freelists.org
Subject: Temp space in 10g and data insert speed
List,
OS Redhat AS 3.0 u3 memory 750 Meg swap 1.5 Gig
Oracle 10.1.0.3
I have created the database and included the temporary temp tablespace.
The sga is created using the sga_target and sga_max parameters using
685 Meg
and there still is free memory via the TOP command. The swap space does
not show as being used.
The tables have been created along with the indexes and I am now
starting to insert the data.
My question. In the past I have had to create large temp tablespaces to
handle the inserts with indexes but on this database I see no increase
in the size of the temp tablespace. The manuals describe the temp
tablespace as being used for the "user" created objects and sorts
created by the "users".
The inserts are taking a long time ( hours) to insert 4 million rows.
What could be the possible option that I have missed in creating the
database that
would allow for inserts and index creation to be faster. I know that I
should drop the indexes during dataloads but using my play/test box with
9i is a lot faster inserting data into the same table.
=20
Listed is my init.ora---
# create to replace the spfilelnxdb.ora REDHATDB DATABASE
########################################################################
#######
# database identification
db_name=3Dredhatdb
db_domain=3Dglc
instance_name=3Dlinuxdb
service_names=3Dredhatdb.glc
# database files
control_files=3D("/data/control01.ctl","/u01/data/control02.ctl","/u02/da=
t
a/contro
l03.ctl")
open_cursors=3D700
db_block_size=3D8192
# db_cache_size=3D130023424
# database memory usage
sga_max_size =3D 685500000
sga_target =3D 685500000
#shared_pool_size=3D184549376
# large_pool_size=3D16777216
# java_pool_size=3D8388608
sort_area_size=3D65636
sort_area_retained_size=3D65636
# misc database parameters
processes=3D200
# log_buffer =3D 163840
remote_login_passwordfile=3DEXCLUSIVE
os_authent_prefix=3D""
compatible=3D10.1.0.2.0
fast_start_mttr_target=3D300
# audit_trail =3D false # if you want auditing
timed_statistics =3D true # if you want timed stastics
# archive information
log_checkpoint_timeout=3D1800 log_archive_dest_1=3D'LOCATION=3D/u02/archlogs' log_archive_format=3Darch_%t_%s_%r.arc
max_dump_file_size=3D10000 utl_file_dir =3D /DATA utl_file_dir =3D /DATA/DATA utl_file_dir =3D /DATA/LOG utl_file_dir =3D /DATA/PAR utl_file_dir =3D /oratrace/bdump NLS_DATE_FORMAT =3D "MM-DD-YYYY"
undo_management=3DAUTO undo_retention=3D600 undo_tablespace=3DUNDOTBS
Thanks for the input as I continue to search the doc's and metalink. Ron=20
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 11 2005 - 09:18:11 CST
 |
 |