Re: pro*c performance question

>>>>> Regarding Re: pro*c performance question; saeed_at_ov.COM (Saeed Talebbeik) adds:
  ST> NNTP-Posting-Host: wylie.ov.com

  ST> Thanks to the two folks from Oracle that responded to my question regarding the
  ST> pro*c performance question. Yesterday I did run the tkprof on my program. There is
  ST> one point about my queries that I did not mention. These queries are against the
  ST> psuedo tables that collect statistics. I am talking about the v$.. tables. I have
  ST> noticed that anytime that we perform a join operation on any of these tables the amount
  ST> of time spent in the "fecth" phase go sky high. For example here is the output of the tkprof
  ST> for one statement:
  ST> Select nvl(sst.value,0) int b:1 from sys.v$sysstat sst , sys.v_$statname ssn
  ST> where ssn.name=:b2 and ssn.statistic#=sst.statistic#
  ST>           count     cpu    elap   phys   cr   cur   rows
  ST> Parse:     1         4      4      0     0     0   
  ST> Execute:  201        8      25     0     0     0     0
  ST> Fetch:    201       289    301     0     0     0    201

I don't know what kind of machine you're using, but an average elapsed time of around 1.5 milliseconds per fetch is pretty good... In your opinion, what would be a fully acceptable response time for this ?

  ST> This is the statement that has been executed many times. As you can see we have parsed
  ST> the statement only once which is good news. We have executed many times (201 times) and
  ST> the execute time is only 8 however the fetch phase is 289. Please note that this 
  ST> query returns one row as its result so the array processing will not solve the
  ST> problem here. 

Is your program polling certain statistics ? If you're using Oracle7, you might consider using a database procedure to do the polling, and then distribute values with the dbms_pipe or dbms_alert package.

Is your program selecting a limited number of (arbitrary) statistics ? You might consider building a dynamic WHERE-clause (yes, this would mean re-parsing). The benefit would be using the array-interface for a single fetch. Construct a WHERE clause like:

select ... where sst.statistic# in (1,2,5,4,34,65,66,.....) or
select ... where ssn.name in ( 'recursive calls', 'physical reads', ... )

  ST> I also talked to Oracle tech support and they think that since pseudo tables are 
  ST> not referenced by row number in the memory it will take a long time to sort things out.
  ST> Also it seems like the close statement which I have still left in my code has not 
  ST> affected the parsing phase. In other words the preprocessor has not released all
  ST> the cursor resources and it can still find the context in memory thus avoiding reparsing.

Point taken.

  ST> According to Oracle documents the fetch phase is the phase where rows get selected
  ST> and sorted. Does this mean that it is the fetch phase that does most of the
  ST> work and not the execute phase? Anyway I will try to play with this query maybe 

Without getting too technical, I think you can assume that Oracle will do all the work in the fetch-phase whenever possible. Of course there are lots of situations where this would be impossible: for example if you require the result to be sorted or need to use temporary segments (for set-operations, distinct, group by, etc). Please note that these operations might not always need temporary segments, it's hard to generalize.

  ST> changing the order of things to see if there is any effect. Again if any of the
  ST> folks out there have any insight into this I will appreciate if they could post a 
  ST> reply.
  ST>                                                                      Regards,
  ST>                                                                    <<<saeed>>>

Hope this helps,

Magnus

--

Magnus Lonnroth
Tech.Sales & Consultant
Oracle Sweden
Mail: mloennro_at_oracle.com