Performance tunning of Oracle applications

[learner]

Dear Sir,

I am new at oracle financials application. We are using Oracle financials 11.0.28. And our users are not satisfied by oracle financials performance.

We start our server two times in a day. After four hour our system performance is working very slowly. And often we face problems about Oracle services.

I highly appreciated your reply about these matters.

Regards with advance thank,

M. Asif

[admin]

Dear Asif,
1- Oracle Financials has nothing to do with performance and this is a standard application.
2- You need to check the performance of your hardware and database.
3- Check if any request is not taking request.
4- Check your instance parameters of your database.
5- Restart is killing your performance as when you restart your cache is no more.
6- 11.0.28 is not a correct version of Oracle applications.

Thanks

[learner]

Sir,
We have total 4 GB ram and we allocate 2 GB ram to oracle. And instance parameters Are

_trace_files_public TRUE
spin_count 2000
processes 150
sessions 170
timed_statistics FALSE
timed_os_statistics 0
resource_limit FALSE
license_max_sessions 0
license_sessions_warning 0
lm_procs 96
lm_ress 6000
lm_locks 12000
ogms_home
parallel_transaction_resource_timeout 300
cpu_count 4
instance_groups
event 10210 trace name context forever, level 2, 10211 trace name context forever, level 2, 10235 trace name context forever, level 1, 10246 trace name context forever, level 2, 7267 trace name errorstack level 4, 3106 trace name errorstack level 4, 10076 trace name context forever, level 1
shared_pool_size 1048576000
shared_pool_reserved_size 52428800
shared_pool_reserved_min_alloc 5K
large_pool_size 10485760
large_pool_min_alloc 16K
pre_page_sga FALSE
shared_memory_address 0
hi_shared_memory_address 0
use_indirect_data_buffers FALSE
use_ism TRUE
lock_sga FALSE
lock_sga_areas 0
instance_nodeset
lock_name_space
enqueue_resources 5000
nls_language american
nls_territory america
nls_sort binary
nls_date_language
nls_date_format DD-MON-RR
nls_currency
nls_numeric_characters .,
nls_iso_currency
nls_calendar
disk_asynch_io TRUE
tape_asynch_io TRUE
dbwr_io_slaves 0
lgwr_io_slaves 0
arch_io_slaves 0
backup_disk_io_slaves 0
backup_tape_io_slaves FALSE
db_file_direct_io_count 64
control_files e:\oa\orant\database\CTL1PROD.DBF
db_file_name_convert
log_file_name_convert
db_block_buffers 64000
buffer_pool_keep
buffer_pool_recycle
db_block_checksum FALSE
db_block_size 8192
db_block_checkpoint_batch 8
db_block_lru_statistics FALSE
db_block_lru_extended_statistics 0
db_writer_processes 1
db_block_lru_latches 2
db_block_max_dirty_target 4294967294
max_commit_propagation_delay 90000
compatible 8.0.4
compatible_no_recovery 0.0.0
log_archive_start FALSE
log_archive_buffers 4
log_archive_buffer_size 127
log_archive_dest %RDBMS80%\
log_archive_duplex_dest
log_archive_min_succeed_dest 1
log_archive_format ARC%s.%t
log_buffer 163840
log_checkpoint_interval 10000
log_checkpoint_timeout 0
log_block_checksum FALSE
log_small_entry_max_size 80
log_simultaneous_copies 4
db_files 255
db_file_simultaneous_writes 4
db_file_multiblock_read_count 32
log_files 255
read_only_open_delayed FALSE
parallel_server FALSE
gc_lck_procs 1
gc_latches 8
gc_releasable_locks 0
gc_rollback_locks
gc_files_to_locks
gc_defer_time 10
thread 0
freeze_DB_for_fast_instance_recovery FALSE
log_checkpoints_to_alert FALSE
recovery_parallelism 0
control_file_record_keep_time 7
temporary_table_locks 170
dml_locks 500
row_locking always
serializable FALSE
replication_dependency_tracking TRUE
delayed_logging_block_cleanouts TRUE
instance_number 0
max_rollback_segments 30
transactions 187
transactions_per_rollback_segment 49
rollback_segments rbs1, rbs2, rbs3, rbs4, rbs5, rbs6, rbs7, rbs8, rbs9
cleanup_rollback_entries 20
transaction_auditing TRUE
discrete_transactions_enabled FALSE
sequence_cache_entries 100
sequence_cache_hash_buckets 89
row_cache_cursors 10
os_roles FALSE
max_enabled_roles 20
remote_os_authent FALSE
remote_os_roles FALSE
O7_DICTIONARY_ACCESSIBILITY TRUE
remote_login_passwordfile SHARED
dblink_encrypt_login FALSE
license_max_users 0
db_domain WORLD
global_names FALSE
distributed_lock_timeout 60
distributed_transactions 46
max_transaction_branches 8
distributed_recovery_connection_hold_time 200
commit_point_strength 1
mts_service PROD
mts_rate_log_size
mts_rate_scale
mts_dispatchers
mts_servers 0
mts_max_servers 20
mts_max_dispatchers 5
local_listener
mts_listener_address
mts_multiple_listeners FALSE
open_links 4
open_links_per_instance 4
close_cached_open_cursors FALSE
optimizer_features_enable 8.0.0
fixed_date
audit_trail NONE
sort_area_size 1048576
sort_area_retained_size 0
sort_direct_writes AUTO
sort_write_buffers 2
sort_write_buffer_size 32768
sort_spacemap_size 512
sort_read_fac 20
db_name PROD
open_cursors 300
ifile
sql_trace FALSE
_optimizer_undo_changes TRUE
os_authent_prefix OPS$
optimizer_mode RULE
sql92_security FALSE
blank_trimming FALSE
always_anti_join NESTED_LOOPS
partition_view_enabled FALSE
b_tree_bitmap_plans FALSE
star_transformation_enabled FALSE
complex_view_merging FALSE
push_join_predicate FALSE
fast_full_scan_enabled FALSE
parallel_broadcast_enabled FALSE
parallel_adaptive_multi_user FALSE
always_semi_join STANDARD
optimizer_max_permutations 80000
optimizer_index_cost_adj 100
optimizer_index_caching 0
serial_reuse DISABLE
cursor_space_for_time FALSE
session_cached_cursors 0
text_enable FALSE
remote_dependencies_mode TIMESTAMP
utl_file_dir e:\oa\orant\plsql80\temp
plsql_v2_compatibility FALSE
job_queue_processes 0
job_queue_interval 60
job_queue_keep_connections FALSE
snapshot_refresh_processes 0
snapshot_refresh_interval 60
snapshot_refresh_keep_connections FALSE
optimizer_percent_parallel 0
optimizer_search_limit 5
parallel_min_percent 0
parallel_default_max_instances 0
cache_size_threshold 6400
create_bitmap_area_size 8388608
bitmap_merge_area_size 1048576
parallel_min_servers 0
parallel_max_servers 5
parallel_server_idle_time 5
allow_partial_sn_results FALSE
parallel_instance_group
ops_admin_group
parallel_execution_message_size 2148
parallel_min_message_pool 96660
hash_join_enabled TRUE
hash_area_size 0
hash_multiblock_io_count 1
background_dump_dest %RDBMS80%\trace
user_dump_dest %RDBMS80%\trace
max_dump_file_size 10240
oracle_trace_enable FALSE
oracle_trace_facility_path %OTRACE80%\ADMIN\FDF\
oracle_trace_collection_path %OTRACE80%\ADMIN\CDF\
oracle_trace_facility_name oracled
oracle_trace_collection_name
oracle_trace_collection_size 5242880
object_cache_optimal_size 102400
object_cache_max_size_percent 10
session_max_open_files 0
aq_tm_processes 0

Regards and thank
M. Asif

[admin]

First of all check if any process is not scheduled using sysadmin and it is taking resources.

Secondly let us know what happens after 3 to 4 hours that you need to restart the server? thanks

[learner]

After 3 or 4 hours Oracle financial system works very slowly and user reports takes time.

Thanks in advance
Regards
M. Asif

[ahmadbilal]

Use top Command To View resource utilization(When you Feel More Resource Utilization then normal also check your reports there might be probelms in report{more Links and Formula Columns You Used Report ill gone slower}).



[quote]After 3 or 4 hours Oracle financial system works very slowly and user reports takes time.

Thanks in advance
Regards
M. Asif


<i><div align="right">Originally posted by learner

[learner]

I have checked reports there is no problem in reports.

Regards
M. Asif

[ahmadbilal]

Okay Mr. Asif
Would you like to tell me which reports are runing when performance goes down.They Are standarded report or customized reports.how many users are connected with instance.
How did you verify that there is no problem in reports.

Use This To Monitor Concurent average Request Time

topic.asp?TOPIC_ID=572
<b>Get Average Runtime of A Concurrent</b>





[quote]I have checked reports there is no problem in reports.

Regards
M. Asif

<i><div align="right">Originally posted by learner

[learner]

i am collecting information.

i want to check how much reports available in financial system. can you give me query?

Regards
M. Asif

[learner]

When both standard and customized reports are running system performance is going down. There are 60 users are connected with instance.

When I run these reports on our backup server. Reports runs fine.

Regards
M Asif

[admin]

Login as sysdba from sqlplus and get output of the following query,

sql > select * from v$sga

for both test and prod.

Secondly compere all parameter values of both the systems and let us know if there is any process running on prod server. thanks

[ahmadbilal]

<b><font face="Verdana">select * from fnd_concurrent_programs_vl </font id="Verdana"> </b>

[quote]i am collecting information.

i want to check how much reports available in financial system. can you give me query?

Regards
M. Asif

<i><div align="right">Originally posted by learner

[learner]

Sir Actually i want to check how much report available <b>Module Wise</b> in fianancial Application.

Regards
M. Asif

[learner]

<b>Performance tuning of Oracle applications</b>

We achieve some success about this topic and we improve performance of our Oracle financial server.

Two or three days I will monitor server performance and then I will send a presentation what step which I have done.

Regards
M Asif

[ahmadbilal]

check topic.asp?TOPIC_ID=572

[quote]Sir Actually i want to check how much report available <b>Module Wise</b> in fianancial Application.

Regards
M. Asif

<i><div align="right">Originally posted by learner