Oracle一些优化点笔记

非专业ORACLE，记录一些压测前常用优化点

1 准备数据空间

set pagesize 100
set linesize 300
col TABLESPACE_NAME for a20
col FILE_ID for 999
col FILE_NAME for a100

select tablespace_name, file_id,file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;


alter tablespace users add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/users01.dbf' size 31G;  
alter tablespace users add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/users02.dbf' size 31G;  
alter tablespace users add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/users03.dbf' size 31G;  
alter tablespace users add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/users04.dbf' size 31G;  
alter tablespace users add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/users05.dbf' size 31G;  
alter tablespace users add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/users06.dbf' size 31G;  
alter tablespace users add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/users07.dbf' size 31G;  
alter tablespace users add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/users08.dbf' size 31G;  
alter tablespace users add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/users09.dbf' size 31G;  
alter tablespace UNDOTBS1 add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/undotbs1_01.dbf' size 31G;  
alter tablespace UNDOTBS1 add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/undotbs1_02.dbf' size 31G;  
alter tablespace SYSTEM add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/system_01.dbf' size 31G;  
alter tablespace SYSAUX add datafile '/d01/app/oracle/oradata/ORCL12C/datafile/sysaux_01.dbf' size 31G;  
alter tablespace TEMP add tempfile '/d01/app/oracle/oradata/ORCL12C/datafile/temp_01.dbf' size 31G;  
alter tablespace TEMP add tempfile '/d01/app/oracle/oradata/ORCL12C/datafile/temp_02.dbf' size 31G;  

2 设置ORACLE 批量提交参数

SQL> alter system set commit_write='batch,nowait';  

3 使用强制软解析

alter system set cursor_sharing=force;  

4 使用O_DIRECT

alter system set filesystemio_options=setall scope=spfile;  
alter system reset disk_asynch_io scope=spfile;  

5 修改最大连接数，打开游标数。

alter system set processes=1000 scope=spfile;  
ALTER SYSTEM SET open_cursors=900 SCOPE=BOTH;  
alter system reset session_cached_cursors scope=spfile;  

6 新增redo log file

新增redo log file，默认只有3个50MB的redo log，产生大量log file switch (checkpoint incomplete)和log file switch completion等待事件。

select group#,sequence#,bytes,members,status from v$log;  
  
select member from v$logfile;  

增加

alter database add logfile group 4 ('/d01/app/oracle/oradata/ORCL12C/onlinelog/redo04.log') size 2g;  
alter database add logfile group 5 ('/d01/app/oracle/oradata/ORCL12C/onlinelog/redo05.log') size 2g;  
alter database add logfile group 6 ('/d01/app/oracle/oradata/ORCL12C/onlinelog/redo06.log') size 2g;  
alter database add logfile group 7 ('/d01/app/oracle/oradata/ORCL12C/onlinelog/redo07.log') size 2g;  
alter database add logfile group 8 ('/d01/app/oracle/oradata/ORCL12C/onlinelog/redo08.log') size 2g;  
alter database add logfile group 9 ('/d01/app/oracle/oradata/ORCL12C/onlinelog/redo09.log') size 2g;  
alter database add logfile group 10 ('/d01/app/oracle/oradata/ORCL12C/onlinelog/redo10.log') size 2g;  
  

生效

alter system switch logfile;  
alter system switch logfile;  
alter system switch logfile;  
alter system switch logfile;  
alter system checkpoint;  

select group#,sequence#,bytes,members,status from v$log;  

alter database drop logfile group 1;  
alter database drop logfile group 2;  
alter database drop logfile group 3;  

alter database add logfile group 1 ('/d01/app/oracle/oradata/ORCL12C/onlinelog/redo01.log') size 2g;  
alter database add logfile group 2 ('/d01/app/oracle/oradata/ORCL12C/onlinelog/redo02.log') size 2g;  
alter database add logfile group 3 ('/d01/app/oracle/oradata/ORCL12C/onlinelog/redo03.log') size 2g;  

select group#,sequence#,bytes,members,status from v$log;  

7 SGA调整

共18GB左右，当前内存为64GB

show sga;
Total System Global Area 2.0267E+10 bytes
Fixed Size            3721272 bytes
Variable Size         2617247688 bytes
Database Buffers     1.7583E+10 bytes
Redo Buffers           63385600 bytes

调整到内存的1/2

alter system set sga_max_size=32G scope=spfile;  
alter system set sga_target=32G scope=spfile;  
alter system set filesystemio_options=directio scope=spfile;  
alter system set disk_asynch_io=false scope=spfile; 

8 重启

shutdown

sqlplus /nolog
SQL> conn / as sysdba
SQL> startup