Oracle 18c 数据库性能调优

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;