Storage Indexes - Oracle Exadata Recipes: A Problem-Solution Approach - page 562

large tables. If they do, the workload is most often characterized as an OLTP workload, which generally means that the
queries issued against these tables are often single-row lookups and serviced without a physical read (or Smart Scan).

■ We acknowledge that all oracle database workloads are different, but, generally speaking, if you are relying
on storage indexes for performance and your applications are issuing smart scans, and if the usability of storage indexes
were being adversely impacted by dML, we would hazard to guess that you likely have a larger application design issue
to address.

Note

19-7. Disabling Storage Indexes
Problem
You wish to disable storage indexes to mitigate potential performance variability for your Oracle database workloads.

Solution
In this recipe, you will learn how to disable storage indexes. Before disabling storage indexes, we will execute a test
query and show the storage index I/O savings:

SQL> select count(*) from d14.myobj_test where col4 ='SOURCE$';
COUNT(*)
----------
1000
SQL>@ lst19-01-exastorind-mysess.sql
Statistic Value (MB)
-------------------------------------------------------------------- -----------------------
cell physical IO bytes saved by storage index 1,844.75
... Output omitted for brevity

Now, execute the following ALTER SYSTEM command to disable storage indexes for your database:

SQL> alter system set "_kcfis_storageidx_disabled"=TRUE scope=both sid='*';
System altered.
SQL>

If you re-execute the same query, you will see that there are no I/O savings from storage indexes:

SQL> select count(*) from d14.myobj_test where col4 ='SOURCE$';
COUNT(*)
----------
1000
SQL>@ lst19-01-exastorind-mysess.sql
Statistic Value (MB)
-------------------------------------------------------------------- -----------------------
cell physical IO bytes saved by storage index 0.00