/*
Runstats
Runstats is a tool to compare two different methods of doing the same thing and show which one is
superior. You supply the two different methods and Runstats does the rest. Runstats simply measures three key things:
• Wall clock or elapsed time: This is useful to know, but not the most important piece of
information.
• System statistics: This shows, side by side, how many times each approach did something
(such as a parse call, for example) and the difference between the two.
• Latching: This is the key output of this report.
*/
--In order to use Runstats, you need to set up access to several V$ views, create a table to hold the statistics, and
--create the Runstats package. You will need access to four V$ tables (those magic, dynamic performance tables):
--V$STATNAME, V$MYSTAT, V$TIMER and V$LATCH. Here is a view I use:
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;
--a small table to collect the statistics:
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;
--The parameter, p_difference_threshold, is used to control the amount of data printed at the end. Runstats
--collects statistics and latching information for each run, and then prints a report of how much of a resource each test
--(each approach) used and the difference between them. You can use this input parameter to see only the statistics
--and latches that had a difference greater than this number. By default, this is zero, and you see all of the outputs.
create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop( p_difference_threshold in number default 0 );
end;
/
--The package begins with some global variables.
--These will be used to record the elapsed times for our runs:
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_cpu_time;
END;
--Next is the RS_MIDDLE routine. This procedure simply records the elapsed time for the first run of our test in
--G_RUN1. Then it inserts the current set of statistics and latches. If we were to subtract these values from the ones we
--saved previously in RS_START, we could discover how many latches the first method used, how many cursors (a
--statistic) it used, and so on.
--Last, it records the start time for our next run:
PROCEDURE rs_middle
IS
BEGIN
g_run1 := (dbms_utility.get_cpu_time-g_start);
INSERT INTO run_stats
SELECT 'after 1', stats.* FROM stats;
g_start := dbms_utility.get_cpu_time;
END;
--The next and final routine in this package is the RS_STOP routine. Its job is to print out the aggregate CPU times for
--each run and then print out the difference between the statistic/latching values for each of the two runs (only printing
--out those that exceed the threshold):
PROCEDURE rs_stop(p_difference_threshold IN NUMBER DEFAULT 0)
IS
BEGIN
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*100,2) || '% of the time' );
END IF;
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', 16 ) || lpad( 'Run2', 16 ) || lpad( 'Diff', 16 ) );
FOR x IN
(SELECT rpad( a.name, 30 )
|| TO_CHAR( b.value -a.value, '999,999,999,999' )
|| TO_CHAR( c.value -b.value, '999,999,999,999' )
|| TO_CHAR( ( (c.value-b.value)-(b.value-a.value)), '999,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 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', 14 ) || lpad( 'Run2', 19 ) || lpad( 'Diff', 18 ) || lpad( 'Pct', 11 ) );
FOR x IN
(SELECT TO_CHAR( run1, '9,999,999,999,999' )
|| TO_CHAR( run2, '9,999,999,999,999' )
|| TO_CHAR( diff, '9,999,999,999,999' )
|| TO_CHAR( ROUND( run1/DECODE( run2, 0, to_number(0), 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;
/
DROP TABLE BIG_TABLE;
1000000
-- Big TABLE
CREATE TABLE big_table AS
SELECT rownum id,
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
OBJECT_ID,
DATA_OBJECT_ID,
OBJECT_TYPE,
CREATED,
LAST_DDL_TIME,
TIMESTAMP,
STATUS,
TEMPORARY,
GENERATED,
SECONDARY,
NAMESPACE,
EDITION_NAME
FROM all_objects
WHERE 1=0
;
ALTER TABLE big_table nologging;
DECLARE
l_cnt NUMBER;
l_rows NUMBER := &numrows;
BEGIN
INSERT /*+ append */
INTO big_table
SELECT rownum id,
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
OBJECT_ID,
DATA_OBJECT_ID,
OBJECT_TYPE,
CREATED,
LAST_DDL_TIME,
TIMESTAMP,
STATUS,
TEMPORARY,
GENERATED,
SECONDARY,
NAMESPACE,
EDITION_NAME
FROM all_objects
WHERE rownum <=
&numrows;
--
l_cnt := sql%rowcount;
COMMIT;
WHILE (l_cnt < l_rows)
LOOP
INSERT /*+ APPEND */
INTO big_table
SELECT rownum+l_cnt,
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
OBJECT_ID,
DATA_OBJECT_ID,
OBJECT_TYPE,
CREATED,
LAST_DDL_TIME,
TIMESTAMP,
STATUS,
TEMPORARY,
GENERATED,
SECONDARY,
NAMESPACE,
EDITION_NAME
FROM big_table a
WHERE rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
COMMIT;
END LOOP;
END;
/
ALTER TABLE big_table ADD CONSTRAINT big_table_pk PRIMARY KEY(id);
EXEC dbms_stats.gather_table_stats( 'EODA', 'BIG_TABLE', estimate_percent=> 1);
TRUNCATE TABLE T1;
TRUNCATE TABLE T2;
CREATE TABLE t1 AS
SELECT * FROM big_table WHERE 1=0;
CREATE TABLE t2 AS
SELECT * FROM big_table WHERE 1=0;
-- rs_start
exec runstats_pkg.rs_start;
INSERT INTO t1
SELECT * FROM big_table WHERE rownum <= 1000000;
COMMIT;
-- rs_middle
EXEC runstats_pkg.rs_middle;
BEGIN
FOR x IN
( SELECT * FROM big_table WHERE rownum <= 1000000
)
LOOP
INSERT INTO t2 VALUES X;
END LOOP;
COMMIT;
END;
/
-- rs_stop
exec runstats_pkg.rs_stop(1000000);
Runstats
Runstats is a tool to compare two different methods of doing the same thing and show which one is
superior. You supply the two different methods and Runstats does the rest. Runstats simply measures three key things:
• Wall clock or elapsed time: This is useful to know, but not the most important piece of
information.
• System statistics: This shows, side by side, how many times each approach did something
(such as a parse call, for example) and the difference between the two.
• Latching: This is the key output of this report.
*/
--In order to use Runstats, you need to set up access to several V$ views, create a table to hold the statistics, and
--create the Runstats package. You will need access to four V$ tables (those magic, dynamic performance tables):
--V$STATNAME, V$MYSTAT, V$TIMER and V$LATCH. Here is a view I use:
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;
--a small table to collect the statistics:
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;
--The parameter, p_difference_threshold, is used to control the amount of data printed at the end. Runstats
--collects statistics and latching information for each run, and then prints a report of how much of a resource each test
--(each approach) used and the difference between them. You can use this input parameter to see only the statistics
--and latches that had a difference greater than this number. By default, this is zero, and you see all of the outputs.
create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop( p_difference_threshold in number default 0 );
end;
/
--The package begins with some global variables.
--These will be used to record the elapsed times for our runs:
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_cpu_time;
END;
--Next is the RS_MIDDLE routine. This procedure simply records the elapsed time for the first run of our test in
--G_RUN1. Then it inserts the current set of statistics and latches. If we were to subtract these values from the ones we
--saved previously in RS_START, we could discover how many latches the first method used, how many cursors (a
--statistic) it used, and so on.
--Last, it records the start time for our next run:
PROCEDURE rs_middle
IS
BEGIN
g_run1 := (dbms_utility.get_cpu_time-g_start);
INSERT INTO run_stats
SELECT 'after 1', stats.* FROM stats;
g_start := dbms_utility.get_cpu_time;
END;
--The next and final routine in this package is the RS_STOP routine. Its job is to print out the aggregate CPU times for
--each run and then print out the difference between the statistic/latching values for each of the two runs (only printing
--out those that exceed the threshold):
PROCEDURE rs_stop(p_difference_threshold IN NUMBER DEFAULT 0)
IS
BEGIN
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*100,2) || '% of the time' );
END IF;
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', 16 ) || lpad( 'Run2', 16 ) || lpad( 'Diff', 16 ) );
FOR x IN
(SELECT rpad( a.name, 30 )
|| TO_CHAR( b.value -a.value, '999,999,999,999' )
|| TO_CHAR( c.value -b.value, '999,999,999,999' )
|| TO_CHAR( ( (c.value-b.value)-(b.value-a.value)), '999,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 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', 14 ) || lpad( 'Run2', 19 ) || lpad( 'Diff', 18 ) || lpad( 'Pct', 11 ) );
FOR x IN
(SELECT TO_CHAR( run1, '9,999,999,999,999' )
|| TO_CHAR( run2, '9,999,999,999,999' )
|| TO_CHAR( diff, '9,999,999,999,999' )
|| TO_CHAR( ROUND( run1/DECODE( run2, 0, to_number(0), 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;
/
DROP TABLE BIG_TABLE;
1000000
-- Big TABLE
CREATE TABLE big_table AS
SELECT rownum id,
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
OBJECT_ID,
DATA_OBJECT_ID,
OBJECT_TYPE,
CREATED,
LAST_DDL_TIME,
TIMESTAMP,
STATUS,
TEMPORARY,
GENERATED,
SECONDARY,
NAMESPACE,
EDITION_NAME
FROM all_objects
WHERE 1=0
;
ALTER TABLE big_table nologging;
DECLARE
l_cnt NUMBER;
l_rows NUMBER := &numrows;
BEGIN
INSERT /*+ append */
INTO big_table
SELECT rownum id,
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
OBJECT_ID,
DATA_OBJECT_ID,
OBJECT_TYPE,
CREATED,
LAST_DDL_TIME,
TIMESTAMP,
STATUS,
TEMPORARY,
GENERATED,
SECONDARY,
NAMESPACE,
EDITION_NAME
FROM all_objects
WHERE rownum <=
&numrows;
--
l_cnt := sql%rowcount;
COMMIT;
WHILE (l_cnt < l_rows)
LOOP
INSERT /*+ APPEND */
INTO big_table
SELECT rownum+l_cnt,
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
OBJECT_ID,
DATA_OBJECT_ID,
OBJECT_TYPE,
CREATED,
LAST_DDL_TIME,
TIMESTAMP,
STATUS,
TEMPORARY,
GENERATED,
SECONDARY,
NAMESPACE,
EDITION_NAME
FROM big_table a
WHERE rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
COMMIT;
END LOOP;
END;
/
ALTER TABLE big_table ADD CONSTRAINT big_table_pk PRIMARY KEY(id);
EXEC dbms_stats.gather_table_stats( 'EODA', 'BIG_TABLE', estimate_percent=> 1);
TRUNCATE TABLE T1;
TRUNCATE TABLE T2;
CREATE TABLE t1 AS
SELECT * FROM big_table WHERE 1=0;
CREATE TABLE t2 AS
SELECT * FROM big_table WHERE 1=0;
-- rs_start
exec runstats_pkg.rs_start;
INSERT INTO t1
SELECT * FROM big_table WHERE rownum <= 1000000;
COMMIT;
-- rs_middle
EXEC runstats_pkg.rs_middle;
BEGIN
FOR x IN
( SELECT * FROM big_table WHERE rownum <= 1000000
)
LOOP
INSERT INTO t2 VALUES X;
END LOOP;
COMMIT;
END;
/
-- rs_stop
exec runstats_pkg.rs_stop(1000000);
댓글
댓글 쓰기