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

Home
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


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

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:

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

 
 
 

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.