| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> How to tune for "too many" users ?
Hello, NG
what would be the right strategy to tune the instance, so that "too many"
users do not slow down
the whole thing.
My situation:
1 AIX 4.3.3 on Escala Server, Oracle 8.1.7 EE with inhouse database,
dedicated server, 8 GB of data (so it is fairly small)
max. 200 users logged on over Net8 in a C/S env
using Oracle Forms 4.5/Reports 5 as inhouse app (I know, desupported, but I
have no choice).
The app references a lot of PL/SQL packages, but no Java or other
new-fashioned things.
We migrated from 8.0.5 last year, we never had the following symptom that
time, which is coming up
more and more over the last 4-6 weeks.
Symptom:
When more than 10-20 users are active (monitored by v$session etc. and
parallely by HORA tool),
I see performance decreasing significantly even on very small queries. At
these times, I obsere only very few locks
and almost no blockings (which is not the issue here, I have a solution for
this). Sometimes, starting a Form
may take 10 or more seconds ... before this symptom, i the past I only had
(rare) problems with blocking sessions;
this has been solved. So, when there are no blockings anymore, what could be
the major cause for those jams ?
There must be a solution for this, since Oracle was designed to cope with much more loads than this ...
My problem is that the inhouse app on Forms is so complex that it is almost
impossible for me to detect
who is producing the most bottleneck in that moment. A query on v$sqlarea is
not fast enough for this, e.g.
Prompt 1. What is user XYZ doing ?
prompt ############################################################
SELECT
-- s.username, --who
-- s.user#, --who(UserID)
s.status, s.sid, --Session s.serial#, s.action, --with what s.lockwait, substr(s.terminal,1,8) PC, -- s.sql_address, -- s.sql_hash_value,
a.sql_text
FROM
v$session s,
v$sqlarea a
WHERE s.username = 'XYZ'
AND a.address = s.sql_address
AND a.hash_value = s.sql_hash_value
ORDER BY PC
/
and so I just query v$session for active sessions.
I know that there may be more details to ask for about the instance
(parameters, sga, app design),
for now I would like to know where to start from, because reading several
manuals,
I seem to miss the right hint ...
Here are the instance parameters:
1* select name, value from v$parameter SQL> select substr(name,1,30), substr(value,1,40) from v$parameter 2 order by name;
SUBSTR(NAME,1,30) SUBSTR(VALUE,1,40)
------------------------------ ----------------------------------------
active_instance_count always_anti_join NESTED_LOOPS always_semi_join STANDARD aq_tm_processes 0 audit_file_dest ?/rdbms/audit audit_trail NONE background_core_dump full background_dump_dest /u01/app/oracle/admin/ora8/bdump backup_tape_io_slaves FALSE bitmap_merge_area_size 1048576 blank_trimming FALSE buffer_pool_keep buffer_pool_recycle commit_point_strength 1 compatible 8.1.7.0.0 control_file_record_keep_time 7 control_files /u01/oradata/ora8/control01.ctl, /u02/or... core_dump_dest /u01/app/oracle/admin/ora8/cdump cpu_count 2 create_bitmap_area_size 8388608 cursor_sharing EXACT cursor_space_for_time FALSE db_block_buffers 30000 db_block_checking FALSE db_block_checksum FALSE db_block_lru_latches 4 db_block_max_dirty_target 30000 db_block_size 4096 db_domain world db_file_direct_io_count 64 db_file_multiblock_read_count 32 db_file_name_convert db_files 40 dblink_encrypt_login FALSE db_name ora8 dbwr_io_slaves 0 db_writer_processes 4 disk_asynch_io TRUE distributed_transactions 275 dml_locks 500 enqueue_resources 2075 event fast_start_io_target 30000
gc_defer_time 10 gc_files_to_locks gc_releasable_locks 0 gc_rollback_locks 0-128=32!8REACH global_names TRUE hash_area_size 1280000 hash_join_enabled TRUE hash_multiblock_io_count 0 hi_shared_memory_address 0 hs_autoregister TRUE ifile /u01/app/oracle/admin/ora8/pfile/configo...instance_groups
SUBSTR(NAME,1,30) SUBSTR(VALUE,1,40)
------------------------------ ----------------------------------------
instance_name ora8 instance_number 0 java_max_sessionspace_size 0 java_pool_size 32768 java_soft_sessionspace_limit 0 job_queue_interval 60 job_queue_processes 1 large_pool_size 0 license_max_sessions 0 license_max_users 0 license_sessions_warning 0 lm_locks 12000 lm_ress 6000
lock_sga FALSE log_archive_dest /sicher/arch/ log_archive_dest_state_1 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_1 log_archive_dest_2 log_archive_dest_3 log_archive_dest_4 log_archive_dest_5 log_archive_duplex_dest log_archive_format ARC_%T_%S.arc log_archive_max_processes 1 log_archive_min_succeed_dest 1 log_archive_start FALSE log_archive_trace 0 log_buffer 524288 log_checkpoint_interval 100000 log_checkpoints_to_alert FALSE log_checkpoint_timeout 1800 log_file_name_convert max_commit_propagation_delay 700 max_dump_file_size 10240 max_enabled_roles 30 max_rollback_segments 220 mts_circuits 0 mts_dispatchers mts_listener_address mts_max_dispatchers 5 mts_max_servers 20 mts_multiple_listeners FALSE mts_servers 0 mts_service ora8 mts_sessions 0 nls_calendar nls_comp nls_currency nls_date_format
SUBSTR(NAME,1,30) SUBSTR(VALUE,1,40)
------------------------------ ----------------------------------------
nls_iso_currency nls_language AMERICAN nls_numeric_characters nls_sort nls_territory AMERICA nls_time_format nls_timestamp_format nls_timestamp_tz_format nls_time_tz_format object_cache_max_size_percent 10 object_cache_optimal_size 102400 open_cursors 1500 open_links 4 open_links_per_instance 4 ops_interconnects optimizer_features_enable 8.1.7 optimizer_index_caching 0 optimizer_index_cost_adj 100 optimizer_max_permutations 80000 optimizer_mode CHOOSE optimizer_percent_parallel 0 oracle_trace_collection_name 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_broadcast_enabled FALSE parallel_execution_message_siz 2148 parallel_instance_group 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 plsql_v2_compatibility FALSE pre_page_sga FALSE processes 300 query_rewrite_enabled FALSE query_rewrite_integrity enforced rdbms_server_dn read_only_open_delayed FALSE recovery_parallelism 0 remote_dependencies_mode TIMESTAMP remote_login_passwordfile NONE remote_os_authent FALSE remote_os_roles FALSE replication_dependency_trackin TRUE resource_limit FALSE
SUBSTR(NAME,1,30) SUBSTR(VALUE,1,40)
------------------------------ ----------------------------------------
rollback_segments r01, r02, r03, r04, r05, r06, r07, r08, ...
r31
row_locking always serializable FALSE serial_reuse DISABLE service_names ora8.WORLD, escala1. session_cached_cursors 0 session_max_open_files 10 sessions 1000 shadow_core_dump FULL shared_memory_address 0 shared_pool_reserved_size 8000000 shared_pool_size 80000000 sort_area_retained_size 0 sort_area_size 640000 sort_multiblock_read_count 2 sql_trace FALSE sql_version NATIVE sql92_security FALSE standby_archive_dest ?/dbs/arch star_transformation_enabled FALSE tape_asynch_io TRUE text_enable FALSE thread 0 timed_os_statistics 0 timed_statistics FALSE tracefile_identifier transaction_auditing TRUE transactions 1100 transactions_per_rollback_segm 5 use_indirect_data_buffers FALSE user_dump_dest /u01/app/oracle/admin/ora8/udump use_sigio TRUE utl_file_dir /u01/utlfile
204 Zeilen ausgewählt.
Some heavily dml-ed table have freelists > 1 ans initrans > 1, all tables and indexes have maxtrans 255.
Maybe some guru can see more than me ... I do not ask for a glassball solution, I would be happy with some tips. Is it an instance tuning issue, or would I have to focus more on the app (which would be dredging the oceans ...) ?
Thank You in advance, sorry for the long post Jan Received on Wed Apr 30 2003 - 03:03:04 CDT
 |
 |