Parse to execute ratio in oracle

Posted on 2006-07-05
Medium Priority
Last Modified: 2008-02-26


 I have oracle9i enterprise edition release - 64-bit .
 In toad  Server Statistics, i have seen the following analysis,

Library  Cache Get Hit  Ratio - 89,2053 -- Dynamic or Unsharable SQL ?
Parse to Execute Ration - 84,1742 -- High Parse to Execute ration
DBWR Average Scan Depth - 765,7342 -- Number of DB_FILES too high

Last two are red, i think database require some tuning,
 Please, some body tell me what i should do to resolve it,


Question by:umar1farooq
  • 3
  • 2
  • 2
  • +5
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 17042202
Hello umar1farooq,

where do you find these lines? SQLPlus? What the stadpack sais?


LVL 14

Expert Comment

ID: 17042206
High Parse to Execute ratio
When a new SQL statement is issued and it does not exist in the shared pool then it has to be parsed fully.This is referred to as hard parsing and is expensive in terms of CPU.

Hard parsing might some times requqire application related changes. For example you might want to consider using prepared statements in your application.

Also you could think of increasing your shared pool size.

Abd regarding the NUMBER of DB_FILES too high issue, TOAD always seems to compalin about this.
Starting from ORacle 9i, this shouldn't be a problem. I guess you could ignore it.

LVL 16

Expert Comment

ID: 17043065
You need to ensure that the programmers are using bind variables.  If they are not, incrasing the shared pool size will actually decrease performance, because no matter how large you make the shared pool, it will get filled with unique statements, then have to search a larger pool before it determines that.

There are two solutions, the first is to look in the v$sql table for sql statement that are 'similar'. Meaning, it is like:
select a,b,c from mytable where x='ONEVAL'
and another like
select a,b,c from mytable where x='TWOVAL'

if you find hundreds (or even thousands) of statements that look identical, but have different x='VALUE', clauses, then these are the sql statements that need to be tackled.  They should look something like
select a,b,c from mytable where x=:VARNAME

If you are unable to have the source app changed, for instance it is a commercial package that you have purchased, then the solution to this problem is to use a login trigger for the logins that use the offending application.  In the login trigger, execute:
alter session set cursor_sharing = similar

This will force oracle to change ALL literal values to bind variable.

The problem could indeed be unprepared statement, or small shared pool, but typically is just use of literals in statements that should have bind variables. Note that it is ok to have literals in sql statement, but not for values that change for each statement. If the value is suppose to be static, then it is fine as a literal.
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

LVL 35

Accepted Solution

Mark Geerlings earned 500 total points
ID: 17043662
The number of DB_FILES is a relatively simple issue.  How many database files does your database have now?  What size are they?  Does your server O/S support files larger than 2GB?

The other two issues ("Library Cache Get Hit Ratio" and "Parse to Execute Ratio") are related.  I think RCorfman is correct, this is probably caused by an application that does not use bind variables.  If your application was developed on SQL Server, or claims to "support" SQL Server, Oracle and other databases, it may not be optimized for Oracle.  An after-logon trigger like RCorfman suggested, may be your best solution to this, but you will have to test that!  Some applications tolerate this and don't have a problem with it, but others do not!
LVL 19

Expert Comment

ID: 17043933
agree with RCorfman, your problem most likely is located in your application.

not sure why you get db_files error, it might try to  show that you may have too many data files on a same physical disk. Ideally you would spread different data files between different disks or controllers.
LVL 19

Expert Comment

ID: 17043941
>>get db_files error
     sorry, not "error". You might check to see if you have unnecessary data files for same tablespace within one disk.
LVL 16

Expert Comment

ID: 17048118
What is output of the following commands.

SQL> show parameter db_files

SQL> select count(*) from dba_data_files;
LVL 48

Expert Comment

ID: 17049026
Easy way to workaround lack of bind variables:

CURSOR_SHARING parameter (8.1.6 onwards)
<Parameter:CURSOR_SHARING> is a new parameter introduced in Oracle8.1.6. It should be used with caution in this release. If this parameter is set to FORCE then literals will be replaced by system generated bind variables where possible. For multiple similar statements which differ only in the literals used this allows the cursors to be shared even though the application supplied SQL uses literals. The parameter can be set dynamically at the system or session level thus:
        ALTER SESSION SET cursor_sharing = FORCE;
        ALTER SYSTEM SET cursor_sharing = FORCE;
or it can be set in the init.ora or SPFILE (9i onwards) file.
Note: As the FORCE setting causes system generated bind variables to be used in place of literals, a different execution plan may be chosen by the cost based optimizer (CBO) as it no longer has the literal values available to it when costing the best execution plan.
In Oracle9i, it is possible to set CURSOR_SHARING=SIMILAR. SIMILAR causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized. This enhancement improves the usability of the parameter for situations where FORCE would normally cause a different, undesired execution plan. With CURSOR_SHARING=SIMILAR, Oracle determines which literals are "safe" for substitution with bind variables. This will result in some SQL not being shared in an attempt to provide a more efficient execution plan.

Author Comment

ID: 17049084
with the following query i got these results

select request_misses, request_failures, free_space
from V$shared_pool_reserved

COL3=FREE_SPACE,   1,81,39

please tell me are these results are ok, require to increase in shared pool

Author Comment

ID: 17049095
the output of this query is 87
select count(*) from dba_data_files;

LVL 35

Expert Comment

by:Mark Geerlings
ID: 17050163
That number of datafiles (87) is larger than some Oracle databases, but it shouldn't be a problem.  I'm curious, are most of these files the same size, and is that size close to the maximum for your server O/S?

Before you make changes to your shared_pool_size, you need to determine whether your application uses bind variables, or not.  If not, making your shared_pool_larger may just make performance worse.  Try this query, and tell us whether your statements look more like the first example below from our system (with a bind variable) or the second example (without a bind variable):

select sql_text from v$sqlarea

Example with a bind variable (:b1)

Example with no bind variable (a literal value "10")
LVL 16

Expert Comment

ID: 17051346
in regards to the bind variable issue, This can be quite serious issue for the database performance. I do not recommend using cursor_sharing=force. Use cursor_sharing=similar if necessary. If this is an application you have control over though, you are much, much better off working with the programmers to have bind variables utilized. The database's optimizer can do a better job if it is aware of intended literals than it can will all forced literals.  That said, in the application I support (it is a commercial package), we have to use cursor sharing and it made a significant difference in our database performance.

In regards to the datafile question. This could be related to particular table(s) being spread across multiple data files. Do you have multiple datafile associated to a single tablespace? Or are they only 1 or 2 datafiles per tablespace? Try this query and let us know the results.
select tablespace_name,count(*) from dba_data_files
 group by tablespace_name having count(*) > 3
LVL 19

Expert Comment

ID: 17051397
as I said:

>>You might check to see if you have unnecessary data files for same tablespace within one disk.

      select file_name,tablespace_name from dba_data_files
      order by tablespace_name

try to see if that is true.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question