Re: elapsed time less cpu time???

On Fri, 14 Nov 2003 16:10:14 -0000, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>If you look at the trace file you will find that
>elapsed time is reported in microseconds,
>but cpu time is reported with a granularity
>of 1/100 sec, which is then faked into micro
>seconds - The figures will all be multiples of
>10,000 (perhaps with with a value like 17 added
>to confuse the issue).
>
>So CPU times will be zero on may lines, and
>then 10,000 on one line - whilst the ela might
>run at a steady few hundred per line. The final
>result is probably due to rounding errors across
>the 1,335 fetches that are recorded.
>
>50,000 rows per second seems quite reasonable
>to me - I bet your developer can't read them that
>quickely ;)

Thanks for the explanation. Makes sense to me.

Just as a follow-on "rest of the story" as I was starting to dig into this, the developer came back and said "I found it - it wasn't Oracle. Never mind." Typical.

But, since I don't get to do much of this kind of investigation, and even less when I'm not under the gun, I decided to persue it further, just as an educational exercise. I discovered that the table had never been analyzed, so ran dbms_stats.gather_schema_stats, then re-ran the trace. The plan went from

Table Access by Index Rowid

    Index Range Scan

to

Full Table Scan

and execution went from 0.44 sec elapsed to 0.21 secs elapsed. Further evidence that FTS is not always evil.

And with that level of savings, I've already spent more time on this than will be saved over the life of the application! Received on Fri Nov 14 2003 - 11:15:51 CST