RE: Very bad Execute to Parse ratio

Most of your parses are "soft" (99.9%).
So, could it be that your app does lots of connects/disconnects?

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Paul Sherman
Sent: Tuesday, October 18, 2005 11:25 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Very bad Execute to Parse ratio



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: