Re: Trace file Anomolies

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: 12 Nov 2004 15:52:46 -0800
Message-ID: <4ef2fbf5.0411121552.1aa579dc@posting.google.com>

mccmx_at_hotmail.com (Matt) wrote in message ...

> > My guess is that the (recreatable) chunk(s) containing the plan in the
> > library cache got flushed out, and since the STAT lines contain
> > nothing more than the plan, they got simply skipped.
> >
> > If that's correct, all it takes is a stressed library cache (eg no
> > bind variables) that is trapped in the vicious loop fill/flush
> > partially/fill again/etc and you are very likely to observe missing
> > STAT lines.
> >
>
> This sounds like a perfectly viable answer - my shared pool is
> overworked as a result of some SQL using literals so this could very
> well be the case.

I think it is the case. I've artificially stressed the library cache by running the following code (on an otherwise idle instance, 9.2.0.5) in another session, just before exiting sqlplus in the main session:

declare
  l_cursor integer;
begin
  for i in 1..100000000 loop
    l_cursor := dbms_sql.open_cursor;
    dbms_sql.parse (l_cursor,

                    'select '||i||' from dual', 
                    dbms_sql.native);

    dbms_sql.close_cursor (l_cursor);
  end loop;
end;
/

And the STAT line was missing - so same behaviour seen for "alter system flush shared pool".

Moreover, at the end the plan was actually missing from the library cache:

dellera_at_ORACLE9I> select address from v$sql where sql_text = 'select * from dual';

ADDRESS

---

6AD2859C
6AD2859C

dellera_at_ORACLE9I> select count(*) from v$sql_plan where address in (select address from v$sql where sql_text = 'select * from dual');

  COUNT(*)

---

         0

dellera_at_ORACLE9I> select * from dual;

DUM

---
X

dellera_at_ORACLE9I> select count(*) from v$sql_plan where address in
(select address from v$sql where sql_text = 'select * from dual');

  COUNT(*)
----------
         2

Interesting ...
Alberto Dell'Era

Received on Fri Nov 12 2004 - 17:52:46 CST