||Oracle Concepts by Burleson
Analyzing a Schema for
Cost-Based SQL Optimization
This exercise will allow you to view the
important statistics that are gathered within the Oracle database.
Your first task is to analyze your pubs schema. To do this, you
must “analyze” all of the tables and indexes in the Oracle database.
For example, and analyze command for the authors table might look like
table authors compute statistics;
The problem with using the analyze command is
that you must know the names of all of your tables and indexes. To
simplify the gathering of statistics for the CBO, you can use the
dbms_utility.analyze_schema utility to examine the table and indexes
are store statistics inside the data dictionary.
The following command will analyze all tables
and indexes are are owned by the pubs user:
procedure completed successfully.
Now try this statement on your Oracle
This command will completely analyze all
tables and indexes in your sample database, and populate the
dba_tables and dba_indexes data dictionary views with statistics about
the nature of the tables and indexes. The cost-based optimizer
will use these statistics to make intelligent decisions about the
optimal execution plan for the SQL statements.
Now that we have statistics, we can now take a
look into the dba_tables and dba_indexes views:
owner = 'PUBS';
Here is the output:
AVG_ROW_LEN CHAIN_CNT NUM_ROWS
------------------------------ ----------- ---------- ----------
Now, after having analyzed your schema, rerun
the query from the previous step, and note the changes in the
statistics. Contrast your personal findings with the topics in the
reading “What is your optimizer philosophy?”, and discuss how often it
is “necessary” to re-analyze a schema for the cost-based optimizer.
Post your thoughts and notes in the discussion forum.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA