help about oralce parameters

[phonee]

We have an Oracle 8174 database running on COMPAQ GS160.
It is an OLTP server which has 400--500 sessions .
It has 50--70 active sessions .
Our server has 8 CPU and 8GB Mem.
Tonight we will upgrade our COMPAQ GS160 from 8 CPU 8GB Mem to 16CPU 16 GB Mem.
But we will change our database parameters later.
The fellowing is our pfile now (8 CPU and 8GB Mem).
Would anybody give me some advise of our parameters later (16 CPU and 16GB Mem). Thanks !




initSID.ora




#
# Copyright (c) 1991, 1998 by Oracle Corporation
#
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site. Important system parameters
# are discussed, and example settings given.
#
# Some parameter settings are generic to any size installation.
# For parameters that require different values in different size
# installations, three scenarios have been provided: SMALL, MEDIUM
# and LARGE. Any parameter that needs to be tuned according to
# installation size will have three settings, each one commented
# according to installation size.
#
# Use the following table to approximate the SGA size needed for the
# three scenarious provided in this file:
#
# -------Installation/Database Size------
# SMALL MEDIUM LARGE
# Block 2K 4500K 6800K 17000K
# Size 4K 5500K 8800K 21000K
#
# To set up a database that multiple instances will be using, place
# all instance-specific parameters in one file, and then have all
# of these files point to a master file using the IFILE command.
# This way, when you change a public
# parameter, it will automatically change on all instances. This is
# necessary, since all instances must run with the same value for many
# parameters. For example, if you choose to use private rollback' segments,
# these must be specified in different files, but since all gc_*
# parameters must be the same on all instances, they should be in one file.
#
# INSTRUCTIONS: Edit this file and the other INIT files it calls for
# your site, either by using the values provided here or by providing
# your own. Then place an IFILE= line into each instance-specific
# INIT file that points at this file.
#
# NOTE: Parameter values suggested in this file are based on conservative
# estimates for computer memory availability. You should adjust values upward
# for modern machines.
#
###############################################################################

db_name = "ahbill"
instance_name = ahbill

service_names = ahbill


control_files = ("/devices/rvol/jfdbdg/v2_control1", "/devices/rvol/jfdbdg/v2_control2", "/devices/rvol/jfdbdg/v2_control3")

open_cursors = 1000
max_enabled_roles = 30

#DB_BUFFERS=858849280
db_block_buffers =65000
db_block_size = 16384

#shared_pool_size=300M
shared_pool_size = 614572800

large_pool_size = 15728640
java_pool_size = 20971520

log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
enqueue_resources = 8192
#processes = 300
processes = 600 #modified by xienan at 13 Aug,2002

#log_buffer = 16M
log_buffer = 16777216

# audit_trail = false # if you want auditing
# timed_statistics = false # if you want timed statistics
# max_dump_file_size = 10000 # limit trace file size to 5M each

# Uncommenting the lines below will cause automatic archiving if archiving has
# been enabled using ALTER DATABASE ARCHIVELOG.
# log_archive_start = true
# log_archive_dest_1 = "location=/array/orabill/app/oracle/admin/ahbill/arch"
# log_archive_format = arch_%t_%s.arc

# If using private rollback segments, place lines of the following
# form in each of your instance-specific init.ora files:
#rollback_segments = ( RBS0, RBS1, RBS2 )

rollback_segments = (RBS00,RBS01,RBS02,RBS03,RBS04,RBS05,RBS09,RBS10,RBS11,RBS12,RBS13,RBS14,RBS15,RBS16,RBS17,RBS18,RBS 19)


# Global Naming -- enforce that a dblink has same name as the db it connects to
# global_names = false

# Uncomment the following line if you wish to enable the Oracle Trace product
# to trace server activity. This enables scheduling of server collections
# from the Oracle Enterprise Manager Console.
# Also, if the oracle_trace_collection_name parameter is non-null,
# every session will write to the named collection, as well as enabling you
# to schedule future collections from the console.
# oracle_trace_enable = true

# define directories to store trace and alert files
background_dump_dest = /array/orabill/app/oracle/admin/ahbill/bdump
core_dump_dest = /array/orabill/app/oracle/admin/ahbill/cdump
#Uncomment this parameter to enable resource management for your database.
#The SYSTEM_PLAN is provided by default with the database.
#Change the plan name if you have created your own resource plan.# resource_manager_plan = system_plan
user_dump_dest = /array/orabill/app/oracle/admin/ahbill/udump



remote_login_passwordfile = none

os_authent_prefix = none

# The following parameters are needed for the Advanced Replication Option
#job_queue_processes = 4
#job_queue_interval = 60
distributed_transactions = 250
open_links = 4

#mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
# Uncomment the following line when your listener is configured for SSL
# (listener.ora and sqlnet.ora)
# mts_dispatchers = "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)"

#mts_dispatchers = "(protocol=TCP)"

db_files = 254
compatible = "8.1.0"
sort_area_size = 2048000
sort_area_retained_size = 20480

[pando]

Are your controlfiles on raw devices? And are the raw devices in same Volume Group or Disk? if not I would fire the one who setup the database

Also I can see your company need to contract urgently one experienced Oracle dba

in the meanwhile read

http://download-west.oracle.com/docs...a96533/toc.htm

and go to metalink and read some notes

[SANJAY_G]

What are the objectives for this hardware upgrade? Based on that only you can make use of extra available processing power and memory.

[agasimani]

phonee, I think your dba needs some training on oracle.

As far as settings are concerned, it all depends on your goals and objectives. Since you have spent so much money on the hardware and software, why dont you take an expert DBA's help to setup your database. That will do a lot good for your company's future.

[DaPi]

You seem to be doing OLTP without being in ARCHIVELOG mode.
In which case, I think you should forget tuning until that's sorted.

[phonee]

hi ,pando .
Our controlfiles on raw devices. And the raw devices are in same Volume Group ,but different Disks.

hi ,SANJAY_G.
The objectives for this hardware upgrade is just because we get
these hardare from other servers .

Hi,DaPi.
We really do OLTP without being in ARCHIVELOG mode.
But we have a backup way of EMC SRDF .


Here is the status of our databse.
We have two Oracle 8174 databases running on COMPAQ cluster .
The storage is EMC Symmetrix 8430.
And the databases are installed on share disks.
Two GS160: node A 8CPU 11GB Mem;node B 8CPU 8GB Mem.
Tonight We will upgrade Node B to 16 CPU 16GB Mem.
Next night We will upgrade Node A to 16 CPU 16GB Mem.
Node A runs business service.
Node B runs billing service.

I made a mistake just now . We upgrade the hardware not because of low performance.The pfile of first article is belong to Node B.
And the Node B have few active session .(less than 20) .



1.Node A:
It is an OLTP server which has 400--500 sessions .
It has 50--70 active sessions .
orabus@Ahyz1> vmstat 1 30
Virtual Memory Statistics: (pagesize = 8192)
procs memory pages intr cpu
r w u act free wire fault cow zero react pin pout in sy cs us sy id
34 697 201 1M 253K 137K 1G 66M 356M 1M 173M 46K 4K 15K 15K 42 19 39
33 693 203 1M 254K 137K 1412 140 607 0 426 0 8K 20K 18K 71 28 1
28 695 204 1M 254K 137K 1282 92 438 0 324 0 8K 19K 18K 71 28 2
43 686 201 1M 254K 137K 1767 114 467 0 353 0 7K 19K 17K 71 29 0
37 689 203 1M 253K 137K 2474 172 689 0 374 0 8K 19K 18K 70 29 0
35 695 203 1M 254K 137K 3336 193 918 0 667 0 3K 10K 8K 73 27 0
39 694 198 1M 254K 137K 1600 91 488 0 341 0 8K 20K 18K 72 27 1
26 698 208 1M 254K 137K 1921 98 511 0 315 0 6K 20K 17K 72 27 1
33 691 205 1M 255K 137K 1206 57 302 0 170 0 8K 20K 18K 71 27 2
26 700 202 1M 256K 137K 2024 127 565 0 411 0 8K 22K 18K 70 29 1
41 688 201 1M 255K 137K 2636 122 614 0 367 0 11K 23K 18K 69 30 1
35 691 204 1M 255K 137K 1905 100 722 0 392 0 11K 22K 20K 70 30 1

the following is the pfile of Node A.



#
# Copyright (c) 1991, 2000 by Oracle Corporation
#
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site. Important system parameters
# are discussed, and example settings given.
#
# Some parameter settings are generic to any size installation.
# For parameters that require different values in different size
# installations, three scenarios have been provided: SMALL, MEDIUM
# and LARGE. Any parameter that needs to be tuned according to
# installation size will have three settings, each one commented
# according to installation size.
#
# Use the following table to approximate the SGA size needed for the
# three scenarious provided in this file:
#
# -------Installation/Database Size------
# SMALL MEDIUM LARGE
# Block 2K 4500K 6800K 17000K
# Size 4K 5500K 8800K 21000K
#
# To set up a database that multiple instances will be using, place
# all instance-specific parameters in one file, and then have all
# of these files point to a master file using the IFILE command.
# This way, when you change a public
# parameter, it will automatically change on all instances. This is
# necessary, since all instances must run with the same value for many
# parameters. For example, if you choose to use private rollback' segments,
# these must be specified in different files, but since all gc_*
# parameters must be the same on all instances, they should be in one file.
#
# INSTRUCTIONS: Edit this file and the other INIT files it calls for
# your site, either by using the values provided here or by providing
# your own. Then place an IFILE= line into each instance-specific
# INIT file that points at this file.
#
# NOTE: Parameter values suggested in this file are based on conservative
# estimates for computer memory availability. You should adjust values upward
# for modern machines.
#
###############################################################################

db_name = "ahbus"
instance_name = ahbus

service_names = ahbus


control_files = ("/dev/rvol/busvg01/v1_control1", "/dev/rvol/busvg01/v1_control2", "/dev/rvol/busvg01/v1_control3")

open_cursors = 1500
max_enabled_roles = 30

db_block_buffers = 307200

shared_pool_size = 828375040

large_pool_size = 15728640
#java_pool_size = 20971520
java_pool_size = 1048576

#Modified by Liang Hao at 20021206
#log_checkpoint_interval = 20000
#log_checkpoint_timeout = 18000

log_checkpoint_interval = 409600
log_checkpoint_timeout = 1800

processes = 600
#log_buffer=10485760

# audit_trail = false # if you want auditing
# timed_statistics = false # if you want timed statistics
# max_dump_file_size = 10000 # limit trace file size to 5M each
max_dump_file_size = 5M # Modified by Xie Nan to limit trace file size to 5M each

# Uncommenting the lines below will cause automatic archiving if archiving has
# been enabled using ALTER DATABASE ARCHIVELOG.
# log_archive_start = true
# log_archive_dest_1 = "location=/array/orabus/app/oracle/admin/ahbus/arch"
# log_archive_format = arch_%t_%s.arc

# If using private rollback segments, place lines of the following
# form in each of your instance-specific init.ora files:
#rollback_segments = (RBS0,RBS1,RBS2,RBS3,RBS4,RBS5,RBS6,RBS7,RBS8,RBS9,RBS10,RBS11,RBS12,RBS13,RBS14,RBS15,RBS16,RBS17,R BS18,RBS19,RBS20,RBS21,RBS22,RBS23,RBS24,RBS25,RBS26,RBS27,RBS28,RBS29)

rollback_segments = (RBS0,RBS1,RBS2,RBS3,RBS4,RBS5,RBS6,RBS7,RBS8,RBS9,RBS10,RBS11,RBS12,RBS13,RBS14,RBS15,RBS16,RBS17,R BS18,RBS19,RBS20,RBS23,RBS24,RBS25,RBS26,RBS27,RBS28,RBS29)

# Global Naming -- enforce that a dblink has same name as the db it connects to
# global_names = false

# Uncomment the following line if you wish to enable the Oracle Trace product
# to trace server activity. This enables scheduling of server collections
# from the Oracle Enterprise Manager Console.
# Also, if the oracle_trace_collection_name parameter is non-null,
# every session will write to the named collection, as well as enabling you
# to schedule future collections from the console.
# oracle_trace_enable = true

# define directories to store trace and alert files
background_dump_dest = /array/orabus/app/oracle/admin/ahbus/bdump
core_dump_dest = /array/orabus/app/oracle/admin/ahbus/cdump
#Uncomment this parameter to enable resource management for your database.
#The SYSTEM_PLAN is provided by default with the database.
#Change the plan name if you have created your own resource plan.# resource_manager_plan = system_plan
user_dump_dest = /array/orabus/app/oracle/admin/ahbus/udump
db_block_size = 8192
remote_login_passwordfile = none

os_authent_prefix = ""

# The following parameters are needed for the Advanced Replication Option
#job_queue_processes = 4
#job_queue_interval = 60
distributed_transactions = 200
open_links = 4

#mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
# Uncomment the following line when your listener is configured for SSL
# (listener.ora and sqlnet.ora)
# mts_dispatchers = "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)"

#compatible = "8.1.0"
sort_area_size = 2097152
sort_area_retained_size = 2097152

session_cached_cursors = 400

parallel_max_servers = 0
fast_start_parallel_rollback = false

#db_writer_processes = 4
#add by fuxg
db_files = 400
distributed_transactions = 200
#db_block_lru_latches = 24
enqueue_resources = 8192
#session_cached_cursors = 30

#added 29 by Oracle OCT
#cursor_space_for_time=true
_sqlexec_progression_cost=0
#CURSOR_SHARING = FORCE
hash_area_size = 10240000
resource_limit = true

#change 20021106 by LuGH NeuSoftTS
cursor_sharing = exact
#change 20021107 by LuGH NeuSoftTS
cursor_space_for_time=FALSE
db_block_lru_latches=48
db_writer_processes=8
log_buffer=1048576
compatible=8.1.7.4

[SANJAY_G]

Originally Posted by Phonee
hi ,SANJAY_G.
The objectives for this hardware upgrade is just because we get
these hardare from other servers .

Lucky, you. Here I have to justify every dollar and cent.

[JChiappa]

Envy, envy ... Upgrade without a harsh justification... Well, surely, this db config appears to be ** questionable ** at least. Some points :

a) sort_area_size of just 2 Mb, with SO MUCH RAM available ???? ANy special desire for disk sorts , here ?

b) hash_area_size around 10 Mb, BUT without hash_multiblock_io_count ? MAYBE this could be correct, but many times, not. And talkink about blocked I/O, how about db_file_multiblock_read_count, sort_multiblock_read_count, this WAS considered / analyzed ??

c) with SO MUCH ram, how about a keep pool ?? And a bigger large_pool_size , maybe ?

d) you REALLY want a timeout for logs, sometimes this will NOT be the best approach, maybe log files of BIG sizes, NO timeout and a even bigger log_checkpoint_interval would be better

e) NO timed_statistics ??? the performance impact of this parameters is REALLY small in recent versions of Oracle dbs, and the info is SO EXTREMELY useful and important what I leave it activated in ALL my dbs

f) cursor_sharing ??? This is just a BIG and UGLY "crutch" to a ILL-CONFORMED app, and it CAN bring collateral bad effects, SPECIALLY in internal db SQLs, see http://asktom.oracle.com/pls/ask/f?p...:3696883368520 for some examples. IF this is a PROD system, DEMAND corrections from the system producer.


In resume, BRING URGENTLY a DBA here, many possible improvements are showing.

Regards,

Chiappa

[pando]

Well if the database is OLTP sort area size doesnt need to be large and there must be few hash joins so hash area size shouldnt be large as well.

Large pool, I am not sure why is used, MTS used? RMAN used? PQ used? If none is used I dont see the point of setting large pool or even higher value

db_file_multiblock_read_count is just, TOO LARGE

JChiappa have you seen cursor_sharing is commented? They are not using it

[adrianomp]

hi ,SANJAY_G.
The objectives for this hardware upgrade is just because we get
these hardare from other servers .

If it ain't broke, don't fix it yet.