What is Storage Index in Exadata System?
Storage Index in Exadata is another important feature which helps to reduce useless I/Os for query execution.
A storage index is a memory-based structure that reduces the amount of physical I/O performed in an Exadata cell. The storage index keeps track of minimum and maximum column values of a chunk in GRID Disk and this information us used to avoid useless I/Os.
A few point of Storage Indexes:
- Normal index in database maps location of rows of the table. On the other hand, Exadata storage index don’t locate rows of table.
- They identify the areas (Storage Regions) that definitely will not contain the values, thus eliminating them from I/O processing
- They act as negative indexes, just the opposite of traditional database indexes, which are for locating—not eliminating—the database blocks that may contain the information
- Storage indexes are not stored on disk; they are resident in the memory of the storage cell servers.
- They are created automatically after the storage cells receive repeated queries—with predicates—for columns.
- No user intervention is needed to create or maintain storage indexes because they are memory-resident structures.
Few Limitations:
- Storage Indexes are mostly used for full table scan.
- As Storage Indexes are created during run time and store in memory so it will disappear when the storage cells are rebooted or need to overwrite for memory storage or aged out.
- Storage Indexes are not useful for encrypted table.
How does storage index work?
Note::: If you want to test Storage Index on VM Exadata simulator, has to set at least 7 – 8 GB memory for cells server. otherwise it will not use the Storage Indexes.
SQL> set timing on
SQL> show parameter cell_offload_processing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offload_processing boolean TRUE
SQL> select * from sales where sales_id=14781;
SALES_ID PRODUCT_ CHANNEL_ID CUSTOMER_ID AMOUNT ORDER_DAT SHIP_DATE
---------- -------- ---------- ----------- ---------- --------- ---------
14781 iPhone X 1 281 1200 08-APR-06 09-APR-06
Elapsed: 00:00:02.01
No Index on this table.
SQL> select table_name, index_name, column_name from dba_ind_columns where table_owner='SMARTSCAN' and table_name='SALES'; no rows selected
Execution Plan of the query.
SQL> set linesize 180
SQL> set pagesize 100
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1qc4459n8y9zj, child number 1
-------------------------------------
select * from sales where sales_id=14781
Plan hash value: 781590677
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 34438 (100)| |
|* 1 | TABLE ACCESS STORAGE FULL| SALES | 1393 | 108K| 34438 (1)| 00:06:54 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("SALES_ID"=14781)
filter("SALES_ID"=14781)
Note
-----
- dynamic sampling used for this statement (level=2)
23 rows selected.
With 1st run no storage index created.
SQL> select name,value/1024/1024 as mb from v$statname natural join v$mystat
where name in ('cell physical IO interconnect bytes returned by smart scan',
'cell physical IO bytes saved by storage index');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan .144981384
Query re-run again and it took only 3 millisecond as storage index has been created.
SQL> select * from sales where sales_id=14781;
SALES_ID PRODUCT_ CHANNEL_ID CUSTOMER_ID AMOUNT ORDER_DAT SHIP_DATE
---------- -------- ---------- ----------- ---------- --------- ---------
14781 iPhone X 1 281 1200 08-APR-06 09-APR-06
Elapsed: 00:00:00.03
SQL> select name,value/1024/1024 as mb from v$statname natural join v$mystat
where name in ('cell physical IO interconnect bytes returned by smart scan',
'cell physical IO bytes saved by storage index');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 983.664063
cell physical IO interconnect bytes returned by smart scan .146598816
Execution Plan did not change.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
SQL_ID 1qc4459n8y9zj, child number 0
-------------------------------------
select * from sales where sales_id=14781
Plan hash value: 781590677
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 34429 (100)| |
|* 1 | TABLE ACCESS STORAGE FULL| SALES | 1 | 41 | 34429 (1)| 00:06:54 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("SALES_ID"=14781)
filter("SALES_ID"=14781)
19 rows selected.
After rebooted cell servers. Set parameter cell_offload_processing to false.
SQL> connect smartscan/******** Connected. SQL> set timing on SQL> alter session set cell_offload_processing=false; Session altered.
Run the query number of time and it took around 19 seconds each run which is much slower than earlier (with Storage Index)
SQL> select * from sales where sales_id=14781;
SALES_ID PRODUCT_ CHANNEL_ID CUSTOMER_ID AMOUNT ORDER_DAT SHIP_DATE
---------- -------- ---------- ----------- ---------- --------- ---------
14781 iPhone X 1 281 1200 08-APR-06 09-APR-06
Elapsed: 00:00:18.79
Same execution plan is using but query execution time is hundreds time slower and prediction is same.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1qc4459n8y9zj, child number 0
-------------------------------------
select * from sales where sales_id=14781
Plan hash value: 781590677
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 34429 (100)| |
|* 1 | TABLE ACCESS STORAGE FULL| SALES | 1 | 41 | 34429 (1)| 00:06:54 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SALES_ID"=14781)
18 rows selected.
No Storage index is using.
SQL> select name,value/1024/1024 as mb from v$statname natural join v$mystat
where name in ('cell physical IO interconnect bytes returned by smart scan',
'cell physical IO bytes saved by storage index');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan 0
