oracle快速解析,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