NC Cloud 适配不同规格的oracle 数据库 参数调优指南 oracle优化

1、16G的数据库
alter system set processes = 455 scope=spfile;
alter system set workarea_size_policy = AUTO scope=spfile;
alter system set sga_max_size ='9318M' scope=spfile;
alter system set large_pool_size = 614400 scope=spfile;
alter system set java_pool_size = 10971520 scope=spfile;
alter system set open_cursors = 2000 scope=spfile;
alter system set sga_target = 0 scope=spfile;
alter system set db_cache_size ='6522M' scope=spfile;
alter system set log_buffer = 20971520 scope=spfile;
alter system set lock_sga = FALSE scope=spfile;
alter system set optimizer_index_cost_adj = 40 scope=spfile;
alter system set optimizer_dynamic_sampling = 4 scope=spfile;
alter system set shared_pool_size ='1397M' scope=spfile;
alter system set pga_aggregate_target ='1996M' scope=spfile;
alter system set "_b_tree_bitmap_plans" = FALSE scope=spfile;
alter system set "_optimizer_skip_scan_enabled" = FALSE scope=spfile;
alter system set "_optimizer_cartesian_enabled" = FALSE scope=spfile;
alter system set use_indirect_data_buffers = FALSE scope=spfile;
alter system set event = ‘10949 trace name context forever, level 1' scope=spfile;
alter system set audit_trail = NONE scope=spfile;
alter system set deferred_segment_creation = FALSE scope=spfile;
alter system set "_optimizer_group_by_placement" = FALSE scope=spfile;
DBMS_AUTO_TASK_ADMIN.disable(client_name => ‘sql tuning advisor',operation => NULL,window_name => NULL);END;
DBMS_AUTO_TASK_ADMIN.disable( client_name => ‘auto space advisor',operation => NULL, window_name => NULL);
BEGIN dbms_auto_task_admin.disable(client_name => ‘auto optimizer stats collection',operation => NULL,window_name => NULL);end;
alter system set "_sql_plan_directive_mgmt_control"=0 scope=spfile;
alter system set "_optimizer_dsdir_usage_control"=0 scope=spfile;
alter system set optimizer_dynamic_sampling=2 scope=spfile;
alter system set TEMP_UNDO_ENABLED=true scope=spfile;

2、32G的数据库
alter system set processes = 945 scope=spfile;
alter system set workarea_size_policy = AUTO scope=spfile;
alter system set sga_max_size ='19353M' scope=spfile;
alter system set large_pool_size = 614400 scope=spfile;
alter system set java_pool_size = 10971520 scope=spfile;
alter system set open_cursors = 2000 scope=spfile;
alter system set sga_target = 0 scope=spfile;
alter system set db_cache_size ='14450M' scope=spfile;
alter system set log_buffer = 20971520 scope=spfile;
alter system set lock_sga = FALSE scope=spfile;
alter system set optimizer_index_cost_adj = 40 scope=spfile;
alter system set optimizer_dynamic_sampling = 4 scope=spfile;
alter system set shared_pool_size ='2903M' scope=spfile;
alter system set pga_aggregate_target ='4147M' scope=spfile;
alter system set "_b_tree_bitmap_plans" = FALSE scope=spfile;
alter system set "_optimizer_skip_scan_enabled" = FALSE scope=spfile;
alter system set "_optimizer_cartesian_enabled" = FALSE scope=spfile;
alter system set use_indirect_data_buffers = FALSE scope=spfile;
alter system set event = ‘10949 trace name context forever, level 1' scope=spfile;
alter system set audit_trail = NONE scope=spfile;
alter system set deferred_segment_creation = FALSE scope=spfile;
alter system set "_optimizer_group_by_placement" = FALSE scope=spfile;
DBMS_AUTO_TASK_ADMIN.disable(client_name => ‘sql tuning advisor',operation => NULL,window_name => NULL);END;
DBMS_AUTO_TASK_ADMIN.disable( client_name => ‘auto space advisor',operation => NULL, window_name => NULL);
BEGIN dbms_auto_task_admin.disable(client_name => ‘auto optimizer stats collection',operation => NULL,window_name => NULL);end;
alter system set "_sql_plan_directive_mgmt_control"=0 scope=spfile;
alter system set "_optimizer_dsdir_usage_control"=0 scope=spfile;
alter system set optimizer_dynamic_sampling=2 scope=spfile;
alter system set TEMP_UNDO_ENABLED=true scope=spfile;

3、64G的数据库
alter system set processes = 1925 scope=spfile;
alter system set workarea_size_policy = AUTO scope=spfile;
alter system set sga_max_size ='41872M' scope=spfile;
alter system set large_pool_size = 614400 scope=spfile;
alter system set java_pool_size = 10971520 scope=spfile;
alter system set open_cursors = 2000 scope=spfile;
alter system set sga_target = 0 scope=spfile;
alter system set db_cache_size ='33591M' scope=spfile;
alter system set log_buffer = 20971520 scope=spfile;
alter system set lock_sga = FALSE scope=spfile;
alter system set optimizer_index_cost_adj = 40 scope=spfile;
alter system set optimizer_dynamic_sampling = 4 scope=spfile;
alter system set shared_pool_size ='6280M' scope=spfile;
alter system set pga_aggregate_target ='6000M' scope=spfile;
alter system set "_b_tree_bitmap_plans" = FALSE scope=spfile;
alter system set "_optimizer_skip_scan_enabled" = FALSE scope=spfile;
alter system set "_optimizer_cartesian_enabled" = FALSE scope=spfile;
alter system set use_indirect_data_buffers = FALSE scope=spfile;
alter system set event = ‘10949 trace name context forever, level 1' scope=spfile;
alter system set audit_trail = NONE scope=spfile;
alter system set deferred_segment_creation = FALSE scope=spfile;
alter system set "_optimizer_group_by_placement" = FALSE scope=spfile;
DBMS_AUTO_TASK_ADMIN.disable(client_name => ‘sql tuning advisor',operation => NULL,window_name => NULL);END;
DBMS_AUTO_TASK_ADMIN.disable( client_name => ‘auto space advisor',operation => NULL, window_name => NULL);
BEGIN dbms_auto_task_admin.disable(client_name => ‘auto optimizer stats collection',operation => NULL,window_name => NULL);end;
alter system set "_sql_plan_directive_mgmt_control"=0 scope=spfile;
alter system set "_optimizer_dsdir_usage_control"=0 scope=spfile;
alter system set optimizer_dynamic_sampling=2 scope=spfile;
alter system set TEMP_UNDO_ENABLED=true scope=spfile;

4、256G的数据库
alter system set processes=7000 scope=spfile;
alter system set workarea_size_policy = AUTO scope=spfile;
alter system set sga_max_size ='168080M' scope=spfile;
alter system set large_pool_size = 614400 scope=spfile;
alter system set java_pool_size = 10971520 scope=spfile;
alter system set open_cursors = 2000 scope=spfile;
alter system set sga_target = 0 scope=spfile;
alter system set db_cache_size ='140868M' scope=spfile;
alter system set log_buffer = 20971520 scope=spfile;
alter system set lock_sga = FALSE scope=spfile;
alter system set optimizer_index_cost_adj = 40 scope=spfile;
alter system set optimizer_dynamic_sampling = 4 scope=spfile;
alter system set shared_pool_size ='25212M' scope=spfile;
alter system set pga_aggregate_target ='6000M' scope=spfile;
alter system set "_b_tree_bitmap_plans" = FALSE scope=spfile;
alter system set "_optimizer_skip_scan_enabled" = FALSE scope=spfile;
alter system set "_optimizer_cartesian_enabled" = FALSE scope=spfile;
alter system set use_indirect_data_buffers = FALSE scope=spfile;
alter system set event = ‘10949 trace name context forever, level 1' scope=spfile;
alter system set audit_trail = NONE scope=spfile;
alter system set deferred_segment_creation = FALSE scope=spfile;
alter system set "_optimizer_group_by_placement" = FALSE scope=spfile;
DBMS_AUTO_TASK_ADMIN.disable(client_name => ‘sql tuning advisor',operation => NULL,window_name => NULL);END;
DBMS_AUTO_TASK_ADMIN.disable( client_name => ‘auto space advisor',operation => NULL, window_name => NULL);
BEGIN dbms_auto_task_admin.disable(client_name => ‘auto optimizer stats collection',operation => NULL,window_name => NULL);end;
alter system set "_sql_plan_directive_mgmt_control"=0 scope=spfile;
alter system set "_optimizer_dsdir_usage_control"=0 scope=spfile;
alter system set optimizer_dynamic_sampling=2 scope=spfile;
alter system set TEMP_UNDO_ENABLED=true scope=spfile;