Very bad Execute to Parse ratio
- From: "Paul Sherman" <psherman@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 Oct 2005 11:24:40 -0400
Hello all,
Would anyone care to give me some feedback/pointers on improving the
ratio of # of parses to # of executions?
Background:
Oracle 9i (9.2.0.6). Using STATSPACK, YAPP. Session_cached_cursors set
to 500. Open_cusrsors set to 1000. Cursor_sharing set to SIMILAR.
Serial_reuse = DSIABLE. Cursor_space_for_time = FALSE. Shared_pool size
is 320MB, much larger that I need right now, but will need all of it
(perhaps more than I can get at) once load/stress testing begins next
month. Same deal with buffer_cache.
Reason for question: trying to get as much performance tuning done
before the application and its database are exposed to a much greater
stress.
For many of the application's SQL, I see a 1-to-1 relationship between
parses and executions (3 examples below). Now, I know I should, for best
performance, parse once and execute many times. I've reviewed the
documentation on how to reduce the parses, but as I see bind variables
being used, I am unsure as to where to go from here, esp. what to tell
the application people. Note: When cursor_sharing was set to EXACT, I
got worse performance that I am getting now.
SQL_TEXT
P_CALLS SHAR_MEM PERS_MEM LOAD_VER OPEN_VER FETCHES
EXECUTIONS LOADS INVALS CPU_TIME LAST_LOAD
----------------------------------------------------------------------
---------- ------------- ----------
---------- ---------- ---------- ----------
---------- ---------- ---------- -------------------
SELECT VERSION FROM LICENSE WHERE KEY_C 10702 8873
620 1 1 10702
10702 1 0 1125000
2005-10-18/09:48:25
ODE = :WHERE_KEY AND PROF_NAME = :WHERE_
PROF
SELECT VERSION FROM GROUPS_VERSIONS WHE 3277 8260
620 1 1 3277
3277 1 0 218750
2005-10-18/09:48:25
RE KEY_NUM = :WHERE_KEY AND PROF_NAME =
:WHERE_PROF
SELECT VERSION FROM PROCESSES_DEFINITIO 1816 8539
620 1 1 1816
1816 1 0 78125
2005-10-18/09:48:26
NS WHERE KEY_CODE = :WHERE_KEY AND PROF_
NAME = :WHERE_PROF
Below, you can see that the Execute to Parse ratio is ridiculously low.
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.90 In-memory Sort %: 100.00
Library Hit %: 99.47 Soft Parse %: 99.90
Execute to Parse %: 3.15 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 102.00 % Non-Parse CPU: 96.21
Any help/assistance would be greatly appreciated.
Regards,
Paul
------------------------------
Paul R. Sherman
Database Administrator
Turbine Inc.
60 Glacier Drive, Suite 4000
Westwood, Ma. 02090
Phone (781) 407-4139
Fax (781) 329-5463
psherman@xxxxxxxxxxx
-------------------------------
Other related posts:
