 |
|
Oracle Concepts by Burleson
Consulting |
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
this:
analyze
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:
SQL> execute
DBMS_UTILITY.ANALYZE_SCHEMA('PUBS', 'ESTIMATE')
PL/SQL
procedure completed successfully.
SQL>
Now try this statement on your Oracle
database.
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:
select
table_name,
avg_row_len,
chain_cnt,
num_rows
from
dba_tables
where
owner = 'PUBS';
Here is the output:
TABLE_NAME
AVG_ROW_LEN CHAIN_CNT NUM_ROWS
------------------------------ ----------- ---------- ----------
AUTHOR
76 0
10
BOOK
68 0
20
BOOK_AUTHOR
16 0
25
EMP
42 0
10
JOB
22 0
4
PLAN_TABLE
0 0
0
PUBLISHER
49 0
10
SALES
29 0
100
STORE
62 0
10
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:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
