参数配置
alter system set "_bloom_filter_enabled"=FALSE scope=spfile;
alter system set "_bloom_pruning_enabled"=FALSE scope=spfile;
alter system set "_cleanup_rollback_entries"=20000 scope=spfile;
alter system set "_clusterwide_global_transactions"=FALSE scope=spfile;
alter system set "_connect_by_use_union_all"=old_plan_mode scope=spfile;
alter system set "_datafile_write_errors_crash_instance"=false scope=spfile;
alter system set "_db_block_numa"=1 scope=spfile;
alter system set "_enable_NUMA_support"=FALSE scope=spfile;
alter system set "_fix_control"='14142884:ON','8560951:ON','8893626:OFF','9344709:OFF','9195582:OFF','9380298:ON','13704562:OFF','16053273:OFF','8611462:OFF','17760375:OFF','17938754:OFF' scope=spfile;
alter system set "_gc_policy_time"=0 scope=spfile;
alter system set "_gc_undo_affinity"=FALSE scope=spfile;
alter system set "_memory_imm_mode_without_autosga"=false scope=spfile;
alter system set "_nlj_batching_enabled"=0 scope=spfile;
alter system set "_nlj_batching_misses_enabled"=0 scope=spfile;
alter system set "_optim_peek_user_binds"=false scope=spfile;
alter system set "_optimizer_mjc_enabled"=false scope=spfile;
alter system set "_optimizer_use_feedback"=FALSE scope=spfile;
alter system set "_optimizer_extended_cursor_sharing"=NONE scope=spfile;
alter system set "_optimizer_extended_cursor_sharing_rel"=NONE scope=spfile;
alter system set "_optimizer_adaptive_cursor_sharing"=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 "_resource_manager_always_on"=FALSE scope=spfile;
alter system set "_serial_direct_read"=NEVER scope=spfile;
alter system set "_smu_debug_mode"=134217728 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 deferred_segment_creation=false scope=spfile;
alter system set dispatchers='' scope=spfile;
alter system set distributed_lock_timeout=600 scope=spfile;
alter system set enable_goldengate_replication=TRUE scope=spfile;
alter system set event="10949 trace name context forever:28401 trace name context forever, level 1:44951 trace name context forever, level 32" scope=spfile;
alter system set optimizer_index_cost_adj=80 scope=spfile;
alter system set parallel_force_local=TRUE scope=spfile;
alter system set resource_limit=TRUE scope=spfile;
alter system set memory_target=0 scope=spfile;
alter system set sga_target=0 scope=spfile;
alter system set control_file_record_keep_time=30 scope=spfile;
alter system set db_file_multiblock_read_count=16 scope=spfile;
alter system set "_ktb_debug_flags"=8 scope=spfile;
alter system set "_use_single_log_writer"=TRUE scope=spfile;
alter system set "_optimizer_partial_join_eval"=FALSE scope=spfile;
alter system set "_optimizer_aggr_groupby_elim"=FALSE scope=spfile;
alter system set "_optimizer_reduce_groupby_key"=FALSE scope=spfile;
alter system set "cell_offload_processing"=FALSE scope=spfile;
alter system set "_optimizer_dsdir_usage_control"=0 scope=spfile;
alter system set "_sql_plan_directive_mgmt_control"=0 scope=spfile;
alter system set "_rowsets_enabled"=FALSE scope=spfile;
alter system set "_use_adaptive_log_file_sync"=FALSE scope=spfile;
--close inmemory
alter system set inmemory_size=0 scope=spfile;
alter system set inmemory_query=disable scope=spfile;
----根据具体情况设置
alter system set db_writer_processes=10 scope=spfile;
alter system set parallel_max_servers=60 scope=spfile;
alter system set job_queue_processes=10 scope=spfile;
alter system set open_links_per_instance=256 scope=spfile;
alter system set db_files=5000 scope=spfile;
alter system set session_max_open_files=500 scope=spfile;
alter system set open_cursors=1000 scope=spfile;
alter system set session_cached_cursors=100 scope=spfile;
alter system set processes=4000 scope=spfile;
--_ksmg_granule_size 2015年9月改大 便于突破连接数瓶颈，根据实际情况修改，受限于SGA大小，和processes大小有关
alter system set "_ksmg_granule_size"=33554432 scope=spfile;
--以下根据具体情况设置
alter system set sga_max_size=40G scope=spfile;
alter system set db_cache_size=25G scope=spfile;
alter system set shared_pool_size=8G scope=spfile;
alter system set java_pool_size=1G scope=spfile;
alter system set large_pool_size=1G scope=spfile;
alter system set pga_aggregate_target=10G scope=spfile;
默认的ASM实例参数，不足以支撑生产，务必进行以下参数的调整。
按如下指令完成两套新计费库的ASM实例参数调整：
alter system set memory_max_target=2000M scope=spfile;
alter system set memory_target=2000M scope=spfile;
alter system set large_pool_size=32M scope=spfile;
alter system set pga_aggregate_target=516M scope=spfile;
alter system set sga_max_size=1500M scope=spfile;
alter system set sga_target=1500M scope=spfile;
alter system set shared_pool_reserved_size=120M scope=spfile;
alter system set shared_pool_size=750M scope=spfile;
alter system set processes=2000 scope=spfile;
alter system set sessions=3000 scope=spfile;
alter system set "_asm_hbeatiowait"=120 scope=spfile;
--其它需要关闭或调整的
自动作业：auto space advisor、sql tuning advisor 、ORACLE_OCM下的两个job
调整awr保留时间
关闭文件自动扩展
auto optimizer stats collection自动统计信息收集时间调整
自动分段顾问：标识出应该被重组的段以节约空间，任务名是“auto space advisor”
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/
------关闭sql tunning
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
--查看auto space advisor及sql tunning是否关闭
select client_name,status from DBA_AUTOTASK_CLIENT
SQL> exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB')
PL/SQL procedure successfully completed.
SQL> exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB')
PL/SQL procedure successfully completed.
SQL> select JOB_NAME, ENABLED, STATE from dba_scheduler_jobs where owner = 'ORACLE_OCM';
JOB_NAME ENABLED STATE
---------------------------------------- ---------- --------------------
MGMT_CONFIG_JOB FALSE DISABLED
MGMT_STATS_CONFIG_JOB FALSE DISABLED
View Merge 是 12C 引入的新特性，也是一种优化手段。当查询中引用了 View 或 inline view 时，
优化器可以将主查询中的查询条件并入视图当中去进行优化选择以获得代价最小的执行计划。而如果视图不属于当前执行语句的用户，
View Merge 就可能存在潜在 OPTIMIZER_SECURE_VIEW_MERGING（默认是 TRUE）控制。当执行语句的用户缺乏对视图的 MERGE VIEW 权限，
也没有 MERGE ANY VIEW 权限时，是否允许优化器进行 View Merge 优化。
grant MERGE ANY VIEW to username;
