High parse to execution Ratio on database

[Gupta, Brijesh]

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

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Brijesh Gupta

Oracle Production DBA