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.
