Lesson 4	Overriding the default Optimizer Mode
Objective	Override the default optimizer mode for a Query

Overriding the Default Optimizer Mode for a Query when tuning Oracle SQL

In Oracle SQL, you can override the default optimizer mode for a specific query by using "optimizer hints" or by setting a session-level optimizer mode. Here’s how:

Using Optimizer Hints (Preferred Approach)

Oracle provides optimizer hints that you can embed within a SQL statement to influence how the optimizer processes the query.

The
```
/*+ OPTIMIZER_MODE(...) */
```
hint allows you to override the default optimizer mode.

Example: Changing the Optimizer Mode for a Query
```
SELECT /*+ OPTIMIZER_MODE(FIRST_ROWS) */ employee_id, first_name, last_name
FROM employees
WHERE department_id = 10;
```
- ALL_ROWS: Optimizes for the best throughput (default for cost-based optimizer).
- FIRST_ROWS(n): Optimizes for fast response time, retrieving the first n rows quickly.
- RULE: Uses the rule-based optimizer (deprecated in Oracle 10g and later).
Changing the Optimizer Mode at the Session Level

If you want to change the optimizer mode for all queries in a session, you can modify the session parameter using:
```
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
```
or
```
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;
```
This will apply to all queries executed in the session.
Changing the Optimizer Mode for the Entire Database

If you want to set a new default optimizer mode for the entire database, you can modify the system parameter:
```
ALTER SYSTEM SET OPTIMIZER_MODE = FIRST_ROWS;
```
However, this change will affect all queries running on the database instance.
Using SQL Profiles or SQL Plan Baselines (Advanced)

If you need more fine-grained control over optimizer behavior, you can use SQL Profiles or SQL Plan Baselines to influence execution plans without modifying the SQL statements.

As you saw, it is possible to reset the default optimizer mode for all queries. However, you may also override the optimizer mode on a query-by-query basis. There are two ways to override the default optimizer mode for any query, the use of the ALTER SESSION command and the use of SQL hints.

Using the ALTER SESSION command
If you want to override the default optimizer mode for any SQL that you execute (so long as it does not contain hints), issue the following command:
```
ALTER SESSION SET OPTIMIZER_GOAL=all_rows, first_rows, or rule
```
The snippet below shows how to do this in SQL*Plus:
```
SQL> alter session set optimizer_goal=rule;
Session altered.
```
Using SQL Hints
While the ALTER SESSION command is very good for testing and development, a more surefire and permanent way of changing SQL is by using SQL hints. SQL hints are used to ensure that the access path for the SQL never changes. While there are many dozens of hints that can be added to SQL, these are the hints that change the optimizer mode for the entire statement:
```
/*+ all_rows */ 
/*+ first_rows */
/*+ rule */ 
```
Because Oracle SQL hints are embedded in comments, you will NEVER get an error message if the hint contains a syntax error. Hence, you must be very careful to run an EXPLAIN PLAN on all SQL after the hint has been added to ensure that it is doing what you intended.

Hints as Insurance

Again, it is always a good idea to add a hint to “cement” the access path into every SQL statement after it has been tuned, even if the hint matches the default optimizer mode. This is great insurance against careless errors. If another DBA mistakenly changes the default optimizer mode for your database, your query will still execute at optimal performance. Now let us conclude this module with a review.

Oracle does provide several optimizer hints that can be used to influence the query execution plan. Here's a bit more detail on the /*+ OPTIMIZER_MODE(...) */ hint:

Syntax: /*+ OPTIMIZER_MODE(optimizer_mode) */
Usage: This hint is placed at the beginning of the SQL statement inside a comment block with the + sign, which signals to Oracle that what follows is a hint.

Optimizer Modes you can specify include:

ALL_ROWS: Optimizes for best throughput, aiming to retrieve all rows as quickly as possible.
FIRST_ROWS(n): Optimizes for the best response time to return the first n rows. Here, n can be 1, 10, 100, or 1000.
CHOOSE: This mode lets Oracle choose the optimization method based on whether statistics are present or not. If statistics exist, it behaves like ALL_ROWS; if not, it uses FIRST_ROWS.

Remember, while hints can be useful for performance tuning, they should be used judiciously because they can reduce the flexibility of the optimizer to adapt to changes in data distribution or system resources. It's generally recommended to let the optimizer do its job unless you've identified specific performance issues that hints can address effectively.