Re: Very bad Execute to Parse ratio

Hi

Why would you set CURSOR_SHARING=SIMILAR in the presence of bind variables? It seems a little odd to me. In what way was the performance worse?

The most common reason for getting repeated parses is for the app developer to do (pseudo code)

for each i in loop
preparestatement (sql)
bind variable (i)
execute
next i

instead of

preparestatement
for each i in loop
bind variable(i)
execute
next i

On 10/18/05, Paul Sherman <psherman_at_turbine.com> wrote:
>
> 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 <http://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
> -------------------------------
>
>

--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

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