Hi,
Recently we have migrated Our Oracle 10g DataBase from Windows NT to IBM AIX Box. Unfortunately, the Database Performance is gone down when compared to Windows NT environment. Since been a week we are working to pick the problem. We have altered the init.ora parameters to see the database behaviour., But there no Improvement is been observerd.
Below are the Init.Ora Parameters ,
Name Value Description
tracefile_identifier null trace file custom identifier
lock_name_space null lock name space used for generating lock names for standby/clone database
processes 395 user processes
sessions 439 user and system sessions
timed_statistics TRUE maintain internal timing statistics
timed_os_statistics 0 internal os statistic gathering interval in seconds
resource_limit TRUE master switch for resource limit
license_max_sessions 0 maximum number of non-system user sessions allowed
license_sessions_warning 0 warning level for number of non-system user sessions
cpu_count 16 number of CPUs for this instance
instance_groups null list of instance group names
event null debug event control - default null string
sga_max_size 15032385536 max total SGA size
pre_page_sga FALSE pre-page sga for process
shared_memory_address 0 SGA starting address (low order 32-bits on 64-bit platforms)
hi_shared_memory_address 0 SGA starting address (high order 32-bits on 64-bit platforms)
use_indirect_data_buffers FALSE Enable indirect data buffers (very large SGA on 32-bit platforms)
lock_sga TRUE Lock entire SGA in physical memory
shared_pool_size 0 size in bytes of shared pool
large_pool_size 0 size in bytes of large pool
java_pool_size 0 size in bytes of java pool
streams_pool_size 50331648 size in bytes of the streams pool
shared_pool_reserved_size 84724940 size in bytes of reserved area of shared pool
java_soft_sessionspace_limit 0 warning limit on size in bytes of a Java sessionspace
java_max_sessionspace_size 0 max allowed size in bytes of a Java sessionspace
spfile /oracle/app/product/10.2.0.3.0/dbs/spfileCALMDB.ora server parameter file
instance_type RDBMS type of instance to be executed
trace_enabled FALSE enable KST tracing
nls_language AMERICAN NLS language name
nls_territory AMERICA NLS territory name
nls_sort null NLS linguistic definition name
nls_date_language null NLS date language name
nls_date_format null NLS Oracle date format
nls_currency null NLS local currency symbol
nls_numeric_characters null NLS numeric characters
nls_iso_currency null NLS ISO currency territory name
nls_calendar null NLS calendar system name
nls_time_format null time format
nls_timestamp_format null time stamp format
nls_time_tz_format null time with timezone format
nls_timestamp_tz_format null timestampe with timezone format
nls_dual_currency null Dual currency symbol
nls_comp null NLS comparison
nls_length_semantics BYTE create columns using byte or char semantics by default
nls_nchar_conv_excp FALSE NLS raise an exception instead of allowing implicit conversion
fileio_network_adapters null Network Adapters for File I/O
filesystemio_options asynch IO operations on filesystem files
disk_asynch_io FALSE Use asynch I/O for random access devices
tape_asynch_io TRUE Use asynch I/O requests for tape devices
dbwr_io_slaves 0 DBWR I/O slaves
backup_tape_io_slaves FALSE BACKUP Tape I/O slaves
resource_manager_plan null resource mgr top plan
cluster_interconnects null interconnects for RAC use
file_mapping FALSE enable file mapping
gcs_server_processes 0 number of background gcs server processes to start
active_instance_count null number of active instances in the cluster database
sga_target 15032385536 Target size of SGA
control_files /oradata10/oradata/CALMDB/control/CONTROL02.CTL control file names list
db_file_name_convert null datafile name convert patterns and strings for standby/clone db
log_file_name_convert null logfile name convert patterns and strings for standby/clone db
control_file_record_keep_time 0 control file record keep time in days
db_block_buffers 0 Number of database blocks cached in memory
db_block_checksum TRUE store checksum in db blocks and check during reads
db_block_size 8192 Size of database block in bytes
db_cache_size 2147483648 Size of DEFAULT buffer pool for standard block size buffers
db_2k_cache_size 0 Size of cache for 2K buffers
db_4k_cache_size 0 Size of cache for 4K buffers
db_8k_cache_size 0 Size of cache for 8K buffers
db_16k_cache_size 0 Size of cache for 16K buffers
db_32k_cache_size 0 Size of cache for 32K buffers
db_keep_cache_size 0 Size of KEEP buffer pool for standard block size buffers
db_recycle_cache_size 0 Size of RECYCLE buffer pool for standard block size buffers
db_writer_processes 6 number of background database writer processes to start
buffer_pool_keep null Number of database blocks/latches in keep buffer pool
buffer_pool_recycle null Number of database blocks/latches in recycle buffer pool
db_cache_advice ON Buffer cache sizing advisory
max_commit_propagation_delay 0 Max age of new snapshot in .01 seconds
compatible 10.2.0.3.0 Database will be completely compatible with this software version
remote_archive_enable TRUE remote archival enable setting
log_archive_config null log archive config parameter
log_archive_start FALSE start archival process on SGA initialization
log_archive_dest null archival destination text string
log_archive_duplex_dest null duplex archival destination text string
log_archive_dest_1 null archival destination #1 text string
log_archive_dest_2 null archival destination #2 text string
log_archive_dest_3 null archival destination #3 text string
log_archive_dest_4 null archival destination #4 text string
log_archive_dest_5 null archival destination #5 text string
log_archive_dest_6 null archival destination #6 text string
log_archive_dest_7 null archival destination #7 text string
log_archive_dest_8 null archival destination #8 text string
log_archive_dest_9 null archival destination #9 text string
log_archive_dest_10 null archival destination #10 text string
log_archive_dest_state_1 enable archival destination #1 state text string
log_archive_dest_state_2 enable archival destination #2 state text string
log_archive_dest_state_3 enable archival destination #3 state text string
log_archive_dest_state_4 enable archival destination #4 state text string
log_archive_dest_state_5 enable archival destination #5 state text string
log_archive_dest_state_6 enable archival destination #6 state text string
log_archive_dest_state_7 enable archival destination #7 state text string
log_archive_dest_state_8 enable archival destination #8 state text string
log_archive_dest_state_9 enable archival destination #9 state text string
log_archive_dest_state_10 enable archival destination #10 state text string
log_archive_max_processes 2 maximum number of active ARCH processes
log_archive_min_succeed_dest 1 minimum number of archive destinations that must succeed
standby_archive_dest ?/dbs/arch standby database archivelog destination text string
log_archive_trace 0 Establish archivelog operation tracing level
log_archive_local_first TRUE Establish EXPEDITE attribute default value
log_archive_format %t_%s_%r.dbf archival destination format
fal_client null FAL client
fal_server null FAL server list
log_buffer 176918528 redo circular buffer size
log_checkpoint_interval 0 # redo blocks checkpoint threshold
log_checkpoint_timeout 0 Maximum time interval between checkpoints in seconds
archive_lag_target 0 Maximum number of seconds of redos the standby could lose
db_files 200 max allowable # db files
db_file_multiblock_read_count 128 db block to be read each IO
read_only_open_delayed FALSE if TRUE delay opening of read only files until first access
cluster_database FALSE if TRUE startup in cluster database mode
parallel_server FALSE if TRUE startup in parallel server mode
parallel_server_instances 1 number of instances to use for sizing OPS SGA structures
cluster_database_instances 1 number of instances to use for sizing cluster db SGA structures
db_create_file_dest null default database location
db_create_online_log_dest_1 null online log/controlfile destination #1
db_create_online_log_dest_2 null online log/controlfile destination #2
db_create_online_log_dest_3 null online log/controlfile destination #3
db_create_online_log_dest_4 null online log/controlfile destination #4
db_create_online_log_dest_5 null online log/controlfile destination #5
db_recovery_file_dest null default database recovery file location
db_recovery_file_dest_size 0 database recovery files size limit
standby_file_management MANUAL if auto then files are created/dropped automatically on standby
gc_files_to_locks null mapping between file numbers and global cache locks
thread 0 Redo thread to mount
fast_start_io_target 0 Upper bound on recovery reads
fast_start_mttr_target 0 MTTR target of forward crash recovery in seconds
log_checkpoints_to_alert FALSE log checkpoint begin/end to alert file
recovery_parallelism 0 number of server processes to use for parallel recovery
logmnr_max_persistent_sessions 1 maximum number of threads to mine
db_flashback_retention_target 1440 Maximum Flashback Database log retention time in minutes.
dml_locks 1000 dml locks - one for each table modified in a transaction
ddl_wait_for_locks FALSE Disable NOWAIT DML lock acquisitions
replication_dependency_tracking TRUE tracking dependency for Replication parallel propagation
instance_number 0 instance number
transactions 482 max. number of concurrent active transactions
transactions_per_rollback_segment 5 number of active transactions per rollback segment
rollback_segments null undo segment list
undo_management AUTO instance runs in SMU mode if TRUE, else in RBU mode
undo_tablespace UNDOTBS1 use/switch undo tablespace
undo_retention 10800 undo retention in seconds
fast_start_parallel_rollback LOW max number of parallel recovery slaves that may be used
resumable_timeout 0 set resumable_timeout
db_block_checking FALSE header checking and data and index block checking
recyclebin off recyclebin processing
create_stored_outlines null create stored outlines for DML statements
serial_reuse disable reuse the frame segments
ldap_directory_access NONE RDBMS's LDAP access option
os_roles FALSE retrieve roles from the operating system
rdbms_server_dn null RDBMS's Distinguished Name
max_enabled_roles 150 max number of roles a user can have enabled
remote_os_authent FALSE allow non-secure remote clients to use auto-logon accounts
remote_os_roles FALSE allow non-secure remote clients to use os roles
O7_DICTIONARY_ACCESSIBILITY FALSE Version 7 Dictionary Accessibility Support
remote_login_passwordfile NONE password file usage parameter
license_max_users 0 maximum number of named users that can be created in the database
audit_sys_operations TRUE enable sys auditing
global_context_pool_size null Global Application Context Pool Size in Bytes
db_domain null directory part of global database name stored with CREATE DATABASE
global_names TRUE enforce that database links have same name as remote database
distributed_lock_timeout 60 number of seconds a distributed transaction waits for a lock
commit_point_strength 1 Bias this node has toward not preparing in a two-phase commit
instance_name CALMDB instance name supported by the instance
service_names CALMDB service names supported by the instance
dispatchers (PROTOCOL=TCP) (SERVICE=CALMDB) specifications of dispatchers
shared_servers 1 number of shared servers to start up
max_shared_servers null max number of shared servers
max_dispatchers null max number of dispatchers
circuits null max number of circuits
shared_server_sessions null max number of shared server sessions
local_listener null local listener
remote_listener null remote listener
cursor_space_for_time FALSE use more memory in order to get faster execution
session_cached_cursors 200 Number of cursors to cache in a session.
remote_dependencies_mode TIMESTAMP remote-procedure-call dependencies mode parameter
utl_file_dir null utl_file accessible directories list
smtp_out_server null utl_smtp server and port configuration parameter
plsql_v2_compatibility FALSE PL/SQL version 2.x compatibility flag
plsql_compiler_flags INTERPRETED, NON_DEBUG PL/SQL compiler flags
plsql_native_library_dir null plsql native library dir
plsql_native_library_subdir_count 0 plsql native library number of subdirectories
plsql_warnings DISABLE:ALL PL/SQL compiler warnings settings
plsql_code_type INTERPRETED PL/SQL code-type
plsql_debug FALSE PL/SQL debug
plsql_optimize_level 2 PL/SQL optimize level
plsql_ccflags null PL/SQL ccflags
job_queue_processes 10 number of job queue slave processes
parallel_min_percent 0 minimum percent of threads required for parallel query
create_bitmap_area_size 8388608 size of create bitmap buffer for bitmap index
bitmap_merge_area_size 1048576 maximum memory allow for BITMAP MERGE
cursor_sharing FORCE cursor sharing mode
parallel_min_servers 10 minimum parallel query servers per instance
parallel_max_servers 320 maximum parallel query servers per instance
parallel_instance_group null instance group to use for all parallel operations
parallel_execution_message_size 4096 message buffer size for parallel execution
hash_area_size 62914560 size of in-memory hash work area
shadow_core_dump partial Core Size for Shadow Processes
background_core_dump partial Core Size for Background Processes
background_dump_dest /oradata28/oradata/CALMDB/bdump Detached process dump directory
user_dump_dest /oradata28/oradata/CALMDB/udump User process dump directory
max_dump_file_size 10M Maximum size (blocks) of dump file
core_dump_dest /oradata28/oradata/CALMDB/cdump Core dump directory
use_sigio TRUE Use SIGIO signal
audit_file_dest /oracle/app/product/10.2.0.3.0/rdbms/audit Directory in which auditing files are to reside
audit_syslog_level null Syslog facility and level
object_cache_optimal_size 102400 optimal size of the user session's object cache in bytes
object_cache_max_size_percent 10 percentage of maximum size over optimal of the user session's object cache
session_max_open_files 20 maximum number of open files allowed per session
open_links 4 max # open links per session
open_links_per_instance 4 max # open links per instance
commit_write null transaction commit log write behaviour
optimizer_features_enable 10.2.0.3 optimizer plan compatibility parameter
fixed_date null fixed SYSDATE value
audit_trail DB enable system auditing
sort_area_size 31457280 size of in-memory sort work area
sort_area_retained_size 3145728 size of in-memory sort work area retained between fetch calls
db_name TESTDB database name specified in CREATE DATABASE
db_unique_name TESTDB Database Unique Name
open_cursors 2000 max # cursors per session
ifile null include file in init.ora
sql_trace FALSE enable SQL trace
os_authent_prefix ops$ prefix for auto-logon accounts
optimizer_mode ALL_ROWS optimizer mode
sql92_security FALSE require select privilege for searched update/delete
blank_trimming FALSE blank trimming semantics parameter
star_transformation_enabled FALSE enable the use of star transformation
parallel_adaptive_multi_user TRUE enable adaptive setting of degree for multiple user streams
parallel_threads_per_cpu 2 number of parallel execution threads per CPU
parallel_automatic_tuning TRUE enable intelligent defaults for parallel execution parameters
optimizer_index_cost_adj 250 optimizer index cost adjustment
optimizer_index_caching 0 optimizer percent index caching
query_rewrite_enabled TRUE allow rewrite of queries using materialized views if enabled
query_rewrite_integrity enforced perform rewrite using materialized views with desired integrity
sql_version NATIVE sql language version parameter for compatibility issues
pga_aggregate_target 3221225472 Target size for the aggregate PGA memory consumed by the instance
workarea_size_policy AUTO policy used to size SQL working areas (MANUAL/AUTO)
optimizer_dynamic_sampling 2 optimizer dynamic sampling
statistics_level TYPICAL statistics level
skip_unusable_indexes TRUE skip unusable indexes if set to TRUE
optimizer_secure_view_merging TRUE optimizer secure view merging and predicate pushdown/movearound
aq_tm_processes 1 number of AQ Time Managers to start
hs_autoregister TRUE enable automatic server DD updates in HS agent self-registration
dg_broker_start FALSE start Data Guard broker framework (DMON process)
drs_start FALSE start DG Broker monitor (DMON process)
dg_broker_config_file1 /oracle/app/product/10.2.0.3.0/dbs/dr1CALMDB.dat data guard broker configuration file #1
dg_broker_config_file2 /oracle/app/product/10.2.0.3.0/dbs/dr2CALMDB.dat data guard broker configuration file #2
olap_page_pool_size 0 size of the olap page pool in bytes
asm_diskstring null disk set locations for discovery
asm_diskgroups null disk groups to mount automatically
asm_power_limit 1 number of processes for disk rebalancing
sqltune_category DEFAULT Category qualifier for applying hintsets
pls suggest
Thanks
Kr