RE: Temp space in 10g and data insert speed

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Tue, 11 Jan 2005 09:23:34 -0500
Message-ID: <000901c4f7e9$2240f6e0$2004a8c0@development.perceptron.com>

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.

Listed is my init.ora---

# create to replace the spfilelnxdb.ora REDHATDB DATABASE
########################################################################
#######

# database identification

db_name=redhatdb
db_domain=glc
instance_name=linuxdb
service_names=redhatdb.glc
# database files

control_files=("/data/control01.ctl","/u01/data/control02.ctl","/u02/dat a/contro
l03.ctl")
open_cursors=700
db_block_size=8192
# db_cache_size=130023424
# database memory usage

sga_max_size = 685500000
sga_target = 685500000
#shared_pool_size=184549376
# large_pool_size=16777216
# java_pool_size=8388608

sort_area_size=65636
sort_area_retained_size=65636
# misc database parameters

processes=200
# log_buffer = 163840

remote_login_passwordfile=EXCLUSIVE
os_authent_prefix=""
compatible=10.1.0.2.0
fast_start_mttr_target=300
# audit_trail = false # if you want auditing
timed_statistics = true # if you want timed stastics
# archive information

log_checkpoint_timeout=1800
log_archive_dest_1='LOCATION=/u02/archlogs'
log_archive_format=arch_%t_%s_%r.arc

# define directories to store trace and alert files
background_dump_dest=/oratrace/bdump
core_dump_dest=/oratrace/cdump
user_dump_dest=/oratrace/udump

max_dump_file_size=10000
utl_file_dir = /DATA
utl_file_dir = /DATA/DATA
utl_file_dir = /DATA/LOG
utl_file_dir = /DATA/PAR
utl_file_dir = /oratrace/bdump
NLS_DATE_FORMAT = "MM-DD-YYYY"

replication_dependency_tracking = FALSE
# db_file_multiblock_read_count=16
# job_queue_processes=10
#query_rewrite_enabled=FALSE
# star_transformation_enabled=FALSE
# sga_target=203423744
# dispatchers="(PROTOCOL=TCP) (SERVICE=linuxdbXDB)"
# pga_aggregate_target=67108864
# system managed undo and rollback segments

undo_management=AUTO
undo_retention=600
undo_tablespace=UNDOTBS

# asynch i/o configuration

disk_asynch_io = true
tape_asynch_io = true
filesystemio_options=asynch

Thanks for the input as I continue to search the doc's and metalink. Ron

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 11 2005 - 08:24:23 CST