The Query Optimizer (the optimizer)

The Query Optimizer(the optimizer)

The query optimizer is built-in database software.

The query optimizer attempts to generate the most efficient execution plan for a SQL statement.

The query optimizer considers many factors related to the objects referenced and the conditions specified in the query.

This determination is an important step in the processing of any SQL statement.

The optimizer chooses the plan with the lowest cost among all considered candidate plans.

The optimizer uses available statistics to calculate the cost.

The optimizer-:

– Evaluates expressions and conditions
– Uses object and system statistics
– Decides how to access the data
– Decides how to join tables
– Decides which access path is most efficient

The information needed by the optimizer includes-:

-Statistics gathered for the system (I/O, CPU, and so on) as well as schema objects
(number of rows, index, and so on)
-Information in the dictionary
-WHERE clause qualifiers
-Hints supplied by the developer

A SQL statement can be executed in many different ways-:

-Full table scans
-Index scans
-Nested loops
-Hash joins

• A good plan can greatly affect execution time.

• The output from the optimizer is a plan that describes an optimum method of execution.

• The optimizer may not make the same decisions from one version of Oracle Database to the next.

• In recent versions, because more information is available, the optimizer may make different decisions.

Example-:

a query might request information about employees who are managers. If the optimizer statistics indicate that 80% of employees are managers, then the optimizer may decide that a full table scan is most efficient.

However, if statistics indicate that very few employees are managers, then reading an index followed by table access by rowid may be more efficient than a full table scan.

• the database has many internal statistics and tools at its disposal, the optimizer is usually in a better position than the user to determine the optimal method of statement execution.

• For this reason, all SQL statements use the optimizer.

• When you use diagnostic tools such as Enterprise Manager, EXPLAIN PLAN, and SQL*Plus AUTOTRACE, you can see the execution plan that the optimizer chooses.

Note-:

The optimizer has two names based on its functionality: the query optimizer or run-time optimizer and the Automatic Tuning Optimizer (ATO).
The value of ATO depends on sharing SQL cursors.
Cursor sharing is affected by the use of literals, the setting of the CURSOR_SHARING parameter, and histograms.

• The optimizer works in two modes, the first and usual mode is the run-time optimizer that creates the execution plan at run time. In this mode, the optimizer is time-limited; it can only consider a limited number of alternatives.

• The second mode is called the Automatic Tuning Optimizer (ATO). In this mode, the optimizer is given a much longer time to consider more options and gather statistics.

• The ATO can produce a better plan and create a SQL profile that will influence the optimizer to choose the better plan whenever the SQL statement is submitted in the future.

Thanks

With Best Wishes!!!!

Ahmad

ACE Associate
Cloud Architect
Oracle Database Consultant

Certified in-: | 1XAzure | 6XOCI | 1XOCS (GG) | 1XOCE (RAC) | 3XOCP (11g,10g,9i) | 1XOCA (9i) |

My YouTube Channel (OracleRide)
http://www.youtube.com/c/OracleRide

Social Network Link
LinkedIn: https://linkedin.com/in/riaz-ahmad-ansari-62500997
LinkedIn Group: https://www.linkedin.com/groups/10506569
Linkedin Page: https://www.linkedin.com/company/68645722/admin/
Facebook: https://www.facebook.com/ahmad.oracleride
Facebook Group: https://www.facebook.com/groups/549665585939796/
Twitter: https://twitter.com/raansari77
Instagram: https://www.instagram.com/raansari20/