Database Reference
In-Depth Information
also have the
pct_direct_access
column). The value provides the percentage of correct guess for a specific index.
The following example, which is an excerpt of the
iot_guess.sql
script, shows not only the impact of stale guesses
on the number of logical reads but also how to rectify this type of suboptimal situation (note that the index used in the
example is a secondary index):
SQL> SELECT pct_direct_access
2 FROM user_indexes
3 WHERE table_name = 'T' AND index_name = 'I';
PCT_DIRECT_ACCESS
-----------------
76
SQL> SELECT count(pad) FROM t WHERE n > 0;
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 1496 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1496 |
|* 2 | INDEX UNIQUE SCAN| T_PK | 1 | 1000 |
1496
|
|* 3 | INDEX RANGE SCAN| I | 1 | 1000 | 6 |
---------------------------------------------------------------
2 - access("N">0)
3 - access("N">0)
SQL> ALTER INDEX i
UPDATE BLOCK REFERENCES
;
SQL> execute dbms_stats.gather_index_stats(ownname => user, indname => 'i')
SQL> SELECT pct_direct_access
2 FROM user_indexes
3 WHERE table_name = 'T' AND index_name = 'I';
PCT_DIRECT_ACCESS
-----------------
100
SQL> SELECT count(pad) FROM t WHERE n > 0;
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 1006 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1006 |
|* 2 | INDEX UNIQUE SCAN| T_PK | 1 | 1000 |
1006
|
|* 3 | INDEX RANGE SCAN| I | 1 | 1000 | 6 |
---------------------------------------------------------------
