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@xxxxxxxxxxx> 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@xxxxxxxxxxx > ------------------------------- > > -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com