Starting with Performance Tuning - Oracle Database 11gR2 Performance Tuning - page 17

In the example, we have transferred statistics about the entire schema APP_SCHEMA . We can
choose to transfer statistics for the entire database, a table, an index, or a column, using the
corresponding import_* and export_* procedures of the DBMS_STATS package.
There's more...
The COMPUTE STATISTICS and ESTIMATE STATISTICS parameters of the ANALYZE
command are supported only for backward compatibility by Oracle. However, there are other
functionalities of the command that allow validating the structure of a table, index, cluster,
materialized views, or to list the chained or migrated rows:
ANALYZE TABLE employees VALIDATE STRUCTURE;
ANALYZE TABLE employees LIST CHAINED ROWS INTO CHAINED_ROWS;
The first statement validates the structure of the EMPLOYEES table, while the second
command lists the chained rows of the same table into the CHAINED_ROWS table (created
with the script utlchain.sql or utlchn1.sql .)
See also
F Avoiding row chaining in Chapter 3 , Optimizing Storage Structures
Analyzing data using Statspack reports
Statspack was first introduced in Oracle Database 8i R8.1.6. We shall now look at how to
use this tool.
Getting ready
To use Statspack, we have to set up a tablespace to store its structures; if we don't, in
the installation process we have to choose an already existing tablespace— SYSAUX is
the tablespace proposed by default. To create the tablespace, we will use the following
command (with the necessary change in the datafile parameter, according to the
platform used and the database location):
CREATE TABLESPACE statspack
DATAFILE '/u01/oracle/db/STATSPACK.DBF' SIZE 200 M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO PERMANENT ONLINE;
To collect timing information in the dynamic performance views, we have to set the parameter
TIMED_STATISTICS=TRUE , as shown in the recipe about the dynamic performance view.