APEX

Monitoring my Apx 4.2 app on Oracle 12c, Windows Azure serving 200 users most of them in an intensive use with small idle moments, I notice some strange behavior of the session counter as of the server type assigned by those connections.

Doing a test without any user connected, I open the main pageand it start one session on shared server (I'm monitoring it using Toad), just after login in, two more connections appears, both in shared server mode, but if i leave the page with no interaction, those sessions turn to "none" server for a while and then turned to a dedicated server mode. The same sessions.

I assume those connections drain the server resources, limits the performance of the application and cause the slow response time when all the 200 users interact with the app and the DB.

I need to know if this behavior is regular in apex 4.2 and if not, what I must do to prevent it to give to the user all the power on the server.

I've been monitoring / adjusting the DB parameters and right now it have 50 shared_servers and 50 dispatchers. It is on a Windows server 2012 64b 7GB.

The pfile:

# Oracle init.ora parameter file generated by instance cno on 01/30/2016 18:13:20

__data_transfer_cache_size=0

__db_cache_size=1040M

__java_pool_size=16M

__large_pool_size=720M

__oracle_base='C:\OracleDatabase' # ORACLE_BASE set from environment

__pga_aggregate_target=1536M

__sga_target=3584M

__shared_io_pool_size=160M

__shared_pool_size=1584M

__streams_pool_size=32M

_adaptive_window_consolidator_enabled=TRUE

_aggregation_optimization_settings=0

_always_anti_join='CHOOSE'

_always_semi_join='CHOOSE'

_and_pruning_enabled=TRUE

_b_tree_bitmap_plans=TRUE

_bloom_filter_enabled=TRUE

_bloom_folding_enabled=TRUE

_bloom_pruning_enabled=TRUE

_bloom_serial_filter='ON'

_complex_view_merging=TRUE

_compression_compatibility='12.1.0.2.0'

_connect_by_use_union_all='TRUE'

_convert_set_to_join=FALSE

_cost_equality_semi_join=TRUE

_cpu_to_io=0

_diag_adr_trace_dest='C:\ORACLEDATABASE\diag\rdbms\cno\cno\trace'

_dimension_skip_null=TRUE

_distinct_agg_optimization_gsets='CHOOSE'

_eliminate_common_subexpr=TRUE

_enable_type_dep_selectivity=TRUE

_fast_full_scan_enabled=TRUE

_first_k_rows_dynamic_proration=TRUE

_gby_hash_aggregation_enabled=TRUE

_gby_vector_aggregation_enabled=TRUE

_generalized_pruning_enabled=TRUE

_globalindex_pnum_filter_enabled=TRUE

_gs_anti_semi_join_allowed=TRUE

_improved_outerjoin_card=TRUE

_improved_row_length_enabled=TRUE

_index_join_enabled=TRUE

_kks_obsolete_dump_threshold=0

_ksb_restart_policy_times='0'

_ksb_restart_policy_times='60'

_ksb_restart_policy_times='120'

_ksb_restart_policy_times='240' # internal update to set default

_left_nested_loops_random=TRUE

_local_communication_costing_enabled=TRUE

_minimal_stats_aggregation=TRUE

_mmv_query_rewrite_enabled=TRUE

_new_initial_join_orders=TRUE

_new_sort_cost_estimate=TRUE

_nlj_batching_enabled=1

_optim_adjust_for_part_skews=TRUE

_optim_enhance_nnull_detection=TRUE

_optim_new_default_join_sel=TRUE

_optim_peek_user_binds=TRUE

_optimizer_adaptive_cursor_sharing=TRUE

_optimizer_adaptive_plans=TRUE

_optimizer_aggr_groupby_elim=TRUE

_optimizer_ansi_join_lateral_enhance=TRUE

_optimizer_ansi_rearchitecture=TRUE

_optimizer_batch_table_access_by_rowid=TRUE

_optimizer_better_inlist_costing='ALL'

_optimizer_cbqt_no_size_restriction=TRUE

_optimizer_cluster_by_rowid=TRUE

_optimizer_cluster_by_rowid_batched=TRUE

_optimizer_cluster_by_rowid_control=129

_optimizer_coalesce_subqueries=TRUE

_optimizer_complex_pred_selectivity=TRUE

_optimizer_compute_index_stats=TRUE

_optimizer_connect_by_combine_sw=TRUE

_optimizer_connect_by_cost_based=TRUE

_optimizer_connect_by_elim_dups=TRUE

_optimizer_correct_sq_selectivity=TRUE

_optimizer_cost_based_transformation='LINEAR'

_optimizer_cost_hjsmj_multimatch=TRUE

_optimizer_cost_model='CHOOSE'

_optimizer_cube_join_enabled=TRUE

_optimizer_dim_subq_join_sel=TRUE

_optimizer_distinct_agg_transform=TRUE

_optimizer_distinct_elimination=TRUE

_optimizer_distinct_placement=TRUE

_optimizer_dsdir_usage_control=126

_optimizer_eliminate_filtering_join=TRUE

_optimizer_enable_density_improvements=TRUE

_optimizer_enable_extended_stats=TRUE

_optimizer_enable_table_lookup_by_nl=TRUE

_optimizer_enhanced_filter_push=TRUE

_optimizer_extend_jppd_view_types=TRUE

_optimizer_extended_cursor_sharing='UDO'

_optimizer_extended_cursor_sharing_rel='SIMPLE'

_optimizer_extended_stats_usage_control=192

_optimizer_false_filter_pred_pullup=TRUE

_optimizer_fast_access_pred_analysis=TRUE

_optimizer_fast_pred_transitivity=TRUE

_optimizer_filter_pred_pullup=TRUE

_optimizer_fkr_index_cost_bias=10

_optimizer_full_outer_join_to_outer=TRUE

_optimizer_gather_feedback=TRUE

_optimizer_gather_stats_on_load=TRUE

_optimizer_group_by_placement=TRUE

_optimizer_hybrid_fpwj_enabled=TRUE

_optimizer_improve_selectivity=TRUE

_optimizer_inmemory_access_path=TRUE

_optimizer_inmemory_autodop=TRUE

_optimizer_inmemory_bloom_filter=TRUE

_optimizer_inmemory_cluster_aware_dop=TRUE

_optimizer_inmemory_gen_pushable_preds=TRUE

_optimizer_inmemory_minmax_pruning=TRUE

_optimizer_inmemory_table_expansion=TRUE

_optimizer_interleave_jppd=TRUE

_optimizer_join_elimination_enabled=TRUE

_optimizer_join_factorization=TRUE

_optimizer_join_order_control=3

_optimizer_join_sel_sanity_check=TRUE

_optimizer_max_permutations=2000

_optimizer_mode_force=TRUE

_optimizer_multi_level_push_pred=TRUE

_optimizer_multi_table_outerjoin=TRUE

_optimizer_native_full_outer_join='FORCE'

_optimizer_new_join_card_computation=TRUE

_optimizer_nlj_hj_adaptive_join=TRUE

_optimizer_null_accepting_semijoin=TRUE

_optimizer_null_aware_antijoin=TRUE

_optimizer_or_expansion='DEPTH'

_optimizer_order_by_elimination_enabled=TRUE

_optimizer_outer_join_to_inner=TRUE

_optimizer_outer_to_anti_enabled=TRUE

_optimizer_partial_join_eval=TRUE

_optimizer_proc_rate_level='BASIC'

_optimizer_push_down_distinct=0

_optimizer_push_pred_cost_based=TRUE

_optimizer_reduce_groupby_key=TRUE

_optimizer_rownum_bind_default=10

_optimizer_rownum_pred_based_fkr=TRUE

_optimizer_skip_scan_enabled=TRUE

_optimizer_sortmerge_join_inequality=TRUE

_optimizer_squ_bottomup=TRUE

_optimizer_star_tran_in_with_clause=TRUE

_optimizer_strans_adaptive_pruning=TRUE

_optimizer_system_stats_usage=TRUE

_optimizer_table_expansion=TRUE

_optimizer_transitivity_retain=TRUE

_optimizer_try_st_before_jppd=TRUE

_optimizer_undo_cost_change='12.1.0.2'

_optimizer_unnest_corr_set_subq=TRUE

_optimizer_unnest_disjunctive_subq=TRUE

_optimizer_unnest_scalar_sq=TRUE

_optimizer_use_cbqt_star_transformation=TRUE

_optimizer_use_feedback=TRUE

_optimizer_use_gtt_session_stats=TRUE

_optimizer_use_histograms=TRUE

_optimizer_vector_transformation=TRUE

_or_expand_nvl_predicate=TRUE

_ordered_nested_loop=TRUE

_parallel_broadcast_enabled=TRUE

_partition_view_enabled=TRUE

_pga_max_size=212980K

_pivot_implementation_method='CHOOSE'

_pre_rewrite_push_pred=TRUE

_pred_move_around=TRUE

_push_join_predicate=TRUE

_push_join_union_view=TRUE

_push_join_union_view2=TRUE

_px_adaptive_dist_method='CHOOSE'

_px_concurrent=TRUE

_px_cpu_autodop_enabled=TRUE

_px_external_table_default_stats=TRUE

_px_filter_parallelized=TRUE

_px_filter_skew_handling=TRUE

_px_groupby_pushdown='FORCE'

_px_join_skew_handling=TRUE

_px_minus_intersect=TRUE

_px_object_sampling_enabled=TRUE

_px_parallelize_expression=TRUE

_px_partial_rollup_pushdown='ADAPTIVE'

_px_partition_scan_enabled=TRUE

_px_pwg_enabled=TRUE

_px_replication_enabled=TRUE

_px_scalable_invdist=TRUE

_px_single_server_enabled=TRUE

_px_ual_serial_input=TRUE

_px_wif_dfo_declumping='CHOOSE'

_px_wif_extend_distribution_keys=TRUE

_query_rewrite_setopgrw_enable=TRUE

_remove_aggr_subquery=TRUE

_replace_virtual_columns=TRUE

_resource_manager_plan=''

_right_outer_hash_enable=TRUE

_selfjoin_mv_duplicates=TRUE

_smm_max_size=106490

_smm_max_size_static=106490

_smm_min_size=1024

_smm_px_max_size=532480

_smm_px_max_size_static=532480

_sql_model_unfold_forloops='RUN_TIME'

_sqltune_category_parsed='DEFAULT' # parsed sqltune_category

_subquery_pruning_enabled=TRUE

_subquery_pruning_mv_enabled=FALSE

_table_scan_cost_plus_one=TRUE

_union_rewrite_for_gs='YES_GSET_MVS'

_unnest_subquery=TRUE

_use_column_stats_for_function=TRUE

audit_file_dest='C:\ORACLEDATABASE\ADMIN\CNO\ADUMP'

audit_trail='NONE' # ABR 10-1-16. antes DB, cambiado para evitar la escritura de eventos en Windows

commit_logging='batch'

commit_wait='nowait'

compatible='12.1.0.2.0'

connection_brokers='((TYPE=DEDICATED)(BROKERS=1))'

connection_brokers='((TYPE=EMON)(BROKERS=1))' # connection_brokers default value

control_files='C:\ORACLEDATABASE\ORADATA\CNO\CONTROLFILE\O1_MF_C830M1O1_.CTL'

control_files='C:\ORACLEDATABASE\FAST_RECOVERY_AREA\CNO\CONTROLFILE\O1_MF_C830M4DS_.CTL'

control_management_pack_access='NONE' # change default to NONE when system is not EE

core_dump_dest='C:\ORACLEDATABASE\DIAG\RDBMS\CNO\CNO\CDUMP'

db_block_size=8192

db_cache_size=0

db_create_file_dest='C:\OracleDatabase\oradata'

db_domain=''

db_name='CNO'

db_recovery_file_dest='F:\OracleDatabase\fast_recovery_area' # ABR 10-1-16. Cambiado de C:\

db_recovery_file_dest_size=6795M

db_writer_processes=2

diagnostic_dest='C:\ORACLEDATABASE'

dispatchers='(PROTOCOL=tcp)(DISPATCHERS=50)(LISTENER=GDCNO)'

filesystemio_options='SETALL'

job_queue_processes=100

local_listener='gdcno'

log_buffer=12896K # log buffer update

max_shared_servers=150

memory_max_target=5G

memory_target=4G

open_cursors=400 # ABR 6-1-2016 de 300

optimizer_dynamic_sampling=2

optimizer_index_caching=50

optimizer_mode='ALL_ROWS'

parallel_min_servers=0 # kxfpnfy update parallel_min_servers

pga_aggregate_target=768M

plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora

processes=300

query_rewrite_enabled='TRUE'

remote_login_passwordfile='EXCLUSIVE'

resource_manager_plan='SCHEDULER[0x4448]:DEFAULT_MAINTENANCE_PLAN'

result_cache_max_size=0

sga_target=3G

shared_servers=50

skip_unusable_indexes=TRUE

undo_tablespace='UNDOTBS1'

It also runs JasperReports using Tomcat 7.0 with:

-Xms1024m

-Xmx1024m

-XX:PermSize=512m

-XX:MaxPermSize=1024m

-Xss256k

-Dcatalina.home=C:\Program Files\Apache Software Foundation\Tomcat 7.0

-Dcatalina.base=C:\Program Files\Apache Software Foundation\Tomcat 7.0

-Djava.endorsed.dirs=C:\Program Files\Apache Software Foundation\Tomcat 7.0\endorsed

-Djava.io.tmpdir=C:\Program Files\Apache Software Foundation\Tomcat 7.0\temp

-Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager

-Djava.util.logging.config.file=C:\Program Files\Apache Software Foundation\Tomcat 7.0\conf\logging.properties

Any help / advice would be highly appreciate.

Agustin.