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.
#
###############################################################################
# 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 )
# 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)"
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.
You seem to be doing OLTP without being in ARCHIVELOG mode.
In which case, I think you should forget tuning until that's sorted.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
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) .
#
# 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.
#
###############################################################################
# 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)
# 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)"
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.
Reply With Quote
Bookmarks