| 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
Rather consider switching over to PGA_aggregate...
Much more memory efficient than the sort area size params.
George
=20________________________________________________
George Leonard
Oracle Database Administrator
New Dawn Technologies @ Wesbank
E-mail:gleonard_at_wesbank.co.za
=20
You Have The Obligation to Inform One Honestly of the risk, And As a
Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk,
Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Reidy, Ron
Sent: 11 January 2005 17:20 PM
To: RROGERS_at_galottery.org; oracle-l_at_freelists.org
Subject: RE: Temp space in 10g and data insert speed
Did you trace your session? That will tell you where the waits are =3D occurring.
-----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 6:18 AM
To: oracle-l_at_freelists.org
Subject: Temp space in 10g and data insert speed
List,
=20OS 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.
=3D20
Listed is my init.ora---
# create to replace the spfilelnxdb.ora REDHATDB DATABASE ######################################################################## #=3D ######
# database identification
db_name=3D3Dredhatdb
db_domain=3D3Dglc
instance_name=3D3Dlinuxdb
service_names=3D3Dredhatdb.glc
# database files
control_files=3D3D("/data/control01.ctl","/u01/data/control02.ctl","/u02/=
d
a=3D
ta/contro
l03.ctl")
open_cursors=3D3D700
db_block_size=3D3D8192
# db_cache_size=3D3D130023424
# database memory usage
sga_max_size =3D3D 685500000
sga_target =3D3D 685500000
#shared_pool_size=3D3D184549376 # large_pool_size=3D3D16777216 # java_pool_size=3D3D8388608
log_checkpoint_timeout=3D3D1800 log_archive_dest_1=3D3D'LOCATION=3D3D/u02/archlogs' log_archive_format=3D3Darch_%t_%s_%r.arc# define directories to store trace and alert files background_dump_dest=3D3D/oratrace/bdump core_dump_dest=3D3D/oratrace/cdump
max_dump_file_size=3D3D10000 utl_file_dir =3D3D /DATA utl_file_dir =3D3D /DATA/DATA utl_file_dir =3D3D /DATA/LOG utl_file_dir =3D3D /DATA/PAR utl_file_dir =3D3D /oratrace/bdump NLS_DATE_FORMAT =3D3D "MM-DD-YYYY"
# db_file_multiblock_read_count=3D3D16 # job_queue_processes=3D3D10 #query_rewrite_enabled=3D3DFALSE # star_transformation_enabled=3D3DFALSE # sga_target=3D3D203423744 # dispatchers=3D3D"(PROTOCOL=3D3DTCP) (SERVICE=3D3DlinuxdbXDB)" # pga_aggregate_target=3D3D67108864 # system managed undo and rollback segments undo_management=3D3DAUTO
Thanks for the input as I continue to search the doc's and metalink. Ron=3D20
-- http://www.freelists.org/webpage/oracle-l This electronic message transmission is a PRIVATE communication which =3D= contains information which may be confidential or privileged. The information isReceived on Tue Jan 11 2005 - 23:17:51 CST
=3D
intended=3D20 to be for the use of the individual or entity named above. If you are =3D= not the=3D20 intended recipient, please be aware that any disclosure, copying, =3D distribution=3D20 or use of the contents of this information is prohibited. Please notify
=3D
the sender of the delivery error by replying to this message, or notify us
=3D
by telephone (877-633-2436, ext. 0), and then delete it from your system. -- http://www.freelists.org/webpage/oracle-l _________________________________________________________________________= __________________________ The views expressed in this email are, unless otherwise stated, those of = the author and not those of the FirstRand Banking Group an Authorised Financial Service Provider o= r its management. The information in this e-mail is confidential and is intended solely for=
=20the addressee.
Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribut= ion or any action taken or=20 omitted in reliance on this, is prohibited and may be unlawful. Whilst all reasonable steps are taken to ensure the accuracy and integrit= y of information and data=20 transmitted electronically and to preserve the confidentiality thereof, n= o liability or=20 responsibility whatsoever is accepted if information or data is, for what= ever reason, corrupted=20 or does not reach its intended destination.
=20 ________________________________
-- http://www.freelists.org/webpage/oracle-l
 |
 |