Z 사 Case 공유 ….
HW
—————————–
AIX P8 8XX
CPU 11 core (각노드별)
Physical Memory 220G
SGA
—————————–
SGA_TARGET 0
SGA_MAX_SIZE 67584M
DB_CACHE_SIZE 47308M
SHARED_POOL_SIZE 10240M
LARGE_POOL_SIZE 2048M
JAVA_POOL_SIZE 1024M
STREAMS_POOL_SIZE 1024M
SGA_MAX Size 합계 51404M
processes 5000
pga_aggregate_target 45056M
processes 7864M >
KEEP 1024M
—————————-
SGA차이 16179M
Params
—————————–
alter system set “_add_col_optim_enabled” = FALSE scope=spfile;
alter system set “_b_tree_bitmap_plans” = FALSE scope=spfile;
alter system set “_bloom_filter_enabled” = FALSE scope=spfile;
alter system set “_cleanup_rollback_entries” = 500 scope=spfile;
alter system set “_clusterwide_global_transactions” = FALSE scope=spfile;
alter system set “_cursor_features_enabled” = 10 scope=spfile;
alter system set “_db_mttr_advice” = OFF scope=spfile;
alter system set “_enable_NUMA_optimization” = FALSE scope=spfile;
alter system set “_gc_bypass_readers” = FALSE scope=spfile;
alter system set “_gc_integrity_checks” = 0 scope=spfile;
alter system set “_gc_policy_time” = 0 scope=spfile;
alter system set “_gc_read_mostly_locking” = FALSE scope=spfile;
alter system set “_gc_undo_affinity” = FALSE scope=spfile;
alter system set “_in_memory_undo” = FALSE scope=spfile;
alter system set “_library_cache_advice” = FALSE scope=spfile;
alter system set “_memory_imm_mode_without_autosga” = FALSE scope=spfile;
alter system set “_object_statistics” = FALSE scope=spfile;
alter system set “_optim_peek_user_binds” = FALSE scope=spfile;
alter system set “_optimizer_adaptive_cursor_sharing” = FALSE scope=spfile;
alter system set “_optimizer_connect_by_cost_based” = FALSE scope=spfile;
alter system set “_optimizer_order_by_elimination_enabled” = FALSE scope=spfile;
alter system set “_optimizer_push_pred_cost_based” = FALSE scope=spfile;
alter system set “_optimizer_use_feedback” = FALSE scope=spfile;
alter system set “_partition_large_extents” = FALSE scope=spfile;
alter system set “_PX_use_large_pool” = TRUE scope=spfile;
alter system set “_rollback_segment_count” = 1000 scope=spfile;
alter system set “_row_cr” = FALSE scope=spfile;
alter system set “_undo_autotune” = FALSE scope=spfile;
alter system set “_use_adaptive_log_file_sync” = FALSE scope=spfile;
alter system set “audit_trail” = NONE scope=spfile;
alter system set “DB_CACHE_ADVICE” = OFF scope=spfile;
alter system set “DB_FILE_MULTIBLOCK_READ_COUNT” = 32 scope=spfile;
alter system set “db_files” = 2000 scope=spfile;
alter system set “deferred_segment_creation” = FALSE scope=spfile;
alter system set “FAST_START_MTTR_TARGET” = 180 scope=spfile;
alter system set “max_dump_file_size” = ‘1G’ scope=spfile;
alter system set “open_cursors” = 2000 scope=spfile;
alter system set “open_links” = 40 scope=spfile;
alter system set “open_links_per_instance” = 40 scope=spfile;
alter system set “optimizer_dynamic_sampling” = 0 scope=spfile;
alter system set “OPTIMIZER_MODE” = FIRST_ROWS_10 scope=spfile;
alter system set “optimizer_index_cost_adj” = 80 scope=spfile;
alter system set “parallel_force_local” = TRUE scope=spfile;
alter system set “PARALLEL_MAX_SERVERS” = 32 scope=spfile;
alter system set “resource_manager_plan” = FORCE scope=spfile;
alter system set “session_cached_cursors” = 300 scope=spfile;
alter system set “undo_retention” = 3600 scope=spfile;