Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: is it possible to reinitialize v$sqlarea?

Re: is it possible to reinitialize v$sqlarea?

From: Amy Devine <amy.devine_at_telos.com>
Date: 12 Apr 2002 12:53:09 -0700
Message-ID: <9c9691fe.0204121153.4fa1319e@posting.google.com>


Daniel,

oops, i meant catalog.sql, not catproc. i ran catalog.sql because according to documentation, it's function is to "create view of data dictionary tables and dynamic performance views."

But either case, I'd already tried the ALTER SYSTEM FLUSH SHARED_POOL command, but to no avail. I ran my procedure, then did the flush and ran my procedure again and got the same statistics.

Same thing when i ran catalog.sql. The first column of my procedure, "reads" did not zero out for any of the queries. They were either the same, or greater, than when I ran it the previous time.

Amy

Daniel Morgan <damorgan_at_exesolutions.com> wrote in message news:<3CB45BF1.C71878F1_at_exesolutions.com>...
> 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 Fri Apr 12 2002 - 14:53:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US