Re: pro*c performance question

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

          count     cpu    elap   phys   cr   cur   rows
Parse:     1         4      4      0     0     0   
Execute:  201        8      25     0     0     0     0
Fetch:    201       289    301     0     0     0    201

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

I also talked to Oracle tech support and they think that since pseudo tables are not referenced by row number in the memory it will take a long time to sort things out.

Also it seems like the close statement which I have still left in my code has not affected the parsing phase. In other words the preprocessor has not released all the cursor resources and it can still find the context in memory thus avoiding reparsing.

According to Oracle documents the fetch phase is the phase where rows get selected and sorted. Does this mean that it is the fetch phase that does most of the work and not the execute phase? Anyway I will try to play with this query maybe changing the order of things to see if there is any effect. Again if any of the folks out there have any insight into this I will appreciate if they could post a reply.

                                                                     Regards,
                                                                   <<<saeed>>>