In-Memory parallel execution in 11g R2

From: Anupam Pandey <my.oralce@xxxxxxxxx>
To: oracle Freelists <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 17 May 2010 20:03:31 +0530
Hi All,
         I came across this new feature in 11gR2 ( In-Memory parallel
execution).
I wanted to check this feature but having some difficulty.


I executed following sqls to check this feature.

create table mem1
pctfree 99
pctused 1
as
select
    rownum                    id,
    trunc(100 * dbms_random.normal)        val,
    rpad('x',100)                padding
from
    all_objects
where
    rownum <= 10000
;


begin
    dbms_stats.gather_table_stats(
        user,
        'mem1',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/

create table mem2
pctfree 99
pctused 1
as
select
    rownum                    id,
    trunc(100 * dbms_random.normal)        val,
    rpad('x',100)                padding
from
    all_objects
where
    rownum <= 10000
;


begin
    dbms_stats.gather_table_stats(
        user,
        'mem2',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/


select 1
from mem1,
     mem2
where mem1.id = mem2.id ;

Statistics
----------------------------------------------------------

          0  recursive
calls
          0  db block
gets
      10356  consistent
gets
          0  physical
reads
          0  redo
size
     172430  bytes sent via SQL*Net to
client
       7802  bytes received via SQL*Net from
client
        668  SQL*Net roundtrips to/from
client
          0  sorts
(memory)
          0  sorts
(disk)
      10000  rows processed


alter table mem1 parallel;

alter table mem2 parallel;

 select 1
 from mem1,
      mem2
 where mem1.id = mem2.id;

 Statistics
----------------------------------------------------------

       1098  recursive
calls
          0  db block
gets
      23838  consistent
gets
      *  770  physical reads*

          0  redo
size
     172430  bytes sent via SQL*Net to
client
       7802  bytes received via SQL*Net from
client
        668  SQL*Net roundtrips to/from
client
         20  sorts
(memory)
          0  sorts
(disk)
      10000  rows
processed

Here I am not able to justify the physical reads happening in parallel mode
because as per
In-Memory parallel execution of 11g R2 ,PX need to read from buffer cache .


Thanks And Regards,
    Anupam
In-Memory parallel execution in 11g R2

Other related posts:
