Exadata Storagne Index overview:
A storage index is a memory-based structure that reduces the amount of physical I/O required by the cell.
Storage Indexes consist of a minimum and a maximum value for up to 8 columns. This structure is maintained for 1MB chunks of storage (storage region). Storage Indexes are stored in memory only and are never written to disk. Storage index works transparently with no user input. There is no need to create/drop or tune the storage index. Storage index is kept in memory, it disappears when the cell is re-booted. The first queries that run after a cell rebooted automatically cause the storage index to rebuild.
The storage index works best when the following conditions are true:
1. Data is roughly ordered (so that same column values are cluster together)
2.Query has a predicate on a storage index column checking for =, <, > or combination of these.
Monitoring Storage Indexes:
1) Use v$sesstat, v$sysstat and v$sql:
select name, values from v$mystat a, v$statname b
where b.statistic# = s.statistic# and name like '%storage%';
NAME VALUE
---------------------------------------------------------------------- --------------
cell physical IO bytes saved by storage index 0
Note : (after Query run) 5095009234
Cell physical IO bytes saved by storage index : Shows how many bytes of I/O were eliminated by the application of storage indexes at the storage cell level.
Cell physical IO interconnect bytes returned by smart scan : Shows how many bytes of I/O were returned by a smart scan to the database server.
2. Tracing:
Use _CELL_STROAGE_INDEX_DIAG_MODE=TRUE in cellinit.ora file (restart the cellsrv)
Use full comparison operator:
Storage Index can evaluate predicates like =, <, >, <=, >=, BETWEEN, IS NULL, IS NOT NULL
Limitation:
Storage Index DO NOT work with: !=, % (wildcards) and CLOBs
Ref. Oracle Docs./Metalink
A storage index is a memory-based structure that reduces the amount of physical I/O required by the cell.
Storage Indexes consist of a minimum and a maximum value for up to 8 columns. This structure is maintained for 1MB chunks of storage (storage region). Storage Indexes are stored in memory only and are never written to disk. Storage index works transparently with no user input. There is no need to create/drop or tune the storage index. Storage index is kept in memory, it disappears when the cell is re-booted. The first queries that run after a cell rebooted automatically cause the storage index to rebuild.
The storage index works best when the following conditions are true:
1. Data is roughly ordered (so that same column values are cluster together)
2.Query has a predicate on a storage index column checking for =, <, > or combination of these.
Monitoring Storage Indexes:
1) Use v$sesstat, v$sysstat and v$sql:
select name, values from v$mystat a, v$statname b
where b.statistic# = s.statistic# and name like '%storage%';
NAME VALUE
---------------------------------------------------------------------- --------------
cell physical IO bytes saved by storage index 0
Note : (after Query run) 5095009234
Cell physical IO bytes saved by storage index : Shows how many bytes of I/O were eliminated by the application of storage indexes at the storage cell level.
Cell physical IO interconnect bytes returned by smart scan : Shows how many bytes of I/O were returned by a smart scan to the database server.
2. Tracing:
Use _CELL_STROAGE_INDEX_DIAG_MODE=TRUE in cellinit.ora file (restart the cellsrv)
Use full comparison operator:
Storage Index can evaluate predicates like =, <, >, <=, >=, BETWEEN, IS NULL, IS NOT NULL
Limitation:
Storage Index DO NOT work with: !=, % (wildcards) and CLOBs
Ref. Oracle Docs./Metalink
No comments:
Post a Comment