Re: CBO problem

From: eagle fan <eagle.f_at_gmail.com>
Date: Wed, 19 Jul 2006 16:54:37 +0800
Message-ID: <4415a5ed0607190154w4cca6fe7k1b08776ebfceba21@mail.gmail.com>

hi:

refresh the shared pool is too expensive.

Actually any ddl can invalidate it and cause hard parse.

You can do some light ddl on the tables, like allocate extent or grant select privilege.

Here is the test for you:

SQL> @test
SQL> select /* ddl_reparse */ count(*) from test;

  COUNT(*)

---

    378944

SQL> select hash_value,first_load_time,last_load_time from v$sql where sql_text like '%ddl_reparse%' and sql_text not like '%hash_value%';

HASH_VALUE FIRST_LOAD_TIME
LAST_LOAD_TIME

---------- ---------------------------------------------------------
---------------------------------------------------------

1068216913 2006-07-19/01:51:32
2006-07-19/01:51:32

SQL> alter table test allocate extent;

Table altered.

SQL> select hash_value,first_load_time,last_load_time from v$sql where sql_text like '%ddl_reparse%' and sql_text not like '%hash_value%';

HASH_VALUE FIRST_LOAD_TIME
LAST_LOAD_TIME

---------- ---------------------------------------------------------
---------------------------------------------------------

1068216913 2006-07-19/01:51:32

SQL> @test
SQL> select /* ddl_reparse */ count(*) from test;

  COUNT(*)

---

    378944

SQL> select hash_value,first_load_time,last_load_time from v$sql where sql_text like '%ddl_reparse%' and sql_text not like '%hash_value%';

HASH_VALUE FIRST_LOAD_TIME
LAST_LOAD_TIME

---------- ---------------------------------------------------------
---------------------------------------------------------

1068216913 2006-07-19/01:51:32
2006-07-19/01:52:00

On 7/18/06, Nirmalya Das <nirmalya_at_hln.com> wrote:
>
> My queries do include bind variables....
>
> Because of the problem I stated, I flush the shared pool once in a
> while and the
> problem goes away.....
>
> But this is a very expensive operation and of course NOT a permanent
> fix....
>
> What alternatives do we have.....
>
> Is Column Histograms creating the trouble? Should I turn it off?
>
> Quoting Wolfgang Breitling <breitliw_at_centrexcc.com>:
>
> > My guess is that it has nothing to do with IOT and only indirectly with
> > automatic statistics gathering. Unless you changed the default
> > method_opt, the
> > 10g automatic statistics gathering job will gather histograms on all
> columns
> > used in predicates (at least that is an improvement over the "for all
> indexed
> > columns" nonsense). If your queries include bind variables you could see
> the
> > result of different plans being generated due to bind variable
> > peeking at parse
> > time - and then that plan being used for all other BV until the plan
> > ages out or
> > gets invalidated.
> >
> > Quoting Nirmalya Das <nirmalya_at_hln.com>:
> >
> >>
> >> Some queries (which involves some Index Organized Tables) runs like a
> charm
> >> for
> >> some time and once in a while will a get a bad execution plan and will
> >> exceed
> >> the CPU limit assigned in the user profile.
> >>
> >> We use the "Automatic Statistics Gathering" to collect statistics.
> >>
> > --
> > regards
> >
> > Wolfgang Breitling
> > Oracle 7,8,8i,9i OCP DBA
> > Centrex Consulting Corporation
> > www.centrexcc.com
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Eagle Fan

Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 19 2006 - 03:54:37 CDT