This entry has examples about how to gather SQL statement statistics with RUNSTATS. Please first read the entry about the SQL Performance analysis: Oracle performance analysis - Tracing and performance evaluation
Note: I have written the code on a fresh install Oracle 10gR2 XE, so that you can follow step by step and see the errors and solutions. You may get different errors for other versions.
Version: Oracle XE Release 10.2.0.1.0
Server: Suse Linux 9.2 on VMWare Workstation 5.5.3
Client: SQL*Plus: Release 10.2.0.1.0 on Windows XP Proffessional Console
Some parameters about code:
SYSDBA user & password: sys/1234
Test user & password: test/test
Server machine name: linux
SQL> conn sys/1234@linux as sysdba
Connected.
SQL> drop user test cascade;
User dropped.
SQL> create user test identified by test;
User created.
SQL> grant connect, resource to test;
Grant succeeded.
SQL> grant create view to test;
Grant succeeded.
SQL> grant select on v_$statname to test;
Grant succeeded.
SQL> grant select on v_$mystat to test;
Grant succeeded.
SQL> grant select on v_$latch to test;
Grant succeeded.
SQL> grant select on v_$timer to test;
Grant succeeded.
SQL> conn test/test@linux
Connected.
SQL> create global temporary table run_stats
2 ( runid varchar2(15),
3 name varchar2(80),
4 value int )
5 on commit preserve rows;
Table created.
SQL>
SQL> create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch
8 union all
9 select 'STAT...Elapsed Time', hsecs from v$timer;
View created.
SQL>
SQL> create or replace package runstats_pkg
2 as
3 procedure rs_start;
4 procedure rs_middle;
5 procedure rs_stop( p_difference_threshold in number default 0 );
6 end;
7 /
Package created.
SQL>
SQL> create or replace package body runstats_pkg
2 as
3
4 g_start number;
5 g_run1 number;
6 g_run2 number;
7
8 procedure rs_start
9 is
10 begin
11 delete from run_stats;
12
13 insert into run_stats
14 select 'before', stats.* from stats;
15
16 g_start := dbms_utility.get_time;
17 end;
18
19 procedure rs_middle
20 is
21 begin
22 g_run1 := (dbms_utility.get_time-g_start);
23
24 insert into run_stats
25 select 'after 1', stats.* from stats;
26 g_start := dbms_utility.get_time;
27
28 end;
29
30 procedure rs_stop(p_difference_threshold in number default 0)
31 is
32 begin
33 g_run2 := (dbms_utility.get_time-g_start);
34
35 dbms_output.put_line
36 ( 'Run1 ran in ' || g_run1 || ' hsecs' );
37 dbms_output.put_line
38 ( 'Run2 ran in ' || g_run2 || ' hsecs' );
39 dbms_output.put_line
40 ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
41 '% of the time' );
42 dbms_output.put_line( chr(9) );
43
44 insert into run_stats
45 select 'after 2', stats.* from stats;
46
47 dbms_output.put_line
48 ( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||
49 lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );
50
51 for x in
52 ( select rpad( a.name, 30 ) ||
53 to_char( b.value-a.value, '999,999,999' ) ||
54 to_char( c.value-b.value, '999,999,999' ) ||
55 to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data
56 from run_stats a, run_stats b, run_stats c
57 where a.name = b.name
58 and b.name = c.name
59 and a.runid = 'before'
60 and b.runid = 'after 1'
61 and c.runid = 'after 2'
62 -- and (c.value-a.value) > 0
63 and abs( (c.value-b.value) - (b.value-a.value) )
64 > p_difference_threshold
65 order by abs( (c.value-b.value)-(b.value-a.value))
66 ) loop
67 dbms_output.put_line( x.data );
68 end loop;
69
70 dbms_output.put_line( chr(9) );
71 dbms_output.put_line
72 ( 'Run1 latches total versus runs -- difference and pct' );
73 dbms_output.put_line
74 ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||
75 lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );
76
77 for x in
78 ( select to_char( run1, '999,999,999' ) ||
79 to_char( run2, '999,999,999' ) ||
80 to_char( diff, '999,999,999' ) ||
81 to_char( round( run1/run2*100,2 ), '99,999.99' ) || '%' data
82 from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
83 sum( (c.value-b.value)-(b.value-a.value)) diff
84 from run_stats a, run_stats b, run_stats c
85 where a.name = b.name
86 and b.name = c.name
87 and a.runid = 'before'
88 and b.runid = 'after 1'
89 and c.runid = 'after 2'
90 and a.name like 'LATCH%'
91 )
92 ) loop
93 dbms_output.put_line( x.data );
94 end loop;
95 end;
96
97 end;
98 /
Package body created.
SQL> set serveroutput on
SQL> set timing off
SQL> create table t1 as select owner,object_name from all_objects;
Table created.
SQL> create table t2 as select owner,object_name from all_objects;
Table created.
SQL> create index inx_t2 on t2(owner);
Index created.
SQL> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> select * from t1 where owner='PUBLIC';
OWNER OBJECT_NAME
------------------------------ ------------------------------
PUBLIC DUAL
PUBLIC SYSTEM_PRIVILEGE_MAP
PUBLIC TABLE_PRIVILEGE_MAP
PUBLIC STMT_AUDIT_OPTION_MAP
PUBLIC MAP_OBJECT
PUBLIC DBMS_STANDARD
PUBLIC V$MAP_LIBRARY
...
2765 rows selected.
SQL> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> select /*+index(t2 inx_t2) */ * from t2 where owner='PUBLIC';
OWNER OBJECT_NAME
------------------------------ ------------------------------
PUBLIC DUAL
PUBLIC SYSTEM_PRIVILEGE_MAP
PUBLIC TABLE_PRIVILEGE_MAP
PUBLIC STMT_AUDIT_OPTION_MAP
PUBLIC MAP_OBJECT
PUBLIC DBMS_STANDARD
PUBLIC V$MAP_LIBRARY
...
2765 rows selected.
SQL> exec runStats_pkg.rs_stop;
Run1 ran in 304 hsecs
Run2 ran in 331 hsecs
run 1 ran in 91,84% of the time
Name Run1 Run2 Diff
STAT...table scans (short tabl 1 0 -1
LATCH.resmgr:schema config 0 1 1
LATCH.job_queue_processes para 0 1 1
LATCH.undo global data 7 6 -1
LATCH.resmgr:actses active lis 0 1 1
STAT...shared hash latch upgra 0 1 1
STAT...session cursor cache co 1 0 -1
STAT...heap block compress 6 7 1
STAT...index scans kdiixs1 0 1 1
LATCH.In memory undo latch 0 2 2
LATCH.library cache lock 4 6 2
LATCH.redo allocation 13 16 3
STAT...CPU used by this sessio 15 12 -3
STAT...calls to get snapshot s 5 2 -3
STAT...active txn count during 4 8 4
STAT...cleanout - number of kt 4 8 4
STAT...redo entries 9 13 4
STAT...calls to kcmgcs 4 8 4
LATCH.messages 18 22 4
STAT...consistent gets - exami 4 9 5
LATCH.library cache pin 38 44 6
STAT...recursive cpu usage 13 7 -6
LATCH.library cache 48 55 7
LATCH.active service list 2 10 8
STAT...db block gets 17 29 12
STAT...db block gets from cach 17 29 12
STAT...consistent changes 17 29 12
STAT...db block changes 26 42 16
STAT...bytes received via SQL* 3,188 3,209 21
STAT...DB time 36 15 -21
STAT...CPU used when call star 34 12 -22
STAT...Elapsed Time 311 335 24
LATCH.simulator hash latch 11 44 33
LATCH.simulator lru latch 11 44 33
LATCH.JS queue state obj latch 0 36 36
LATCH.enqueues 2 78 76
LATCH.enqueue hash chains 2 79 77
STAT...no work - consistent re 206 397 191
STAT...consistent gets 218 415 197
STAT...consistent gets from ca 218 415 197
STAT...table scan blocks gotte 206 0 -206
STAT...session logical reads 235 444 209
STAT...undo change vector size 2,132 2,420 288
LATCH.cache buffers chains 550 933 383
STAT...buffer is not pinned co 0 576 576
STAT...redo size 2,780 3,400 620
STAT...table fetch by rowid 0 2,765 2,765
STAT...buffer is pinned count 0 5,139 5,139
STAT...table scan rows gotten 51,831 0 -51,831
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,290 1,962 672 65.75%
PL/SQL procedure successfully completed.
Download Code
Summary
The code below compares Using vs. Not using indexes, the case of not using indexes. Test tables t1 and t2 are created from all_objects, then we create an index on t2.
We execute same queries except in the second case, we force CBO(optimizer) to use index on t2 with giving a hint.
We see we are selecting most of the data on tables, t1 with full table scan, t2 with using index (you can see explain plans with autotrace, that was previous topic). By the cost of traversing the index, the second run costs higher.
|
Set as Homepage
Add to Favourites
Recommend
Contact
