Describe Features of Oracle Optimizer

The Oracle Cost-Based Optimizer (CBO) is a critical component of SQL tuning in Oracle databases. It's the predominant tool used by Oracle to determine the most efficient way to execute a given SQL query. The Oracle CBO uses a variety of inputs to make its decisions, including:

1. Statistics: Oracle collects statistics about the data distribution and storage characteristics of tables, indexes, partitions, and other schema objects in the database. This information is vital to the CBO, as it helps it estimate the cost of a particular query execution plan
2. System Parameters: The Oracle RDBMS has numerous system and session level parameters that impact the optimizer's behavior. For instance, the OPTIMIZER_MODE initialization parameter can be used to set the optimizer's overall goal, which can be either throughput (processing as many rows as possible per unit of time) or response time (processing a transaction in the least amount of time).
3. SQL Hints: SQL developers can influence the optimizer's decisions by using hints, which are instructions embedded in the SQL statement. However, they should be used judiciously, as excessive or improper use can lead to less than optimal execution plans.
4. Query Optimizer Features: Different versions of Oracle come with different features for the optimizer. Some newer features might not be enabled by default, and others might be specific to certain types of SQL operations or workloads.

After considering these factors, the Oracle CBO generates a set of potential execution plans for a query, estimates the cost of each plan, and then selects the plan with the lowest cost. "Cost" in this context is a representation of the resources needed to execute the plan, such as CPU usage, I/O, and network usage. Oracle provides a suite of tools for SQL Tuning, such as SQL Tuning Advisor and SQL Access Advisor, which can provide recommendations for optimizing SQL statements. They are especially useful in identifying high-load SQL, investigating the reasons for their performance, and implementing corrections.
In conclusion, the Oracle Cost-Based Optimizer remains a critical component of SQL tuning in Oracle databases. It helps in deciding the most efficient execution plan for SQL queries based on various inputs like statistics, system parameters, and SQL hints. By selecting the execution plan with the lowest estimated cost, it aims to maximize the performance of SQL operations.

The Oracle cost-based optimizer (CBO) is still the primary tool for performance tuning in Oracle 12c and even in the latest versions of Oracle Database. In fact, it's been the cornerstone of query optimization since its introduction in Oracle 7. Here's why CBO remains essential:

• Dynamic planning: CBO analyzes statistics and chooses the optimal execution plan based on current data volume, distribution, and hardware characteristics. This flexibility is crucial for tailoring performance to your specific environment and data.
• Accuracy: With accurate and up-to-date statistics, CBO can generate highly efficient plans.
• Advanced features: CBO has evolved with newer features like adaptive query optimization and materialized views, further enhancing its capabilities.

While alternative solutions like rule-based optimization (RBO) exist, they offer limited advantages and are generally not recommended for modern performance tuning. However, it's important to remember that even CBO relies on good data statistics to function effectively. Regularly gathering and updating statistics is crucial for optimal performance. Additionally, understanding how CBO works and analyzing its decisions can help you further refine your queries and achieve even better results.

Oracle includes a cost-based optimizer, which can recognize

1. partitions and
2. star schema queries.

With an Oracle database, you can partition a table or index. Partitioning allows you to specify that

1. different rows in a table, or
2. entries in an index,

are stored in different physical locations based on the values in the database object. Partitioning is frequently implemented to avoid unnecessary disk I/O in queries. For instance, if a table and its index are partitioned based on the value of a column containing a state abbreviation, a query looking for rows for a particular state could go directly to the right partition for the state, skipping all other partitions. The cost-based optimizer ^[1] in Oracle is aware of partitions, and will choose the optimal execution plan based on the partitions implemented for the table.

The CBO determines which execution plan is most efficient by considering available access paths and by factoring in information based on statistics for the schema objects (tables or indexes) accessed by the SQL statement. The CBO also considers hints, which are optimization suggestions placed in a comment in the statement. The CBO performs the following steps:

1. The optimizer generates a set of potential plans for the SQL statement based on available access paths and hints.
2. The optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement. The cost is an estimated value proportional to the expected resource use needed to execute the statement with a particular plan. The optimizer calculates the cost of access paths and join orders based on the estimated computer resources, which includes I/O, CPU, and memory. Serial plans with higher costs take more time to execute than those with smaller costs. When using a parallel plan, however, resource use is not directly related to elapsed time.
3. The optimizer compares the costs of the plans and chooses the one with the lowest cost.

The star schema is a type of schema designed for the data warehouse, where a single fact table, which typically contains an enormous number of small rows, is surrounded by a number of dimension tables that typically contain a much smaller number of rows. The previous versions of the cost-based query optimizer might not recognize the existence of a star schema and consequently develop a less-than-optimal execution plan. The cost-based optimizer in Oracle has been improved, automatically recognizing star schemas and making the proper execution plan choice when the star schema exists.

You can influence the decision of the cost-based optimizer through the use of hints. A hint tells the optimizer to use a particular access path in the execution plan. Oracle includes several hints

1. to guide the cost-based optimizer to a star schema;
2. to use parallelism when accessing a partitioned index; and
3. to use a fast full index scan when the index contains all the values needed in a query.

In the next lesson, you will learn how to use stored outlines.

[1]optimizer: Built-in database software that determines the most efficient way to execute a SQL statement. The query optimizer is made up of the query transformer, the estimator, and the plan generator. The optimizer generates a set of potential execution plans for SQL statements, estimates the cost of each plan, calls the plan generator to generate the plan, compares the costs, and chooses the plan with the lowest cost. The database uses this approach when the data dictionary has statistics for at least one of the tables accessed by the SQL statements.