Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Hi,
We observed sudden drop in performance and all batch operations started running slow. The query that used to take 5 minutes in past, is taking 30 minutes now. Also if we terminate that query from batch and run it separately, it finishes well with in 5 minutes.
This is third time in a row it is happening. in past we had to re-build database server with same configuration again and post that issue resolved. First time issue occurred on same database in March 2017 then in Sept 2017 and again now started 3 days back .
We checked with infra team and IO performance seems to be normal. Also DBA's reported no issues.
I know this is very generic question, but have you observed similar issue in past with oracle 12.1.0.2 on AIX machine? Is there any suggestions to check?
Note: Query plan has same access path used but only change is cost of the query.
Below is the parameters list.
lock_name_space
processes 1000
sessions 1536
timed_statistics TRUE
timed_os_statistics 0
resource_limit TRUE
license_max_sessions 0
license_sessions_warning 0
cpu_count 32
instance_groups
event
sga_max_size 34359738368
use_large_pages TRUE
pre_page_sga TRUE
shared_memory_address 0
hi_shared_memory_address 0
use_indirect_data_buffers FALSE
lock_sga FALSE
processor_group_name
shared_pool_size 0
large_pool_size 0
java_pool_size 0
streams_pool_size 0
shared_pool_reserved_size 1281779302
java_soft_sessionspace_limit 0
java_max_sessionspace_size 0
pga_aggregate_limit 19327352832
spfile /u01/app/oracle/product/12.1.0/db_1/dbs/spfileCKBPDDB2.ora
instance_type RDBMS
nls_language AMERICAN
nls_territory AMERICA
nls_sort BINARY
nls_date_language AMERICAN
nls_date_format DD-MON-RR
nls_currency $
nls_numeric_characters .,
nls_iso_currency AMERICA
nls_calendar GREGORIAN
nls_time_format HH.MI.SSXFF AM
nls_timestamp_format DD-MON-RR HH.MI.SSXFF AM
nls_time_tz_format HH.MI.SSXFF AM TZR
nls_timestamp_tz_format DD-MON-RR HH.MI.SSXFF AM TZR
nls_dual_currency $
nls_comp BINARY
nls_length_semantics CHAR
nls_nchar_conv_excp FALSE
fileio_network_adapters
filesystemio_options SETALL
dnfs_batch_size 4096
clonedb FALSE
instant_restore FALSE
disk_asynch_io TRUE
tape_asynch_io TRUE
dbwr_io_slaves 0
backup_tape_io_slaves FALSE
resource_manager_cpu_allocation 32
resource_manager_plan
db_performance_profile
allow_group_access_to_sga FALSE
cluster_interconnects
file_mapping FALSE
service_names CKBPDDB2
threaded_execution FALSE
gcs_server_processes 0
active_instance_count
sga_target 26843545600
memory_target 0
memory_max_target 0
control_files /u02/oradata/CKBPDDB2/control01.ctl, /u03/oradata/CKBPDDB2/control02.ctl, /u04/oradata/CKBPDDB2/control03.ctl
db_file_name_convert
log_file_name_convert
control_file_record_keep_time 7
db_block_buffers 0
db_block_checksum TYPICAL
db_ultra_safe OFF
db_block_size 16384
db_cache_size 134217728
db_2k_cache_size 0
db_4k_cache_size 0
db_8k_cache_size 0
db_16k_cache_size 0
db_32k_cache_size 0
db_keep_cache_size 134217728
db_recycle_cache_size 0
db_big_table_cache_percent_target 0
db_writer_processes 2
buffer_pool_keep
buffer_pool_recycle
db_flash_cache_file
db_flash_cache_size 0
db_cache_advice OFF
compatible 12.0.0
log_archive_dest_1
log_archive_dest_2
log_archive_dest_3
log_archive_dest_4
log_archive_dest_5
log_archive_dest_6
log_archive_dest_7
log_archive_dest_8
log_archive_dest_9
log_archive_dest_10
log_archive_dest_11
log_archive_dest_12
log_archive_dest_13
log_archive_dest_14
log_archive_dest_15
log_archive_dest_16
log_archive_dest_17
log_archive_dest_18
log_archive_dest_19
log_archive_dest_20
log_archive_dest_21
log_archive_dest_22
log_archive_dest_23
log_archive_dest_24
log_archive_dest_25
log_archive_dest_26
log_archive_dest_27
log_archive_dest_28
log_archive_dest_29
log_archive_dest_30
log_archive_dest_31
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_state_6 enable
log_archive_dest_state_7 enable
log_archive_dest_state_8 enable
log_archive_dest_state_9 enable
log_archive_dest_state_10 enable
log_archive_dest_state_11 enable
log_archive_dest_state_12 enable
log_archive_dest_state_13 enable
log_archive_dest_state_14 enable
log_archive_dest_state_15 enable
log_archive_dest_state_16 enable
log_archive_dest_state_17 enable
log_archive_dest_state_18 enable
log_archive_dest_state_19 enable
log_archive_dest_state_20 enable
log_archive_dest_state_21 enable
log_archive_dest_state_22 enable
log_archive_dest_state_23 enable
log_archive_dest_state_24 enable
log_archive_dest_state_25 enable
log_archive_dest_state_26 enable
log_archive_dest_state_27 enable
log_archive_dest_state_28 enable
log_archive_dest_state_29 enable
log_archive_dest_state_30 enable
log_archive_dest_state_31 enable
log_archive_start FALSE
log_archive_dest
log_archive_duplex_dest
log_archive_min_succeed_dest 1
standby_archive_dest ?/dbs/arch
fal_client
fal_server
log_archive_trace 0
log_archive_config
log_archive_format %t_%s_%r.dbf
redo_transport_user
log_archive_max_processes 4
log_buffer 10485760
log_checkpoint_interval 0
log_checkpoint_timeout 0
archive_lag_target 0
_use_single_log_writer TRUE
db_files 1024
db_file_multiblock_read_count 64
read_only_open_delayed FALSE
cluster_database FALSE
parallel_server FALSE
parallel_server_instances 1
cluster_database_instances 1
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
db_recovery_file_dest
db_recovery_file_dest_size 0
standby_file_management MANUAL
db_unrecoverable_scn_tracking TRUE
thread 0
fast_start_io_target 0
fast_start_mttr_target 0
log_checkpoints_to_alert FALSE
db_lost_write_protect NONE
recovery_parallelism 0
enable_goldengate_replication FALSE
db_flashback_retention_target 1440
dml_locks 6756
replication_dependency_tracking TRUE
transactions 1689
transactions_per_rollback_segment 5
rollback_segments
undo_management AUTO
undo_tablespace RBS_UNDO
temp_undo_enabled FALSE
undo_retention 60000
fast_start_parallel_rollback LOW
resumable_timeout 0
instance_number 0
heat_map OFF
inmemory_size 0
db_block_checking FALSE
recyclebin OFF
db_index_compression_inheritance NONE
db_securefile PERMITTED
inmemory_clause_default
inmemory_force DEFAULT
inmemory_query ENABLE
inmemory_max_populate_servers 0
inmemory_trickle_repopulate_servers_percent 1
create_stored_outlines
serial_reuse disable
ldap_directory_access NONE
ldap_directory_sysauth no
os_roles FALSE
rdbms_server_dn
max_enabled_roles 150
remote_os_authent FALSE
remote_os_roles FALSE
sec_case_sensitive_logon FALSE
O7_DICTIONARY_ACCESSIBILITY FALSE
remote_login_passwordfile EXCLUSIVE
license_max_users 0
audit_sys_operations TRUE
DBFIPS_140 FALSE
global_context_pool_size
unified_audit_sga_queue_size 1048576
db_domain
global_names FALSE
distributed_lock_timeout 60
commit_point_strength 1
global_txn_processes 1
instance_name CKBPDDB2
dispatchers
shared_servers 0
max_shared_servers
max_dispatchers
circuits
shared_server_sessions
local_listener
remote_listener
listener_networks
use_dedicated_broker FALSE
connection_brokers ((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1))
cursor_space_for_time FALSE
session_cached_cursors 500
remote_dependencies_mode TIMESTAMP
utl_file_dir *
smtp_out_server
plsql_v2_compatibility FALSE
plsql_warnings DISABLE:ALL
plsql_code_type INTERPRETED
plsql_debug FALSE
plsql_optimize_level 2
plsql_ccflags
plscope_settings IDENTIFIERS:NONE
permit_92_wrap_format TRUE
java_jit_enabled TRUE
java_restrict none
job_queue_processes 1000
parallel_min_percent 0
create_bitmap_area_size 8388608
bitmap_merge_area_size 1048576
cursor_sharing FORCE
result_cache_mode MANUAL
parallel_min_servers 0
parallel_max_servers 480
parallel_instance_group
parallel_execution_message_size 16384
hash_area_size 131072
result_cache_max_size 134217728
result_cache_max_result 5
result_cache_remote_expiration 0
shadow_core_dump partial
background_core_dump partial
background_dump_dest /u01/app/oracle/diag/rdbms/CKBpddb2/CKBPDDB2/trace
user_dump_dest /u01/app/oracle/admin/CKBPDDB2/udump
core_dump_dest /u01/app/oracle/diag/rdbms/CKBpddb2/CKBPDDB2/cdump
use_sigio TRUE
audit_file_dest /u01/app/oracle/admin/CKBPDDB2/adump
audit_syslog_level
object_cache_optimal_size 102400
object_cache_max_size_percent 10
session_max_open_files 10
open_links 4
open_links_per_instance 4
commit_write
commit_wait
commit_logging
optimizer_features_enable 11.2.0.4
fixed_date NONE
audit_trail NONE
sort_area_size 65536
sort_area_retained_size 0
cell_offload_processing TRUE
cell_offload_decryption TRUE
cell_offload_parameters
cell_offload_compaction ADAPTIVE
cell_offload_plan_display AUTO
db_name CKBPDDB2
db_unique_name CKBPDDB2
open_cursors 1000
ifile
sql_trace FALSE
os_authent_prefix ops$
optimizer_mode ALL_ROWS
sql92_security FALSE
blank_trimming FALSE
star_transformation_enabled FALSE
parallel_degree_policy MANUAL
parallel_adaptive_multi_user TRUE
parallel_threads_per_cpu 2
parallel_automatic_tuning FALSE
parallel_io_cap_enabled FALSE
optimizer_index_cost_adj 100
optimizer_index_caching 0
query_rewrite_enabled TRUE
query_rewrite_integrity enforced
pga_aggregate_target 6442450944
workarea_size_policy AUTO
optimizer_dynamic_sampling 2
_optimizer_connect_by_cost_based FALSE
statistics_level TYPICAL
cursor_bind_capture_destination memory+disk
skip_unusable_indexes TRUE
optimizer_secure_view_merging TRUE
ddl_lock_timeout 0
deferred_segment_creation TRUE
optimizer_use_pending_statistics FALSE
optimizer_capture_sql_plan_baselines FALSE
optimizer_use_sql_plan_baselines TRUE
_optimizer_native_full_outer_join force
parallel_min_time_threshold AUTO
parallel_degree_limit CPU
parallel_force_local FALSE
optimizer_use_invisible_indexes FALSE
dst_upgrade_insert_conv TRUE
parallel_servers_target 480
_optimizer_use_feedback FALSE
max_string_size STANDARD
_optimizer_batch_table_access_by_rowid TRUE
optimizer_adaptive_reporting_only FALSE
parallel_degree_level 100
optimizer_adaptive_features TRUE
optimizer_inmemory_aware TRUE
sec_protocol_error_trace_action TRACE
sec_protocol_error_further_action (DROP,3)
sec_max_failed_login_attempts 3
sec_return_server_release_banner FALSE
enable_ddl_logging FALSE
client_result_cache_size 0
client_result_cache_lag 3000
aq_tm_processes 1
hs_autoregister TRUE
xml_db_events enable
dg_broker_start FALSE
dg_broker_config_file1 /u01/app/oracle/product/12.1.0/db_1/dbs/dr1CKBPDDB2.dat
dg_broker_config_file2 /u01/app/oracle/product/12.1.0/db_1/dbs/dr2CKBPDDB2.dat
olap_page_pool_size 0
asm_diskstring
asm_preferred_read_failure_groups
asm_diskgroups
asm_power_limit 1
control_management_pack_access NONE
awr_snapshot_time_offset 0
sqltune_category DEFAULT
spatial_vector_acceleration FALSE
diagnostic_dest /u01/app/oracle
tracefile_identifier
max_dump_file_size unlimited
trace_enabled TRUE
cell_offloadgroup_name
enable_pluggable_database FALSE
pdb_os_credential
pdb_lockdown
pdb_file_name_convert
noncdb_compatible FALSE
common_user_prefix C##
exclude_seed_cdb_view TRUE