afc_db优化

现象：

SC参数入库时，造成SCWS连接数据库查询数据缓慢，同时通过PLSQL登陆明显变慢，参数入库需要20分钟左右

分析：

SQL级分析
表空间大小、表索引、插入时SGA使用率，临时空间占用率
Hint:append 归档情况下，与普通insert产出同样的redo；非归档下，产生redo比普通insert少。
由于系统一直运行，并未在此方面做过更改，所以问题可能不在此方面。
SGA\PGA分析
高速缓冲区命中率，经过查询命中率在90%以上，没有问题。

1 2 3

selectname,valuefromv$sysstat wherenamein(‘consistentgets’,'dbblockgets’,'physicalreads’); select1-(physicalreads/(consistentgets+dbblockgets))fromdual;

如果命中率低于70%，则应该加大INITsid.ORA文件中的DB_BLOCK_BUFFERS(Oracle9i 参数名称为DB_CACHE_SIZE)参数值
磁盘数据写入分析
经检查，系统硬盘单文件写入可达10MB/s，使用率为37%（通过FTP上传100MB以上文件测试，通过iostat -xn 3监控）
但是当通过数据库，调用insert插入10W条以上数据时，磁盘写入大约为1MB/s，使用率100%，磁盘写入率低。
通过上述描述，排除磁盘问题，问题可能出在oracle进程往磁盘写入数据的途中，通过以下脚本测试

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

setserveroutputon; DECLARE latINTEGER; iopsINTEGER; mbpsINTEGER; BEGIN –DBMS_RESOURCE_MANAGER.CALIBRATE_IO(disk_count,max_latency,iops,mbps,lat); DBMS_RESOURCE_MANAGER.CALIBRATE_IO(2,10,iops,mbps,lat); DBMS_OUTPUT.PUT_LINE(‘max_iops=‘||iops); DBMS_OUTPUT.PUT_LINE(‘latency=‘||lat); dbms_output.put_line(‘max_mbps=‘||mbps); end; / max_iops = 22886 latency = 0 max_mbps = 144

如何确保系统使用的是异步I/O呢？进行如下的查询操作就可以了：

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL>COLNAMEFORMATA50 SQL>SELECTNAME,ASYNCH_IOFROMV$DATAFILEF,V$IOSTAT_FILEI WHEREF.FILE#=I.FILE_NO ANDFILETYPE_NAME=’DataFile’;/ NAMEASYNCH_IO ————————————————–——— +DATA/orcl/datafile/system.256.768274859ASYNC_ON +DATA/orcl/datafile/sysaux.257.768274861ASYNC_ON +DATA/orcl/datafile/undotbs1.258.768274861ASYNC_ON +DATA/orcl/datafile/users.259.768274861ASYNC_ON +DATA/orcl/datafile/example.267.768275071ASYNC_ON +DATA/orcl/datafile/undotbs2.268.768275613ASYNC_ON 6rowsselected.

redo log大小对一次大数据量提交的影响

解决方案：

redo log 4组，每组两个文件,文件大小512MB
ps -ef|grep ora_ 查看ora_dbw0的PID
通过TOP命令查看相应PID的CPU使用率
使用IOSTAT工具检查IO状况 iostat -xn 3

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

SQL>shutdownimmediate Databaseclosed. Databasedismounted. ORACLEinstanceshutdown. SQL>startuppfile=’/app/oracle/product/server_ee/11.2.0.3/dbs/initAFC010C1.ora’ ORACLEinstancestarted. TotalSystemGlobalArea2137886720bytes FixedSize2227704bytes VariableSize1627390472bytes DatabaseBuffers503316480bytes RedoBuffers4952064bytes Databasemounted. Databaseopened. SQL>selectstatusfromv$instance; STATUS ———— OPEN SQL>showparametersga NAMETYPEVALUE ——————————————————————– lock_sgabooleanFALSE pre_page_sgabooleanTRUE sga_max_sizebiginteger2G sga_targetbiginteger768M SQL>altersystemsetsga_target=2048M; Systemaltered. SQL>createspfilefrompfile; Filecreated. SQL>

 

增大SGA_MAX_SIZE由768M至2048M，SGA_TARGET由768M至2048M。
磁盘写入增加了大概100KB/s。
设置使用磁盘IO模式，默认oracle IO数据文件存储方式为ASYNCH_IO，修改为以下

1	altersystemsetsystemfileio_options=‘DIRECTIO’scope=spfile;

经测试，SC参数入库由20分钟缩减至2分钟，10倍提升。