Database Reference
In-Depth Information
Name Resolution and Extraction of
Object Statistics
An ORACLE performance optimization assignment may involve the extraction of object statis-
tics used by the cost based optimizer. I frequently use a script called
statistics.sql
, which
reports details of the table structure, table partitions and subpartitions (if present), table and
index cardinality, distinct values in columns, distinct index keys, timestamps of the last statis-
tics gathering, indexes, tablespaces, block size, and LOB columns. The report reveals problems
such as stale or missing statistics, indexes on non-selective columns, or LOBs with the
NOCACHE
option, which cause direct path read and write waits. The report also makes it easy to identify
columns with high selectivity, which are good candidates for indexing, given that these columns
appear as predicates in where-clauses. I highly recommend using the script when investigating
performance problems.
The use of
DBMS_UTILITY.NAME_RESOLVE
renders calling the script much more convenient,
since it is sufficient to provide the name of a table (or synonym) to report on in lowercase
instead of the owner (or schema) and table with exactly the same spelling as in the data dictionary
(usually all upper case letters). The script takes the name of a database object as input, resolves
the name, and then pulls the relevant information from
ALL_*
dictionary views (this is serious
business, not an All-Star Game!), such as
ALL_TABLES
,
ALL_INDEXES
,
ALL_LOBS
, and so on. The
script works without DBA privileges, since
ALL_*
views and not
DBA_*
views are used. The syntax
for running the script is as follows:
sqlplus -s
user
/
password
@statistics[.sql]
{ [
schema
.]
table_name
|
synonym
}
Data dictionary object names that contain lowercase letters must be quoted. The
following illustration shows sample output from the script
statistics.sql
.