Oracle SYS_OP_LBID
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
 
SYS_OP_LBID
Leaf Block ID Scanning /* Has many functional variations, but the point is to scan through index leaf blocks and calculate a measure of the quality of the index. The way it is counted, and the thing being counted, depends on the type of index (viz: bitmap, simple b-tree, local/global index, IOT, or secondary on IOT, cluster). */

SYS_OP_LBID(<object_id>, <block_type>, <table_name.rowid>);
conn uwclass/uwclass

CREATE INDEX ix_serv_inst
ON serv_inst(srvr_id);

SELECT object_id
FROM user_objects
WHERE object_name = 'IX_SERV_INST';

SELECT rows_per_block, count(*) blocks
FROM (
  SELECT /*+ cursor_sharing_exact
             dynamic_sampling(0)
             no_monitoring
             no_expand
             index_ffs(serv_inst,ix_serv_inst)
             noparallel_index(serv_inst,ix_serv_inst)
         */
    sys_op_lbid(89996, 'L', serv_inst.rowid) block_id,
    COUNT(*) rows_per_block
  FROM serv_inst -- t1 sample block (100)
  WHERE srvr_id IS NOT NULL
  GROUP BY sys_op_lbid(89996, 'L', serv_inst.rowid))
GROUP BY rows_per_block;

drop index ix_serv_inst;
 
Working Production Code
A truly staggering amount of time, effort, and resources are expended regularly in a lot of shops rebuilding indexes ... needlessly.

I decided to put a stop to this in an application I was building and coded a procedure that  applies math, not myth, to the problem.

The limits here, 10% and 75% were chosen at random for this demo. You should adjust these to be realistic for your application based on testing. The code also, on the advise of Richard Foote, coalesces rather than rebuilds due to the lower cost and lack of locking.

If you have partitioned tables you can modify the code to look at the partition or subpartition level.
CREATE TABLE idx_maint_hist (
schema_name        VARCHAR2(30),
index_name         VARCHAR2(30),
coalesce_or_rbld   VARCHAR2(1) DEFAULT 'C', -- by default coalesce rather than rebuild
ignore_flag        VARCHAR2(1) DEFAULT 'N', -- by default do not ignore an index
run_date           DATE,
clust_factor       NUMBER,
tab_blks           NUMBER(38),
blks_bef_rbld      NUMBER(38),
blks_aft_rbld      NUMBER(38),
blks_prv_rbld      NUMBER(38),
max_rows_per_lb    NUMBER(10),
leaf_blks_lt75_pct NUMBER(10),
rebuilt            VARCHAR2(1));

ALTER TABLE idx_maint_hist
ADD CONSTRAINT cc_idx_maint_hist_coal_or_rbld
CHECK (coalesce_or_rbld IN ('C','R'));

ALTER TABLE idx_maint_hist
ADD CONSTRAINT cc_idx_maint_hist_ignore_flag
CHECK (ignore_flag IN ('N','Y'));


CREATE OR REPLACE PACKAGE index_util AUTHID CURRENT_USER IS
/*******************************************************
This code is protected under applicable copyright law and may be
freely used and distributed as long as in so doing it remains free. Any
modifications to this code do not constitute a waiver of this restriction.

Copyright (c) 2009 Daniel A. Morgan
Made Available Pursuant To GNU General Public License 2'
*******************************************************/
  PROCEDURE rebuild_indexes;
END index_util;
/

CREATE OR REPLACE PACKAGE BODY index_util IS
/*******************************************************
This code is protected under applicable copyright law and may be
freely used and distributed as long as in so doing it remains free. Any
modifications to this code do not constitute a waiver of this restriction.

Copyright (c) 2009 Daniel A. Morgan
Made Available Pursuant To GNU General Public License 2'
*******************************************************/
---------------------------------------------------
-- private function: returns 0 (zero) if the coalesce produced errors

FUNCTION rebuildNPIndexes(
 pIndexName IN VARCHAR2,
 pType      IN VARCHAR2 DEFAULT 'C',
 pDegree    IN POSITIVE DEFAULT 1)
RETURN NATURAL IS

 vOutVal     NATURAL;
 vRetVal     NATURAL := 0; -- blocks after coalesce
 eRebldError EXCEPTION;
BEGIN
  IF pType = 'C' THEN
    EXECUTE IMMEDIATE 'ALTER INDEX ' || pIndexName || ' COALESCE';
  ELSE
    dbms_index_utl.build_indexes(list => pIndexName, just_unusable => FALSE,
    max_slaves => pDegree, num_errors => vOutVal);
  END IF;

  -- if no errors
  IF vOutVal = 0 THEN
    SELECT ui.leaf_blocks
    INTO vRetVal
    FROM user_indexes ui
    WHERE ui.index_name = pIndexName;
  ELSE
    RAISE eRebldError;
  END IF;

  RETURN vRetVal;
EXCEPTION
  WHEN others THEN
    RETURN vRetVal;
END rebuildNPIndexes;
---------------------------------------------------
PROCEDURE rebuild_indexes IS
 vMinBlkCnt CONSTANT NUMBER := 2;      -- minimum blocks in table to consider
 vBatchSize CONSTANT POSITIVE := 2000; -- bulk collect limit clause
 vDegree    CONSTANT POSITIVE := 2;    -- degree of parallelism for index rebuild
 vDivLine   CONSTANT NUMBER := 0.75;   -- pct above which blocks are considered full
 vActLine   CONSTANT NUMBER := 0.10;   -- pct above which it is ok to coalesce or rebuild
 vMinBytes  CONSTANT NUMBER := 0;      -- usage bytes must exceed this number or ignore
 vMinIOCost CONSTANT NUMBER := 0;      -- i/o cost must exceed this number or ignore
 vIdxObjID  user_objects.object_id%TYPE;
 vIdxTable  user_indexes.table_name%TYPE;
 vSqlStr    VARCHAR2(512);

 TYPE idxAarray IS TABLE OF idx_maint_hist%ROWTYPE;
 l_data idxAarray;

 CURSOR icur IS
 WITH imh AS (SELECT *
            FROM idx_maint_hist imh1
            WHERE imh1.schema_name(+) = USER
            AND imh1.ignore_flag = 'N'
            AND imh1.run_date = (
              SELECT MAX(imh2.run_date)
              FROM idx_maint_hist imh2
              WHERE imh2.schema_name = USER)),
      vsp AS (SELECT v.object_name, SUM(bytes), sum(io_cost)
            FROM v$sql_plan v
            WHERE object_owner = USER
            AND object_type LIKE '%INDEX%'
            GROUP BY v.object_name
            HAVING SUM(bytes) > vMinBytes
            AND SUM(io_cost) > vMinIOCost)
 SELECT user SCHEMA_NAME, ui.index_name,
 imh.coalesce_or_rbld, imh.ignore_flag, imh.run_date,
 CAST(NULL AS NUMBER) clust_factor,
 CAST(NULL AS NUMBER(38)) tab_blks,
 CAST(NULL AS NUMBER(38)) blks_bef_rbld,
 CAST(NULL AS NUMBER(38)) blks_aft_rbld,
 imh.blks_aft_rbld blks_prv_rbld,       -- blocks after the last become previous
 CAST(NULL AS NUMBER(38)) max_rows_per_lb,
 CAST(NULL AS NUMBER(10)) leaf_blks_lt75_pct,
 'N' rebuilt
 FROM imh, vsp, user_indexes ui
 WHERE ui.index_name = vsp.object_name  -- if the index is not used ignore it
 AND ui.index_name = imh.index_name(+)
 AND ui.leaf_blocks >= vMinBlkCnt;      -- ignore indexes with too few leaf blocks
BEGIN
  dbms_output.put_line('Copyright (c) 2009-2010 Daniel A. Morgan');
  dbms_output.put_line('Made Available Pursuant To GNU General Public License 2');
  dbms_application_info.set_module('index_util','index_rebuild');

  OPEN icur;
  LOOP
    FETCH icur BULK COLLECT INTO l_data LIMIT vBatchSize;

    FOR i IN 1 .. l_data.COUNT LOOP
      -- gather the number of table blocks
      SELECT ut.blocks
      INTO l_data(i).tab_blks
      FROM user_tables ut
      WHERE ut.table_name IN (
        SELECT ui.table_name
        FROM user_indexes ui
        WHERE ui.index_name = l_data(i).index_name);

      -- gather index blocks and clustering factor stats
      SELECT ui.clustering_factor, ui.leaf_blocks, table_name
      INTO l_data(i).clust_factor, l_data(i).blks_bef_rbld, vIdxTable
      FROM user_indexes ui
      WHERE ui.index_name = l_data(i).index_name;

      -- gathering leaf block stats requires the object_id
      SELECT MIN(uo.object_id)
      INTO vIdxObjID
      FROM user_objects uo
      WHERE uo.object_name = l_data(i).index_name;

      -- get the maximum number of rows per leaf block
      BEGIN
        vSqlStr :=
        'SELECT MAX(rows_per_block) maxblocks FROM (SELECT /*+ cursor_sharing_exact ' ||
        'dynamic_sampling(0) no_monitoring no_expand index_ffs(' || vIdxTable ||
        ',' || l_data(i).index_name || ') noparallel_index(' || vIdxTable ||
        ',' || l_data(i).index_name || ') */ sys_op_lbid(' || vIdxObjID ||
        ', ''L'', ' || vIdxTable || '.rowid) block_id, ' ||
        'COUNT(*) rows_per_block FROM ' || vIdxTable || ' GROUP BY sys_op_lbid(' ||
        vIdxObjID || ', ''L'', ' || vIdxTable || '.rowid))';

        execute immediate vSqlStr INTO l_data(i).max_rows_per_lb;
      EXCEPTION
        WHEN OTHERS THEN
          l_data(i).max_rows_per_lb := NULL;
      END;

      -- get the number of blocks below the tunable vDivLine threshhold
      BEGIN
        vSqlStr :=
        'SELECT COUNT(rows_per_block) FROM (SELECT /*+ cursor_sharing_exact ' |
        'dynamic_sampling(0) no_monitoring no_expand index_ffs(' || vIdxTable ||
        ',' || l_data(i).index_name || ') noparallel_index(' || vIdxTable ||
        ',' || l_data(i).index_name || ') */ sys_op_lbid(' || vIdxObjID ||
        ', ''L'', ' || vIdxTable || '.rowid) block_id, ' ||
        'COUNT(*) rows_per_block FROM ' || vIdxTable || ' GROUP BY sys_op_lbid(' ||
        vIdxObjID || ', ''L'', ' || vIdxTable || '.rowid))' ||
        ' WHERE rows_per_block < ' || l_data(i).max_rows_per_lb * vDivLine;

        execute immediate vSqlStr INTO l_data(i).leaf_blks_lt75_pct;
      EXCEPTION
        WHEN OTHERS THEN
          l_data(i).leaf_blks_lt75_pct := NULL;
      END;

      -- if the number of unfilled blocks is greater than vMinBlkCnt,
      -- and the number of leaf blocks that are less than vActLine full
      -- exceeds 25% of the index leaf blocks, and a rebuild is
      -- allowed then rebuild the index

      IF (l_data(i).leaf_blks_lt75_pct > vMinBlkCnt)
      AND (l_data(i).leaf_blks_lt75_pct >= (l_data(i).blks_bef_rbld * vActLine)) THEN
        -- the return value is the number of blocks following rebuild
        -- uncomment next 2 lines after testing. Once indexes are rebuilt
        -- it is not easy to make them bad again without flashback database


        IF l_data(i).coalesce_or_rbld IS NULL THEN
          l_data(i).coalesce_or_rbld := 'C';
        END IF;

        IF l_data(i).ignore_flag IS NULL THEN
          l_data(i).ignore_flag := 'N';
        END IF;

--        l_data(i).blks_aft_rbld := rebuildNPIndexes(l_data(i).schema_name || '.' ||
--        l_data(i).index_name, l_data(i).coalesce_or_rbld, vDegree);
        l_data(i).rebuilt := 'Y';
      ELSE
        l_data(i).blks_aft_rbld := l_data(i).blks_bef_rbld;
      END IF;
    END LOOP;

    FORALL j IN 1..l_data.COUNT
    INSERT INTO idx_maint_hist VALUES l_data(j);

    EXIT WHEN l_data.COUNT < vBatchSize;
  END LOOP;
  COMMIT;
  CLOSE icur;

  dbms_application_info.set_module(NULL, NULL);
EXCEPTION
  WHEN OTHERS THEN
    dbms_application_info.set_module(NULL, NULL);
    RAISE;
END rebuild_indexes;
---------------------------------------------------
END index_util;
/

TRUNCATE TABLE idx_maint_hist;

exec index_util.rebuild_indexes;

SELECT index_name, run_date, clust_factor, tab_blks, blks_bef_rbld, rebuilt
FROM idx_maint_hist
WHERE schema_name = USER
ORDER BY 1;

SELECT index_name, blks_bef_rbld, blks_aft_rbld, blks_prv_rbld, max_rows_per_lb, leaf_blks_lt75_pct
FROM idx_maint_hist
WHERE schema_name = USER
ORDER BY 1;

Related Topics
Functions
Indexes
Undocumented Oracle

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved