Re: Expensive SQL

From: Mladen Gogala <no_at_email.here.invalid>
Date: Tue, 23 Aug 2011 16:20:52 +0000 (UTC)
Message-ID: <pan.2011.08.23.16.20.52_at_email.here.invalid>

On Tue, 23 Aug 2011 08:53:14 -0700, dombrooks wrote:

> BTW whilst that bug description mentions baseline capture as the area of
> the bug (or it did when I had cause to have it patched), that's only
> part of it.
> It's very much baseline usage that is affected as well.
>
> What that bug does illustrate is some of the recursive work involved
> with baselines even with capture off and usage on.
>
> So whilst the patch fixes the original issues of the full table scans
> against the sqlobj$auxdata and sqlobj$ objects, using INDEX hints by the
> look of it,
> just the execution of a baselined sql statement involves a couple of
> look ups against these tables and merge operations to update all the
> associated metadata.

I've turned off using the plan baselines altogether. I am also running the following:

SQL> declare
  2 cursor spm is select sql_handle from dba_sql_plan_baselines;   3 stat number;
  4 begin
  5 for c in spm loop
  6 stat:=dbms_spm.drop_sql_plan_baseline(c.sql_handle,null);   7 end loop;
  8 commit;
  9 end;
 10 /

Hopefully, that will clean things up.

-- 
http://mgogala.byethost5.com

Received on Tue Aug 23 2011 - 11:20:52 CDT