AWR/Statspack выложу чуть позже если он там доступен
база на серваке лежит на двух m2 дисках
Samsung 960 PRO 1Tb тут буза и вся статистика и установлен oracle
SSD диск Samsung 970 EVO Plus MZ-V7S500BW тут всякие мат.вюхи для olap отчетов обновление по ночам
объемы обмена незначительные от 4 до 12 MB
по записям самое тяжелое
приход 2 таблицы мастер детаил
масте 30-60 записей
детаил 200-800 записей
расход 2 таблицы мастер детаил
масте 200-4000 записей
детаил 200-10 000 записей
90% полей number 10% varchar2
передача статистик за день 0,5-2 минуты
передача статистик за месяц 7-8 минут
это если 1 поток
с интернетом проблемы бывают но единичные
возможно нужно поменять какой либо параметр но я не знаю какой и вряд ли без подсказок его найду
проводили тест на выходных
1 перегрузили сервак
2 пытались выполнять простые запросы и поиск по станичникам все норм
3 запустили 4 потока повторили пункт 2 все норм
5 запустили 8 потока повторили пункт 2 начались проседания но незначительные
6 запустили 22 потока повторили пункт 2 начались проседания 20 минут сервак тупил потом все нормализовалось
диск уперся во 100% и черед к нему от 8000 до 17000
процессор 15% и выше не поднимался
оперативка 72GB
os Windows server 12
SELECT a.name, a.value FROM v$parameter a
ORDER BY a.name
NAMEVALUEDBFIPS_140FALSEO7_DICTIONARY_ACCESSIBILITYFALSEactive_instance_countallow_global_dblinksFALSEallow_group_access_to_sgaFALSEapprox_for_aggregationFALSEapprox_for_count_distinctFALSEapprox_for_percentilenoneaq_tm_processes1archive_lag_target0asm_diskgroupsasm_diskstringasm_io_processes20asm_power_limit1asm_preferred_read_failure_groupsaudit_file_destE:\APP\ADMIN\XE\ADUMPaudit_sys_operationsTRUEaudit_trailDBautotask_max_active_pdbs2awr_pdb_autoflush_enabledFALSEawr_snapshot_time_offset0background_core_dumppartialbackground_dump_destE:\APP\PRODUCT\12.2.0\DBHOME_1\RDBMS\TRACEbackup_tape_io_slavesFALSEbitmap_merge_area_size1048576blank_trimmingFALSEbuffer_pool_keepbuffer_pool_recyclecdb_clusterFALSEcdb_cluster_namexecell_offload_compactionADAPTIVEcell_offload_decryptionTRUEcell_offload_parameterscell_offload_plan_displayAUTOcell_offload_processingTRUEcell_offloadgroup_namecircuitsclient_result_cache_lag3000client_result_cache_size0clonedbFALSEclonedb_dircluster_databaseFALSEcluster_database_instances1cluster_interconnectscommit_loggingcommit_point_strength1commit_waitcommit_writecommon_user_prefixC##compatible12.2.0connection_brokers((TYPE=DEDICATED)(BROKERS=1)) ((TYPE=EMON)(BROKERS=1))containers_parallel_degree65535control_file_record_keep_time7control_filesE:\APP\ORADATA\XE\CONTROL01.CTL E:\APP\ORADATA\XE\CONTROL02.CTLcontrol_management_pack_accessNONEcore_dump_deste:\app\diag\rdbms\xe\xe\cdumpcpu_count32create_bitmap_area_size8388608create_stored_outlinescursor_bind_capture_destinationmemory+diskcursor_invalidationIMMEDIATEcursor_sharingEXACTcursor_space_for_timeFALSEdata_guard_sync_latency0data_transfer_cache_size0db_16k_cache_size0db_2k_cache_size0db_32k_cache_size0db_4k_cache_size0db_8k_cache_size0db_big_table_cache_percent_target0db_block_buffers0db_block_checkingFALSEdb_block_checksumTYPICALdb_block_size8192db_cache_adviceONdb_cache_size0db_create_file_destdb_create_online_log_dest_1db_create_online_log_dest_2db_create_online_log_dest_3db_create_online_log_dest_4db_create_online_log_dest_5db_domaindb_file_multiblock_read_count128db_file_name_convertdb_files200db_flash_cache_filedb_flash_cache_size0db_flashback_retention_target1440db_index_compression_inheritanceNONEdb_keep_cache_size0db_lost_write_protectNONEdb_namexedb_performance_profiledb_recovery_file_destdb_recovery_file_dest_size0db_recycle_cache_size0db_securefilePREFERREDdb_ultra_safeOFFdb_unique_namexedb_unrecoverable_scn_trackingTRUEdb_writer_processes4dbwr_io_slaves0ddl_lock_timeout0default_sharingmetadatadeferred_segment_creationTRUEdg_broker_config_file1E:\APP\PRODUCT\12.2.0\DBHOME_1\DATABASE\DR1XE.DATdg_broker_config_file2E:\APP\PRODUCT\12.2.0\DBHOME_1\DATABASE\DR2XE.DATdg_broker_startFALSEdiagnostic_destE:\APPdisable_pdb_feature0disk_asynch_ioTRUEdispatchers(PROTOCOL=TCP) (SERVICE=xeXDB)distributed_lock_timeout60dml_locks17036dnfs_batch_size4096dst_upgrade_insert_convTRUEenable_automatic_maintenance_pdbTRUEenable_ddl_loggingFALSEenable_dnfs_dispatcherFALSEenable_goldengate_replicationFALSEenable_pluggable_databaseFALSEenabled_PDBs_on_standby*encrypt_new_tablespacesCLOUD_ONLYeventexafusion_enabled1external_keystore_credential_locationfal_clientfal_serverfast_start_io_target0fast_start_mttr_target0fast_start_parallel_rollbackLOWfile_mappingFALSEfileio_network_adaptersfilesystemio_optionsfixed_dategcs_server_processes0global_namesFALSEglobal_txn_processes1hash_area_size1048576heat_mapOFFhi_shared_memory_address0hs_autoregisterTRUEifileinmemory_adg_enabledTRUEinmemory_clause_defaultinmemory_expressions_usageENABLEinmemory_forceDEFAULTinmemory_max_populate_servers0inmemory_queryENABLEinmemory_size0inmemory_trickle_repopulate_servers_percent1inmemory_virtual_columnsMANUALinstance_abort_delay_time0instance_groupsinstance_modeREAD-WRITEinstance_namexeinstance_number0instance_typeRDBMSinstant_restoreFALSEjava_jit_enabledTRUEjava_max_sessionspace_size0java_pool_size0java_restrictnonejava_soft_sessionspace_limit0job_queue_processes4000large_pool_size0ldap_directory_accessNONEldap_directory_sysauthnolicense_max_sessions0license_max_users0license_sessions_warning0listener_networkslocal_listenerLISTENER_XElock_name_spacelock_sgaFALSElog_archive_configlog_archive_destlog_archive_dest_1log_archive_dest_10log_archive_dest_11log_archive_dest_12log_archive_dest_13log_archive_dest_14log_archive_dest_15log_archive_dest_16log_archive_dest_17log_archive_dest_18log_archive_dest_19log_archive_dest_2log_archive_dest_20log_archive_dest_21log_archive_dest_22log_archive_dest_23log_archive_dest_24log_archive_dest_25log_archive_dest_26log_archive_dest_27log_archive_dest_28log_archive_dest_29log_archive_dest_3log_archive_dest_30log_archive_dest_31log_archive_dest_4log_archive_dest_5log_archive_dest_6log_archive_dest_7log_archive_dest_8log_archive_dest_9log_archive_dest_state_1enablelog_archive_dest_state_10enablelog_archive_dest_state_11enablelog_archive_dest_state_12enablelog_archive_dest_state_13enablelog_archive_dest_state_14enablelog_archive_dest_state_15enablelog_archive_dest_state_16enablelog_archive_dest_state_17enablelog_archive_dest_state_18enablelog_archive_dest_state_19enablelog_archive_dest_state_2enablelog_archive_dest_state_20enablelog_archive_dest_state_21enablelog_archive_dest_state_22enablelog_archive_dest_state_23enablelog_archive_dest_state_24enablelog_archive_dest_state_25enablelog_archive_dest_state_26enablelog_archive_dest_state_27enablelog_archive_dest_state_28enablelog_archive_dest_state_29enablelog_archive_dest_state_3enablelog_archive_dest_state_30enablelog_archive_dest_state_31enablelog_archive_dest_state_4enablelog_archive_dest_state_5enablelog_archive_dest_state_6enablelog_archive_dest_state_7enablelog_archive_dest_state_8enablelog_archive_dest_state_9enablelog_archive_duplex_destlog_archive_formatARC%S_%R.%Tlog_archive_max_processes4log_archive_min_succeed_dest1log_archive_startFALSElog_archive_trace0log_buffer231112704log_checkpoint_interval0log_checkpoint_timeout1800log_checkpoints_to_alertFALSElog_file_name_convertlong_module_actionTRUEmax_datapump_jobs_per_pdb100max_dispatchersmax_dump_file_sizeunlimitedmax_idle_time0max_iops0max_mbps0max_pdbs4098max_shared_serversmax_string_sizeSTANDARDmemory_max_target79456894976memory_target79456894976nls_calendarGREGORIANnls_compBINARYnls_currency$nls_date_formatDD-MON-RRnls_date_languageAMERICANnls_dual_currency$nls_iso_currencyAMERICAnls_languageAMERICANnls_length_semanticsBYTEnls_nchar_conv_excpFALSEnls_numeric_characters.nls_sortBINARYnls_territoryAMERICAnls_time_formatHH.MI.SSXFF AMnls_time_tz_formatHH.MI.SSXFF AM TZRnls_timestamp_formatDD-MON-RR HH.MI.SSXFF AMnls_timestamp_tz_formatDD-MON-RR HH.MI.SSXFF AM TZRnoncdb_compatibleFALSEobject_cache_max_size_percent10object_cache_optimal_size10240000ofs_threads4olap_page_pool_size0one_step_plugin_for_pdb_with_tdeFALSEopen_cursors300open_links4open_links_per_instance4optimizer_adaptive_plansTRUEoptimizer_adaptive_reporting_onlyFALSEoptimizer_adaptive_statisticsFALSEoptimizer_capture_sql_plan_baselinesFALSEoptimizer_dynamic_sampling2optimizer_features_enable12.2.0.1optimizer_index_caching0optimizer_index_cost_adj100optimizer_inmemory_awareTRUEoptimizer_modeALL_ROWSoptimizer_secure_view_mergingTRUEoptimizer_use_invisible_indexesFALSEoptimizer_use_pending_statisticsFALSEoptimizer_use_sql_plan_baselinesTRUEos_authent_prefixOPS$os_rolesFALSEoutbound_dblink_protocolsALLparallel_adaptive_multi_userFALSEparallel_degree_limitCPUparallel_degree_policyMANUALparallel_execution_message_size16384parallel_force_localFALSEparallel_instance_groupparallel_max_servers0parallel_min_percent0parallel_min_servers0parallel_min_time_thresholdAUTOparallel_servers_target512parallel_threads_per_cpu2pdb_file_name_convertpdb_lockdownpdb_os_credentialpermit_92_wrap_formatTRUEpga_aggregate_limit79456894976pga_aggregate_target10297016320plscope_settingsidentifiers:noneplsql_ccflagsplsql_code_typeINTERPRETEDplsql_debugFALSEplsql_optimize_level2plsql_v2_compatibilityFALSEplsql_warningsDISABLE:ALLpre_page_sgaTRUEprocesses2560processor_group_namequery_rewrite_enabledTRUEquery_rewrite_integrityenforcedrdbms_server_dnread_only_open_delayedFALSErecovery_parallelism0recyclebinonredo_transport_userremote_dependencies_modeTIMESTAMPremote_listenerremote_login_passwordfileEXCLUSIVEremote_os_authentFALSEremote_os_rolesFALSEremote_recovery_file_destreplication_dependency_trackingTRUEresource_limitTRUEresource_manage_goldengateFALSEresource_manager_cpu_allocation32resource_manager_planDEFAULT_PLANresult_cache_max_result5result_cache_max_size0result_cache_modeMANUALresult_cache_remote_expiration0resumable_timeout0rollback_segmentssec_case_sensitive_logonTRUEsec_max_failed_login_attempts3sec_protocol_error_further_action(DROP3)sec_protocol_error_trace_actionTRACEsec_return_server_release_bannerFALSEserial_reusedisableservice_namesxesession_cached_cursors50session_max_open_files10sessions3872sga_max_size79456894976sga_min_size0sga_target64424509440shadow_core_dumpnoneshared_memory_address0shared_pool_reserved_size335544320shared_pool_size0shared_server_sessionsshared_servers1shrd_dupl_table_refresh_rate60skip_unusable_indexesTRUEsmtp_out_serversort_area_retained_size0sort_area_size524288spatial_vector_accelerationFALSEspfileE:\APP\PRODUCT\12.2.0\DBHOME_1\DATABASE\SPFILEXE.ORAsql92_securityTRUEsql_traceFALSEsqltune_categoryDEFAULTstandby_archive_dest?#\RDBMSstandby_db_preserve_statesNONEstandby_file_managementMANUALstar_transformation_enabledFALSEstatistics_levelTYPICALstreams_pool_size0tape_asynch_ioTRUEtarget_pdbs0temp_undo_enabledFALSEthread0threaded_executionFALSEtimed_os_statistics0timed_statisticsTRUEtrace_enabledTRUEtracefile_identifiertransactions4259transactions_per_rollback_segment5undo_managementAUTOundo_retention900undo_tablespaceUNDOTBS1unified_audit_sga_queue_size1048576uniform_log_timestamp_formatTRUEuse_dedicated_brokerFALSEuse_large_pagesTRUEuser_dump_destE:\APP\PRODUCT\12.2.0\DBHOME_1\RDBMS\TRACEutl_file_dirworkarea_size_policyAUTOxml_db_eventsenable
план запроса планые примерно все такие и запросы простые full scan может выстрелить на новых объектах где базы еще маленькие
select ih.*
from t_salary_
use@dblink ih
where ih.change_id between :vl_commited_our_change_id and :vl_change_id
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Plan Hash Value :
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE | | 333 | 13653 | 122 | |
| 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T_SALARY_USE | 333 | 13653 | 122 | |
| 3 | INDEX RANGE SCAN | IX_SALARY_USE_CHANGE_ID | 600 | | 3 | |
----------------------------------------------------------------------------------------------
