Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Exadata Storage Index Min/Max Optimization

24 March 2015 1 Comment Written by Christian Antognini

Before discussing the Exadata-specific feature, let’s review what the database engine can do independently of whether Exadata is used. To execute queries containing the min or max functions efficiently, two specific operations are available with B-tree indexes defined on the column referenced in the min or max function. The first, INDEX FULL SCAN (MIN/MAX), is used when a query doesn’t specify a range condition. In spite of its name, however, it performs no full index scan. It simply gets either the rightmost or the leftmost index key:

SQL> SELECT /*+ index(t t_pk) */ min(id) FROM t;
 
   MIN(ID)
----------
         1
 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'iostats last'));
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  gy59myptq5gac, child number 0
-------------------------------------
SELECT /*+ index(t t_pk) */ min(id) FROM t
 
Plan hash value: 2094033419
 
---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE            |      |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_PK |      1 |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------

SQL> SELECT /*+ index(t t_pk) */ min(id) FROM t; MIN(ID) ---------- 1 SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'iostats last')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------- SQL_ID gy59myptq5gac, child number 0 ------------------------------------- SELECT /*+ index(t t_pk) */ min(id) FROM t Plan hash value: 2094033419 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | 2 | INDEX FULL SCAN (MIN/MAX)| T_PK | 1 | 1 | 1 |00:00:00.01 | 2 | ---------------------------------------------------------------------------------------------

The second, INDEX RANGE SCAN (MIN/MAX), is used when the query specifies a condition on the same column used in the function:

SQL> SELECT /*+ index(t t_pk) */ min(id) FROM t WHERE id > 42;
 
   MIN(ID)
----------
        43
 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'iostats last'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  3nxqnh5y5z29b, child number 0
-------------------------------------
SELECT /*+ index(t t_pk) */ min(id) FROM t WHERE id > 42
 
Plan hash value: 4039034112
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   FIRST ROW                  |      |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| T_PK |      1 |      1 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("ID">42)

SQL> SELECT /*+ index(t t_pk) */ min(id) FROM t WHERE id > 42; MIN(ID) ---------- 43 SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'iostats last')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- SQL_ID 3nxqnh5y5z29b, child number 0 ------------------------------------- SELECT /*+ index(t t_pk) */ min(id) FROM t WHERE id > 42 Plan hash value: 4039034112 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | 2 | FIRST ROW | | 1 | 1 | 1 |00:00:00.01 | 2 | |* 3 | INDEX RANGE SCAN (MIN/MAX)| T_PK | 1 | 1 | 1 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID">42)

Unfortunately, this optimization technique can’t be applied when both functions (min and max) are used in the same query. In this type of situation, an index full scan is performed. The following query is an example:

SQL> SELECT /*+ index(t t_pk) */ min(id), max(id) FROM t;
 
   MIN(ID)    MAX(ID)
---------- ----------
         1      10000
 
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'iostats last'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  dn4290uqx6s34, child number 0
-------------------------------------
SELECT /*+ index(t t_pk) */ min(id), max(id) FROM t
 
Plan hash value: 56794325
 
-----------------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |      1 |00:00:00.06 |      32 |
|   1 |  SORT AGGREGATE  |      |      1 |      1 |      1 |00:00:00.06 |      32 |
|   2 |   INDEX FULL SCAN| T_PK |      1 |  10000 |  10000 |00:00:00.03 |      32 |
-----------------------------------------------------------------------------------

SQL> SELECT /*+ index(t t_pk) */ min(id), max(id) FROM t; MIN(ID) MAX(ID) ---------- ---------- 1 10000 SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'iostats last')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- SQL_ID dn4290uqx6s34, child number 0 ------------------------------------- SELECT /*+ index(t t_pk) */ min(id), max(id) FROM t Plan hash value: 56794325 ----------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.06 | 32 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.06 | 32 | | 2 | INDEX FULL SCAN| T_PK | 1 | 10000 | 10000 |00:00:00.03 | 32 | -----------------------------------------------------------------------------------

In Exadata, as of Oracle Database version 12.1.0.2, there is an optimization technique that uses storage indexes instead of B-tree indexes. To take advantage of it, not only a smart scan is required, but a storage index defined on the column referenced in the min or max function must also exist (and that, unfortunately, is something we can’t ensure). Let’s have a look to an example:

  • The table used for the test contains 16GB of data:
SQL> SELECT blocks*block_size AS value
  2  FROM dba_tables JOIN dba_tablespaces USING (tablespace_name)
  3  WHERE table_name = 'ORDERS'
  4  AND owner = sys_context('userenv','current_schema');
 
           VALUE
----------------
  16,725,196,800

SQL> SELECT blocks*block_size AS value 2 FROM dba_tables JOIN dba_tablespaces USING (tablespace_name) 3 WHERE table_name = 'ORDERS' 4 AND owner = sys_context('userenv','current_schema'); VALUE ---------------- 16,725,196,800

  • If the optimization technique is disabled (by default is enabled, but you can control it with the _cell_storidx_minmax_enabled undocumented initialization parameter), as the statistics show, the whole table is read (cell physical IO bytes saved by storage index = 0) and the min and max functions are evaluated by the database instance:
SQL> SELECT /*+ no_parallel */ min(order_total), max(order_total) FROM orders;
 
MIN(ORDER_TOTAL) MAX(ORDER_TOTAL)
---------------- ----------------
               0            21347
 
Elapsed: 00:00:06.24
 
SQL> SELECT name, value
  2  FROM v$mystat NATURAL JOIN v$statname
  3  WHERE name IN ('physical read total bytes',
  4                 'cell physical IO bytes eligible for predicate offload',
  5                 'cell physical IO interconnect bytes returned by smart scan',
  6                 'cell physical IO bytes saved by storage index');
 
NAME                                                                       VALUE
--------------------------------------------------------------- ----------------
physical read total bytes                                         16,725,196,800
cell physical IO bytes eligible for predicate offload             16,725,196,800
cell physical IO bytes saved by storage index                                  0
cell physical IO interconnect bytes returned by smart scan         1,779,074,848

SQL> SELECT /*+ no_parallel */ min(order_total), max(order_total) FROM orders; MIN(ORDER_TOTAL) MAX(ORDER_TOTAL) ---------------- ---------------- 0 21347 Elapsed: 00:00:06.24 SQL> SELECT name, value 2 FROM v$mystat NATURAL JOIN v$statname 3 WHERE name IN ('physical read total bytes', 4 'cell physical IO bytes eligible for predicate offload', 5 'cell physical IO interconnect bytes returned by smart scan', 6 'cell physical IO bytes saved by storage index'); NAME VALUE --------------------------------------------------------------- ---------------- physical read total bytes 16,725,196,800 cell physical IO bytes eligible for predicate offload 16,725,196,800 cell physical IO bytes saved by storage index 0 cell physical IO interconnect bytes returned by smart scan 1,779,074,848

  • If the optimization technique is enabled (which is the default), thanks to a storage index, the cells avoid reading the whole table and much less data is sent back to the database instance (cell physical IO interconnect bytes returned by smart scan is much lower):
SQL> SELECT /*+ no_parallel */ min(order_total), max(order_total) FROM orders;
 
MIN(ORDER_TOTAL) MAX(ORDER_TOTAL)
---------------- ----------------
               0            21347
 
Elapsed: 00:00:00.58
 
SQL> SELECT name, value
  2  FROM v$mystat NATURAL JOIN v$statname
  3  WHERE name IN ('physical read total bytes',
  4                 'cell physical IO bytes eligible for predicate offload',
  5                 'cell physical IO interconnect bytes returned by smart scan',
  6                 'cell physical IO bytes saved by storage index');
 
NAME                                                                       VALUE
--------------------------------------------------------------- ----------------
physical read total bytes                                         16,725,196,800
cell physical IO bytes eligible for predicate offload             16,725,196,800
cell physical IO bytes saved by storage index                     15,565,668,352
cell physical IO interconnect bytes returned by smart scan           123,454,448

SQL> SELECT /*+ no_parallel */ min(order_total), max(order_total) FROM orders; MIN(ORDER_TOTAL) MAX(ORDER_TOTAL) ---------------- ---------------- 0 21347 Elapsed: 00:00:00.58 SQL> SELECT name, value 2 FROM v$mystat NATURAL JOIN v$statname 3 WHERE name IN ('physical read total bytes', 4 'cell physical IO bytes eligible for predicate offload', 5 'cell physical IO interconnect bytes returned by smart scan', 6 'cell physical IO bytes saved by storage index'); NAME VALUE --------------------------------------------------------------- ---------------- physical read total bytes 16,725,196,800 cell physical IO bytes eligible for predicate offload 16,725,196,800 cell physical IO bytes saved by storage index 15,565,668,352 cell physical IO interconnect bytes returned by smart scan 123,454,448

Note that even though the query I used for the test contains both the min and the max function, the optimization technique takes place. In other words, there is no limitation similar to the one just discussed with B-tree indexes.

12.1.0.2, Exadata, Indexes
Restrict the query optimizer through OPTIMIZER_FEATURES_ENABLE and Top-Frequency/Hybrid Histograms
Ad: Trivadis DK Opening Event

No Comments Yet

1 Ping/Trackback

  1. Oracle Exadata Performance: Latest Improvements and Less Known Features | Tanel Poder's Performance & Troubleshooting blog on 24 March 2015 at 14:59

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.