• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3561
  • Last Modified:

Parse to execute ratio in oracle


 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,


  • 3
  • 2
  • 2
  • +5
1 Solution
Ivo StoykovCommented:
Hello umar1farooq,

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


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.

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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark GeerlingsDatabase AdministratorCommented:
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!
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.
>>get db_files error
     sorry, not "error". You might check to see if you have unnecessary data files for same tablespace within one disk.
What is output of the following commands.

SQL> show parameter db_files

SQL> select count(*) from dba_data_files;
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.
umar1farooqAuthor Commented:
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
umar1farooqAuthor Commented:
the output of this query is 87
select count(*) from dba_data_files;

Mark GeerlingsDatabase AdministratorCommented:
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")
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
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 2
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now