Very bad Execute to Parse ratio

From: Paul Sherman <psherman_at_turbine.com>
Date: Tue, 18 Oct 2005 11:24:40 -0400
Message-ID: <C42B11DE285BAA418A797409390EA557647D45@MAIL01.i.turbinegames.com>

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_at_turbine.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 18 2005 - 10:28:03 CDT