Deploying Thomas Kyte's Runstats Tools

Runstats is a script developed by Thomas Kyte that compares the two different methods of doing the same thing to get a better result.

1. Authorization

Select On v_$statname to Livan; Grant succeeded. SQLSelect on v_$mystat to Livan; Grant succeeded. SQLSelect on v_$timer to Livan; Grant succeeded. SQLSelect on v_$latch to Livan; Grant succeeded.

2. Create a View

Sql> Conn livan/Livanconnected.sql>Create or replace view stats2   as Select 'STAT ...'||a.name Name,b.value3   fromV$statname A, V$mystat b4  wherea.statistic# =b.statistic#5Union All6  Select 'LATCH.'||name,gets7   fromV$latch8Union All9  Select 'STAT ... Elapsed Time', Hsecs fromV$timer; View created.

3. Create a temporary table of statistical results

Global temporary table Run_stats   2  (Runid varchar2 ()  ,3  name varchar2  ,4   int)  5on  commit preserve rows; Table created.

4. Create a Runstats Package

--runstats starts calling Rs_start
--rs_middle Call in the middle
--Call Rs_stop when finished and print the report

--Create Baotou

Sql> Create or Replace package runstats_pkg  2as    3       procedure Rs_start;   4       procedure Rs_middle;   5      inch default 0 );   6   end;   7  /packagecreated.

--Create Package body

[[Email protected] ~]$ VI body_runstats_pkg.sql Create or replace package body runstats_pkg asG_start number;    G_RUN1 number; G_run2 number;procedure Rs_start isbegin Delete fromRun_stats; INSERT INTO Run_statsSelect 'before', stats.* fromstats; G_start:=dbms_utility.get_cpu_time;end;procedure Rs_middle isbegin G_RUN1:= (dbms_utility.get_cpu_time-G_start); INSERT INTO Run_statsSelect 'After 1', stats.* fromstats; G_start:=dbms_utility.get_cpu_time;end;procedure rs_stop (p_difference_thresholdinchNumberdefault 0) isbegin G_RUN2:= (dbms_utility.get_cpu_time-G_start); Dbms_output.put_line ('Run1 ran in'|| g_run1 | |'CPU Hsecs'); Dbms_output.put_line ('Run2 ran in'|| g_run2 | |'CPU Hsecs'); if(G_run2 <>0) then Dbms_output.put_line ('Run 1 ran in'|| Round (g_run1/g_run2* -,2) ||'% of the time'); Endif; Dbms_output.put_line (Chr (9)); INSERT INTO Run_statsSelect 'After 2', stats.* fromstats; Dbms_output.put_line (Rpad ('Name', -)|| Lpad ('Run1', A)|| Lpad ('Run2', A)|| Lpad ('Diff', A));  forXinch    (SelectRpad (A.name, -)||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 fromrun_stats a,run_stats b,run_stats cwhereA.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) >0and 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', A)|| Lpad ('Run2', A)|| Lpad ('Diff', A)|| Lpad ('Pct',Ten));  forXinch    (SelectTo_char (RUN1,'9,999,999')||To_char (run2,'9,999,999')||To_char (diff,'9,999,999')||To_char (Round (run1/decode (Run2,0, To_number (0), run2* -,2)),'999.99')||'%'Data from    (Selectsum (b.value-a.value) run1, sum (c.value-b.value) run2, sum (c.value-b.value)-(b.value-a.value)) diff fromrun_stats a,run_stats b,run_stats cwhereA.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;"Body_runstats_pkg.sql"[New]95L, 2589C writtensql>@body_runstats_pkg. SQL the/Package body created.

5. Quiz

Sql>Execute RUNSTATS_PKG.RS_START;PL/SQL procedure successfully completed. SQL> INSERT INTO T1Select* fromdba_objects;72898rows created. SQL>commit; Commit complete. SQL>Execute RUNSTATS_PKG.RS_MIDDLE;PL/SQL procedure successfully completed. SQL>begin2   forXinch(Select* fromdba_objects)3Loop4INSERT into T2 values x; 5end Loop; 6commit; 7end; 8/PL/SQL procedure successfully completed. SQL> Execute Runstats_pkg.rs_stop (10000000);P L/SQL procedure successfully completed. SQL>Setserveroutput Onsql> Execute Runstats_pkg.rs_stop (10000000); Run1 ran In60cpu hsecsRun2 ran in425cpu hsecsrun1Ran In14. A%Of the timename Run1 Run2 diffstat...redo size8,577,680   in,178,972   -,601,292STAT ... Redo Size8,577,680   in,183, the   -,606, -Run1 latches Total versus runs--difference and pctRun1 Run2 Diff Pct146,010 1,069,172   923,162%PL/sql procedure successfully completed.

Show_space Process Reference Bo Master:

http://blog.csdn.net/huang_xw/article/details/7015349

Deploying Thomas Kyte's Runstats Tools