Solved

Parse to execute ratio in oracle

Posted on 2006-07-05
13
3,370 Views
Last Modified: 2008-02-26

Hi,

 I have oracle9i enterprise edition release 9.2.0.6.0 - 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,

regards,

 
0
Comment
Question by:umar1farooq
  • 3
  • 2
  • 2
  • +5
13 Comments
 
LVL 22

Expert Comment

by:Ivo Stoykov
Comment Utility
Hello umar1farooq,

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

HTH

I
0
 
LVL 14

Expert Comment

by:sathyagiri
Comment Utility
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.

0
 
LVL 16

Expert Comment

by:RCorfman
Comment Utility
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.
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 125 total points
Comment Utility
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!
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
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.
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
>>get db_files error
     sorry, not "error". You might check to see if you have unnecessary data files for same tablespace within one disk.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
What is output of the following commands.

SQL> show parameter db_files

SQL> select count(*) from dba_data_files;
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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;
        or
        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.
0
 

Author Comment

by:umar1farooq
Comment Utility
with the following query i got these results

select request_misses, request_failures, free_space
from V$shared_pool_reserved


COL1=REQUEST_MISSES,   1,1,39
COL2=REQUEST_FAILURES,  1,41,39
COL3=FREE_SPACE,   1,81,39

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

Author Comment

by:umar1farooq
Comment Utility
the output of this query is 87
select count(*) from dba_data_files;

  87
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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)
SELECT PROGRAM   FROM CUS_PROGRAM  WHERE CUS_NBR = :b1

Example with no bind variable (a literal value "10")
SELECT START_DAY   FROM MRP_WEEKS  WHERE WEEK_NBR = 10
0
 
LVL 16

Expert Comment

by:RCorfman
Comment Utility
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
0
 
LVL 19

Expert Comment

by:actonwang
Comment Utility
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now