Donald K. Burleson Oracle Tips

Measuring high SQL parse calls inside Oracle

One of the first things that an Oracle DBA does when checking the performance of any database is to check for high-use SQL statements.  The script below will display all SQL where the number of parse calls is more than twice the number of SQL executions.  The output from this script is a good starting point for detailed SQL tuning.  This query can also be modified to display the most frequently executed SQL statements that reside in the library cache.

prompt

prompt **********************************************************

prompt  SQL High parse calls

prompt **********************************************************

prompt

select

   sql_text,

   parse_calls,

   executions

from

  v$sqlarea

where parse_calls > 300

and

   executions < 2*parse_calls

and

executions > 1;

This script is great for finding non-reusable SQL statements that contain embedded literals.  As you may know, non-reusable SQL statements place a heavy burden on the Oracle library cache.  Prior to Oracle8, system with lot’s of non-reusable SQL have to have the library cache downsized (by decreasing the shared_pool parameters) or by periodically issuing a “alter system flush shared pool” command.

In Oracle8, you can take advantage of the cursor_sharing initialization parameter.   When cursor_sharing-FORCE, Oracle8i will re-write the SQL with literal values so it can use a host variable instead.  This is a great “silver bullet” for system where the literal SQL cannot be changed.

We will explore cursor_sharing in detail in a later tip.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm