Storage Indexes - Part VIII

Exadata storage indexes depends on smart scan, which in-turn depend on direct path reads (either serial or parallel). However Oracle will generally use serial direct path reads for large objects, but when the objects are partitioned, Oracle may fail to recognize that the object is “large” while accessing the individual partitions on the table, because Oracle look at the size of each individual segments.  This might result in some partitions not being read via direct path read and hence no smart scan mechanism thus disabling any storage indexes for that partition.

 

The same goes with compression in place, when the data is being compressed the reduced size of the compressed segments will be even less likely to trigger the serial direct path reads and the problem becomes even more noticeable.

 

Here is the table that sized 1400MB in size

 

c##rajesh@PDB1> create table t

  2  nologging

  3  as

  4  select *

  5  from big_table;

 

Table created.

 

c##rajesh@PDB1> exec show_space('T');

Unformatted Blocks .....................               0

FS1 Blocks (0-25) ......................               0

FS2 Blocks (25-50) .....................               0

FS3 Blocks (50-75) .....................               0

FS4 Blocks (75-100).....................               0

Full Blocks ............................         182,648

Total Blocks............................         188,416

Total Bytes.............................   1,543,503,872

Total MBytes............................           1,472

Unused Blocks...........................           5,100

Unused Bytes............................      41,779,200

Last Used Ext FileId....................              24

Last Used Ext BlockId...................      18,268,160

Last Used Block.........................           3,092

 

PL/SQL procedure successfully completed.

 

Here is the script that was used for execution.

 

c##rajesh@PDB1> $ type script.sql

set termout off

select * from t where owner ='JYU';

set termout on

 

c##rajesh@PDB1> select s.name,m.value

  2  from v$statname s ,

  3      v$mystat m

  4  where s.statistic# = m.statistic#

  5  and s.name in ('cell physical IO bytes saved by storage index',

  6  'cell physical IO interconnect bytes returned by smart scan' );

 

NAME                                                                    VALUE

------------------------------------------------------------ ----------------

cell physical IO bytes saved by storage index                               0

cell physical IO interconnect bytes returned by smart scan                  0

 

We make repeated execution to warmup the storage cells to build the storage index and see if that adds benefit to the execution.

 

c##rajesh@PDB1> @script.sql

c##rajesh@PDB1> @script.sql

c##rajesh@PDB1> @script.sql

c##rajesh@PDB1> @script.sql

c##rajesh@PDB1> select s.name,m.value

  2  from v$statname s ,

  3      v$mystat m

  4  where s.statistic# = m.statistic#

  5  and s.name in ('cell physical IO bytes saved by storage index',

  6  'cell physical IO interconnect bytes returned by smart scan' );

 

NAME                                                                    VALUE

------------------------------------------------------------ ----------------

cell physical IO bytes saved by storage index                      3842637824

cell physical IO interconnect bytes returned by smart scan            5940000

 

the storage index is helping us here, that got saved nearly 3+GB of data being transferred from storage to database layer, and the amount of the data that returned from storage to database layer in this case was just as few as 5MB.

 

Now let’s see the effect of compression place

 

c##rajesh@PDB1> alter table t compress for archive high;

 

Table altered.

 

c##rajesh@PDB1> alter table t move online parallel 8;

 

Table altered.

 

c##rajesh@PDB1> exec show_space('T');

Unformatted Blocks .....................               0

FS1 Blocks (0-25) ......................               0

FS2 Blocks (25-50) .....................               0

FS3 Blocks (50-75) .....................               0

FS4 Blocks (75-100).....................               4

Full Blocks ............................           9,621

Total Blocks............................           9,752

Total Bytes.............................      79,888,384

Total MBytes............................              76

Unused Blocks...........................               0

Unused Bytes............................               0

Last Used Ext FileId....................              24

Last Used Ext BlockId...................      23,909,120

Last Used Block.........................             536

 

PL/SQL procedure successfully completed.

 

c##rajesh@PDB1> exec dbms_stats.gather_table_stats(user,'T',degree=>4,no_invalidate=>false);

 

PL/SQL procedure successfully completed.

 

The effect of compression has reduced the size from 1400+ MB to just 76 MB.  Let’s run the queries post the compression

 

c##rajesh@PDB1> select s.name,m.value

  2  from v$statname s ,

  3      v$mystat m

  4  where s.statistic# = m.statistic#

  5  and s.name in ('cell physical IO bytes saved by storage index',

  6  'cell physical IO interconnect bytes returned by smart scan' );

 

NAME                                                                    VALUE

------------------------------------------------------------ ----------------

cell physical IO bytes saved by storage index                               0

cell physical IO interconnect bytes returned by smart scan                  0

 

c##rajesh@PDB1> @script.sql

c##rajesh@PDB1> @script.sql

c##rajesh@PDB1> @script.sql

c##rajesh@PDB1> @script.sql

c##rajesh@PDB1> @script.sql

c##rajesh@PDB1> select s.name,m.value

  2  from v$statname s ,

  3      v$mystat m

  4  where s.statistic# = m.statistic#

  5  and s.name in ('cell physical IO bytes saved by storage index',

  6  'cell physical IO interconnect bytes returned by smart scan' );

 

NAME                                                                    VALUE

------------------------------------------------------------ ----------------

cell physical IO bytes saved by storage index                               0

cell physical IO interconnect bytes returned by smart scan            1894136

 

c##rajesh@PDB1>

 

No matter how often we run this queries now, no storage index is used. This confirms that storage index will not be in use for smaller segments. Ofcourse for smaller segments that sounds reasonable and for larger segments that favor direct path reads storage indexes plays a major role in eliminating the portion of region where the requested data can’t exist.