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.

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.

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.