共计 3864 个字符,预计需要花费 10 分钟才能阅读完成。
非专业 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
正文完