Re: is it possible to reinitialize v$sqlarea?

What on earth made you think to run catproc? Yikes!

Try:
ALTER SYSTEM FLUSH SHARED_POOL; Daniel Morgan

Amy Devine wrote:

> 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:36:21 CDT