Execute to Parse %:

From: Brian <brian_at_databaseknowledge.com>
Date: 28 Jun 2005 11:12:27 -0700
Message-ID: <1119982347.127514.304240@g47g2000cwa.googlegroups.com>

Buffer Nowait %: 100.00
Redo NoWait %: 99.99
Buffer Hit %: 99.87
In-memory Sort %: 100.00
Library Hit %: 99.54
Soft Parse %: 99.21
Execute to Parse %: 72.79
Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 93.49 %
Non-Parse CPU: 98.17

The Execute to Parse Ratio is always in the 68-74% range. I don't understand why, the db runs one pl/sql application. The application has 50,000 lines in 80 packages. Every sql statement in the application uses bind variables.

The shared pool has 45M free.

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache;
96.056

select
  to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,

  to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%'
soft_parses,
  to_char(100 * hard / calls, '999990.00') || '%'  hard_parses

from
( select value calls from sys.v_$sysstat where name = 'parse count
(total)' ),
( select value hard from sys.v_$sysstat where name = 'parse count
(hard)' ),
( select value sess from sys.v_$sysstat where name = 'session cursor
cache hits' )

Shows: 0.00%, 96.53%, 3.47% -- I have cursor caching set to 0 for now

Anyone have any suggestions? Received on Tue Jun 28 2005 - 13:12:27 CDT