In this entry I have included two quick demos on how to install and test RUNSTATS package in Oracle. My working environment is Oracle 10g release 2 on IBM AIX, my oracle client is SQL*Plus: Release 10.2.0.1.0 on Windows XP Proffessional Console.
Before we start to install the package, we need to make sure the user have access to V$STATNAME, V$MYSTAT, and V$LATCH. You must be granted direct SELECT privileges (not via a role) on SYS.V_$STATNAME, SYS.V_$MYSTAT, and SYS.V_$LATCH.
drop user demo cascade;
create user demo identified by demo;
grant connect, resource to demo;
grant create view to demo;
grant select on v_$statname to demo;
grant select on v_$mystat to demo;
grant select on v_$latch to demo;
grant select on v_$timer to demo;
conn demo/demo@orcl
What the following script do are:
- Create a view to gather the statistics
- Create a temporary table to store the statistics.
- Runstats package.
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;
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;
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;
/
Compare the performance between the HEAP table and IOT table:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as demo
SQL>
SQL> create table heap as select * from dual;
Table created
SQL> create table iot(dummy primary key)
2 organization index
3 as select * from DUAL;
Table created
SQL> analyze table HEAP compute statistics;
Table analyzed
SQL> analyze table IOT compute statistics;
Table analyzed
SQL> set serveroutput on size 1000000;
SQL> exec RUNSTATS_PKG.rs_start;
PL/SQL procedure successfully completed
SQL> declare
2 x varchar(1);
3 begin
4 for i in 1 .. 10000 loop
5 select dummy into x from heap;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed
SQL> exec RUNSTATS_PKG.rs_middle;
PL/SQL procedure successfully completed
SQL> declare
2 x varchar2(1);
3 begin
4 for i in 1 .. 10000 loop
5 select dummy into x from iot;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed
SQL> exec RUNSTATS_PKG.rs_stop;
Run1 ran in 151 hsecs
Run2 ran in 110 hsecs
run 1 ran in 137.27% of the time
Name Run1 Run2 Diff
LATCH.session timer 1 0 -1
LATCH.session allocation 3 2 -1
LATCH.session idle bit 38 37 -1
LATCH.library cache lock alloc 1 2 1
LATCH.simulator hash latch 2 3 1
LATCH.active checkpoint queue 1 0 -1
STAT...free buffer requested 5 6 1
STAT...calls to kcmgcs 4 5 1
STAT...calls to kcmgas 0 1 1
STAT...redo entries 9 10 1
STAT...redo ordering marks 0 1 1
LATCH.KWQP Prop Status 1 0 -1
LATCH.KMG MMAN ready and start 1 0 -1
LATCH.job_queue_processes para 1 0 -1
LATCH.OS process allocation 0 1 1
LATCH.channel operations paren 6 8 2
STAT...bytes received via SQL* 1,021 1,019 -2
STAT...session cursor cache hi 5 7 2
LATCH.bufq statistics 2 0 -2
LATCH.Memory Queue 2 0 -2
LATCH.kks stats 6 4 -2
LATCH.In memory undo latch 2 0 -2
LATCH.undo global data 0 2 2
LATCH.redo allocation 2 0 -2
LATCH.redo writing 1 3 2
LATCH.object queue header oper 9 7 -2
LATCH.simulator lru latch 1 3 2
STAT...db block changes 47 43 -4
STAT...enqueue releases 7 3 -4
STAT...enqueue requests 8 3 -5
STAT...consistent changes 37 32 -5
LATCH.kwqbsn:qsga 6 0 -6
LATCH.qmn task queue latch 6 0 -6
LATCH.active service list 7 0 -7
LATCH.library cache pin 20,116 20,109 -7
LATCH.Shared B-Tree 8 0 -8
LATCH.shared pool 10,081 10,073 -8
LATCH.messages 14 6 -8
STAT...db block gets from cach 39 30 -9
STAT...db block gets 39 30 -9
STAT...workarea memory allocat -14 -4 10
STAT...recursive cpu usage 58 46 -12
LATCH.row cache objects 21 33 12
LATCH.library cache lock 96 81 -15
LATCH.library cache 20,227 20,212 -15
STAT...CPU used when call star 64 48 -16
STAT...CPU used by this sessio 64 48 -16
STAT...undo change vector size 2,140 2,160 20
LATCH.checkpoint queue latch 9 32 23
LATCH.JS queue state obj latch 30 0 -30
STAT...DB time 156 116 -40
STAT...Elapsed Time 162 120 -42
STAT...redo size 2,892 2,940 48
LATCH.SQL memory manager worka 6 73 67
LATCH.enqueues 80 9 -71
LATCH.enqueue hash chains 79 8 -71
STAT...index scans kdiixs1 0 10,000 10,000
STAT...table scan rows gotten 10,000 0 -10,000
STAT...table scans (short tabl 10,000 0 -10,000
STAT...no work - consistent re 10,001 1 -10,000
STAT...shared hash latch upgra 0 10,000 10,000
STAT...table scan blocks gotte 10,000 0 -10,000
STAT...consistent gets from ca 30,009 10,010 -19,999
STAT...consistent gets 30,009 10,010 -19,999
STAT...calls to get snapshot s 30,005 10,004 -20,001
STAT...session logical reads 30,048 10,040 -20,008
LATCH.cache buffers chains 60,183 20,137 -40,046
STAT...session uga memory max 130,816 65,408 -65,408
STAT...session pga memory max 131,072 0 -131,072
STAT...session pga memory -196,608 0 196,608
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
111,061 70,857 -40,204 156.74%
PL/SQL procedure successfully completed
SQL>
From the test result we can easily find out the IOT performs much less latching and runs a lot faster than the heap.
RUNSTATS is a very handy tool when we need to compare a number of different solutions. I recommend every Oracle DBA or developer have it installed in your environment.
No comments:
Post a Comment