Home > Workload Solutions > Oracle > Guides > Design Guide—Modernize Your Oracle Database Server Platform and Accelerate Deployments > Database configuration best practices
This section discusses the following configuration testcases.
The baseline configuration SGA size was set to 128 GB. The AWR report indicated that the DB file sequential read takes used 71.4% DB time. Increasing the SGA size reduces the DB file sequential reads. Based on the AWR recommendation, we set SGA size to 256 GB.
Use the following steps to increase the SGA size to 256 GB. Perform the following SQL statements after connecting to the database instance with the sysdba privilege:
alter system set sga_max_size = 256GB scope=spfile;
alter system set sga_target = 256GB scope=spfile;
After these steps are complete, restart the database instance.
In the baseline performance test, the AWR report showed significant log file sync wait events. To decrease the log file sync waits, we dropped the previous three groups of 200 MB redo logs and re-created five redo log groups, each with 8 GB size.
We re-created five new redo log groups each 8 GB size and dropped the three original smallest size of redo log groups by performing the following SQL commands with system privilege:
ALTER DATABASE ADD LOGFILE GROUP 1('+REDO') SIZE 8G;
ALTER DATABASE ADD LOGFILE GROUP 2('+REDO') SIZE 8G;
ALTER DATABASE ADD LOGFILE GROUP 3('+REDO') SIZE 8G;
ALTER DATABASE ADD LOGFILE GROUP 4('+REDO') SIZE 8G;
ALTER DATABASE ADD LOGFILE GROUP 5('+REDO') SIZE 8G;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 6;
To reduce the log file sync wait events, we allocated higher priority to the log writer process (LGWR) by setting this parameter to ‘’LMS*|VKTM|LGWR’ from the default value (’LMS*|LM1*|LM2*|LM3*|LM4*|LM5*|LM6*|LM7*|LM8*|LM9*). Implement this setting by running the following SQL command as system user:
alter system set _high_priority_processess=’LMS*|VKTM|LGWR’ scope=spfile
Restart the database instance.
Thoroughly test this setting before implementing it to your system. See the Oracle support DOC: Doc ID 1373500.1 for more information.
Use the following command to check the current value of this parameter, run this query as sys or system user:
select x.ksppinm name, x.ksppdesc description, y.ksppstvl value
from sys.x$ksppi x, sys.x$ksppcv y
where 1=1 and x.inst_id = y.inst_id and x.indx = y.indx
and x.ksppinm ='_high_priority_processes’;