connect / as sysdba
create view dual2
as select dummy from x$dual
where instance = USERENV('INSTANCE');
grant select on dual2 to public;
create public synonym dual2 for dual2;
connect scott/tiger;
create table temp
( sqlno number(3),
b_a varchar2(2),
stat number,
value number
);
SQL l
1
declare
2
v_dummy date;
3
v_start number;
4
begin
5
insert into temp select 1,'b',statistic#,value from v$mystat;
6
v_start := dbms_utility.get_time;
7
for i in 1..2000 loop
8
select sysdate into v_dummy from dual;
9
end loop;
10
dbms_output.put_line('1st stat used: '||(dbms_utility.get_time - v_start)||' seconds');
11
12
insert into temp select 1,'ab',statistic#,value from v$mystat;
13
v_start := dbms_utility.get_time;
14
for i in 1..2000 loop
15
select sysdate into v_dummy from dual2;
16
end loop;
17
dbms_output.put_line('2st stat used: '||(dbms_utility.get_time - v_start)||' seconds');
18
insert into temp select 2,'a',statistic#,value from v$mystat;
19
end;
20*
SQL /
1st stat used: 57 seconds
2st stat used: 45 seconds
PL/SQL procedure successfully completed.
SQL l
1
select (select name from v$statname where statistic# = t2.stat) name, t1.value value1, t2.value value2
2
from
3
(
4
select 1,b.stat,b.value - a.value value
5
from
6
(select * from temp
7
where sqlno=1
8
and b_a = 'b') a,
9
(select * from temp
10
where sqlno=1
11
and b_a = 'ab')
b
12
where b.stat = a.stat
13
) t1,
14
(select 2,t3.stat,t4.value - t3.value value
15
from
16
(select * from temp
17
where sqlno=1
18
and b_a = 'ab') t3,
19
(select * from temp
20
where sqlno=2
21
and b_a = 'a')
t4
22
where t4.stat = t3.stat
23
) t2
24
where t1.stat = t2.stat
25* and t1.value <t2.value
SQL /
NAME
VALUE1
VALUE2
---------------------------------------- ---------- ----------
session logical reads
10016
20
enqueue releases
1
2
db block gets
8011
15
consistent gets
2005
5
db block changes
8
18
free buffer requested
1
2
calls to kcmgcs
0
2
calls to get snapshot scn: kcmgss
2004
2002
redo entries
5
11
redo size
5124
5928
no work - consistent read gets
2002
2
table scans (short tables)
2000
0
table scan rows gotten
2000
0
table scan blocks gotten
2000
0
buffer is not pinned count
2004
4
15 rows selected.
