Oracle Concepts - Oracle Tables and
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
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
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
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.
=> '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 and beyond you
have the ability 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.
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:
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:
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
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
for 30% off directly from the publisher.