Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 
     Oracle:

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


 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.