Re: timed_statistics anyone?

<snip>....

>If you switch on timed statistics, the server has to issue two calls to
>the system timer ( before and after each operation ) for every parse,
>execute and fetch, including recursive SQL, executed by the server. For
>even moderately used apps, this will amount to 100's of millions of timer
>calls per day.

||| Timing captures alot more that SQL parsing. I *think*, but do not

    know, that oracle's internal timing resolution is very crude, but that     it also internally posts wait events, disk reads, etc *within* clock     ticks ( v$timer rate? better? ) In that way, it can know that something     waited, but not for how long. Witness the "WAITED SHORT TIME" entry     in, e.g. v$session_wait. If I tick every .01 sec, and I get ten     reads done in that period, oracle will give you the ten count for     the number of reads, and compute the "average wait" as one millisecond.     ....which may be accurate for a logical read. I may do, to continue the     example, one thousand latches, for an "average" latchwait of 10 microseconds.

    In reality, some latches are likely faster ( 10 microseconds is a looonnnggg time

    for a multiple hundreds of MHz chip ) and some may spin and be longer. I don't

    think oracle tracks these directly -- you get to figure it out by looking at the

    count of operations, time to complete (count) operations, number of spins and

    waits in that timeframe, etc. etc.

    So, in short ( finally! ) I don't think oracle times everything at it's natural evolution.

    That would *not* work, period. What I think it does, however, is run a crude clock,

    and count all events, and between the two, can often get good averages....

>
>However, in my experience, the overhead is actually very little. I run
>timed_statistics and sql_trace = true on my production servers all day
>every day. No one notices when I switch it on, or occasionally switch it
>off. Note that sql_trace = true probably adds an even larger load, as it
>is continually generating trace reports and writing them to disc.
>

|| If no one notices, that's the whole ball of wax, isn't it? Received on Fri Oct 23 1998 - 09:34:20 CDT