Poor Performance on Oracle9i

From: eavanzi <eavanzi_at_romi.com.br>
Date: 5 May 2006 08:09:38 -0700
Message-ID: <1146841778.288445.187360@v46g2000cwv.googlegroups.com>

Recently, we have migrated from 8.1.7.4 to 9.2.0.7 and now the performance is terrible!!
Please, help us!!
The database server is Sun E6500 (24 SPARC processors and 24GB RAM mem) and Solaris 2.6.
The database server is connected to IBM Shark Storage 2105 F20 using two gigabit fiber channels. The size database is 400GB. We have 350 Oracle Applications active users. We have the Oracle Applications 11.5.9 running in another server - Sun Fire V880 (4 processors SPARC and 8GB RAM mem) and Solaris 8. The statspack is showing data contention in latches, specially, cache buffers chains and library cache, as follows:

CACHE BUFFERS CHAINS
   Gets = 561344520
   Sleeps = 77469
   Percentage = 48,99%
   Cumulative = 48,99%
LIBRARY CACHE
   Gets = 55715493
   Sleeps = 55456
   Percentage = 35,07%
   Cumulative = 84,06 %

Please, checking if the init.ora parameters are correct.

db_name = PROD
control_files =
/prod11i/app/proddata/control01.ctl,/db1prod/oradata/PROD/control02.ctl, /db1prod/oradata/PROD/control03.ctl
db_block_size = 8192
compatible = 9.2.0
_system_trig_enabled = TRUE

O7_DICTIONARY_ACCESSIBILITY = TRUE

row_locking  = always
nls_language = american
nls_territory = america
nls_date_format = DD-MON-RR
nls_numeric_characters = ".,"
nls_sort = binary
nls_comp = binary
nls_length_semantics  = byte
max_enabled_roles = 100

user_dump_dest = /prod11i/app/proddb/9.2.0/admin/PROD_mercurio/udump background_dump_dest =
/prod11i/app/proddb/9.2.0/admin/PROD_mercurio/bdump core_dump_dest = /prod11i/app/proddb/9.2.0/admin/PROD_mercurio/cdump max_dump_file_size = 1024
timed_statistics = false
_trace_files_public = TRUE

sql_trace = FALSE
processes = 2500
sessions = 5000
db_files = 1024
dml_locks = 10000
open_cursors = 2200
cursor_sharing = EXACT
session_cached_cursors = 4000
enqueue_resources = 32000

db_cache_size = 1G
db_block_checking = FALSE
db_block_checksum = TRUE
log_checkpoint_timeout = 1200
log_checkpoint_interval = 2147483647

log_buffer = 10485760
log_checkpoints_to_alert = TRUE
shared_pool_size = 1400000000
shared_pool_reserved_size = 140000000
_shared_pool_reserved_min_alloc = 4100

pga_aggregate_target = 2G
large_pool_size = 8000000
java_pool_size = 70000000
utl_file_dir = *
aq_tm_processes = 1
job_queue_processes = 10
parallel_max_servers = 20
parallel_min_servers = 0

log_archive_dest=/archPROD/PROD
log_archive_format=arch%s.dbf
log_archive_start=true
undo_management = AUTO
undo_tablespace = APPS_UNDOTS1

undo_retention = 14400

undo_suppress_errors = false
workarea_size_policy = AUTO
olap_page_pool_size  = 4194304

_index_join_enabled = FALSE
_b_tree_bitmap_plans = FALSE
_optimizer_undo_changes = false
_optimizer_mode_force = true

db_file_multiblock_read_count = 8
optimizer_max_permutations = 2000
_sort_elimination_cost_ratio = 5
_like_with_bind_as_equality = TRUE
_fast_full_scan_enabled = FALSE

query_rewrite_enabled = true
_sqlexec_progression_cost = 2147483647

java_soft_sessionspace_limit = 10485760
optimizer_features_enable = 9.2.0
event="10235 trace name context forever, level 1" IFILE=/prod11i/app/proddb/9.2.0/dbs/PROD_mercurio_ifile.ora

Somebody can help us, please?
There are some tips to improve the performance on 9.2.0.7? Thank you. Received on Fri May 05 2006 - 10:09:38 CDT

Poor Performance on Oracle9i - 9.2.0.7