Index Enhancements   «Prev  Next»

Lesson 6 Computing Statistics
Objective Describe two methods of creating statistics.

Computing Oracle Statistics

The cost-based optimizer is the preferred method of optimization for an Oracle database. The calculation of the cost of a particular execution path is determined by a set of statistics that describe the composition of the tables and indexes in the database. You have two choices as to when you want to gather these all-important statistics.

Compute Statistics by means of an Index

CREATE INDEX index_name ON table_name
  (column_list) COMPUTE STATISTICS;

CREATE INDEX Required keywords.
index_name Unique name for the index.
ON Required keyword.
table_name The unique name of the table the index will be based on.
column_list A list of columns whose values will make up the index.
COMPUTE STATISTICS Required keywords for computing statistics during index creation.

Gathering Statistics During a Rebuild

You can gather statistics on an index while rebuilding the index, as the following example shows:
SQL>alter index hr.emp_emp_id_pk rebuild compute statistics;
Index altered.
You save time by having the database gather statistics while it's rebuilding the index.

During index creations, you may be able to eliminate subsequent sorts by using the compute statistics clause of the create index command and gathering the statistics as the index is created.

Eliminate the Need to Query Undo Segments

When performing a query, Oracle will need to maintain a read-consistent image of the rows queried. If a row is modified by another user, the database will need to retrieve the earlier version of the blocks from the undo segment to see the row as it existed at the time your query began. Application designs that call for queries to frequently access data that others may be changing at the same time force the database to do more work, it has to look in multiple locations for one piece of data. Again, this is a design issue. DBAs may be able to size the undo segment areas to reduce the possibility of queries encountering errors, but correcting the fundamental problem requires a change to the application design.

What does the Database need to Know?

Oracle's optimizer relies on statistics when it evaluates the thousands of possible paths to take during the execution of a query. How you manage those statistics can significantly impact the performance of your queries.

Keep Your Statistics Updated

How often should you gather statistics? With each major change to the data in your tables, you should reanalyze the tables. If you have partitioned the tables, you can analyze them on a partition-by-partition basis. You can use the Automatic Statistics Gathering feature to automate the collection of statistics. By default, that process gathers statistics during a maintenance window from 11 P.M. to 7 A.M. each night and all day on weekends. Since the analysis job is usually a batch operation performed after hours, you can tune it by improving sort and full table scan performance at the session level. The result will be greatly enhanced performance for the sorts and full table scans the analysis performs.

Computing statistics as a part of the creation process for an index incurs very little overhead. If the COMPUTE STATISTICS keywords are not a part of the

statement, no statistics will be created until you explicitly compute them. You can also compute statistics explicitly with the following syntax with the ANALYZE command:

Compute Statistics
  1. Required keywords
  2. The name of the target index
  3. Required keywords to compute statistics
  4. Required keyword
  5. A list of the columns to compute statistics for, or the keyword ALL for all columns

When to compute statistics

Keep in mind that statistics are always relative. The basic relevance of statistics is to give an approximation of the size and composition of the objects in your database. These statistics are then used to calculate the cost of a particular execution path option. If the composition of your database changes in a relatively small way, the new statistics will probably not change the decision of the cost-based optimizer. Adding 1,000 rows to a table that already contains 100,000 rows will usually not change the outcome of cost-based optimization. However, if those 1,000 rows add 1,000 unique values to a table that currently contains only 100 unique values, the new rows could indeed affect the selection of an execution path. In general, you should compute statistics as a standard part of your maintenance procedures, or whenever an event, such as a large data load, could affect the composition of your database. In the next lesson, you will learn about domain indexing.

Ad Oracle 12c Performance Tuning

Computing Statistics- Quiz

Click the Quiz link below to test your knowledge about index enhancements.
Computing Statistics - Quiz

SEMrush Software