Oracle 12cR2 Extended Statistics
Introduction to Oracle Optimizer
What is the Oracle Optimizer?
Think of the Oracle Optimizer as a smart GPS for your data. When you ask the database a question using a SQL query, there are often many different routes it could take to find the answer. Some routes are quick and direct, while others are long and winding. The Optimizer's job is to find the most efficient route, or execution plan, to fetch your data.
It's a core, built-in component of the Oracle Database that works automatically every time you run a query. You don't see it, but it's always there, analyzing your request and figuring out the quickest way to get the job done. Its goal is to create an execution plan with the lowest possible cost, which is Oracle's internal measurement of the work required for a query.
The Optimizer's main purpose is to determine the most efficient execution plan for a SQL statement.
Choosing the Best Path
An execution plan is the step-by-step recipe the database follows. For any non-trivial query, there can be hundreds or even thousands of possible plans.
Let's say you have a large employees table and you run this query:
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales';
The Optimizer might consider several paths:
-
Full Table Scan: Read every single row in the
employeestable and check if thedepartmentis 'Sales'. This is like reading an entire book from cover to cover to find one specific sentence. -
Index Scan: If an index exists on the
departmentcolumn, the database can use it like a book's index. It can look up 'Sales' in the index and jump directly to the rows for that department, which is usually much faster.
The Optimizer evaluates these (and other) potential plans by estimating their cost. It then chooses the one it predicts will use the fewest resources, such as CPU time and disk I/O.
How It Decides
To make an informed decision, the Optimizer relies heavily on statistics about your data. These aren't just any statistics; they are detailed metrics collected by the database about the contents of your tables and indexes. This information gives the Optimizer a clear picture of the data's landscape.
Good statistics are crucial. Without them, the Optimizer is flying blind and might choose a very inefficient plan.
Key factors the Optimizer considers include:
-
Object Statistics: Information about database objects like tables and indexes. For example, how many rows are in a table, or how many levels deep an index is.
-
System Statistics: Information about the hardware, such as CPU speed and I/O performance.
-
Bind Variables: The values supplied to a query when it's executed.
-
Initialization Parameters: Settings in the database's configuration file that can influence the Optimizer's behavior.
The most important of these are the object statistics. The Optimizer uses them to estimate the cardinality, or the number of rows it expects to get back from each step of an operation. This estimate is the single most important input for choosing a plan.
| Statistic | What It Tells the Optimizer |
|---|---|
| Number of Rows | The total size of the table. |
| Number of Blocks | How much physical space the table occupies on disk. |
| Number of Distinct Values | How many unique values exist in a column (e.g., a country column might have ~200 distinct values). |
| Data Distribution (Histograms) | How the values are spread out. Are they evenly distributed, or are some values much more common than others? |
By combining all this information, the Optimizer builds a sophisticated model to predict the cost of various execution plans and selects the one it believes is best. This complex, behind-the-scenes process is what makes Oracle's query performance so powerful.
What is the primary goal of the Oracle Optimizer?
An execution plan that involves reading every single row in a table is known as a(n) ____.
Now that you understand the role of the Optimizer, you're ready to see how we can provide it with even better information to make smarter decisions.