|
|
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.
|
|
