Oracle Exadata — “Storage Index”
A storage index is a memory area that reduces the amount of physical I/O required by the Exadata Cell when accessing data. A storage index stores the minimum and maximum values retrieved based on your application data where clause predicates and builds storage indexes based on usage with size 1 MB by default.
A storage improves a query performance by filtering unrequired I/O. It is easy to maintain & query the memory based storage index and I/O effectively used, in turn it increases I/O bandwidth of the cell in total while handling it usage very few resources.
You can influence storage index behavior using a number of methods, including forcing direct path reads with full table scans, disabling storage indexes. They are used during direct path read operations, when the queries contain predicates (WHERE clause) and if the underlying ASM disk group has the cell.smart_scan_capable=TRUE attribute set.
☑️ How does Storage Index work ?
These storage regions maintain data distribution statistics of in memory structure called region index. This information is stored in up to 8 columns. Storage index is a collection of the region indexes. Each region inex having having the data distribution of columns that are well clustered. Exadata storage server having logic logic that transperantly determines which cloumns are clustered enough to be included in the region index. Different parts of the same table can potentially have different column set in their corresponding region index.
The Storage Index performs well when the following conditions are true:
✅The data is roughly ordered so that the same colum values are clustered together.
✅The query has a predicate on a storage index column checking for =, <, > or a combination of these.
It is important to note that the storage index works transparently with no user input. There is no need to create, drop or tune the storage index. The only way to influence storage index is to load your tables using presorted data.
As the storage index is kept in memeory, it disappers when the cell is rebooted. The first queries that run after a cell is rebooted automatically cause the storage index to be rebuilt.
☑️ Performance Optimization for SQL queries with Min/Max fun:
The Exadata Storage index can be used to Optimize queries that contain MIN & MAX functions. As the query is procesed, running minimum and maximum values tracked. Before issuing an I/O, the minimum and maximum values cached in the storage index for the data region are checked against the running values to decide weather ot not the I/O is requred.
Overall this optimization can result in significant I/O pruning during the course of a query, which improves query performance. The slide shows an example that benefits from this optimization.
Business intelligence tools that get the shape of a table by querying the minimum or maximum value for each column benefit greatly from this optimization
Note that Oracle Database release 12.1.0.2 is the minimum required database software version
In this practice, you are introduced to the storage index capability of Exadata. You will execute a query multiple times and examine statistics to measure the effect of storage index.
✅How to measure the I/O savings provided by storage indexes using the cell physical IO bytes saved by storage index statistic.
Begin by issuing a SQL statement that qualifies for Exadata Smart Scan and run the script the script my_session.sql to display the I/O savings as a result of storage indexes:
SQL> select count(*) from mytbl.myobj_comp
where object_id between 200 and 300;
COUNT(*)
— — — — —
193010
Elapsed: 00:00:00.20
my_session.sql
SQL> select stat.name, sess.value/1024/1024 value
from v$mystat sess, v$statname stat
where stat.statistic# = sess.statistic#
and stat.name in
(‘cell physical IO bytes eligible for predicate offload’,
‘cell physical IO interconnect bytes’,
‘cell physical IO bytes saved by storage index’,
‘cell physical IO interconnect bytes returned by smart scan’)
order by 1;
Statistic Value (MB)
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — cell physical IO bytes eligible for predicate offload 2,255.09
cell physical IO bytes saved by storage index 1,344.27
cell physical IO interconnect bytes 1.51
cell physical IO interconnect bytes returned by smart scan 1.49
SQL>
In this output, we can see that our query completed in under a second and we saved over 1,344 megabytes of I/O to Exadata’s cell disks by use of storage indexes. Part of the reason for the very fast performance was certainly related to Smart Scan, but a large part of this was related to I/O savings via storage indexes.
One important consideration to be aware of with storage indexes is that in order for Exadata to produce storage index I/O savings, the data in these indexes will need to be written to the index’s storage regions. In other words, storage index data needs to be “primed” on the cell server’s region index memory structures in order for them to be utilized. If we were to run the previous query the first time, you would see no storage index I/O savings because the region indexes would contain no relevant data. In the following example, we will bounce our database and execute the same test case twice to demonstrate the point:
[oracle@cm01dbm01 source]$ srvctl stop database -d edw
[oracle@cm01dbm01 source]$ srvctl start database -d edw
SQL> select count(*) from mytbl.myobj_comp
where object_id between 200 and 300;
COUNT(*)
— — — — —
193010
Elapsed: 00:00:20.50 <− First execution
SQL> set echo off
SQL> @my_session.sql
Statistic Value (MB)
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
cell physical IO bytes eligible for predicate offload 2,255.09
cell physical IO bytes saved by storage index .00 <− First execution
cell physical IO interconnect bytes 1.72
cell physical IO interconnect bytes returned by smart scan 1.68
SQL> select count(*) from mytbl.myobj_comp
2 where object_id between 100 and 200;
COUNT(*)
— — — — —
101000
Elapsed: 00:00:00.21 <− Second execution
SQL> @ my_session.sql
Statistic Value (MB)
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
cell physical IO bytes eligible for predicate offload 2,255.09
cell physical IO bytes saved by storage index 1,344.27 <− Second execution
cell physical IO interconnect bytes 1.51
cell physical IO interconnect bytes returned by smart scan 1.49
As you can see from these two tests, both queries took advantage of Smart Scan but, in the first query, there was zero I/O savings are a result of storage indexes. The next query, however, ran in a fraction of the time (00.20 seconds as compared to almost 21(20.50) seconds) as a result of 1,344 megabytes of savings via storage indexes.
The query in my_session.sql measures storage index savings for your specific session by querying the V$MYSTAT view.
✅Trace Storage Index:
Storage index trace information is stored on the storage cell, not the diagnostics directory of database server. The context of the trace files is the cell services operating system processes on the storage cells, not database instance.
You can enable tracing for storage indexes and interpret the storage index trace files by setting a parameter for your session as below.
SQL> alter session set “_kcfis_storageidx_diag_mode”=2;
The above parameter enables storage index tracing in debug mode.
After this run the query qualified for smart scan like (Select count(*) from exatest.objlist where object_id between 200 and 300).
When the query completes log in to one of your storage cells and navigate to your diagnostics/trace file directory (/var/log/oracle/diag/asm/cell/[cell server name]/trace) eg: /var/log/oracle/diag/asm/cell/exacell1/trace
On the above location there will be number of trace files that begin with the svtrc* to view.
[root@exacell1 trace]# ls -alrt svtrc*|tail −5
-rw-r — — — 1 root celladmin 3363 Aug 03 00:38 svtrc_12190_75.trc
-rw-r — — — 1 root celladmin 265 Aug 03 00:38 svtrc_12910_71.trm
-rw-r — — — 1 root celladmin 1414 Aug 03 00:38 svtrc_12910_71.trc
[root@cm01cel01 trace]# vi svtrc_12910_75.trc
In the trace file you will search for the string RIDX which stands for “region index”.
The contents of the trace file will have the RIDX section which contains information (like SQLID) about region indexes (storage indexes). You can further query the V$SQL against the SQLID to know the SQL Statement issued.
✅ Disabling Storage Indexes:
You can use ALTER SYSTEM command to disable storage indexes for your database.
SQL> alter system set “_kcfis_storageidx_disabled”=TRUE scope=both sid=’*’;
System altered.
☑️Example Oracle Storage Index on X8-M :
1. Establish a terminal connection to ex01dbadmin01 as the oracle user and configure your environment to access the dbm database.
[oracle@gateway ~]$ ssh oracle@ex01dbadmin01
oracle@ex01dbadmin01’s password: <password>
[oracle@ex01dbadmin01 ~]$ . oraenv
ORACLE_SID = [oracle] ? dbm1
The Oracle base has been set to /u01/app/oracle
[oracle@ex01dbadmin01 ~]$
2. Connect to your database with SQL*Plus. Log in as the sh user
[oracle@qr01dbadm01 ~]$ sqlplus sh/<password>@dbmpdb1
SQL*Plus: Release 19.0.0.0.0 — Production on Thu Aug 25 19:47:09
2022
Version 19.9.0.0.0
Copyright © 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Thu Aug 25 2022 19:41:08 +00:00Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.9.0.0.0
SQL>
3. Execute the following SQL script and verify that the statistics are at or near zero values. If any statistics are significantly greater than zero, then reconnect and retry.
SQL> @/home/oracle/labs/lab04–02–03.sql SQL> select a.name, b.value/1024/1024 MB 2 from v$sysstat a, v$mystat b 3 where a.statistic# = b.statistic# and 4 (a.name in (‘physical read total bytes’, 5 ‘physical write total bytes’, 6 ‘cell IO uncompressed bytes’) 7 or a.name like ‘cell phy%’);
NAME MB
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
physical read total bytes 0
physical write total bytes 0
cell physical IO interconnect bytes 0
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 0
cell physical IO bytes eligible for smart IOs 0
cell physical IO bytes saved by columnar cache 0
cell physical IO bytes saved by storage index 0
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO bytes processed for IM capacity 0
cell physical IO bytes processed for IM query 0
cell physical IO bytes processed for no memcompress 0
cell physical IO interconnect bytes returned by smart scan 0
cell physical write bytes saved by smart file initialization 0
cell IO uncompressed bytes 0
cell physical write IO bytes eligible for offload 0
cell physical write IO host network bytes written during offloa 0
18 rows selected
4. Execute the following query in the SQL script:
SQL> @/home/oracle/labs/lab04–02–04.sql SQL> select cust_gender,count(*) from mycustomers jjk
where cust_income_level = ‘C:50000–69999’
group by cust_gender;
C COUNT(*)
- — — — — —
M 134958
F 134890
5. Repeat the SQL script from step 3
SQL> @/home/oracle/labs/lab04–02–03.sql SQL> select a.name, b.value/1024/1024 MB 2 from v$sysstat a, v$mystat b 3 where a.statistic# = b.statistic# and 4 (a.name in (‘physical read total bytes’, 5 ‘physical write total bytes’, 6 ‘cell IO uncompressed bytes’) 7 or a.name like ‘cell phy%’);
NAME MB
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
physical read total bytes 650.265625
physical write total bytes 0
cell physical IO interconnect bytes 3.75598145
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 650.25
cell physical IO bytes eligible for smart IOs 650.25
cell physical IO bytes saved by columnar cache 0
cell physical IO bytes saved by storage index 0
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO bytes processed for IM capacity 0
cell physical IO bytes processed for IM query 0
cell physical IO bytes processed for no memcompress 0
cell physical IO interconnect bytes returned by smart scan 3.74035645
cell physical write bytes saved by smart file initialization 0
cell IO uncompressed bytes 650.25
cell physical write IO bytes eligible for offload 0
cell physical write IO host network bytes written during offloa 0
18 rows selected.
The statistics show that the query in step 4 was conducted using Smart Scan. Note, however, that cell physical IO bytes saved by storage index is zero. This is because storage indexes are memory structures, which do not persist when the Exadata cells are restarted. They are dynamically built when tables are referenced for the first time after the cells restart. Now that the mycustomers table has been scanned as a result of the query in step 4, all subsequent queries on the mycustomers table can benefit from whatever storage indexes the Exadata cells automatically create.
6. Reconnect to your database to reset the session-level statistics.
SQL> connect sh/@dbmpdb1 Connected. SQL>
7. Repeat the SQL script from step 3 and verify that the statistics are again at or near zero values. If any statistics are significantly greater than zero, then reconnect again and retry.
SQL> @/home/oracle/labs/lab04–02–03.sql SQL> select a.name, b.value/1024/1024 MB 2 from v$sysstat a, v$mystat b 3 where a.statistic# = b.statistic# and 4 (a.name in (‘physical read total bytes’, 5 ‘physical write total bytes’, 6 ‘cell IO uncompressed bytes’) 7 or a.name like ‘cell phy%’);
NAME MB
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
physical read total bytes 0
physical write total bytes 0
cell physical IO interconnect bytes 0
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 0
cell physical IO bytes eligible for smart IOs 0
cell physical IO bytes saved by columnar cache 0
cell physical IO bytes saved by storage index 0
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO bytes processed for IM capacity 0
cell physical IO bytes processed for IM query 0
cell physical IO bytes processed for no memcompress 0
cell physical IO interconnect bytes returned by smart scan 0
cell physical write bytes saved by smart file initialization 0
cell IO uncompressed bytes 0
cell physical write IO bytes eligible for offload 0
cell physical write IO host network bytes written during offloa 0
18 rows selected
8. Re-execute the SQL script from step 4.
SQL> @/home/oracle/labs/lab04–02–04.sql SQL> select cust_gender,count(*) from mycustomers 2 where cust_income_level = ‘C: 50000–69999’ 3 group by cust_gender;
C COUNT(*)
- — — — — —
M 134958
F 134890
9. Repeat the SQL script from step 3
SQL> @/home/oracle/labs/lab04–02–03.sql SQL> select a.name, b.value/1024/1024 MB 2 from v$sysstat a, v$mystat b 3 where a.statistic# = b.statistic# and 4 (a.name in (‘physical read total bytes’, 5 ‘physical write total bytes’, 6 ‘cell IO uncompressed bytes’) 7 or a.name like ‘cell phy%’);
NAME MB
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
physical read total bytes 650.25
physical write total bytes 0
cell physical IO interconnect bytes 3.55298615
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 650.25
cell physical IO bytes eligible for smart IOs 650.25
cell physical IO bytes saved by columnar cache 0
cell physical IO bytes saved by storage index 595.460938
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO bytes processed for IM capacity 0
cell physical IO bytes processed for IM query 0
cell physical IO bytes processed for no memcompress 0
cell physical IO interconnect bytes returned by smart scan 3.55298615
cell physical write bytes saved by smart file initialization 0
cell IO uncompressed bytes 54.7890625
cell physical write IO bytes eligible for offload 0
cell physical write IO host network bytes written during offloa 0
18 rows selected
This time you will see that the query in step 8 benefits from the storage index. Instead of conducting more than 650 MB of I/O inside the cells, storage indexes were used to bypass more than 595 MB of I/O. In other words, approximately 55 MB of I/O was conducted instead of 650 MB. Queries that benefit from storage indexes can execute more quickly using fewer resources, which allows other workloads to benefit from the unused I/O resources.
10. Exit your SQL*Plus session.
