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

 

 


 

 

 

 

 

 

 

Reusable SQL Inside the Library Cache

Oracle Tips by Burleson Consulting

One of the biggest problems with Oracle SQL prior to Oracle8i was that execution plans for SQL could not be stored. In Oracle8i, we have the ability to use the optimizer plan stability feature to create stored outlines to store the execution plan for a SQL statement, and I will discuss this new feature in Chapter 13. When Oracle recognizes an incoming SQL statement, it hashes the SQL syntax to the Remote DBA_hints view. If a stored outline exists, the execution plan is extracted from the ol$hints table, thereby bypassing the overhead of reparsing the SQL. However, Oracle still has problems recognizing “similar” SQL statements. For example, Oracle library cache will examine the following SQL statements and conclude that they are not identical:

SELECT * FROM customer;
Select * From Customer;

While capitalizing a single letter, adding an extra space between verbs, or using a different variable name might seem trivial, the Oracle software is not sufficiently intelligent to recognize that the statements are identical. Consequently, Oracle will reparse and execute the second SQL statement, even though it is functionally identical to the first SQL statement.

The best way to prevent SQL reloads is to encapsulate all SQL into stored procedures, and place these stored procedures into packages. This use of stored procedures removes all SQL from application programs and moves the SQL into Oracle’s data dictionary. This method also has the nice side effect of making all calls to the Oracle database look like a logical function. For example, instead of having a complex SQL statement inside a program, you would have a single call to a stored procedure.

There are other ways to make storage reusable within the library cache. The cursor_space_for_time init.ora parameter can be used to speed executions within the library cache. Setting cursor_space_for_time to FALSE tells Oracle that a shared SQL area can be deallocated from the library cache to make room for a new SQL statement. Setting cursor_space_for_time to TRUE means that all shared SQL areas are pinned in the cache until all application cursors are closed. When this parameter is set to TRUE, Oracle will not bother to check the library cache on subsequent execution calls, because it has already pinned the SQL in the cache. This technique can improve the performance for some queries, but cursor_space_for_time should not be set to TRUE if there are cache misses on execution calls. Cache misses indicate that the shared_pool_size is already too small, and forcing the pinning of shared SQL areas will only aggravate the problem.

Another way to improve performance on the library cache is to use the init.ora session_cached_cursors parameter. As you probably know, Oracle checks the library cache for parsed SQL statements, but session_cached_cursors can be used to cache the cursors for a query. This is especially useful for tasks that repeatedly issue parse calls for the same SQL statement—for instance, where a SQL statement is repeatedly executed with a different variable value. An example would be the following SQL request that performs the same query 50 times, once for each state:

select
   sum(dollars_sold)
from
   sales_table
where
   region = :var1;

In Oracle8i, we can also reduce excessive SQL reparsing by setting cursor_sharing=force. This initialization parameter will dynamically rewrite all SQL that contains literal values and replace the literals with host variables. For applications that dynamically generate SQL with embedded literals, cursor_sharing is a godsend that can dramatically reduce library cache overhead and improve the performance of SQL.

Now that we have reviewed techniques for efficiently using library cache storage, let’s look at a STATSPACK report that will show us what is happening inside Oracle. There are several metrics that address the inner workings of the library cache.


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