| Performance tuning of Oracle latches [message #62914] |
Tue, 24 August 2004 06:11  |
Ken Jones
Messages: 70
Registered: January 2004
|
Member |
|
|
Guys,
I have built a 9204 data warehouse for ETL called MIPRD on Solaris 8 and 150Gb in size. Please see init.ora:-
##############################################################################
# File: initMIPRD.ora
#
# Description: init.ora for PRD MI Database.
#
# History: 26/07/2004 KJ Created.
#
##############################################################################
# Database Identifier Parameters #
db_name = "MIPRD"
db_domain = WORLD
instance_name = MIPRD
service_names = MIPRD.WORLD
global_names = TRUE
# Control Files #
control_files = ("/u601/oradata/MIPRD/control01.ctl","/u602/oradata/MIPRD/control02.ctl"
,"/u603/oradata/MIPRD/control03.ctl")
# Dump parameters #
background_dump_dest = /u601/app/oracle/admin/MIPRD/bdump
core_dump_dest = /u601/app/oracle/admin/MIPRD/cdump
user_dump_dest = /u601/app/oracle/admin/MIPRD/udump
max_dump_file_size = 10000
# Private Rollback Segments assigned to Instance #
rollback_segments = ( R01, R02, R03, R04, R05, R06, R07, R08 )
transactions_per_rollback_segment = 20
max_rollback_segments = 650
# Archive Log parameters #
#log_archive_dest_1 = 'location=/u606/archive/dumps/MIPRD MANDATORY'
#log_archive_min_succeed_dest = 1
#log_archive_start = true
#log_archive_format = arch_%t_%s.dbf
#log_archive_max_processes = 2
# ASYNC Parameters #
disk_asynch_io = TRUE
tape_asynch_io = TRUE
# Buffer Cache parameters #
db_block_size = 16384
db_file_multiblock_read_count = 4
db_cache_size = 960M
# Tracing Parameters #
timed_statistics = false
sql_trace = false
audit_trail = FALSE
# SGA Sizing Parameters #
log_buffer = 67108864
shared_pool_size = 100000000
# PGA Sizing Parameters #
open_cursors = 1024
pga_aggregate_target = 4G
# System Parameters #
compatible = 9.2.0.0.0
utl_file_dir = /batch_jobs/journals
remote_login_passwordfile = exclusive
_disable_multiple_block_sizes=true
db_writer_processes = 6
undo_management = AUTO
# Optimizer Parameters #
optimizer_mode = CHOOSE
optimizer_index_caching = 100
optimizer_index_cost_adj = 20
optimizer_max_permutations = 2000
# Log Switch Parameters #
log_checkpoint_interval = 1000000
log_checkpoint_timeout = 0
# Materialized View Parameters #
query_rewrite_enabled = true
query_rewrite_integrity = trusted
partition_view_enabled =true
# Parallel Parameters #
parallel_automatic_tuning = TRUE
Archiving is switched off until after the initial load. the load is running now and I am running performance queries on all relevant parts of the instance - all looks good apart from contention on one latch - process queue reference.
select name,((IMMEDIATE_GETS)/GREATEST((IMMEDIATE_GETS + IMMEDIATE_MISSES),1))*100 no_wait
from v$latch
where immediate_gets + immediate_misses > 0
and ((IMMEDIATE_GETS)/GREATEST((IMMEDIATE_GETS + IMMEDIATE_MISSES),1))*100 < 90;
NAME NO_WAIT
------------------------------------------- ----------
process queue reference 36.5881583
Does anyone have any information regarding this latch and how would I minimise contention on this latch. Thanks in advance,
Ken.
|
|
|
|
| Re: Performance tuning of Oracle latches [message #62928 is a reply to message #62914] |
Tue, 24 August 2004 13:31  |
Yong Huang
Messages: 5
Registered: August 2004
|
Junior Member |
|
|
I find two types of articles on Metalink about this latch. One is about parallel executions. Note:1574.999 (or should I call it a thread?) says this just shows you're using parallel executions. Contention on this latch itself doesn't mean you have performance problems. The second is PMON problem. See bug 3415971.8. If this is the case, you'll see ORA-600 [[5238]] in your trace files.
BTW, what're the top wait events? Make sure you have timed_statistics set to true.
Yong Huang
|
|
|
|
| Re: Performance tuning of Oracle latches [message #62938 is a reply to message #62928] |
Wed, 25 August 2004 02:31 |
Ken Jones
Messages: 70
Registered: January 2004
|
Member |
|
|
Hi Yong,
Thanks for the help. I am using parallel execution and do not have 600 errors so maybe it is just informational.
I do not have timed_statistics on as I am doing the large initial ETL process at the moment so everything is swithed off which will impact perf.
Thanks again,
Ken
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
