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

Solution
In this recipe, you will learn how to enable tracing for storage indexes and interpret the storage index trace files.

First, set the undocumented _kcfis_storageidx_diag_mode parameter to 2 in your
current session using the ALTER SESSION command. This will enable storage index tracing
in debug mode:

1.

SQL> alter session set "_kcfis_storageidx_diag_mode"=2;

2. Next, execute a query that you believe should benefit from storage indexes. In the

following, we will execute one of the queries introduced in a previous recipe in this
chapter:

SQL> select count(*) from d14.myobj_ordered
2 where object_name between 'a' and 'm';
COUNT(*)
----------
38000
Elapsed: 00:00:00.10
SQL>

3. After the query completes, log in to one of your storage cells and navigate to your

diagnostics/trace file directory. In all of the versions of Exadata that we have worked
on, this is located in $ADR_BASE/diag/asm/cell/[cell server name]/trace and
symbolically linked to /var/log/oracle/diag/asm/cell/[cell server name]/trace:

[root@cm01cel01 ~]# cd $ADR_BASE/diag/asm/cell/cm01cel01/trace
[root@cm01cel01 trace]# pwd
/opt/oracle/cell11.2.3.1.1_LINUX.X64_120607/log/diag/asm/cell/cm01cel01/trace
[root@cm01cel01 trace]#

■ Storage index trace information is stored on the storage cell, not the diagnostics directory on your database
server. the context of the trace files is the cell services operating system processes on the storage cells, not your
database instance.

Note

You will find a number of trace files that begin with the svtrc* ; examine the contents of
one the recently created files:

4.

[root@cm01cel01 trace]# ls -alrt svtrc*|tail −5
-rw-r----- 1 root celladmin 3363 Nov 28 00:38 svtrc_12910_75.trc
-rw-r----- 1 root celladmin 265 Nov 28 00:38 svtrc_12910_71.trm
-rw-r----- 1 root celladmin 1414 Nov 28 00:38 svtrc_12910_71.trc
-rw-r----- 1 root celladmin 260 Nov 28 00:38 svtrc_12910_43.trm
-rw-r----- 1 root celladmin 1414 Nov 28 00:38 svtrc_12910_43.trc
[root@cm01cel01 trace]# vi svtrc_12910_43.trc