Parse to execute ratio in oracle

Hello umar1farooq,

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

HTH

I

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.

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;
        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.

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

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

  87

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

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.