Comparison between exadata and non-exadata executions test case

First we need to load data

SQL> alter session set tracefile_identifier=’ABC’;

Session altered.

Elapsed: 00:00:00.01
SQL> alter system set events ‘10046 trace name context forever, level 12′;

System altered.

Elapsed: 00:00:00.18
SQL> create table t1(c1 number,c2 char(2000));

Table created.

Elapsed: 00:00:00.03
SQL> set autotrace on;
SQL> set autotrace traceonly;
SQL> insert into t1 select rownum,’A’ from dual connect by rownum<100000;

99999 rows created.

Elapsed: 00:00:22.81

Exadata plan

 

Non-Exadata plan

Tkprof-exadata

 

tkprof-non-exadata

 

Statistics of how many blocks in table

SQL> select BLOCKS from user_tables where table_name=’T1′;

BLOCKS
———-
67217

 

Full table scan with count

SQL> select count(1) from t1;

Elapsed: 00:00:00.06

Exadata plan

 

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Full table scan with all data from table.Buffer fetched will be much higher than count

SQL> select * from t1;

199998 rows selected.

Elapsed: 00:00:01.62

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Create index in both exadata and non-exadata

SQL> create index i1_t1 on t1(c1);

Index created.

Elapsed: 00:00:00.22
SQL> exec dbms_stats.gather_table_stats(user,’T1′);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.34

Statistics of how many blocks in Index

SQL> select BLEVEL,LEAF_BLOCKS from user_indexes where index_name=’I1_T1′;

BLEVEL LEAF_BLOCKS
———- ———–
1 443

Index range scan

SQL> select * from t1 where c1 between 1 and 15;

30 rows selected.

Elapsed: 00:00:00.00

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Retrieve single block for index scan with table access

 

SQL> select * from t1 where c1=1;

Elapsed: 00:00:00.00

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Retrieve single block for index scan without table access

 

SQL> select c1 from t1 where c1=1;

Elapsed: 00:00:00.01

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Index fast scan to check behavior of index access

 

SQL> select count(c1) from t1;

Elapsed: 00:00:00.02

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata