Re: Re-setting V$tables

Here is one that resets all of them and for all users:

shutdown force

Creating a new session will almost take care of v$sesstat for you. It depend on triggers and client tool you use for this. They may do some after logon activity for you.

In practice you need to take a snapshot from session/system/sql views before you execute your test then take the delta from the numbers after the test execution. Restarting and memory flushing will make your test result unrealistic. The database may be forced to do physical I/O in place of logical and do some extra parsing.

There are tools/scripts available in Oracle and Internet to download including statspack/AWR, Tom Kyte's runstat (http://asktom.oracle.com/~tkyte/runstats.html), and others.

Sergey

On 10/10/06, Bobak, Mark <Mark.Bobak_at_il.proquest.com> wrote:
> V$SQL - alter system flush shared_pool; ;-) (Of course, only works for
> non-pinned SQL.)
>
> V$SESSTAT/V$SYSSTAT - I don't think it's possible.
>
> I did read, in a presentation from Anjo, I think, that it's possible to
> reset stats on disk I/O...but damned if I can remember how....I'll see
> if I can find it......
>
> Found it! From Anjo's "San Stories" available as PDF on
> http://www.oaktable.net/ , exec dbms_system.kcfrms will reset stats
> shown in: V$FILESTAT, V$TEMPSTAT, and V$SESSION_EVENT for I/O related
> events.
>
> I think you're out of luck for V$SESSTAT/V$SYSSTAT....
>
> Hope that helps,
>
> -Mark
>
>
> --
> Mark J. Bobak
> Senior Oracle Architect
> ProQuest Information & Learning
>
> There is nothing so useless as doing efficiently that which shouldn't be
> done at all. -Peter F. Drucker, 1909-2005
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala
> Sent: Tuesday, October 10, 2006 9:01 PM
> To: oracle-l
> Subject: Re-setting V$tables
>
> Does anybody know how to reset s$sesstat, v$sysstat and v$sql without
> restarting the database?
>
> --
> Mladen Gogala
> http://www.mladen-gogala.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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