BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Generating the SQL Execution Plan

Oracle Tips by Burleson Consulting

Because Oracle SQL is a declarative data access language, the SQL statement does not specify the navigation path to service the request. While using SQL as a purely declarative language is a great goal, in practice Oracle has found that hints are sometimes required to ensure that the optimizer generates an optimal execution plan. The job of the Oracle optimizer is to determine the fastest and most efficient way to service the query. When we talk about optimizing a query, you need to remember that Oracle makes a distinction between the speed of a query and the efficiency of a query. Oracle has two optimizer goals. Each of these goals reflects a general approach to satisfying SQL requests.

  • Maximize speedThis goal focuses on returning the result set in the minimal amount of elapsed time. This goal is common for online transaction processing systems (OLTP), where fast response time is the primary goal. This goal is implemented with Oracle’s first_rows optimizer mode.

  • Minimize resourcesThis goal concentrates of servicing the query while using the minimum amount of machine and disk resources. This goal is most appropriate for batch-oriented Oracle databases where the speed of query completion is not a major concern. This goal is implemented with Oracle’s all_rows optimizer mode.

While I will be going into great detail about the optimizer mode in Chapter 4, suffice it to say that each Oracle optimizer mode uses a different set of goals for determining the optimal execution plan. The default optimizer mode for Oracle SQL is set by the initialization parameter called optimizer_mode.

When Oracle accepts the parsed SQL to generate the execution plan, it looks to the Oracle data dictionary for guidance. The setting of the Oracle initialization parameters and the presence of index and table statistics influence the decision of the SQL optimizers.

Oracle uses two types of SQL optimizers, the rule-based optimizer (RBO) and the cost-based optimizer (CBO). Although I will be discussing each of these modes in Chapters 14 and 15, you need to understand that Oracle relies on data dictionary information to determine the best method to retrieve the data. With the CBO, Oracle uses statistics that are created from running the analyze command, and with the RBO Oracle uses information about indexes in the data dictionary.

Oracle's default optimizer mode is called “choose.” In the “choose” optimizer mode, Oracle will execute the rule-based optimizer if there are no statistics present for the table, or it will execute the cost-based optimizer if statistics are present. The danger with using the choose optimizer mode arises in cases where one Oracle table in a complex query has statistics and the other tables do not have statistics. When only some tables contain statistics, Oracle will use the cost-based optimization and estimate statistics (by sampling 5000 rows) for the other tables in the query at run time. This can cause significant slowdown in the performance of the individual query. Be careful when using the choose option.

When you give Oracle the ability to choose the optimizer mode, Oracle will favor the cost-based approach if any of the tables in the query have statistics. (Statistics are created with the analyze table command.) For example, if a three-table join is specified in choose mode and statistics exist for one of the three tables, Oracle will decide to use the cost-based optimizer. When this happens, the Oracle CBO will inspect the num_rows column of the Remote DBA_tables view. If num_rows is null, Oracle still estimate statistics based on a 5000-row sample. If the num_rows column is zero, Oracle will not perform a run-time table analysis. In short, if Oracle analyzes the table at run time, this action will dramatically slow down the query. Next, let’s take a look at the different types of table access methods.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter