One of the problems in Oracle9i was the single bit-flag that was used to monitor index usage.  The flag can be set with the alter index xxx monitoring usage command, and see if the index was accessed by querying the v$object_usage view. 

The goal of any index access is to use the most selective index for a query.  This would be the one that produces the smallest number of rows.  The Oracle data dictionary is usually quite good at this, but it is up to the DBA to define the index.  Missing function-based indexes are a common source of suboptimal SQL execution because Oracle will not use an indexed column unless the WHERE clause matches the index column exactly.

Tracking SQL nested loop joins

As a review, nested loop joins are the most common method for Oracle to match rows in multiple tables.  Nested loop joins always invoke an index and they are never parallelized.  The following awr_nested_join_alert. sql script to count nested loop joins per hour (See book errata for details):

Note:  These scripts will only track SQL that you have directed Oracle to capture via your threshold settings in AWR or STATSPACK, and STATSPACK and AWR will not collect "transient SQL" that did not appear in v$sql at snapshot time.  Hence, not all SQL will appear in these reports.  See my notes here on adjusting the SQL capture thresholds and what SQL is included in AWR/STATSPACK tables?

The output below shows the number of total nested loop joins during the snapshot period along with a count of the rows processed and the associated disk I/O.  This report is useful where the DBA wants to know if increasing pga_aggregate_target  will improve performance.

               Nested Loop Join Thresholds

              Nested

               Loops        Rows        Disk         CPU

Date           Count   Processed       Reads        Time

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

04-10-10 16       22         750         796   4,017,301

04-10-10 17       25         846           6   3,903,560

04-10-10 19       26         751       1,430   4,165,270

04-10-10 20       24         920           3   3,940,002

04-10-10 21       25         782           5   3,816,152

04-10-11 02       26         905           0   3,935,547

04-10-11 03       22       1,001           0   3,918,891

04-10-11 04       29         757           8   3,939,071

04-10-11 05       28         757         745   4,395,197

04-10-11 06       24         839           4   4,010,775

In the report above, nested loops are favored by SQL that returns a small number of rows_processed than hash joins, which tend to return largest result sets.

This is a STATSPACK script that will track nested loops joins over time:

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts: