谢 谢 收 藏 http://www.qqread.com/oracle/2008/03/w401442.html
进入讨论组讨论。
| set echo off set feedback off set heading off set linesize 40 set pagesize 55 set verify off set termout off column rpt new_value rpt select instance_name||'_'||to_char(sysdate,'YYYYMMDDHH24MISS')||'_vsysstat_ioworkload.LST' rpt from v$instance; set termout on prompt prompt prompt ^^^^^^^^^^^^^ prompt Report Name : ../LST/&&rpt prompt ^^^^^^^^^^^^^ spool ../LST/&&rpt column sr1 new_value sr1 column sw1 new_value sw1 column lr1 new_value lr1 column lw1 new_value lw1 column tbr1 new_value tbr1 column tbw1 new_value tbw1 set termout off SELECT sum(decode(name,'physical read total IO requests',value,0)- decode(name,'physical read total multi block requests',value,0)) sr1, sum(decode(name,'physical write total IO requests',value,0)- decode(name,'physical write total multi block requests',value,0)) sw1, sum(decode(name,'physical read total multi block requests',value,0)) lr1, sum(decode(name,'physical write total multi block requests',value,0)) lw1, sum(decode(name,'physical read total bytes',value,0)) tbr1, sum(decode(name,'physical write total bytes',value,0)) tbw1 FROM v$sysstat; set termout on prompt prompt prompt ^^^^^^^^^^^^ prompt First Sample prompt ^^^^^^^^^^^^ prompt Number of Small Reads : &&sr1 prompt Number of Small Writes: &&sw1 prompt Number of Large Reads : &&lr1 prompt Number of Large Writes: &&lw1 prompt Total Bytes Read : &&tbr1 prompt Total Bytes Written : &&tbw1 prompt prompt prompt Enter the amount of time (in seconds) you would like this process to sleep for sampling data prompt ^^^^^^^^^^^^^^^^^^ prompt Sleep Time (secs): &&sleeptime prompt ^^^^^^^^^^^^^^^^^^ exec DBMS_LOCK.SLEEP (&&sleeptime); column sr2 new_value sr2 column sw2 new_value sw2 column lr2 new_value lr2 column lw2 new_value lw2 column tbr2 new_value tbr2 column tbw2 new_value tbw2 set termout off SELECT sum(decode(name,'physical read total IO requests',value,0)- decode(name,'physical read total multi block requests',value,0)) sr2, sum(decode(name,'physical write total IO requests',value,0)- decode(name,'physical write total multi block requests',value,0)) sw2, sum(decode(name,'physical read total multi block requests',value,0)) lr2, sum(decode(name,'physical write total multi block requests',value,0)) lw2, sum(decode(name,'physical read total bytes',value,0)) tbr2, sum(decode(name,'physical write total bytes',value,0)) tbw2 FROM v$sysstat; set termout on prompt prompt prompt ^^^^^^^^^^^^^ prompt Second Sample prompt ^^^^^^^^^^^^^ prompt Number of Small Reads : &&sr2 prompt Number of Small Writes: &&sw2 prompt Number of Large Reads : &&lr2 prompt Number of Large Writes: &&lw2 prompt Total Bytes Read : &&tbr2 prompt Total Bytes Written : &&tbw2 prompt prompt prompt ^^^^^^^^^ prompt Results : prompt ^^^^^^^^^ column sri new_value sri column swi new_value swi column tsi new_value tsi column srp new_value srp column swp new_value swp column lri new_value lri column lwi new_value lwi column tli new_value tli column lrp new_value lrp column lwp new_value lwp column tr new_value tr column tw new_value tw column tm new_value tm SELECT ROUND((&&sr2-&&sr1)/&&sleeptime,3) sri, ROUND((&&sw2-&&sw1)/&&sleeptime,3) swi, ROUND(((&&sr2-&&sr1)+(&&sw2-&&sw1))/&&sleeptime,3) tsi, ROUND((&&sr2-&&sr1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3) srp, ROUND((&&sw2-&&sw1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3) swp, ROUND((&&lr2-&&lr1)/&&sleeptime,3) lri, ROUND((&&lw2-&&lw1)/&&sleeptime,3) lwi, ROUND(((&&lr2-&&lr1)+(&&lw2-&&lw1))/&&sleeptime,3) tli, ROUND((&&lr2-&&lr1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3) lrp, ROUND((&&lw2-&&lw1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3) lwp, ROUND(((&&tbr2-&&tbr1)/&&sleeptime)/1048576,3) tr, ROUND(((&&tbw2-&&tbw1)/&&sleeptime)/1048576,3) tw, ROUND((((&&tbr2-&&tbr1)+(&&tbw2-&&tbw1))/&&sleeptime)/1048576,3) tm FROM dual; SELECT 'Small Read IOPS = '||ROUND((&&sr2-&&sr1)/&&sleeptime,3)||' IOPS', 'Small Write IOPS = '||ROUND((&&sw2-&&sw1)/&&sleeptime,3)||' IOPS', 'Total Small IOPS = '||ROUND(((&&sr2-&&sr1)+(&&sw2-&&sw1))/&&sleeptime,3)||' IOPS', 'Small Read I/O % = '||ROUND((&&sr2-&&sr1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3)||' %', 'Small Write I/O % = '||ROUND((&&sw2-&&sw1)/DECODE(((&&sr2-&&sr1)+(&&sw2-&&sw1)),0,1,((&&sr2-&&sr1)+(&&sw2-&&sw1)))*100,3)||' %', 'Large Read IOPS = '||ROUND((&&lr2-&&lr1)/&&sleeptime,3)||' IOPS', 'Large Write IOPS = '||ROUND((&&lw2-&&lw1)/&&sleeptime,3)||' IOPS', 'Total Large IOPS = '||ROUND(((&&lr2-&&lr1)+(&&lw2-&&lw1))/&&sleeptime,3)||' IOPS', 'Large Read I/O % = '||ROUND((&&lr2-&&lr1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3)||' %', 'Large Write I/O % = '||ROUND((&&lw2-&&lw1)/DECODE(((&&lr2-&&lr1)+(&&lw2-&&lw1)),0,1,((&&lr2-&&lr1)+(&&lw2-&&lw1)))*100,3)||' %', 'Total Read = '||ROUND(((&&tbr2-&&tbr1)/&&sleeptime)/1048576,3)||' MBPS', 'Total Written = '||ROUND(((&&tbw2-&&tbw1)/&&sleeptime)/1048576,3)||' MBPS', 'Total MBPS = '||ROUND((((&&tbr2-&&tbr1)+(&&tbw2-&&tbw1))/&&sleeptime)/1048576,3)||' MBPS' FROM dual ; prompt Small Read IOPS = &&sri IOPS prompt Small Write IOPS = &&swi IOPS prompt Total Small IOPS = &&tsi IOPS prompt Small Read I/O % = &&srp % prompt Small Write I/O % = &&swp % prompt Large Read IOPS = &&lri IOPS prompt Large Write IOPS = &&lwi IOPS prompt Total Large IOPS = &&tli IOPS prompt Large Read I/O % = &&lrp % prompt Large Write I/O % = &&lwp % prompt Total Read = &&tr MBPS prompt Total Written = &&tw MBPS prompt Total MBPS = &&tm MBPS spool off undefine sleeptime |
相关图文阅读
频道图文推荐
健 康 咨 询
时 尚 咨 询
相关专题
- Oracle语句优化规则汇总(8) (17次浏览)
- Data Guard学习物理standby高级管理 (17次浏览)
- 基于SQL几个常用的几个系统表 (15次浏览)
- 甲骨文如何解析语义谜题 (13次浏览)
- 一步一步配置Oracle Stream (11次浏览)
- 用“kill”命令终止“Oracle”的过程 (9次浏览)
- 如何查找运行系统里的“bad sql”语句 (9次浏览)
- 讲解“Oracle”下导出某用户所有表的方法 (8次浏览)
- Oracle语句优化规则汇总(5) (8次浏览)
- Oracle更新操作优化 (8次浏览)
