Re: Re-setting V$tables

At 10:42 PM 10/10/2006, Mladen Gogala wrote:

>Here's the deal: I have some long running applications which I want
>to profile, without
>restarting them. The applications are long running and not
>restartable without cleanup.
>Very surprising for developers, the application actually uses
>DBMS_APPLICATION_INFO. I
>need to create a profile and say which module is the most expensive
>one. I was investigating
>several possibilities:
>1) Resetting statistics table whenever module boundaries are crossed and
> thus collect the statistics with ease and elegance.
>2) Creating a table to which I would store the values at beginning and at the
> end, forcing me to write a SQL to calculate them. Unfortunately,
> I'll have
> to do this.
>3) Writing a Perl script which would produce the desired results by
>storing the
> intermediate results in hashes. Unfortunately, this is more work then 2),
> especially if I use analytic functions like "lead" or "lag".
>
>I was trying to see whether there is a way of getting away with
>option 1) under 10.2.0.2.
>Apparently not.

You could create GTT "copies" for the v$ views you are interested in. At the beginning of a module you just copy the current content of the v$view into the corresponding GTT and at the end of the module you just select the diff of the v$view and its GTT before starting the next cycle. Hardly more work than option 1.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l