Index B-Tree Level Analysis (qindblev.sql)

What This Script Does

This script analyzes the B-tree level (BLEVEL) of database indexes to identify fragmented or inefficient indexes that may benefit from rebuilding. The B-tree level indicates the depth of the index structure - higher levels suggest more I/O operations are needed to locate data, which can impact query performance. The script categorizes indexes based on their B-tree levels and identifies those needing attention.

The Script

ttitle off
rem
ttitle 'Index B level report'
rem
set linesize 132
set pause on
define Owner='&owner'
define index_name='&index_name'
define statson='&statson'
column index_name format a33 wrap head "Index_Name"
column table_name format a33 wrap head "Table_Name"
column index_type format a10     wrap head "Index_Type"
column LAST_ANALYZED format a15     wrap head "Analyzed_Date"
column blev  format 99     wrap head "Index_level"
column ok  format a15     wrap head  "Index_State"

break on report  table_name
select table_name, index_name,index_type, LAST_ANALYZED,nvl(blevel,-1) blev,
        decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',
                                  2,'OK BLEVEL',3,'OK BLEVEL',
                                  4,'OK BLEVEL',null,'No Stats', 'BLEVEL HIGH') OK
               from dba_indexes
        where owner  like nvl(upper('&owner'),'%')
        and       index_name  like nvl(upper('&index_name'),'%')
         and      (blevel is not null or  (blevel is null and upper( '&statson')  = 'Y'))
         ORDER BY BLEV DESC,table_name,index_type,index_name;

 undefine Owner
 undefine index_name
 undefine statson

Key Features

B-Tree Level Assessment: Analyzes index depth and structure efficiency
Performance Classification: Categorizes indexes as OK, needs attention, or problematic
Flexible Filtering: Filter by owner, index name, and statistics status
Statistics Validation: Shows indexes with and without current statistics
Maintenance Prioritization: Orders results by B-tree level for maintenance planning
Comprehensive Coverage: Analyzes all index types (B-tree, bitmap, function-based)

Usage

@qindblev.sql

Input Parameters:

owner: Schema owner name or pattern (press Enter for all)
index_name: Index name or pattern (press Enter for all)
statson: Show indexes without statistics? (Y/N)

Required Privileges

SELECT on DBA_INDEXES

Sample Output

                                             Index B level report

Table_Name                        Index_Name                        Index_Type Analyzed_Date   Index_level Index_State
--------------------------------- --------------------------------- ---------- --------------- ----------- ---------------
ORDERS                            ORD_CUST_DATE_IDX                 NORMAL     15-JAN-24              5 BLEVEL HIGH
CUSTOMERS                         CUST_EMAIL_IDX                    NORMAL     14-JAN-24              5 BLEVEL HIGH
PRODUCTS                          PROD_CAT_SUBCAT_IDX               NORMAL     13-JAN-24              4 OK BLEVEL
EMPLOYEES                         EMP_DEPT_MANAGER_IDX              NORMAL     16-JAN-24              4 OK BLEVEL
ORDER_ITEMS                       ORDITEM_PROD_IDX                  NORMAL     15-JAN-24              3 OK BLEVEL
SALES_HISTORY                     SALES_REGION_IDX                  BITMAP     12-JAN-24              3 OK BLEVEL
CUSTOMERS                         CUST_PHONE_IDX                    NORMAL     14-JAN-24              2 OK BLEVEL
EMPLOYEES                         EMP_SALARY_IDX                    NORMAL     16-JAN-24              2 OK BLEVEL
PRODUCTS                          PROD_SKU_IDX                      NORMAL     13-JAN-24              1 OK BLEVEL
DEPARTMENTS                       DEPT_NAME_IDX                     NORMAL     17-JAN-24              1 OK BLEVEL
REGIONS                           REG_CODE_IDX                      NORMAL     17-JAN-24              0 OK BLEVEL
STATUS_CODES                      STATUS_LOOKUP_IDX                 NORMAL     17-JAN-24              -1 No Stats

Key Output Columns

Table_Name: Name of the table the index belongs to
Index_Name: Name of the index
Index_Type: Type of index (NORMAL, BITMAP, FUNCTION-BASED, etc.)
Analyzed_Date: Date when index statistics were last gathered
Index_level: B-tree level (depth) of the index
Index_State: Assessment of index efficiency based on B-tree level

Understanding B-Tree Levels

B-Tree Level Interpretation

Optimal Levels (0-4)

-- Level 0-1: Excellent performance
--   - Single or double level access
--   - Minimal I/O operations
--   - Very efficient for small to medium tables

-- Level 2-3: Good performance
--   - Normal for medium to large tables
--   - Acceptable I/O overhead
--   - Standard production levels

-- Level 4: Acceptable performance
--   - Higher but still reasonable
--   - May indicate growth or fragmentation
--   - Monitor for further increases

Problematic Levels (5+)

-- Level 5+: Performance concern
--   - Excessive I/O operations
--   - Potential performance impact
--   - Consider rebuild or reorganization
--   - May indicate significant fragmentation

Index Performance Impact

I/O Operations

-- I/O impact by level:
-- Level 1: 2 I/O operations (root + leaf)
-- Level 2: 3 I/O operations (root + branch + leaf)
-- Level 3: 4 I/O operations (root + 2 branches + leaf)
-- Level 5: 6 I/O operations (significant overhead)

Query Performance

-- Performance degradation:
-- Higher levels = more block reads
-- Increased response time
-- Higher CPU and I/O utilization
-- Reduced concurrent user capacity

Common Use Cases

Index Maintenance Planning
- Identify indexes requiring rebuild
- Prioritize maintenance activities
- Plan maintenance windows
- Schedule proactive index optimization
Performance Troubleshooting
- Diagnose slow query performance
- Identify fragmented indexes
- Correlate index efficiency with response times
- Support query optimization efforts
Capacity Planning
- Monitor index growth patterns
- Assess storage requirements
- Plan infrastructure capacity
- Predict maintenance needs
Database Health Monitoring
- Regular index health assessments
- Proactive maintenance identification
- Performance degradation prevention
- System optimization planning

Advanced Analysis

Index Fragmentation Assessment

Fragmentation Indicators

-- High B-tree levels may indicate:
-- Index key deletion patterns
-- Unbalanced data distribution
-- Poor index key selection
-- Physical storage fragmentation

Growth Pattern Analysis

-- Analyze growth patterns:
-- Compare current vs. historical levels
-- Identify rapidly degrading indexes
-- Correlate with data loading patterns
-- Plan preventive maintenance

Performance Correlation

Query Performance Impact

-- Correlate B-tree levels with:
-- Query execution times
-- I/O statistics
-- CPU utilization
-- User response times

System Resource Usage

-- Resource impact assessment:
-- Buffer pool utilization
-- I/O subsystem load
-- CPU processing overhead
-- Concurrent user capacity

Filtering Examples

Schema-Specific Analysis

-- Analyze specific schema:
Enter value for owner: SALES
Enter value for index_name: %
Enter value for statson: N

Problem Index Identification

-- Find problematic indexes:
Enter value for owner: %
Enter value for index_name: %
Enter value for statson: N
-- Look for BLEVEL HIGH in results

Index Pattern Analysis

-- Analyze specific index patterns:
Enter value for owner: %
Enter value for index_name: %CUST%
Enter value for statson: Y

Statistics Coverage Review

-- Include indexes without statistics:
Enter value for owner: %
Enter value for index_name: %
Enter value for statson: Y

Index Rebuild Decision Matrix

Rebuild Recommendations

High Priority (Level 5+)

-- Immediate rebuild candidates:
-- B-tree level 5 or higher
-- Frequently accessed indexes
-- Performance-critical applications
-- Production environment impact

Medium Priority (Level 4)

-- Monitor and plan rebuild:
-- B-tree level 4
-- Moderate access frequency
-- Development/test environments
-- Schedule during maintenance windows

Low Priority (Level 0-3)

-- Normal maintenance cycle:
-- B-tree levels 0-3
-- Include in regular maintenance
-- Monitor for changes
-- No immediate action required

Rebuild Planning

Resource Requirements

-- Plan rebuild resources:
-- Disk space (2-3x index size)
-- CPU and I/O capacity
-- Memory allocation
-- Maintenance window duration

Impact Assessment

-- Assess rebuild impact:
-- Application downtime requirements
-- User access restrictions
-- Concurrent operation effects
-- Validation procedures

Index Optimization Strategies

Preventive Measures

Index Design

-- Optimize index design:
-- Choose selective leading columns
-- Avoid unnecessary composite indexes
-- Consider index compression
-- Plan for data growth patterns

Maintenance Scheduling

-- Implement proactive maintenance:
-- Regular B-tree level monitoring
-- Scheduled rebuild cycles
-- Automated alert thresholds
-- Performance trend analysis

Rebuild Alternatives

Online Rebuilds

-- Use online rebuild options:
-- ALTER INDEX REBUILD ONLINE
-- Minimize application impact
-- Monitor resource utilization
-- Plan for longer completion times

Coalesce Operations

-- Consider coalesce as alternative:
-- ALTER INDEX COALESCE
-- Less resource intensive
-- Reduces fragmentation
-- Maintains index availability

Automation and Monitoring

Automated Monitoring

Threshold Alerts

-- Set up automated alerts:
-- B-tree level threshold exceeded
-- Fragmentation percentage limits
-- Performance degradation detection
-- Maintenance schedule compliance

Trend Analysis

-- Monitor trends:
-- B-tree level changes over time
-- Index growth patterns
-- Performance correlation
-- Maintenance effectiveness

Integration with Tools

Database Monitoring

-- Integrate with monitoring tools:
-- Enterprise Manager integration
-- Custom dashboard creation
-- Alert notification systems
-- Performance baseline comparison

Maintenance Automation

-- Automate maintenance tasks:
-- Scheduled rebuild scripts
-- Conditional rebuild logic
-- Resource utilization monitoring
-- Success/failure reporting

Best Practices

Regular Monitoring

-- Establish monitoring routine:
-- Weekly B-tree level assessments
-- Monthly trend analysis
-- Quarterly comprehensive reviews
-- Annual strategy evaluations

Maintenance Planning

-- Plan effective maintenance:
-- Prioritize by business impact
-- Schedule during off-peak hours
-- Coordinate with application teams
-- Validate post-rebuild performance

Documentation

-- Maintain documentation:
-- Index maintenance history
-- Performance improvement metrics
-- Rebuild decision criteria
-- Lesson learned documentation

dindex.sql - Comprehensive index analysis
dindstats.sql - Index statistics analysis
dlowcardind.sql - Low cardinality index analysis
rebuild_index.sql - Generate index rebuild statements

Performance Validation

Pre-Rebuild Metrics

-- Capture before metrics:
-- Query response times
-- I/O statistics
-- CPU utilization
-- Index scan efficiency

Post-Rebuild Validation

-- Validate improvements:
-- B-tree level reduction
-- Performance improvement
-- Resource utilization changes
-- User experience enhancement

Long-term Monitoring

-- Monitor long-term effectiveness:
-- Maintenance frequency requirements
-- Performance sustainability
-- Growth pattern changes
-- Optimization strategy adjustments

Summary

This script is essential for:

Index Maintenance - Identifying indexes requiring rebuild or optimization
Performance Optimization - Maintaining optimal index efficiency and query performance
Capacity Planning - Understanding index growth patterns and resource requirements
Proactive Management - Preventing performance degradation through regular monitoring
Resource Optimization - Prioritizing maintenance activities based on business impact