This will be the prolongation of the previous post.
create table t
(x int primary key,
y varchar2(80) )
/
insert into t(x,y)
select level, rpad('*',80,'*')
from dual
connect by level <= 1000
/
commit;
exec dbms_stats.gather_table_stats(user,'T');
create or replace function get_stats
return number as
l_val number ;
begin
select s2.value
into l_val
from v$sysstat s1,
v$mystat s2
where s1.statistic# = s2.statistic#
and s1.name like 'consistent gets' ;
return l_val;
end;
/
Lets do a simple select and measure the consistent gets in both 10g and 11g database.
rajesh@ORA10G> declare
2 l_stats number;
3 begin
4 l_stats := get_stats ;
5 for x in ( select x from t, (select mod(level,1000)+1 as l
6 from dual
7 connect by level <= 100000 ) t1
8 where t.x = t1.l )
9 loop
10 null;
11 end loop;
12
13 dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats ) );
14 end;
15 /
Total Consistent gets = 101001
PL/SQL procedure successfully completed.
rajesh@ORA11G> declare
2 l_stats number;
3 begin
4 l_stats := get_stats ;
5 for x in ( select x from t, (select mod(level,1000)+1 as l
6 from dual
7 connect by level <= 100000 ) t1
8 where t.x = t1.l )
9 loop
10 null;
11 end loop;
12
13 dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats ) );
14 end;
15 /
Total Consistent gets = 2602
PL/SQL procedure successfully completed.
This is some what an expected behaviour, since oracle 11g decided to cache block after each lookup in the hope it re-uses its again. Instead of hitting the buffer cache again.
As soon as we start reading the data randomly, this effect disappered.
rajesh@ORA10G> /* Reading PK Randomly */
rajesh@ORA10G> declare
2 l_stats number;
3 begin
4 l_stats := get_stats ;
5 for x in ( select x from t, (select trunc(dbms_random.value(1,1000)) as l
6 from dual
7 connect by level <= 100000 ) t1
8 where t.x = t1.l )
9 loop
10 null;
11 end loop;
12
13 dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats ) );
14 end;
15 /
Total Consistent gets = 101001
PL/SQL procedure successfully completed.
rajesh@ORA11G> /* Reading PK Randomly */
rajesh@ORA11G> declare
2 l_stats number;
3 begin
4 l_stats := get_stats ;
5 for x in ( select x from t, (select trunc(dbms_random.value(1,1000)) as l
6 from dual
7 connect by level <= 100000 ) t1
8 where t.x = t1.l )
9 loop
10 null;
11 end loop;
12
13 dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats ) );
14 end;
15 /
Total Consistent gets = 102262
PL/SQL procedure successfully completed.
Table Access by Index Rowid
rajesh@ORA10G>
rajesh@ORA10G> declare
2 l_stats number;
3 begin
4 l_stats := get_stats ;
5 for x in ( select Y from t, (select mod(level,1000)+1 as l
6 from dual
7 connect by level <= 100000 ) t1
8 where t.x = t1.l )
9 loop
10 null;
11 end loop;
12
13 dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats ) );
14 end;
15 /
Total Consistent gets = 201001
PL/SQL procedure successfully completed.
What we are doing here is reading data from table itself (not from the index). We have to do an Index unique scan followed by an Table access by rowid, resulting in Two logical IO's per row. Now lets see what happens in Oracle 11g.
rajesh@ORA11G>
rajesh@ORA11G> declare
2 l_stats number;
3 begin
4 l_stats := get_stats ;
5 for x in ( select Y from t, (select mod(level,1000)+1 as l
6 from dual
7 connect by level <= 100000 ) t1
8 where t.x = t1.l )
9 loop
10 null;
11 end loop;
12
13 dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats ) );
14 end;
15 /
Total Consistent gets = 102602
PL/SQL procedure successfully completed.
you can see we got only single IO per row. In other words Index acess were neutralized. Optimization still works for index even though we acess tables inbetween them.
Table Self Join
When we add the same table twice in join, it adds up in Oracle 10g but not in Oracle 11g.
rajesh@ORA10G> /* How about Same Table Twice */
rajesh@ORA10G> declare
2 l_stats number;
3 begin
4 l_stats := get_stats ;
5 for x in ( select t.x from t, (select mod(level,1000)+1 as l
6 from dual
7 connect by level <= 100000 ) t1, t t3
8 where t.x = t1.l
9 and t3.x = t1.l )
10 loop
11 null;
12 end loop;
13
14 dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats ) );
15 end;
16 /
Total Consistent gets = 202002
PL/SQL procedure successfully completed.
rajesh@ORA11G> /* How about Same Table Twice */
rajesh@ORA11G> declare
2 l_stats number;
3 begin
4 l_stats := get_stats ;
5 for x in ( select t.x from t, (select mod(level,1000)+1 as l
6 from dual
7 connect by level <= 100000 ) t1, t t3
8 where t.x = t1.l
9 and t3.x = t1.l )
10 loop
11 null;
12 end loop;
13
14 dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats ) );
15 end;
16 /
Total Consistent gets = 2602
PL/SQL procedure successfully completed.
Its still equivalent to having table ( 'T' ) only once in the Self join.
Across Fetches
But this optimization doesn't spanned across different fetches.
rajesh@ORA11G> /* Reading same value across fetches */
rajesh@ORA11G> declare
2 l_stats number;
3 begin
4 l_stats := get_stats ;
5 for x in ( select x from t, (select 1 as l
6 from dual
7 connect by level <= 100000 ) t1
8 where t.x = t1.l )
9 loop
10 null;
11 end loop;
12
13 dbms_output.put_line (' Total Consistent gets = ' || ( get_stats - l_stats ) );
14 end;
15 /
Total Consistent gets = 1008
PL/SQL procedure successfully completed.
Here we join the data set 'T1' with table 'T' for the value x = 1 for 100 K iterations. but the join become sucess for 1000 iterations. so we get consistent gets close to that.
By default cursor for loop does implicit array fetch of 100 rows and looks like consistent gets cannot operate across fetches.
In overall it looks like a nice feature and it seems Oracle 11g have introduced these little tricks and features all over the place
