Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  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:


PL/SQL procedure completed successfully.


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:

   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:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.