Oracle 18c性能调优
调优修改oracle参数配置：
需求，需要将一下参数设置为：
HUGE_PAGE 78874
SGA 165893111808
PGA 55297703936
REDO LOG 2048M
LOG_BUFFER 18M
OPTIMIZER_MODE ALL_ROWS
UNDO_RETENTION 900
hugepages修改
vi /etc/sysctl.conf
vm.nr_hugepages = 78874
[root@localhost ~]# sysctl -p使其生效
先修改：
alter system set sga_max_size=166000M scope=spfile;
完后，停止：shutdown immediate;启动：startup;
再修改：
SGA 165893111808
alter system set sga_target=165000M;
修改PGA：
alter system set pga_aggregate_target=55297m scope=both;
show parameter pga;
修改REDO LOG
查看当前日志组的状态
select group#,members,bytes/1024/1024,status from v$log;
查看在线日志组的位置
select MEMBER from v$logfile;
新增group 4-6日志组，大小为2048M
alter database add logfile group 4 ('/u01/oracle/oradata/ORCL/onlinelog/redo04.log') size 2048M;
alter database add logfile group 5 ('/u01/oracle/oradata/ORCL/onlinelog/redo05.log') size 2048M;
alter database add logfile group 6 ('/u01/oracle/oradata/ORCL/onlinelog/redo06.log') size 2048M;
alter database add logfile group 7 ('/u01/oracle/oradata/ORCL/onlinelog/redo07.log') size 2048M;
alter database add logfile group 8 ('/u01/oracle/oradata/ORCL/onlinelog/redo08.log') size 2048M;
alter database add logfile group 9 ('/u01/oracle/oradata/ORCL/onlinelog/redo09.log') size 2048M;
alter database add logfile group 10 ('/u01/oracle/oradata/ORCL/onlinelog/redo10.log') size 2048M;
alter database add logfile group 11 ('/u01/oracle/oradata/ORCL/onlinelog/redo11.log') size 2048M;
切换当前日志到新的日志组
alter system switch logfile;
alter system switch logfile;
删除旧的日志组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
查看修改后的日志组的状态信息：
select group#,members,bytes/1024/1024,status from v$log;
修改logbuf
alter system set log_buffer=18M scope=spfile;
完后，停止：shutdown immediate;启动：startup;
修改OPTIMIZER_MODE ALL_ROWS
查看参数类型：
show parameter optimizer_mode;
查看UNDO_RETENTION的值；show parameter undo_retention
扩充redo log；
将日志扩充到4096后；
alter database add logfile group 1 ('/u01/oracle/oradata/ORCL/onlinelog/redo01a.log','/u01/oracle/oradata/ORCL/onlinelog/redo01b.log')SIZE 4096M;
ALTER DATABASE ADD LOGFILE GROUP 2('/u01/oracle/oradata/ORCL/onlinelog/redo02a.log','/u01/oracle/oradata/ORCL/onlinelog/redo02b.log')SIZE 4096M;
ALTER DATABASE ADD LOGFILE GROUP 3('/u01/oracle/oradata/ORCL/onlinelog/redo03a.log','/u01/oracle/oradata/ORCL/onlinelog/redo03b.log')SIZE 4096M;
ALTER DATABASE ADD LOGFILE GROUP 4('/u01/oracle/oradata/ORCL/onlinelog/redo04a.log','/u01/oracle/oradata/ORCL/onlinelog/redo04b.log')SIZE 4096M;
