Exadata Storage Index Part-2 (Demo)

Continue from previous blog.....

In previous blog we have explained storage index and it's features. Now we will check real time performance benefits of storage index.

we have flush shared pool and buffer cache to determine exact value and benefits given by Storage Index.

Below query will show that how much physical IO bytes has been saved by storage Index.

SQL> select name, value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

NAME                                                                  VALUE

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

cell physical IO bytes saved by storage index                             0

Enabling the storage index

SQL> alter session set "_kcfis_storageidx_disabled"=false;

Session altered.

Executing command on employee table

SQL> select ID from EMPLOYEE where ID is null;

no rows selected

Now we will check how much bytes has been saved by storage index with below query.

SQL> select name, value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

NAME                                                                  VALUE

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

cell physical IO bytes saved by storage index                      48684323

Above value shows that we have saved 48684323 IO bytes saved with storage index while executing the query.

Now we will disable storage index with below command and execute the same query

SQL> alter session set "_kcfis_storageidx_disabled"=true;

Session altered.

SQL> select USER_ID from EXDM where USER_ID is null;

no rows selected

Checking that how much storage IO bytes has been saved with above query.

SQL> select name, value from v$mystat s, v$statname n where n.statistic# = s.statistic# and name like '%storage%';

NAME                                                                  VALUE

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

cell physical IO bytes saved by storage index                      48684323

Above value shows the same value which was showing in previous query and no increment in value that means there is no IO bytes saved by storage while executing second query as it has been executed without storage index feature.