Runstats 指令碼
轉Thomas Kyte's Runstats
功能對比sql之間的parse,latch等等
[@more@]Requirements
In order to run this test harness you must at a minimum have:
- Access to V$STATNAME, V$MYSTAT, v$TIMER and V$LATCH
- You must be granted select DIRECTLY on SYS.V_$STATNAME, SYS.V_$MYSTAT, SYS.V_$TIMER and SYS.V_$LATCH. It will not work to have select on these via a ROLE. (conn / as sysdba
grant select on SYS.V_$STATNAME to USER...)
- The ability to create a table -- run_stats -- to hold the before, during and after information.
- The ability to create a package -- rs_pkg -- the statistics collection/reporting piece
--The table
create global temporary table run_stats ( runid varchar2(15), name varchar2(80), value int ) on commit preserve rows;-- view:
create or replace view stats as select 'STAT...' || a.name name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# union all select 'LATCH.' || name, gets from v$latch union all select 'STAT...Elapsed Time', hsecs from v$timer;--package :
create or replace package runstats_pkg as procedure rs_start; procedure rs_middle; procedure rs_stop( p_difference_threshold in number default 0 ); end; / create or replace package body runstats_pkg as g_start number; g_run1 number; g_run2 number; procedure rs_start is begin delete from run_stats; insert into run_stats select 'before', stats.* from stats; g_start := dbms_utility.get_time; end; procedure rs_middle is begin g_run1 := (dbms_utility.get_time-g_start); insert into run_stats select 'after 1', stats.* from stats; g_start := dbms_utility.get_time; end; procedure rs_stop(p_difference_threshold in number default 0) is begin g_run2 := (dbms_utility.get_time-g_start); dbms_output.put_line ( 'Run1 ran in ' || g_run1 || ' hsecs' ); dbms_output.put_line ( 'Run2 ran in ' || g_run2 || ' hsecs' ); dbms_output.put_line ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) || '% of the time' ); dbms_output.put_line( chr(9) ); insert into run_stats select 'after 2', stats.* from stats; dbms_output.put_line ( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) ); for x in ( select rpad( a.name, 30 ) || to_char( b.value-a.value, '999,999,999' ) || to_char( c.value-b.value, '999,999,999' ) || to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data from run_stats a, run_stats b, run_stats c where a.name = b.name and b.name = c.name and a.runid = 'before' and b.runid = 'after 1' and c.runid = 'after 2' -- and (c.value-a.value) > 0 and abs( (c.value-b.value) - (b.value-a.value) ) > p_difference_threshold order by abs( (c.value-b.value)-(b.value-a.value)) ) loop dbms_output.put_line( x.data ); end loop; dbms_output.put_line( chr(9) ); dbms_output.put_line ( 'Run1 latches total versus runs -- difference and pct' ); dbms_output.put_line ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) ); for x in ( select to_char( run1, '999,999,999' ) || to_char( run2, '999,999,999' ) || to_char( diff, '999,999,999' ) || to_char( round( run1/run2*100,2 ), '99,999.99' ) || '%' data from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2, sum( (c.value-b.value)-(b.value-a.value)) diff from run_stats a, run_stats b, run_stats c where a.name = b.name and b.name = c.name and a.runid = 'before' and b.runid = 'after 1' and c.runid = 'after 2' and a.name like 'LATCH%' ) ) loop dbms_output.put_line( x.data ); end loop; end; end; / /* exec runStats_pkg.rs_start; exec runStats_pkg.rs_middle; exec runStats_pkg.rs_stop; */
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7557086/viewspace-981277/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- runstats安裝方法
- db2 reorg,runstatsDB2
- iOS逆向 Shell指令碼+指令碼重簽名iOS指令碼
- 常用指令碼學習手冊——Bat指令碼指令碼BAT
- Bash指令碼指令碼
- powershell指令碼指令碼
- jpsall指令碼指令碼
- perl指令碼指令碼
- shell指令碼指令碼
- MySQL指令碼MySql指令碼
- java 指令碼Java指令碼
- JMeter指令碼JMeter指令碼
- hadoop_批量命令指令碼&同步檔案指令碼Hadoop指令碼
- 初學指令碼指令碼
- python 常用指令碼Python指令碼
- Redis - Lua 指令碼Redis指令碼
- Linux指令碼分享Linux指令碼
- shell指令碼案例指令碼
- 19、python 指令碼Python指令碼
- SQLServer 常用指令碼SQLServer指令碼
- 備份指令碼指令碼
- 位元組碼指令
- 常用shell指令碼指令碼
- C# 指令碼C#指令碼
- MySQL 常用指令碼MySql指令碼
- Linux Shell指令碼Linux指令碼
- redolog生成指令碼指令碼
- 自動化指令碼安裝mysql shell指令碼範例指令碼MySql
- appium ios java 指令碼如何用指令執行,例如 adb 那種方式執行指令碼APPiOSJava指令碼
- Locust 程式碼指令碼實現指令碼
- nGrinder中快速編寫groovy指令碼01-指令碼結構指令碼
- ExcelWeb指令碼助手,自定義指令碼,批量操作Excel與網頁ExcelWeb指令碼網頁
- AE指令碼:顏色管理快速配色指令碼ColorBob-Macw指令碼ORBMac
- shell 指令碼加密 | shc指令碼加密
- sqoop指令碼批量生成OOP指令碼
- Elasticsearch 指令碼分組Elasticsearch指令碼
- 執行shell指令碼指令碼
- pyenv 安裝指令碼指令碼
- Mysql replication check指令碼MySql指令碼