guys,
I am installing IDES EHP4 on a solaris box with oracle 10g. The import is running very slow. in 20 hours, it has only imported 10 out of 105 jobs. Top comand shows i have 26 GB free physical memory (out of 48GB total) and also 123 GB free swap out of 150 GB total. CPU load is also only 3 %. I have already configured the project file acording to sap recommedation. MY oracle init file configuration looks like this. Also, you will see my project and user_attr files below. Please advise what i can change tomake things go faster.
###########################################################
(c)Copyright SAP AG, Walldorf #
@(#) $Id: //bc/701-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/INITSIDBASE.ORA#1 $
###########################################################
###########################################################
PART I, TUNING PARAMETERS #
###########################################################
SYSTEM GLOBAL AREA BUFFERS
Total System Global Area = sum(bytes) of all caches
Variable Size = shared_pool_size (approx.)
Database Buffers = db_block_buffers * db_block_size
Redo Buffers = log_buffer
#
unit of shared_pool_size: bytes
unit of db_block_buffers: number of cached blocks
unit of log_buffer: bytes
shared_pool_size = 2264924160
10% of shared_pool_size
shared_pool_reserved_size = 226492416
log_buffer = 1048576
MANAGING SORTS
A small sort requires sort_area_retained_size in
memory. Larger sorts are allocating segments in
PSAPTEMP. They are using sort_area_size in memory.
sort_area_retained_size = 0
sort_area_size = 2097152
#sort_spacemap_size = 512
PGA-PARAMETERS
open_cursors = 800
#cursor_space_for_time = true
LOCKS
dml_locks = processes * 50
dml_locks = 4000
#enqueue_resources = 8000
CPU-PARAMETERS
#spin_count = 200
#_cpu_count = 4
TUNING ARCHIVING
#log_archive_buffer_size 127
#log_archive_buffers 4
USE VECTOR READ
#use_readv = true
OTHER TUNING PARAMETERS
#sequence_cache_entries = 100
#sequence_cache_hash_buckets = 89
###########################################################
PART II, CHANGEABLE PARAMETERS #
###########################################################
CONTROL-FILES
the controlfiles should be mirrored to every new
database mount point @SAPDATA_HOME@/sapdata<#>
#control_files = XXXXX
AUTOMATICLY STARTED BACKGROUND PROCESSES
MANAGING LOG SWITCHES
checkpoints occur only when switching logs, if
log_checkpoint_interval * size of OS blocks > size of
the actual redo log
log_checkpoint_interval = 0
#log_checkpoint_timeout = 0
PROCESS-PARAMETERS
The number of allocated semaphores is equal to the
number of processes
sessions = 1.2 * processes
processes = 500
sessions = 600
AUDITING AND STATISTICS
sql_trace=TRUE
audit_trail = true
db_block_lru_extended_statistics = 1000
db_block_lru_statistics = true
###########################################################
PART III, STATIC PARAMETERS #
###########################################################
DB-NAME
db_name = XXX
DB-BLOCKSIZE
db_block_size = 8192
DB-FILES
db_files = 254
OPTIMIZER MODE
#optimizer_mode = choose
#optimizer_search_limit = 3
PATHS / DESTINATIONS / TRACES
/xxx\
: trace files of the background
processes
xxxx
/saptrace/usertrace: trace files of the user processes
log_archive_dest is a destination, not a path.
The archivefiles get the name
XXXXXXX
background_dump_dest = xxxx
user_dump_dest = xxxx
core_dump_dest = /xxxxx
log_archive_dest = xxxxxx
#log_archive_format = %t_%s
OTHERx
reduce_alarm only supported on HP
#reduce_alarm = TRUE
ORACLE OPS PARAMETER
remote_os_authent = true
retention time for RMAN backup information in control file
control_file_record_keep_time = 30
#see SAP note 124361
#db_file_multiblock_read_count = 8
log_checkpoints_to_alert = true
OPTIMIZER MODE
#optimizer_index_cost_adj = 10
AUDITING AND STATISTICS
#timed_statistics = true
compatible = 10.2.0
parallel_execution_message_size = 16384
query_rewrite_enabled = false
replication_dependency_tracking = false
star_transformation_enabled = true
undo_retention = 43200
undo_tablespace = PSAPUNDO
undo_management = AUTO
pga_aggregate_target = 3019898880
sga_max_size = 84529848320
db_cache_size = 4264924160
job_queue_processes = 1
remote_login_passwordfile = exclusive
recyclebin = off
event = "10191 trace name context forever, level 1"
FILESYSTEMIO_OPTIONS = setall
OPTIMPEEK_USER_BINDS = FALSE
BTREE_BITMAP_PLANS = FALSE
INDEXJOIN_ENABLED = FALSE
INMEMORY_UNDO = FALSE
OPTIMIZERMJC_ENABLED = FALSE
SORTELIMINATION_COST_RATIO = 10
TABLELOOKUP_PREFETCH_SIZE = 0
USER_ATTR FILE --- I have replaced my sid with SID for privacy reasons ************************************
adm::::profiles=Log Management
lp::::profiles=Printer Management
postgres::::type=role;profiles=Postgres Administration,All
root::::project=MYSID;auths=solaris.*,solaris.grant;profiles=Web Console Management,All;lock_after_retries=no;min_label=admin_low;clearance=admin_high
SIDADM::::project=SID
oraSID::::project=SID
********************************************************************************************************************
PROJECT FILE --- my sid replaced with 'SID'*********************************
system:0::::
user.root:1::::process.max-file-descriptor=(basic,65536,deny),(priv,65536,deny);process.max-sem-nsems=(priv,512,deny);project.max-sem-ids=(priv,512,deny);project.max-shm-ids=(priv,256,deny);project.max-shm-memory=(priv,18446744073709551615,deny)
noproject:2::::
default:3::::process.max-file-descriptor=(basic,65536,deny),(priv,65536,deny);process.max-sem-nsems=(priv,512,deny);project.max-sem-ids=(priv,512,deny);project.max-shm-ids=(priv,256,deny);project.max-shm-memory=(priv,18446744073709551615,deny)
group.staff:10::::
SID:400:SAP System SID:SIDadm,root,oraDBSID::process.max-file-descriptor=(basic,65536,deny),(priv,65536,deny);process.max-sem-nsems=(priv,512,deny);project.max-sem-ids=(priv,512,deny);project.max-shm-ids=(priv,256,deny);project.max-shm-memory=(priv,18446744073709551615,deny)
**************************************************************************************************************************************
what can i do to make the import faster ? my system definitely has enough resources but i am not able to utilizethem all. Thanks in advance
*FYI - I chose 4 as the parallel processes. I have 12 physical CPUs on my system and each physical CPU has 2 virtual CPUs. Should i restart the installation with more parallel processes. I have read that they can be set to 3-4 * no. of CPUs. should i set itto 48 or more ?*
Edited by: rajivshekhar on May 10, 2010 8:35 PM
Edited by: rajivshekhar on May 10, 2010 8:37 PM