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
1declare
2v_dummy date;
3v_start number;
4begin
5insert into temp select 1,'b',statistic#,value from v$mystat;
6v_start := dbms_utility.get_time;
7for i in 1..2000 loop
8 select sysdate into v_dummy from dual;
9end loop;
10dbms_output.put_line('1st stat used: '(dbms_utility.get_time - v_start)' seconds');
11
12insert into temp select 1,'ab',statistic#,value from v$mystat;
13v_start := dbms_utility.get_time;
14for i in 1..2000 loop
15 select sysdate into v_dummy from dual2;
16end loop;
17dbms_output.put_line('2st stat used: '(dbms_utility.get_time - v_start)' seconds');
18insert into temp select 2,'a',statistic#,value from v$mystat;
19end;
20*
SQL /
1st stat used: 57 seconds
2st stat used: 45 seconds
PL/SQL procedure sUCcessfully completed.
SQL l
1select (select name from v$statname where statistic# = t2.stat) name, t1.value value1, t2.value value2
2from
3(
4select 1,b.stat,b.value - a.value value
5from
6(select * from temp
7where sqlno=1
8and b_a = 'b') a,
9(select * from temp
10where sqlno=1
11and b_a = 'ab')b
12where b.stat = a.stat
13) t1,
14(select 2,t3.stat,t4.value - t3.value value
15from
16(select * from temp
17where sqlno=1
18and b_a = 'ab') t3,
19(select * from temp
20where sqlno=2
21and b_a = 'a')t4
22where t4.stat = t3.stat
23) t2
24where t1.stat = t2.stat
25* and t1.value <t2.value
SQL /
NAME VALUE1 VALUE2
---------------------------------------- ---------- ----------
session logical reads 10016 20
enqueue releases12
db block gets8011 15
consistent gets20055
db block changes8 18
free buffer requested 12
calls to kcmgcs 02
calls to get snapshot scn: kcmgss2004 2002
redo entries5 11
redo size5124 5928
no work - consistent read gets 20022
table scans (short tables) 20000
table scan rows gotten 20000
table scan blocks gotten 20000
buffer is not pinned count 20044
15 rows selected.
right"（出处：清风软件下载学院）
