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 .
 
Search WWH ::




Custom Search