High-Performance Oracle Database Applications:Oracle DBA Performance And Tuning

Table of Contents

Of course, this script could be extended to feed the SQL statements into Oracle’s explain plan utility, where long-table full-table scans could be detected and indexes created for these queries.

Note: Oracle’s bstat/estat utility also provides information on the number of full-table scans incurred by the database as a whole. Please see Chapter 11 for more information.

Part of the job of the Oracle DBA is to detect badly out-of-balance indexes and schedule their reconstruction. Unfortunately, very dynamic tables (i.e., those with high INSERT and UPDATE activity) may always have some issues with out-of-balance indexes. As Oracle indexes grow, two things happen. The first is called a split, where a new node is created at the same index level as the existing node. As each level becomes full, the index may spawn, or create a new level to accommodate the new rows.

There are six Oracle dictionary values that are used to describe indexes:

• BLEVEL—This is the number of levels that the index has spawned. Even for very large indexes, there should never be more than four levels. Each BLEVEL represents an additional I/O that must be performed against the index tree.

• LEAF_BLOCKS—This is a reference to the total number of leaf blocks.

• DISTINCT_KEYS—This is a reference to the cardinality of the index. If this value is less than 10, you may want to consider redefining the index as a bitmapped index.

• AVG_DATA_BLOCKS_PER_KEY—This is a measure of the size of the index and the cardinality of the index. A low cardinality index (e.g., sex or region) will have high values, as will very large indexes.

• CLUSTERING_FACTOR—This is the most important measure in this report, since it measures how balanced the index is. If the clustering factor is greater than the number of blocks in the index, then the index has become out of balance due to a large volume of insert or delete operations. If the clustering factor is more than 50 percent of the number of rows in the table that it is indexing, you may want to consider dropping and re-creating the index.

• AVG_LEAF_BLOCKS_PER_KEY—This is always one, with the exception of non-unique indexes.

Now that we understand the basic constructs of indexes, let’s look at a dictionary query (Listing 6.3) that will tell us the structure of our indexes. Note that this query assumes that your Oracle database is using the cost-based optimizer, and that your tables have been analyzed with the ANALYZE TABLE command.

Listing 6.3 index.sql shows the details for indexes.

SET PAGESIZE 999;
SET LINESIZE 100;

COLUMN c1 HEADING 'Index'      FORMAT a19;
COLUMN c3 HEADING 'S'          FORMAT a1;
COLUMN c4 HEADING 'Level'      FORMAT 999;
COLUMN c5 HEADING 'leaf blks'  FORMAT 999,999;
COLUMN c6 HEADING 'dist. Keys' FORMAT 99,999,999;
COLUMN c7 HEADING 'Bks/Key'    FORMAT 99,999;
COLUMN c8 HEADING 'Clust Ftr'  FORMAT 9,999,999;
COLUMN c9 HEADING 'Lf/Key'     FORMAT 99,999;
SPOOL index.lst;

SELECT
  owner||'.'||index_name      c1,
  substr(status,1,1)          c3,
  blevel                      c4,
  leaf_blocks                 c5,
  distinct_keys               c6,
  avg_data_blocks_per_key     c7,
  clustering_factor           c8,
  avg_leaf_blocks_per_key     c9
FROM dba_indexes
WHERE
OWNER NOT IN ('SYS','SYSTEM')
ORDER BY blevel desc, leaf_blocks desc;

SPOOL OFF;

Listing 6.4 shows the output of index.sql.

Listing 6.4 The output of index.sql.

                              Leaf    dist.      Bks/     Clust     Lf/
Index                S Level  Blks    Keys       Key      Ftr       Key
-------------------  - -----  ------  ---------  ------   --------  -----
RPT.LOB_SHPMT_PK     V   2    25,816  3,511,938       1    455,343      1

RPT.SHP_EK_CUST_INV  V   2    23,977  2,544,132       1  1,764,915      1

RPT.SHP_FK_GLO_DEST  V   2    23,944     22,186     112  2,493,095      1

RPT.LSH_FK_SHP       V   2    22,650  1,661,576       1    339,031      1

RPT.SHP_FK_ORL_ORIG  V   2    21,449        404     806    325,675     53

RPT.LSA_FK_LSH       V   2    21,181  2,347,812       1    996,641      1

RPT.LSH_FK_LOB       V   2    19,989        187   4,796    896,870    106

RPT.SHPMT_PK         V   2    19,716  3,098,063       1  1,674,264      1

RPT.SHP_FK_CAR       V   2    18,513        689     390    268,859     26

RPT.SHP_EK_ROLE_TY_  V   2    17,847         10  24,613    246,134  1,784

RPT.SHP_FK_SPT       V   2    16,442          4  46,872    187,489  4,110

RPT.INV_EK_INV_NUM   V   2    16,407  2,014,268       1    518,206      1

RPT.SHP_FK_ORL_DEST  V   2    15,863        385     692    266,656     41

RPT.SHP_FK_SRC       V   2    15,827         10  17,469    174,694  1,582

RPT.INV_LINE_ITEM_P  V   2    14,731  2,362,216       1    102,226      1

Here we see that we have no indexes that are more than two levels deep. Note also that we have two indexes with a clustering factor of more than 1 million. Should either of these tables have less than 3 million rows (clustering factor greater than 50 percent of rows), we will want to schedule a time to drop and re-create the index.

Bitmapped Indexes

Prior to release 7.3 of Oracle, it was never recommended that the DBA create an index on any fields that were not “selective” and had less than 50 unique values. Imagine, for example, how a traditional B-tree index would appear if a column such as REGION were indexed. With only four distinct values in the index, the SQL optimizer would rarely determine that an index scan would speed up a query; consequently, the index would never be accessed. Of course, the only alternative would be to invoke a costly full-table scan of the table. Today, we are able to use bitmapped indexes for low cardinality indexes.

It is interesting to note that bitmapped indexes have been used in commercial databases since Model 204 was introduced in the late 1960s. However, their usefulness had been ignored until the data warehouse explosion of 1994 made it evident that a new approach to indexing was needed to resolve complex queries against very large tables.

Bitmapped indexes are a new feature of Oracle 7.3 that allow for very fast boolean operations against low cardinality indexes. Complex AND and OR logic is performed entirely within the index—the base table need never be accessed. Without a bitmapped index, some decision support queries would be impossible to service without a full-table scan.

Table of Contents