Search

Top 60 Oracle Blogs

Recent comments

Exadata storage indexes

We had a question on AskTOM inquiring about how to handle the issue of only 8 storage indexes being possible on an Exadata engineered system. If you are unfamiliar with what a storage index is, they are part of the suite of features often referred to as the “secret sauce” that can improve query performance on Exadata systems by holding more metadata about the data that is stored on disk. You can get an introduction to the concept of storage indexes here.

Well..the good news is, the limit of 8 has been lifted in recent versions of the Exadata software up to 24. When I replied to the questioner, they demanded proof Smile, which I was glad to see (even though it means more work for me). Because one of my philosophies for AskTOM is that people should never just trust what they read on a forum (AskTOM or otherwise).

There is no dictionary view or performance view that is a direct path into the storage indexes, that is, you cannot see what is “in” them at any given point in time. However, there is a statistic “cell physical IO bytes saved by storage index” which gives cumulative information about how useful a storage index was, so whenever that statistic increments after a query execution, we know that some savings were made.

To keep the source code down to a manageable size, I’ll do the proof with just 12 columns, but you can easily ramp this up to 24 columns and you will see the same results. First I’ll create a table with 12 columns of nicely clustered data, which will make them obvious candidates for storage indexes as queries occur on them.



SQL> create table t as
  2  select
  3    trunc(rownum/1000) c1,
  4    trunc(rownum/1000) c2,
  5    trunc(rownum/1000) c3,
  6    trunc(rownum/1000) c4,
  7    trunc(rownum/1000) c5,
  8    trunc(rownum/1000) c6,
  9    trunc(rownum/1000) c7,
 10    trunc(rownum/1000) c8,
 11    trunc(rownum/1000) c9,
 12    trunc(rownum/1000) c10,
 13    trunc(rownum/1000) c11,
 14    trunc(rownum/1000) c12,
 15    rpad(rownum,500) data
 16  from
 17    ( select 1 from dual connect by level < 5000 ),
 18    ( select 1 from dual connect by level < 5000 );

Table created.

With an initial query,  you can see that there are no storage index benefits in play yet.



SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index                             0
cell XT granule IO bytes saved by storage index                           0

So to ensure that my storage indexes are working at all,  I’ll run a series of queries against column C1 and check before and after values for the “cell physical IO bytes saved by storage index” statistic.



SQL> declare
  2    x varchar2(1000);
  3  begin
  4  for i in 1 .. 20
  5  loop
  6    execute immediate 'select max(data) from t where c1 < '||(i*10+1000) into x;
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                        VALUE
---------------------------------------------------------------- ----------------
cell physical IO bytes saved by storage index                        316601630720
cell XT granule IO bytes saved by storage index                                 0

At this point we know that storage indexes are available and in use on this database. Because the creation and usage of storage indexes is not in the hands of the developer, but a decision made by the Exadata software layer, I’ll now throw a selection of queries at the database on columns C1 to C12 with varying ranges to “let the database know” that these columns are important to me, and hence that storage indexes are going to be of benefit.



SQL> declare
  2    x varchar2(1000);
  3  begin
  4  for c in 1 .. 12 loop
  5    for i in 1 .. 20
  6    loop
  7      execute immediate 'select max(data) from t where c'||c||' < '||(i*10+1000) into x;
  8    end loop;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          3950481989632
cell XT granule IO bytes saved by storage index                                    0

Now all is in readiness to prove that we have storage indexes for all 12 columns. I can now issue a simple query against each of the 12 columns C1 to C12, and each time I would expect to see the cell physical IO bytes saved by storage index” statistic increment. Once I have got to column C12, I’ll loop back around to C1 just to guard against the scenario that perhaps storage indexes are being thrown away as I move to other column queries.



SQL> select max(data) from t where c1 < 2000;

MAX(DATA)
-------------------------------
999999

SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          3965752631296
cell XT granule IO bytes saved by storage index                                    0

SQL>
SQL> select max(data) from t where c2 < 2000;

MAX(DATA)
-------------------------------
999999

SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          3981023272960
cell XT granule IO bytes saved by storage index                                    0

SQL>
SQL> select max(data) from t where c3 < 2000;

MAX(DATA)
-------------------------------
999999

SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          3996293914624
cell XT granule IO bytes saved by storage index                                    0

SQL>
SQL> select max(data) from t where c4 < 2000;

MAX(DATA)
-------------------------------
999999

SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          4011564556288
cell XT granule IO bytes saved by storage index                                    0

SQL>
SQL> select max(data) from t where c5 < 2000;

MAX(DATA)
-------------------------------
999999

SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          4026835197952
cell XT granule IO bytes saved by storage index                                    0

SQL>
SQL> select max(data) from t where c6 < 2000;

MAX(DATA)
-------------------------------
999999

SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          4042105839616
cell XT granule IO bytes saved by storage index                                    0

SQL>
SQL> select max(data) from t where c7 < 2000;

MAX(DATA)
-------------------------------
999999

SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          4057376481280
cell XT granule IO bytes saved by storage index                                    0

SQL>
SQL> select max(data) from t where c8 < 2000;

MAX(DATA)
-------------------------------
999999

SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          4072647122944
cell XT granule IO bytes saved by storage index                                    0

SQL>
SQL> select max(data) from t where c9 < 2000;

MAX(DATA)
-------------------------------
999999


SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          4087917764608
cell XT granule IO bytes saved by storage index                                    0

SQL>
SQL> select max(data) from t where c10 < 2000;

MAX(DATA)
-------------------------------
999999

SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          4103188406272
cell XT granule IO bytes saved by storage index                                    0

SQL>
SQL> select max(data) from t where c11 < 2000;

MAX(DATA)
-------------------------------
999999

SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          4118459047936
cell XT granule IO bytes saved by storage index                                    0

SQL>
SQL> select max(data) from t where c12 < 2000;

MAX(DATA)
-------------------------------
999999

SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          4133729689600
cell XT granule IO bytes saved by storage index                                    0

SQL>
SQL> select max(data) from t where c1 < 2000;

MAX(DATA)
-------------------------------
999999

SQL>
SQL> select name, value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and name like '%storage index%';

NAME                                                                           VALUE
---------------------------------------------------------------- -------------------
cell physical IO bytes saved by storage index                          4149000331264
cell XT granule IO bytes saved by storage index                                    0

As you can see, I got storage index benefit from all 12 queries, thus proving that we have indeed lifted the limit from 8 storage indexes. It is now up to 24 columns….trust me Smile