针对以下参数进行优化调整
| NAME | ISDEFAULT | NAME | ISDEFAULT |
| audit_trail | FALSE | sga_max_size | FALSE |
| cluster_database | TRUE | sga_target | FALSE |
| compatible | FALSE | undo_retention | FALSE |
| control_file_record_keep_time | FALSE | _b_tree_bitmap_plans | FALSE |
| cpu_count | TRUE | _cleanup_rollback_entries | FALSE |
| db_files | FALSE | _clusterwide_global_transactions | FALSE |
| deferred_segment_creation | FALSE | _datafile_write_errors_crash_instance | FALSE |
| enable_ddl_logging | FALSE | _gc_undo_affinity | FALSE |
| open_cursors | FALSE | _index_partition_large_extents | FALSE |
| optimizer_features_enable | TRUE | _memory_imm_mode_without_autosga | FALSE |
| parallel_force_local | FALSE | _optimizer_adaptive_cursor_sharing | FALSE |
| parallel_max_servers | FALSE | _optimizer_extended_cursor_sharing | FALSE |
| pga_aggregate_target | FALSE | _optimizer_extended_cursor_sharing_rel | FALSE |
| processes | FALSE | _optimizer_null_aware_antijoin | FALSE |
| resource_limit | FALSE | _optimizer_use_feedback | FALSE |
| resource_manager_plan | FALSE | _partition_large_extents | FALSE |
| sec_case_sensitive_logon | FALSE | _undo_autotune | FALSE |
| session_cached_cursors | FALSE | _use_adaptive_log_file_sync | FALSE |
脚本如下：（sga 大小可跟据自身情况修改大小）
- 备份现在有 pfile 文件
- 自动对比参数配置
- 自动判断是否 RAC 环境
- 生成调整命令
#!/bin/bash
#==============================================================#
# File : oracle_check.sh #
# Ctime : 2022-01-20 #
# Mtime : 2025-10-24 #
# Desc : Oracle Database Config for single/standlone/rac #
# Version : 2.3.0 #
# Author : 楚枫默寒 #
# Copyright (C) 2022-2099 #
# Note : Check_Oracle.sh Y 显示说明 #
# Check_Oracle.sh 不显示说明(默认) #
#==============================================================#
if [[ ! $USER == "oracle" ]];then
echo "请使用oracle用户执行"
exit 1
fi
function color_setting(){
RC='\033[31;1m' #红色 error
GC='\033[32;1m' #绿色 success
YC='\033[33;1m' #黄色 warning
BC='\033[34;1m' #蓝色 output
DC='\033[35;1m' #粉色 detail
AC='\033[36;1m' #天蓝 info
EC='\033[0m' #黑白 EC
}
function file_create(){
ALTIME=$(date +%Y%m%d_%H%M%S)
BACKPATH='/home/oracle'
DBNAME=$ORACLE_SID
# 定义默认SQL文件路径
DEFAULT_SQL_FILE="/tmp/oracle_check_default.sql"
DEFAULT_LOG_FILE="/tmp/oracle_check_default.log"
PARAMETER_CONF_FILE='/tmp/oracle_check_para_conf.conf'
ORACLE_CHECK_GE_FILE="/tmp/oracle_check_ge.txt"
ORACLE_CHECK_11_FILE="/tmp/oracle_check_11.txt"
ORACLE_CHECK_RA_FILE="/tmp/oracle_check_ra.txt"
ORACLE_CHECK_AL_FILE="/tmp/oracle_check_al.txt"
os_memory_total=$(awk '/MemTotal/{print $2}' /proc/meminfo) || { echo "Error: Failed to get MemTotal from /proc/meminfo"; exit 1; }
sga_target=$((${os_memory_total} * 8 * 8 / 100 / 1024))
pga_target=$((${os_memory_total} * 8 * 2 / 100 / 1024))
cpu_core=$(egrep 'processor' /proc/cpuinfo |wc -l)
if [[ ${cpu_core} -gt 2 ]];then let "set_cpu=${cpu_core}-2"; else let "set_cpu=${cpu_core}-1"; fi
echo "
[NAME] |[VALUE] |[db_version]
sga_target |${sga_target} |mode_gen
sga_max_size |${sga_target} |mode_gen
pga_aggregate_target |${pga_target} |mode_gen
audit_trail |NONE |mode_gen
_b_tree_bitmap_plans |FALSE |mode_gen
_cleanup_rollback_entries |2000 |mode_gen
control_file_record_keep_time |31 |mode_gen
cpu_count |${set_cpu} |mode_gen
_datafile_write_errors_crash_instance |FALSE |mode_gen
db_files |60000 |mode_gen
deferred_segment_creation |FALSE |mode_gen
event |'28401 trace name context forever,level 1', '10949 trace name context forever,level 1' |mode_gen
job_queue_processes |1000 |mode_gen
open_cursors |3000 |mode_gen
_optimizer_adaptive_cursor_sharing |FALSE |mode_gen
_optimizer_use_feedback |FALSE |mode_gen
parallel_max_servers |64 |mode_gen
processes |3000 |mode_gen
session_cached_cursors |1000 |mode_gen
_undo_autotune |FALSE |mode_gen
undo_retention |10800 |mode_gen
_optimizer_extended_cursor_sharing |NONE |mode_gen
_optimizer_extended_cursor_sharing_rel |NONE |mode_gen
enable_ddl_logging |TRUE |mode_11
_index_partition_large_extents |FALSE |mode_11
_memory_imm_mode_without_autosga |FALSE |mode_11
_optimizer_null_aware_antijoin |FALSE |mode_11
_partition_large_extents |FALSE |mode_11
_PX_use_large_pool |TRUE |mode_11
resource_limit |TRUE |mode_11
resource_manager_plan |'force:' |mode_11
sec_case_sensitive_logon |FALSE |mode_11
_use_adaptive_log_file_sync |FALSE |mode_11
_gc_policy_time |0 |mode_rac
_clusterwide_global_transactions |FALSE |mode_rac
_gc_undo_affinity |FALSE |mode_rac
parallel_force_local |TRUE |mode_rac" |awk -F '|' '{printf "%-40s%-95s%-10s\n",$1,"|"$2,"|"$3}' > ${PARAMETER_CONF_FILE}
echo "
_b_tree_bitmap_plans|说明:对于OLTP系统,Oracle可能会将两个索引上的ACCESS PATH得到的rowid进行bitmap操作再回表,这种操作有时逻辑读很高,对于此类SQL使用复合索引才能从根本上解决问题.
_datafile_write_errors_crash_instance|说明:在PDB由于某些原因丢失数据文件后,允许CDB继续运行.注意:只对PDB的非系统数据文件有效.
_cleanup_rollback_entries|该参数指定回滚时每次回滚的ENTRIES个数,默认为100,设置成2000加快回滚速度.
_gc_policy_time|DRM(Dynamic Resource Mastering)负责将Cache资源Remaster到频繁访问这部分数据的节点上,从而提高RAC的性能.但是DRM在实际使用中存在诸多Bug,频繁的DRM会引发实例长时间Hang住甚至是宕机,建议关闭DRM.
_gc_undo_affinity|建议关闭集群 Undo Affinity,降低集群交互,避免触发相关BUG.
_memory_imm_mode_without_autosga|说明:11.2.0.3开始,即使是手工管理内存方式下,如果某个POOL内存吃紧,Oracle仍然可能会自动调整内存,用这个参数来关闭这种行为.
_optimizer_adaptive_cursor_sharing|隐含参数_optimizer_adaptive_cursor_sharing能控制自适应式游标共享的部分行为,由Oracle自适应的处理绑定变量的窥探,但这可能会触发性能问题.Oracle建议在非技术指导下,将其关闭掉.
_optimizer_adaptive_plans|说明:关闭自适应执行计划.
_optimizer_ads_use_result_cache|说明:12c中关闭result_cache,容易触发latch free等bug.
_optimizer_aggr_groupby_elim|19567916.8,Wrong results when GROUP BY uses nested queries in 12.1.0.2.
_optimizer_cost_based_transformation|说明:关闭COST查询转换.
_optimizer_extended_cursor_sharing|建议禁用自适应游标共享,将隐含参数_optimizer_extended_cursor_sharing设置为NONE.
_optimizer_extended_cursor_sharing_rel|建议禁用自适应游标共享,将隐含参数_optimizer_extended_cursor_sharing_rel设置为NONE.
_optimizer_mjc_enabled|说明:某些场景下,需要MERGEJOINCARTESIAN.
_optimizer_null_aware_antijoin|用于解决在反连接(Anti-Join)时,关联列上存在空值(NULL)或关联列无非空约束的问题.但是该参数不稳定,存在较多的Bug,为避免触发相关Bug,建议关闭.
_optimizer_reduce_groupby_key|说明:'Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause'.
_optimizer_use_feedback|基数反馈(CardinalityFeedback)是Oracle11.2中引入的关于SQL性能优化的新特性,该参数存在不稳定因素,可能会带来执行效率的问题,建议关闭优化器反馈
_partition_large_extents|建议关闭分区使用大的初始化区(Extent).
_PX_use_large_pool|并行执行的从属进程在工作时需要交换数据和信息,默认从SharedPool中分配内存空间.当_PX_use_large_pool=TRUE时并行进程将从LargePool中分配内存,减少对共享池(SharedPool)的争用.
_undo_autotune|隐含参数_undo_autotune负责undo retention(即undo段的保持时间)的自动调整,若由Oracle自动负责undo retention,则Oracle会根据事务量来占用undo表空间,可能会形成undo表空间的争用,建议将其关闭.
_use_adaptive_log_file_sync|Oracle默认启用,可能会导致比较严重的写日志等待(log file sync的平均单次等待时间较高),建议关闭此功能.
_index_partition_large_extents|初始化分区大小为8M,创建具有大量分区的表时,耗时大幅增长,对比关闭该特性的情况会大非常多,建议关闭.
sec_case_sensitive_logon|数据库密码默认区分大小写,建议关闭.
audit_trail|由于审计表(AUD$)存放在SYSTEM表空间,因此为了不影响系统的性能,保护SYSTEM表空间,建议把AUD$移动到其他的表空间上,或者关闭审计.
control_file_record_keep_time|指定控制文件记录的保留时间.
deferred_segment_creation|延迟段创建会导致使用Direct方式的Export出来的DMP文件无法正常导入(文档ID1604983.1),建议关闭延迟段创建的特性.
enable_ddl_logging|说明:在11g里面,打开这个参数可以将ddl语句记录在alert日志中.以便于某些故障的排查.建议在OLTP类系统中使用.
event|说明:这个参数主要设置2个事件:
event|1)10949事件用于关闭11g的自动serial direct path read特性,避免出现过多的直接路径读,消耗过多的IO资源.
event|2)28401事件用于关闭11g数据库中用户持续输入错误密码时的延迟用户验证特性,避免用户持续输入错误密码时产生大量的row cache lock或library cache lock等待,严重时使数据库完全不能登录.
job_queue_processes|说明:默认1000,建议调整为CPU核数.
optimizer_dynamic_sampling|(默认2)说明:动态采样,有些场景下,可根据时间情况调大采样级别,比如调到4.
optimizer_index_cost_adj|(默认100)说明:优化器计算通过索引扫描访问表数据的cost开销某些场景下,值越大优化器越倾向于使用全表扫描.相反,值越小,优化器越倾向于使于索引扫描.可根据实际情况进行调整,比如调小到40.
parallel_force_local|为了降低集群间的数据交互,建议并行进程强制在本地实例分配,以便降低集群间的数据交互.
parallel_max_servers|说明:这个参数默认值与CPU相关,OLTP系统中将这个参数设置小一些,可以避免过多的并行对系统造成冲击.
_clusterwide_global_transactions |通常是因为 OGG 经典抽取模式不支持分布式事务才设置的,建议关闭.">/tmp/oracle_check_explain.txt
# 定义SQL模板内容
sql_template=$(cat <<'EOF'
SET line 900 newpage none heading off feedback off pagesize 0 echo on newp none trimout on trimspool on
col NAME for a50
col VALUE for a120
spool /tmp/oracle_check_default.log
select NAME||'|'||NVL(VALUE,'NULL')||'|'||decode(issys_modifiable,'FALSE','SPFILE','IMMEDIATE','BOTH') scope from v$parameter where NAME in ('cpu_count','audit_trail','event','sga_target','sga_max_size',
'pga_aggregate_target','cluster_database','processes','open_cursors','session_cached_cursors','db_files','_undo_autotune','undo_retention','control_file_record_keep_time',
'_b_tree_bitmap_plans','optimizer_features_enable','deferred_segment_creation','_optimizer_adaptive_cursor_sharing','_optimizer_extended_cursor_sharing','_optimizer_extended_cursor_sharing_rel',
'_optimizer_use_feedback','_cleanup_rollback_entries','_datafile_write_errors_crash_instance','parallel_max_servers','resource_limit','resource_manager_plan','_optimizer_null_aware_antijoin',
'_PX_use_large_pool','_partition_large_extents','_index_partition_large_extents','_use_adaptive_log_file_sync','_memory_imm_mode_without_autosga','enable_ddl_logging',
'sec_case_sensitive_logon','parallel_force_local','_gc_policy_time','_gc_undo_affinity','_clusterwide_global_transactions','job_queue_processes');
EOF
)
# 写入SQL模板到目标文件，并添加基本错误处理
if ! echo "$sql_template" > "$DEFAULT_SQL_FILE"; then
echo "Error: Failed to write to $DEFAULT_SQL_FILE"
exit 1
fi
cat > /tmp/oracle_check_bak.sql <<EOF
create pfile='${BACKPATH}/pfile_${DBNAME}_${ALTIME}.ora' from spfile;
EOF
#echo "成功创建 SQL 文件: $DEFAULT_SQL_FILE"
execute_sql "/tmp/oracle_check_default.sql"
execute_sql "/tmp/oracle_check_bak.sql"
}
function split_Bar() {
echo "#==================================================================================================================================================================================================================================#"
}
function execute_sql() {
local sql_file=$1
if [[ $USER == "root" ]]; then
su - oracle -c "sqlplus -s / as sysdba < $sql_file" || { echo "Error: Failed to execute $sql_file"; exit 1; }
elif [[ $USER == "oracle" ]]; then
sqlplus -s / as sysdba < $sql_file >/dev/null || { echo "Error: Failed to execute $sql_file"; exit 1; }
fi
}
function compare_scripts(){
if [[ -s ${DEFAULT_LOG_FILE} ]];then
db_version=$(grep -w "optimizer_features_enable" ${DEFAULT_LOG_FILE} | awk -F '|' '{print $2}')
raccheck=$(grep -w "cluster_database" ${DEFAULT_LOG_FILE} | awk -F '|' '{print $2}')
local items_list=$(cat ${PARAMETER_CONF_FILE}|egrep -v '\['|awk -F '|' '{print $1}') #参数列表
for param in ${items_list};do
#当前数据库配置
local check_comp=$(grep -w "${param}" ${DEFAULT_LOG_FILE}|awk -F '|' '{print $2}'|tr -d "' ") #提取当前数据库 配置值
local check_scop=$(grep -w "${param}" ${DEFAULT_LOG_FILE} |awk -F '|' '{print $3}') #提取当前数据库 scope值
#修改对比值
local modi_comp=$(grep -w "${param}" ${PARAMETER_CONF_FILE}|awk -F '|' '{print $2}'|tr -d "'| ") #对比值
local modi_conf=$(grep -w "${param}" ${PARAMETER_CONF_FILE}|awk -F '|' '{print $2}'|tr -s ' ') #修改值
local modi_scop=$(grep -w "${param}" ${PARAMETER_CONF_FILE}|awk -F '|' '{print $3}'|tr -d ' ') #数据库模式
if [[ -z ${check_comp} ]];then
check_comp=NoSet;
fi
if [[ -z ${check_scop} ]];then
check_scop=SPFILE;
fi
case ${param} in
sga_target|sga_max_size|pga_aggregate_target)
let check_comp=${check_comp}/1024/1024
modi_conf=${modi_conf}"M" ;;
esac
case ${modi_scop} in
mode_gen)
ofile=${ORACLE_CHECK_GE_FILE};;
mode_11)
ofile=${ORACLE_CHECK_11_FILE};;
mode_rac)
ofile=${ORACLE_CHECK_RA_FILE};;
esac
if [[ ${check_comp} != ${modi_comp} ]] && [[ ${check_comp} -lt ${modi_comp} ]];then
if [[ ${raccheck} = "FALSE" ]];then
echo -e "| ${param} |当前配置为:| ${check_comp} |调整命令:|alter system set \"${param}\"=${modi_conf} scope=${check_scop};" >> ${ofile}
else
echo -e "| ${param} |当前配置为:| ${check_comp} |调整命令:|alter system set \"${param}\"=${modi_conf} sid='*' scope=${check_scop};" >>${ofile}
fi
fi
done
else
echo -e ${RC}"未找到配置文件!"${EC}
exit 1
fi
}
function result_output(){
local T1="# 通 用 参 数 #"
local T2="# 11G 环 境 参 数 #"
local T3="# RAC 环 境 参 数 #"
local T4="# 无参数需调整！ #"
split_Bar
if [[ -s ${ORACLE_CHECK_GE_FILE} ]]; then
echo "${T1}"
split_Bar
default_list=$(awk -F'|' '{printf $2}' ${ORACLE_CHECK_GE_FILE})
for dl in ${default_list};do
case ${dl} in
sga_target)
grep -R "sga_target" ${ORACLE_CHECK_GE_FILE}|awk -F'|' '{printf "%s %-45s %-s '${AC}'%15s '${EC}'%s '${AC}'%-136s '${EC}'%-1s\n","#",$2,"|"$3"|",$4,"|"$5"|",$6,"#"}';;
sga_max_size)
grep -R "sga_max_size" ${ORACLE_CHECK_GE_FILE}|awk -F'|' '{printf "%s %-45s %-s '${AC}'%15s '${EC}'%s '${AC}'%-136s '${EC}'%-1s\n","#",$2,"|"$3"|",$4,"|"$5"|",$6,"#"}';;
pga_aggregate_target)
grep -R "pga_aggregate_target" ${ORACLE_CHECK_GE_FILE}|awk -F'|' '{printf "%s %-45s %-s '${AC}'%15s '${EC}'%s '${AC}'%-136s '${EC}'%-1s\n","#",$2,"|"$3"|",$4,"|"$5"|",$6,"#"}'
split_Bar;;
*)
egrep -R "${dl}" ${ORACLE_CHECK_GE_FILE}|awk -F'|' '{printf "%s %-45s %-s '${AC}'%15s '${EC}'%s '${AC}'%-136s '${EC}'%-1s\n","#",$2,"|"$3"|",$4,"|"$5"|",$6,"#"}';;
esac
done
if [[ ${db_version} =~ ^11 ]] && [[ -s ${ORACLE_CHECK_11_FILE} ]]; then
split_Bar
echo "${T2}"
split_Bar
awk -F'|' '{printf "%s %-45s %-s '${AC}'%15s '${EC}'%s '${AC}'%-136s '${EC}'%-1s\n","#",$2,"|"$3"|",$4,"|"$5"|",$6,"#"}' ${ORACLE_CHECK_11_FILE}
fi
if [[ ${raccheck} = "TRUE" ]] && [[ -s ${ORACLE_CHECK_RA_FILE} ]]; then
split_Bar
echo "${T3}"
split_Bar
awk -F'|' '{printf "%s %-45s %-s '${AC}'%15s '${EC}'%s '${AC}'%-136s '${EC}'%-1s\n","#",$2,"|"$3"|",$4,"|"$5"|",$6,"#"}' ${ORACLE_CHECK_RA_FILE}
fi
else
echo "${T4}"
fi
split_Bar
}
function print_instructions(){
cat ${ORACLE_CHECK_GE_FILE} ${ORACLE_CHECK_11_FILE} ${ORACLE_CHECK_RA_FILE} > ${ORACLE_CHECK_AL_FILE}
work=$(awk '{print $2}' "${ORACLE_CHECK_AL_FILE}")
for n in $work; do
grep -wR "$n" /tmp/oracle_check_explain.txt | awk -F '[|]' '{printf "%-40s%-100s\n",$1,$2}'
done
}
#==========================#
# 清理输出 #
#==========================#
function main(){
color_setting
file_create
compare_scripts
clear
result_output
if [[ ${1} =~ ^(Y|y)$ ]]; then
print_instructions
fi
rm -f /tmp/oracle_check_*
rm -f $0
}
main "$@"
最后修改时间：2025-10-24 15:32:46
文章转载自楚枫默寒，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
