You can use the analyze command to collect statistics about database objects. The cost-based
optimizer can use these statistics to determine the best execution path to use. The analyze command
has an option that detects and records chained rows in tables. Its syntax is
analyze table TABLE_NAME list chained rows into CHAINED_ROWS;
The analyze command will put the output from this operation into a table called CHAINED_
ROWS in your local schema. The SQL to create the CHAINED_ROWS table is in a file named
utlchain.sql, in the $ORACLE_HOME/rdbms/admin directory. The following query will select the
most significant columns from the CHAINED_ROWS table:
select
Owner_Name, /*Owner of the data segment*/
Table_Name, /*Name of the table with the chained rows*/
Cluster_Name, /*Name of the cluster, if it is clustered*/
Head_RowID /*Rowid of the first part of the row*/
from CHAINED_ROWS;
The output will show the RowIDs for all chained rows, allowing you to quickly see how many of
the rows in the table are chained. If chaining is prevalent in a table, that table should be rebuilt
with a higher value for pctfree.
You can see the impact of row chaining by querying V$SYSSTAT. The V$SYSSTAT entry for
the ???table fetch continued row??? statistic will be incremented each time Oracle selects data from
a chained row. This statistic will also be incremented when Oracle selects data from a spanned
row??”a row that is chained because it is greater than a block in length.
Pages:
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438