Re: CBO problem
- From: "eagle fan" <eagle.f@xxxxxxxxx>
- To: nirmalya@xxxxxxx
- Date: Wed, 19 Jul 2006 16:54:37 +0800
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@xxxxxxx> 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@xxxxxxxxxxxxx>:
> 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@xxxxxxx>:
>
>>
>> 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
--
//www.freelists.org/webpage/oracle-l
--
Eagle Fan
Oracle DBA
Other related posts:
