Re: Oracle performance tuning question
- From: joel garry <joel-garry@xxxxxxxx>
- Date: Mon, 30 Jul 2007 16:18:31 -0700
On Jul 29, 2:28 pm, sybra...@xxxxxxxxx wrote:
On Sun, 29 Jul 2007 08:07:45 -0700, "jshen....@xxxxxxxxx"
db_name = "codb"
instance_name = codb
service_names = co65db
control_files = ("/oracle/oradata/codb/control/rlvcontrol01", "/oracle/
oradata/codb/control/rlvcontrol02", "/oracle/oradata/pi
n65db/control/rlvcontrol03")
open_cursors = 500
max_enabled_roles = 30
shared_pool_size = 800000000
large_pool_size = 30M
java_pool_size = 0
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
#processes = 600
processes = 1200
log_buffer = 5242880
log_archive_start = true
log_archive_dest_1 = "location=/arch/codb"
log_archive_format = arch_%t_%s.arc
#DBCA uses the default database value (30) for max_rollback_segments
#100 rollback segments (or more) may be required in the future
#Uncomment the following entry when additional rollback segments are
created and made online
#max_rollback_segments = 101
# If using private rollback segments, place lines of the following
# form in each of your instance-specific init.ora files:
rollback_segments =
(COROLL00,COROLL01,COROLL02,COROLL03,COROLL04,COROLL05,COROLL06,COROLL07,COROLL08,COROLL09,COROLL10,P
INROLL11,COROLL12,COROLL13,COROLL14,COROLL15,COROLL16,COROLL17,COROLL18,COROLL19,COROLL20,COROLL21,COROLL22,COROLL23,PIN
ROLL24,COROLL25,COROLL26,COROLL27,COROLL28,COROLL29,COROLL30,COROLL31,COROLL32,COROLL33,COROLL34,COROLL35,COROLL36,PINRO
LL37,COROLL38,COROLL39,COROLL40,COROLL41,COROLL42,COROLL43,COROLL44,COROLL45,COROLL46,COROLL47,COROLL48,COROLL49,COROLL
50,COROLL51,COROLL52,COROLL53,COROLL54,COROLL55,COROLL56,COROLL57,COROLL58,COROLL59,COROLL60,COROLLBIG,COROLL61,COROLL6
2,COROLL63,COROLL64,COROLL65,COROLL66,COROLL67,COROLL68,COROLL69,COROLL70,COROLL71,COROLL72,COROLL73,COROLL74,COROLL75,
COROLL76,COROLL77,COROLL78,COROLL79,COROLL80,COROLL81,COROLL82,COROLL83,COROLL84,COROLL85,COROLL86,COROLL87,COROLL88,PI
NROLL89,COROLL90,COROLL91,COROLL92,COROLL93,COROLL94,COROLL95,COROLL96,COROLL97,COROLL98,COROLL99)
# Global Naming -- enforce that a dblink has same name as the db it
connects to
global_names = true
# 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 = /oracle/app/oracle/admin/codb/bdump
core_dump_dest = /oracle/app/oracle/admin/codb/cdump
user_dump_dest = /oracle/app/oracle/admin/codb/udump
db_block_size = 4096
remote_login_passwordfile = exclusive
os_authent_prefix = ""
compatible = "8.1.0"
sort_area_size = 65536
sort_area_retained_size = 65536
disk_asynch_io = TRUE
db_files=1024
#lock_sga=true
#
db_block_lru_latches=12
audit_trail = false
transaction_auditing = false
optimizer_mode = rule
db_file_multiblock_read_count = 16
db_writer_processes=4
parallel_execution_message_size=8192
sort_multiblock_read_count = 8
UTL_FILE_DIR=/oracle/migrate
#
HPUX_SCHED_NOAGE = 178
Setting lock_sga to true would be yet another rash measure.
I don't think it's so rash if you set pre_page_sga, which preloads the
sizing. I don't think I've used it myself on hp-ux, since any actual
usage of swap space seems to send everything to hell anyways.
You would need to closely verify your current parameters.
To mention a few things with the worst first:
your db_block_size is 4k, while as far as I remember HP always reads
8k blocks. So, even if you do a single block read and ask for 4k, HP
will still read 8k, and simply transfer only 4k to Oracle.
Changing db_block_size requires rebuilding the database, so beware.
db_file_multiblock_read_count seems too small, as HP can read 1 Mb
ahead.
shared_pool_size is probably way too big. Did you ever query v$sgastat
to find out how much shared pool is free?
Large_pool is set to 30M and you don't use it, as you are not using
MTS
sort_area_size is 64k, should be 1 M.
If he's using RMAN, large_pool might be useful. Mine's set to twice
that. Depends on how exactly RMAN is being used.
But then the biggest problem of them all:
you didn't set db_cache_size or db_block_buffers.
The default is only 200 blocks.
You should verify this parameter at once!
You are not using any cache!
I'm not surprised you are waiting for disk big time.
LOL if that's the case!
As I remember it (and I could be wrong - or perhaps it varied by exact
server hardware, I seem to recall there was a webpage at hp that had
the matrix) the OP's configuration must be 64 bit Oracle. However if
I'm wrong and it is 32 bit Oracle, there are metalink notes about how
to play games to get an SGA > 1.75G (Note:77310.1).
Note:68105.1 commonly misconfigured system parameters, is handy.
The oracle server is running with Async I/O.
Should I reconstruct DB files to larger size and fewer number ?
These two statements will lead to some problem, I think. Doesn't 11i
require raw devices with Async?
jg
--
@home.com is bogus.
http://news.com.com/8301-10784_3-9747705-7.html
.
- References:
- Oracle performance tuning question
- From: jshen.cad@xxxxxxxxx
- Re: Oracle performance tuning question
- From: sybrandb
- Oracle performance tuning question
- Prev by Date: OT - anyone looking to log some mileage during Open World 2007?
- Next by Date: Re: obvious bugs with 10.2.0.2 and aix5L
- Previous by thread: Re: Oracle performance tuning question
- Next by thread: GLOBAL_NAME vs. SERVICE_NAMES
- Index(es):
Relevant Pages
|
