Tuning Limbo: How Low Can You Go?

Every limbo boy and girl
All around the limbo world
Gonna do the limbo rock
All around the limbo clock.

Popular song.

An SQL query is only as efficient as the number of data blocks it needs to read in order to find the data; the fewer the better. Of course this depends on the access paths to the data (e.g. indexes) and the execution plan chosen by the optimizer. In this essay, we consider a typical SQL query and try our best to reduce the number of blocks that we need to read. All options are on the table, including indexes, statistics, and hints.

It Takes Two to Tango

Create two tables modeled after DBA_TABLES and DBA_INDEXES respectively; the first table contains information about tables and the second table contains information about indexes. The requirement is to print the details of all tables which have at least one bitmap index.

CREATE TABLE my_tables AS

SELECT all_tables.*

  FROM all_tables;

CREATE TABLE my_indexes AS

SELECT all_indexes.*

  FROM all_tables JOIN all_indexes

    ON all_tables.owner = all_indexes.table_owner

   AND all_tables.table_name = all_indexes.table_name;

Note that you will need SELECT ANY DICTIONARY or DBA privilege to retrieve data from DBA_TABLES and DBA_INDEXES; you may need to get help from your database administrator or to create your own Oracle database on your PC or laptop for this exercise.

The First Solution

Here is a simple solution to the problem that is listed above.

SELECT DISTINCT t.owner,

                t.table_name,

                t.tablespace_name

           FROM my_tables t, my_indexes i

          WHERE t.owner = i.table_owner

            AND t.table_name = i.table_name

            AND i.index_type = 'BITMAP';

Testing the Solution

Let's execute the query twice; the first execution requires parsing overhead but the second execution will not have any. Five rows are retrieved each time and the second execution requires reads exactly 104 blocks. You can use DBMS_XPLAN to display the execution plan chosen by the optimizer.

SQL> SET autotrace on statistics

SQL> SET pagesize 80

SQL>

SQL> COLUMN owner format a20

SQL> COLUMN table_name format a20

SQL> COLUMN tablespace_name format a20

SQL>

SQL> SELECT DISTINCT t.owner,

  2                  t.table_name,

  3                  t.tablespace_name

  4             FROM my_tables t, my_indexes i

  5            WHERE t.owner = i.table_owner

  6              AND t.table_name = i.table_name

  7              AND i.index_type = 'BITMAP';

OWNER                TABLE_NAME           TABLESPACE_NAME

-------------------- -------------------- --------------------

SH                   FWEEK_PSCAT_SALES_MV EXAMPLE

SH                   PRODUCTS             EXAMPLE

SH                   CUSTOMERS            EXAMPLE

SH                   SALES

SH                   COSTS

Statistics

----------------------------------------------------------

        132  recursive calls

          0  db block gets

        222  consistent gets

         98  physical reads

          0  redo size

        645  bytes sent via SQL*Net to client

        381  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          5  rows processed

SQL> /

OWNER                TABLE_NAME           TABLESPACE_NAME

-------------------- -------------------- --------------------

SH                   FWEEK_PSCAT_SALES_MV EXAMPLE

SH                   PRODUCTS             EXAMPLE

SH                   CUSTOMERS            EXAMPLE

SH                   SALES

SH                   COSTS

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        104  consistent gets

          0  physical reads

          0  redo size

        645  bytes sent via SQL*Net to client

        381  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          5  rows processed

SQL> SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (NULL, NULL, 'BASIC LAST'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

EXPLAINED SQL STATEMENT:

------------------------

SELECT DISTINCT t.owner,                 t.table_name,

t.tablespace_name            FROM my_tables t, my_indexes i

WHERE t.owner = i.table_owner             AND t.table_name =

i.table_name             AND i.index_type = 'BITMAP'

Plan hash value: 457052432

------------------------------------------

| Id  | Operation           | Name       |

------------------------------------------

|   0 | SELECT STATEMENT    |            |

|   1 |  HASH UNIQUE        |            |

|   2 |   HASH JOIN         |            |

|   3 |    TABLE ACCESS FULL| MY_INDEXES |

|   4 |    TABLE ACCESS FULL| MY_TABLES  |

------------------------------------------

Submitting Your Solution

Send your solution to askdbspecialists@dbspecialists.com. The winner will receive their choice of an iPod Shuffle or a $50 Amazon gift certificate, mailed anywhere in the world. The contest ends on May 14, 2008 and the judge's decision is final!

The Second Solution

Dan Grant from California offered the following solution. He created a "materialized view" which stores the result of joining our two tables; he then created an index with index_type as the leading column. In the absence of parsing overhead, his solution requires the retrieval of just two blocks; a 98% reduction.

SQL> CREATE MATERIALIZED VIEW my_mv

  2  ENABLE QUERY REWRITE

  3  AS SELECT t.owner, t.table_name, t.tablespace_name, i.index_type, COUNT(*)

  4       FROM my_tables t, my_indexes i

  5      WHERE t.owner = i.table_owner

  6        AND t.table_name = i.table_name

  7     GROUP BY t.owner, t.table_name, t.tablespace_name, i.index_type;

Materialized view created.

SQL> ALTER SESSION SET query_rewrite_integrity=stale_tolerated;

Session altered.

SQL> CREATE INDEX my_mv_i ON my_mv(index_type, "COUNT(*)", owner, table_name, tablespace_name);

Index created.

SQL> SELECT   t.owner,

  2           t.table_name,

  3           t.tablespace_name

  4      FROM my_tables t, my_indexes i

  5     WHERE t.owner = i.table_owner

  6       AND t.table_name = i.table_name

  7       AND i.index_type = 'BITMAP'

  8  GROUP BY t.owner, t.table_name, t.tablespace_name

  9    HAVING COUNT (*) >= 1;

OWNER                TABLE_NAME           TABLESPACE_NAME

-------------------- -------------------- --------------------

SH                   FWEEK_PSCAT_SALES_MV EXAMPLE

SH                   PRODUCTS             EXAMPLE

SH                   CUSTOMERS            EXAMPLE

SH                   SALES

SH                   COSTS

Statistics

----------------------------------------------------------

         38  recursive calls

          0  db block gets

        129  consistent gets

          4  physical reads

          0  redo size

        645  bytes sent via SQL*Net to client

        381  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

          5  rows processed

SQL> /

OWNER                TABLE_NAME           TABLESPACE_NAME

-------------------- -------------------- --------------------

SH                   FWEEK_PSCAT_SALES_MV EXAMPLE

SH                   PRODUCTS             EXAMPLE

SH                   CUSTOMERS            EXAMPLE

SH                   SALES

SH                   COSTS

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        645  bytes sent via SQL*Net to client

        381  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          5  rows processed

SQL> SELECT *

  2    FROM TABLE (DBMS_XPLAN.display_cursor (NULL, NULL, 'BASIC LAST'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

EXPLAINED SQL STATEMENT:

------------------------

SELECT   t.owner,          t.table_name,          t.tablespace_name

FROM my_tables t, my_indexes i    WHERE t.owner = i.table_owner

AND t.table_name = i.table_name      AND i.index_type = 'BITMAP' GROUP

BY t.owner, t.table_name, t.tablespace_name   HAVING COUNT (*) >= 1

Plan hash value: 2580647153

--------------------------------------

| Id  | Operation          | Name    |

--------------------------------------

|   0 | SELECT STATEMENT   |         |

|   1 |  FILTER            |         |

|   2 |   HASH GROUP BY    |         |

|   3 |    INDEX RANGE SCAN| MY_MV_I |

--------------------------------------

The Third Solution

Mike Spalinger from Colorado offered the following solution. He stored the materialized view in a hash cluster; his solution requires the retrieval of just one block.

SQL> CREATE CLUSTER my_hash_cluster

2 (index_type VARCHAR2(27))

3 HASHKEYS 5;

Cluster created.

SQL>

SQL> CREATE MATERIALIZED VIEW my_mv

2 CLUSTER my_hash_cluster (index_type)

3 ENABLE QUERY REWRITE

4 AS SELECT t.owner, t.table_name, t.tablespace_name, i.index_type, COUNT(*) cnt

5 FROM my_tables t, my_indexes i

6 WHERE t.owner = i.table_owner

7 AND t.table_name = i.table_name

8 GROUP BY t.owner, t.table_name, t.tablespace_name, i.index_type;

Materialized view created.

SQL>

SQL> ALTER SESSION SET query_rewrite_integrity=stale_tolerated;

Session altered.

SQL> SET autotrace on statistics

SQL>

SQL> SELECT DISTINCT t.owner,

2 t.table_name,

3 t.tablespace_name

4 FROM my_tables t,

5 my_indexes i

6 WHERE t.owner = i.table_owner

7 AND t.table_name = i.table_name

8 AND i.index_type = 'BITMAP';

OWNER TABLE_NAME TABLESPACE_NAME

-------------------- -------------------- --------------------

SH FWEEK_PSCAT_SALES_MV EXAMPLE

SH PRODUCTS EXAMPLE

SH CUSTOMERS EXAMPLE

SH SALES

SH COSTS

Statistics

----------------------------------------------------------

27 recursive calls

0 db block gets

130 consistent gets

0 physical reads

0 redo size

645 bytes sent via SQL*Net to client

381 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

5 rows processed

SQL> /

OWNER TABLE_NAME TABLESPACE_NAME

-------------------- -------------------- --------------------

SH FWEEK_PSCAT_SALES_MV EXAMPLE

SH PRODUCTS EXAMPLE

SH CUSTOMERS EXAMPLE

SH SALES

SH COSTS

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1 consistent gets

0 physical reads

0 redo size

645 bytes sent via SQL*Net to client

381 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

5 rows processed

SQL>

SQL> SELECT *

2 FROM TABLE (DBMS_XPLAN.display_cursor (NULL, NULL, 'BASIC LAST'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

EXPLAINED SQL STATEMENT:

------------------------

SELECT DISTINCT t.owner, t.table_name,

t.tablespace_name FROM my_tables t,

my_indexes i WHERE t.owner = i.table_owner AND

t.table_name = i.table_name AND i.index_type = 'BITMAP'

Plan hash value: 1006555447

------------------------------------

| Id | Operation | Name |

------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | HASH UNIQUE | |

| 2 | TABLE ACCESS HASH| MY_MV |

------------------------------------