Oracle Database Discussions

Dear Sir,

I have an oracle10g installed on a 64 bit OS and want to allocate 8GB to SGA and want to confirm if i am using the right settings

My sysctl.conf settings are :

cat /etc/sysctl.conf
# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.

# Controls IP packet forwarding
net.ipv4.ip_forward = 0

# Controls source route verification
net.ipv4.conf.default.rp_filter = 1

# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0

# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0

# Controls whether core dumps will append the PID to the core filename
# Useful for debugging multi-threaded applications
kernel.core_uses_pid = 1

# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1

# Controls the maximum size of a message, in bytes
kernel.msgmnb = 65536

# Controls the default maxmimum size of a mesage queue
kernel.msgmax = 65536

#########------------------------################
kernel.shmmax = 8589934592
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 3000 32000 100 128
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144


My init Settings are :

*._DB_BLOCK_LRU_LATCHES=16
*._spin_count=7000
*.archive_lag_target=1800
*.audit_sys_operations=TRUE
*.BACKGROUND_DUMP_DEST='/redf/DB/dbf/dump'
*.circuits=10000
*.COMPATIBLE='10.2.0.4'
*.CONTROL_FILES='/redf/DB/dbf/cntl1/DB_cntl1_01.ctl','/redf/DB/dbf/cntl2/DB_cntl2_02.ctl','/redf/DB/dbf/cntl3/DB_cntl3_03.ctl'
*.CORE_DUMP_DEST='/redf/DB/dbf/dump'
*.cursor_sharing='FORCE'
*.DB_BLOCK_SIZE=8192
*.DB_CACHE_SIZE=1024M
*.db_file_multiblock_read_count=64
*.db_file_name_convert='/redf/DB/dbf','/redf/DB/dbf'
*.DB_NAME='DB'
*.db_writer_processes=8
*.dispatchers='(PROTOCOL=TCP)(DISPATCHERS=10)'
*.FAST_START_MTTR_TARGET=300
*.INSTANCE_NAME='DB'
*.JAVA_POOL_SIZE=64M
*.JOB_QUEUE_PROCESSES=1
*.large_pool_size=150M
*.LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XX.XX.XX.XXX)(PORT=1521)))'
*.LOG_ARCHIVE_DEST_1='LOCATION=/redf/DB/arch'
*.LOG_ARCHIVE_DEST_2='LOCATION=/orabackup/redf/DB/arch_new'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='DB%t_%r_%s.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
*.LOG_ARCHIVE_START=TRUE
*.log_buffer=50000000
*.MAX_DISPATCHERS=40
*.max_shared_servers=40
*.O7_DICTIONARY_ACCESSIBILITY=FALSE
*.open_cursors=10000
*.optimizer_index_cost_adj=20
*.parallel_adaptive_multi_user=TRUE
*.parallel_automatic_tuning=TRUE
*.parallel_max_servers=20
*.parallel_min_servers=2
*.processes=4000
*.QUERY_REWRITE_ENABLED='TRUE'
*.QUERY_REWRITE_INTEGRITY='ENFORCED'
*.recovery_parallelism=5
*.recyclebin='OFF'
*.remote_archive_enable='TRUE'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.remote_os_authent=FALSE
*.resource_limit=TRUE
*.session_cached_cursors=200
*.sessions=4000
*.SGA_MAX_SIZE=8000M
*.SGA_TARGET=6500M
*.shared_pool_size=1024M
*.shared_servers=10
*.sort_area_size=10485760
*.sql92_security=TRUE
*.standby_archive_dest='/redf/DB/arch'
*.standby_file_management='AUTO'
*.statistics_level='TYPICAL'
*.timed_statistics=TRUE
*.UNDO_MANAGEMENT='AUTO'
*.undo_retention=1800
*.undo_tablespace='UNDOTBS'
*.USER_DUMP_DEST='/redf/DB/dbf/dump'


Want to confirm is this a proper tuend settings as per memory provided ?

I am aware that it also depends on my appication behaviour , but i just want to check whether the above settings would suffice for a 16GB RAM machine and 64 bit oracle10g

Also let me know if any other setttings apart from this are required , i choose to use SGA Target .Further my application is read intensive . Your suggestions will be highly appreciated.

Thanx