Solved

Oracle 9i(9.2.0.2) performance problems after upgrading from 8.1.6

Posted on 2003-12-02
6
1,635 Views
Last Modified: 2007-12-19
Performance issues after upgrading from 8.1.6 to 9.2.0.2.  From monitoring some of the areas of performance degradation, it looks like the optimizer is not using indices where it looks like it should.  This is not true for all statements.  In particular, I ran a statement in the 9i instance and the optimizer used the full table scan and I reran in the old 8i environment and it is using an index.  Is there something in the init.ora that needs to be set.
?  We have made a few changes in the init.ora,ie,optimizer feature enable set to 8.1.7 and 9 and no difference in performace.  This is the current parameter settings:
O7_DICTIONARY_ACCESSIBILITY = FALSE
_multi_join_key_table_lookup = FALSE
_push_join_union_view = FALSE
_table_lookup_prefetch_size = 0
aq_tm_processes = 0
archive_lag_target = 0
audit_file_dest = ?/rdbms/audit
audit_sys_operations = FALSE
audit_trail = DB
background_core_dump = partial
background_dump_dest = /oracle/admin/xxxx/bdump
backup_tape_io_slaves = FALSE
bitmap_merge_area_size = 1048576
blank_trimming = FALSE
circuits = 335
cluster_database = FALSE
cluster_database_instances = 1
commit_point_strength = 1
compatible = 9.2.0
control_file_record_keep_time = 0
control_files = /oracle/data4/oradata/xxxx/cntrl1BAS.ctl, /oracle/data6/oradata/xxxx/cntrl2BAS.ctl, /oracle/data7/oradata/xxxx/cntrl3BAS.ctl
core_dump_dest = /oracle/admin/xxxx/cdump
cpu_count = 8
create_bitmap_area_size = 8388608
cursor_sharing = EXACT
cursor_space_for_time = TRUE
db_16k_cache_size = 0
db_2k_cache_size = 0
db_32k_cache_size = 0
db_4k_cache_size = 0
db_8k_cache_size = 0
db_block_buffers = 0
db_block_checking = FALSE
db_block_checksum = TRUE
db_block_size = 32768
db_cache_advice = ON
db_cache_size = 134217728
db_domain = world
db_file_multiblock_read_count = 8
db_files = 200
db_keep_cache_size = 0
db_name = basqa2
db_recycle_cache_size = 0
db_writer_processes = 4
dblink_encrypt_login = FALSE
dbwr_io_slaves = 0
dg_broker_config_file1 = ?/dbs/dr1@.dat
dg_broker_config_file2 = ?/dbs/dr2@.dat
dg_broker_start = FALSE
disk_asynch_io = FALSE
dispatchers = (PROTOCOL=TCP)(SER=MODOSE), (PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer), (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)
distributed_lock_timeout = 60
dml_locks = 500
drs_start = FALSE
enqueue_resources = 905
fast_start_io_target = 0
fast_start_mttr_target = 300
fast_start_parallel_rollback = LOW
file_mapping = FALSE
filesystemio_options = setall
global_names = FALSE
hash_area_size = 16384000
hash_join_enabled = TRUE
hi_shared_memory_address = 0
hs_autoregister = TRUE
instance_name = basqa2
instance_number = 0
java_max_sessionspace_size = 0
java_pool_size = 167772160
java_soft_sessionspace_limit = 0
job_queue_processes = 10
large_pool_size = 16777216
license_max_sessions = 0
license_max_users = 0
license_sessions_warning = 0
lock_sga = FALSE
log_archive_dest = /oracle/admin/xxxx/arch
log_archive_dest_state_1 = enable
log_archive_dest_state_10 = enable
log_archive_dest_state_2 = enable
log_archive_dest_state_3 = enable
log_archive_dest_state_4 = enable
log_archive_dest_state_5 = enable
log_archive_dest_state_6 = enable
log_archive_dest_state_7 = enable
log_archive_dest_state_8 = enable
log_archive_dest_state_9 = enable
log_archive_format = archbasmt%S.ARC
log_archive_max_processes = 2
log_archive_min_succeed_dest = 1
log_archive_start = TRUE
log_archive_trace = 0
log_buffer = 1048576
log_checkpoint_interval = 0
log_checkpoint_timeout = 1800
log_checkpoints_to_alert = FALSE
log_parallelism = 1
logmnr_max_persistent_sessions = 1
max_commit_propagation_delay = 99
max_dispatchers = 5
max_dump_file_size = 10240
max_enabled_roles = 148
max_rollback_segments = 73
max_shared_servers = 20
mts_circuits = 335
mts_dispatchers = (PROTOCOL=TCP)(SER=MODOSE), (PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer), (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)
mts_max_dispatchers = 5
mts_max_servers = 20
mts_multiple_listeners = FALSE
mts_servers = 1
mts_service = basqa2
mts_sessions = 330
nls_language = AMERICAN
nls_length_semantics = BYTE
nls_nchar_conv_excp = FALSE
nls_territory = AMERICA
object_cache_max_size_percent = 10
object_cache_optimal_size = 102400
olap_page_pool_size = 33554432
open_cursors = 500
open_links = 4
open_links_per_instance = 4
optimizer_dynamic_sampling = 0
optimizer_features_enable = 8.1.7
optimizer_index_caching = 0
optimizer_index_cost_adj = 100
optimizer_max_permutations = 80000
optimizer_mode = CHOOSE
oracle_trace_collection_path = ?/otrace/admin/cdf
oracle_trace_collection_size = 5242880
oracle_trace_enable = FALSE
oracle_trace_facility_name = oracled
oracle_trace_facility_path = ?/otrace/admin/fdf
os_authent_prefix = ops$
os_roles = FALSE
parallel_adaptive_multi_user = FALSE
parallel_automatic_tuning = FALSE
parallel_execution_message_size = 2176
parallel_max_servers = 5
parallel_min_percent = 0
parallel_min_servers = 0
parallel_server = FALSE
parallel_server_instances = 1
parallel_threads_per_cpu = 2
partition_view_enabled = FALSE
pga_aggregate_target = 25165824
plsql_compiler_flags = INTERPRETED
plsql_native_library_subdir_count = 0
plsql_v2_compatibility = FALSE
pre_page_sga = FALSE
processes = 300
query_rewrite_enabled = false
query_rewrite_integrity = enforced
read_only_open_delayed = FALSE
recovery_parallelism = 0
remote_archive_enable = true
remote_dependencies_mode = SIGNATURE
remote_login_passwordfile = EXCLUSIVE
remote_os_authent = FALSE
remote_os_roles = FALSE
replication_dependency_tracking = TRUE
resource_limit = FALSE
resource_manager_plan = SYSTEM_PLAN
row_locking = always
serial_reuse = DML
serializable = FALSE
service_names = xxxx, xxxx.world
session_cached_cursors = 100
session_max_open_files = 10
sessions = 335
sga_max_size = 1075810880
shadow_core_dump = partial
shared_memory_address = 0
shared_pool_reserved_size = 8388608
shared_pool_size = 167772160
shared_server_sessions = 330
shared_servers = 1
sort_area_retained_size = 0
sort_area_size = 3145728
sql92_security = FALSE
sql_trace = FALSE
sql_version = NATIVE
standby_archive_dest = ?/dbs/arch
standby_file_management = MANUAL
star_transformation_enabled = true
statistics_level = TYPICAL
tape_asynch_io = TRUE
thread = 0
timed_os_statistics = 0
timed_statistics = TRUE
trace_enabled = TRUE
transaction_auditing = TRUE
transactions = 368
transactions_per_rollback_segment = 5
undo_management = AUTO
undo_retention = 10800
undo_suppress_errors = FALSE
undo_tablespace = UNDOTBS1
use_indirect_data_buffers = FALSE
user_dump_dest = /oracle/admin/xxxx/udump
utl_file_dir = *
workarea_size_policy = AUTO


0
Comment
Question by:mhowley
  • 2
6 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 168 total points
ID: 9858886
Analyze all schema. the best way to do this is from OMS or to issue as SYS:
begin
dbms_stats.gather_database_stats();
end;
0
 
LVL 5

Assisted Solution

by:gmyers
gmyers earned 166 total points
ID: 9863719
From monitoring some of the areas of performance degradation, it looks like the optimizer is not using indices where it looks like it should.

Checkout ASKTOM.ORACLE.COM.

From there :
"optimizer_index_caching - percentage of blocks expected to be found in the
buffer cache during an index hit. default of 0 implies that every (logical) LIO
is a (physical) PIO.
optimizer_index_cost_adj - represents relative cost of PIO's for indexed
access vs full scan.  Default value of 100 indicates that an indexed access is
just as costly as a full access.

On many systems (most oltp casual query systems) -- the default values for these
parameters are totally wrong.  The default of 100 for cost adj is way too high.  
The default of 0 for index caching is way too low.
"

Check the parameters on your old Oracle 8i instance (assuming you were using COST base optimizer there).

0
 
LVL 5

Assisted Solution

by:jpkemp
jpkemp earned 166 total points
ID: 9864218
You need to compare your current parameters with the old parameters.

You could try setting the following parameter:

optimizer_features_enable = 8.1.6

- to try to emulate 8.1.6 behaviour as much as possible. However, it is better to set it to the current version (e.g. 9.2) to enable the new features in 9i.

I would consider adjusting one or both of the following as per gmyers' advice:

optimizer_index_caching
optimizer_index_cost_adj

And don't forget to get the stats as per schwertner's advice.

Jeff
0
 
LVL 47

Expert Comment

by:schwertner
ID: 9865040
Also run the compute statistics script often. Every week if possible.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now