Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Concepts by Burleson Consulting

Recognizing the Features of SQL

SQL has become the de-facto standard for database access, and SQL has come a long way since it’s inception in the late 1970’s.  In the early 1980’s SQL was lionized as a revolutionary tool for database access, primary because of its declarative nature and easy syntax.

Prior to SQL, all database access requires physical database navigation.  In other words, the person issuing the query would have to specify the order of execution and which indexes were used to access the data.  SQL overcame this shortcoming by creating an SQL optimizer that creates an execution plan for the SQL.

As we learned in the previous theme SQL has three simple operations, select, project, and join that allow us to completely extract data from inside any relational database table.

Unlike the navigational database languages, SQL is declarative.  By declarative, we mean that the person who writes an SQL statement does not need to specify the navigation to service the query.  Relational database provide an SQL optimizer to determine the optimal execution plan for the SQL statement.

The SQL optimizer analyzed the SQL query and goes to the data dictionary to collect information about the tables that are specified in the query. Using these statistics, the SQL optimizer determines the best execution plan to get the requested rows.  Some of the thing that the optimizer does includes:

1 – Determine the order that the tables will be joined.

2 – Determine the best indexes to use

3 – Determine the best table join method to use

While the basic format of SQL statements is quite simple, sophisticated queries can be very complex, using advanced SQL features such as in-line views, correlated Subqueries, and so on.  Hence, the SQL optimizer has grown into an extremely complex software package, and the major optimizers (Oracle, DB2) cost many millions of dollars to develop.

So, if the SQL optimizers cost millions of dollars, why is SQL tuning necessary?  In the real world, there are an infinite number of possible SQL queries, and it is impossible for any software package to always generate an optimal execution plan.  Hence, the database vendors create “hints” to allow us to change execution plans.  Oracle, the world’s leading relational database has over 20 different hints for changing the behavior of the SQL optimizer.

It is our challenge as a database professional to understand the internals procedures for SQL and be able to manage the parsing and generation of the internal execution plans for the SQL statements.  Once you understand how the SQL optimizer works, you can quickly identify and correct suboptimal SQL queries.

As we read Chapter 1, pay special attention to the sections on the goals of SQL tuning and the basic process of SQL tuning.  We will be returning to these guidelines in more detail later in this course, so it is important that you internalize the basic goals and techniques.

 

For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.