现象:
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倍提升。