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

Understanding the Process of Executing SQL Statements

In order to be effective at tuning SQL statements, the database professional must have an intimate understanding of how the SQL is transformed from the original source code into an executable form.  At a high level, SQL processing is broken down into several steps:

  1. Parsing the source code to locate any syntax errors
  2. Invoking the SQL optimizer to derive an execution plan
  3. Create and run an executable, based on the execution plan
  4. Fetching the results set from the database and return it to the calling query.

Oracle has a special RAM memory region within the System Global Area (SGA) to process SQL statements.  Most relational databases contain multiple areas of RAM caches:

  1. RAM cache for data blocks
  2. RAM cache for SQL statements
  3. RAM for caching the data dictionary
  4. RAM for caching program execution code

Just as the data caches within a relational database use a most frequently used algorithm to cache data blocks that are frequently referenced, the Oracle database provides a special cache called library cache to store frequently executed SQL statements.

Every time an SQL statements enters the Oracle system, Oracle begins by looking to see if the SQL statement has already been processed.  Oracle does this by invoking a hashing algorithm, using the SQL statement as input, and goes to the RAM address to see if the SQL statement has already been parsed.  If the statement has already been parsed, Oracle grabs the executable and immediately re-executes the program, at the same time incrementing the executions column in the v$sql view.

In order to make effective use of the library cache, it is important for the Oracle database professional to ensure that all of the SQL in the library cache is fully reentrant.  By fully reentrant, we mean that the SQL is free of literal variables, such that the SQL can be re-executed using different bind variables as input. For example, the following code would not be considered reentrant because of the embedded literal.

select * from customer where name = ‘BURLESON’;

To make this SQL reentrant, we can replace the literals within the SQL statement with a bind variable, thereby making the SQL fully re-entrant, and re-usable:

select * from customer where name = ‘:var1’;

To facilitate this type of literal substitution, Oracle provides a tool called cursor_sharing.  Setting cursor_sharing=force automates the process of substituting out literal values within SQL statements.  This is very important tool to those Oracle database administrators whose vendor applications generate dynamic SQL that contains literal values.

The best way to locate and tune Oracle SQL statements is by extracting the SQL directly from Oracle's library cache.  The v$sql and v$sql_plan views can be used to allow the immediate extraction of SQL statements from the library cache.  Further, the v$sql_plan view can tell us valuable information about the access patterns of the SQL that are currently within the library cache.

We have to note that the process speed of executing SQL statements can be very quick, and tens of thousands of SQL statements can be executed every minute in a busy Oracle database.  Hence, we need some kind of a monitoring tool that will tell us to salient characteristics of SQL activity over fixed periods of time.  We use the Oracle STATSPACK utility for this purpose and part of your reading assignments will be to understand how we can use STATSPACK to monitor the behavior all the library cache and see the behavior of individual SQL statements over time.



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.