You can monitor the
database??™s V$SYSSTAT view (or the Automatic Workload Repository) for increasing values of the
???table fetch continued row??? action; these indicate the need for the database to access multiple
blocks for a single row.
NOTE
When rows are moved due to inadequate space in the pctfree area,
the move is called a row migration. Row migration will impact the
performance of your transactions.
The DBMS_STATS procedure, while powerful, does not collect statistics on chained rows. You
can still use the analyze command, which is otherwise deprecated in favor of DBMS_STATS, to
reveal chained rows, as in this example:
analyze table employees list chained rows;
NOTE
For indexes that will support a large number of inserts, pctfree may
need to be as high as 50 percent.
Reverse Key Indexes
In a reverse key index, the values are stored backward??”for example, a value of 2201 is stored as
1022. If you use a standard index, consecutive values are stored near each other. In a reverse key
index, consecutive values are not stored near each other. If your queries do not commonly perform
range scans and you are concerned about I/O contention (in a RAC database environment) or
concurrency contention (buffer busy waits statistic in ADDM) in your indexes, reverse key indexes
may be a tuning solution to consider. When sizing a reverse key index, follow the same method
used to size a standard index, as shown in the prior sections of this chapter.
Pages:
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273