Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle Concepts - Oracle Tables and Statistics

Oracle Tips by Burleson Consulting

Oracle Tables and Statistics

The guys who wrote Oracle are pretty smart. One of the things they built in the database is this program called the optimizer. The optimizer?s job is to take SQL statements and decide how to get the data that is being asked for in the SQL statement and how to get it in the quickest way possible.

When a SQL statement is executed, the database must convert the query into an execution plan and choose the best way to retrieve the data. For Oracle, each SQL query has many choices for execution plans, including which index to use to retrieve table row, what order in which to join multiple tables together, and which internal join methods to use (Oracle has nested loop joins, hash joins, star joins, and sort merge join methods). These execution plans are computed by the Oracle cost-based SQL optimizer commonly known as the CBO.

The choice of executions plans made by the Oracle SQL optimizer is only as good as the Oracle statistics. To always choose the best execution plan for a SQL query, Oracle relies on information about the tables and indexes in the query.

Once the optimizer has done its job, it provides an execution plan to Oracle. An execution plan is like a set of instructions that tells Oracle how to go and get the data.

This is a pretty simple plan. In it, the optimizer tells Oracle to first go get all the rows of the EMP department, and then sort those rows (Reading an execution plan is somewhat of an art, so trust us on this one, the full scan of the EMP table is first).

Did you notice in the plan that there is a column called ROWS? This is the number of rows that the query will process. How did Oracle know that it was going to process 100 rows. This Oracle thing is smart, isn?t it.

Well, Oracle isn?t quite that smart. In this case, Oracle knew (or in most cases it?s a good guess) we would process 100 rows because we generated statistics on the EMP table after we created the table. The optimizer uses these statistics to generate execution plans.

The optimizer program uses statistics on tables and on the indexes surrounding those tables, so it?s important to have statistics on both. In the next section, we will show you how to generate statistics on tables and indexes in your database.  Starting with the introduction of the dbms_stats package, Oracle provides a simple way for the Oracle professional to collect statistics for the CBO.

The old-fashioned analyze table and dbms_utility methods for generating CBO statistics are obsolete and somewhat dangerous to SQL performance because they don't always capture high-quality information about tables and indexes. The CBO uses object statistics to choose the best execution plan for all SQL statements.

The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better stats result in faster SQL execution plans.  Here is a sample execution of dbms_stats with the options clause.

exec dbms_stats.gather_schema_stats( -
ownname          => 'SCOTT', -
options          => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt       => 'for all columns size repeat', -
degree           => 15 -
)
 

How to Generate Oracle Statistics

Oracle provides a stored procedure (or program) for you to run that will generate the statistics is needs. Oracle requires statistics on both tables and any associated indexes (we will talk about indexes next), and most of the time you will generate both with just one command.

To generate statistics we use the dbms_stats stored package. There are two procedures contained within the dbms_stats package that you will mostly be interested in, dbms_stats.gather_schma_stats and dbms_stats.gather_table_stats. Also, in Oracle database 10g you will want to gather system statistics and fixed view statistics. Let?s look at each of these operations in a bit more detail next.

There is also an analyze command that you can use to generate statistics. It?s been deprecated in Oracle Database 10g (which means it?s really not supported anymore). So we don?t cover it in this book.

Using dbms_stats.gather_schema_stats

The dbms_stats.gather_schema_stats procedure allows you to gather statistics for all objects in a give schema. This is the easiest way to generate statistics for a large number of objects. Here is an example of using the dbms_stats.gather_schema_stats procedure to gather statistics on the SCOTT schema of a database:

EXEC dbms_stats.gather_schema_stats(?SCOTT?, cascade=>TRUE);

This command will generate statistics on all tables in the SCOTT schema. Since we included the cascade command, the indexes will also have statistics generated on them. This is important, you need statistics on indexes as well as on tables in Oracle!

Of course, this is just the basic way to run this command. Several options are available, but for now as a new DBA this will do.  In fact, Oracle 10g automatically collects database statistics every night out of the box.  Later you will want to investigate some of the Oracle Database 10g statistics gathering options such as histograms, and granularity.

If you create a new table, then it may not be practical or desirable to re-generate statistics on the entire schema if the schema is quite large and the database is very busy. Instead you will use the dbms_stats.gather_table_stats command to generate statistics for a single table, and optionally for related table indexes. Here is an example:

EXEC dbms_stats.gather_table_stats(?SCOTT?,?EMP?,cascade=>TRUE);

In this case we are generating statistics for the EMP table in the SCOTT schema. Again we use the cascade parameter to insure all of the indexes get analyzed.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

 

  

 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.