is it possible to reinitialize v$sqlarea?

From: Amy Devine <amy.devine_at_telos.com>
Date: 10 Apr 2002 08:26:47 -0700
Message-ID: <9c9691fe.0204100726.565f5fda@posting.google.com>

I've been running a PL/SQL program against the v$sqlarea view to identify problem queries in our database. Now that Now I've tuned some of the queries, it is possible to re-initialize the v$sqlarea view to flush out the information about the "old" queries? I've run catproc.sql, but I'm still seeing my "old" statistics. Do I just have to let the system run for awhile and wait for the untuned queries to roll off of my Top25 worst list?

The query that I've been running is:

DECLARE
 top25 number;
 text1 varchar2(4000);
 x number
 len1 number;
CURSOR c1 is
 SELECT disk_reads, substr(sql_text,1,4000)  FROM V$SQLAREA
 ORDER BY disk_reads, desc;
BEGIN
 dbms_output.put_line('reads'|| ' ' ||'text');  OPEN c1;
 for i in 1..25 LOOP
   fetch c1 into top25, text1;
   dbms_output.put_line(rpad(to_char(top25),9||' '||substr(text1,1,66));

   len1:=length(text1);
   x:=66;
   while len1 > x-1 LOOP

     dbms_output.put_line('"      '||substr(text1,x,66));
     x:=x+66;

   END LOOP;
  END LOOP;
END; Thanks,
Amy Devine Received on Wed Apr 10 2002 - 10:26:47 CDT