Lesson 1

The primary purpose of your Oracle database is to provide a way to store information for future retrieval. Oracle has several mechanisms that will improve the performance of data retrieval. The optimizer determines the most efficient way to execute a SQL statement after considering 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 and can greatly affect execution time. A SQL statement can be executed in many different ways, including the following:

1. Full table scans
2. Index scans
3. Nested loops
4. Hash joins

In this module, you will learn how to:

1. Describe the Oracle optimizer's functions
2. Describe optimizer features of Oracle
3. Use stored outlines to predetermine optimizer behavior
4. Describe the components and uses of the DBMS_STATS package
5. Use DBMS_STATS to collect and move statistics
6. Describe the tools available for monitoring database operations
7. Use Oracle Enterprise Manager to monitor database activity

The optimizer and query utilities in Oracle will help you improve the performance of your user queries and in many cases, transparently.

SQL processing uses the following main components to execute a SQL query:

1. The Parser checks both syntax and semantic analysis.
2. The Optimizer uses costing methods, (CBO) cost-based optimizer, or internal rules, rule-based optimizer (RBO), to determine the most efficient way of producing the result of the query.
3. The Row Source Generator receives the optimal plan from the optimizer and outputs the execution plan for the SQL statement.
4. The SQL Execution Engine operates on the execution plan associated with a SQL statement and then produces the results of the query.