Analyzing a schema for cost-based SQL optimization

Remote DBA

Remote DBA Plans

Oracle:

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 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 that 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 database.

This command will completely analyze all tables and indexes in your pubs 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.

Burleson is the American Team

American Flag

BC Remote Oracle Support
P.O. Box 511 • Kittrell, NC, 27544

Remote DBA

Remote DBA Services