High parse to execution Ratio on database

From: Gupta, Brijesh <Brijesh.Gupta_at_Airliquide.com>
Date: Thu, 13 Sep 2001 07:02:09 -0700
Message-ID: <F001.0038D25F.20010913070021@fatcity.com>

Hi
all,
 I am seeing very high parse/execution ratio. These statements are using bind variable.
Can somebody
help me understand why this is high.

Database :
8.0.5
Using Oracle
Application 10.7
SGA = 8.5
GB
Shared pool
: 1.5 GB

Lately I
have stated seeing the sqlarea miss and some shared pool and library cache contention.



PARSE_CALLS EXECUTIONS SQL
Text----------- ----------

---------------------------------------------

2456105 2456106 begin fndcp_tmsrv.monitor_self (:e, :a, :q,

c, :s, :d); end;

2455986 2455986 begin fndcp_tmsrv.read_R_pipe(:ec, :to,

:typ,

:enddt, :rid, :C_pipe, :nl, :usr, :rspap,

:r

sp, :log, :app, :prg, :argc, :a1, :a2,

:a3,

:a4, :a5, :a6, :a7, :a8, :a9, :a10,

a11, :a12, :a13, :a14, :a15, :a16, :a17,

:a18

, :a19, :a20); end;



392457     392457 begin fnd_dcp.request_lock (:h, :s, :e); end;     270960     311405 ALTER
SESSION SET NLS_LANGUAGE = 'AMERICAN'     114800     114800 savepoint
fadosgl_savepoint     114800
114800 select asset_category_id into :b0 from

fa_ad

ditions where asset_id=:b1



83462      83462 savepoint
fadrars_savepoint
83462      83462 update fa_books set

adjustment_required_stat

us='NONE' where (((book_type_code=:b0 and

ass

et_id=:b1) and

adjustment_required_status<>'N

ONE') and date_ineffective is null )

72822 72822 insert into

FA_DEPRN_DETAIL(book_type_code,as                       
set_id,distribution_id,period_counter,deprn_r                       
un_date,deprn_amount,ytd_deprn,deprn_reserve,                       
reval_amortization,reval_deprn_expense,reval_

reserve,ytd_reval_deprn_expense,cost,addition

<FONT face="Courier New" color=#000080

size=2>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Brijesh
Gupta
Oracle Production DBA

Received on Thu Sep 13 2001 - 09:02:09 CDT