Re: pro*c performance question

In article 93Nov4212841_at_demo1.se.oracle.com, mloennro_at_se.oracle.com (Magnus Lonnroth) writes:
> >>>>> 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 ?
>

My assumption was based on the fact that all the v$ tables are pinned into memory and since I will not have any io overhead I expected to see much less fetch time. As you mentioned it seems like there is much more happening in the fetch phase than I originally thought.

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

Yes I am polling for statistics. I am using Oracle 6. Your point about Oracle 7 is well taken. There might be more benefit in using stored procedures than doing the execute and fetch yourself.

> 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', ... )
>

Yes that is right. I will take your suggestion and play with array processing to see if there is any change.

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

Yes , it seems like a lot is happening during the fetch phase than simply accessing the rows in the active set and loading them into correct variables.

> Hope this helps,
>
> Magnus
> --
>

Thanks for your reply. Your suggestions and answers have been very helpful.

                                                       Regards,
                                                      <<<saeed>>>