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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Plan stability

RE: Plan stability

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Tue, 02 Dec 2003 06:59:42 -0800
Message-ID: <F001.005D86B8.20031202065942@fatcity.com>


No, you don't.
You could use dbms_stats to "create" (without analyzing) your statistics (if you know, what kind of data you'll be getting), and then store outline.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Wartiak Rastislav
Sent: Tuesday, December 02, 2003 7:15 AM To: Multiple recipients of list ORACLE-L

I agree, but still you have load data, analyze tables, check explain plan that this is what you actually wanted and store outline.

rw

> Correct.  The point is that stored outlines can be viewed as
> a tool for
> those who like the "predictability" of the RBO.  When the RBO is no
> longer available, the best way I know of to force the CBO to use your
> plan is stored outlines. 
> 
> Gudmundur
> 
>> -----Original Message-----
>> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On Behalf
>> Of Wartiak Rastislav Sent: 2. desember 2003 09:39
>> To: Multiple recipients of list ORACLE-L
>> Subject: RE: Plan stability
>> 
>> 
>> AFAIK RBO cannot be used for partitioned tables, not talking
>> about the fact that RBO might not be supported in future versions.
>> 
>>> What is complicated about stored outlines?  If you don't like those
>>> you can always go back to the RBO.
>>> 
>>> Gudmundur
>>> 

>>>> -----Original Message-----
>>>> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On
>>>> Behalf Of Wartiak Rastislav Sent: 2. desember 2003 08:44
>>>> To: Multiple recipients of list ORACLE-L
>>>> Subject: Plan stability
>>>>
>>>>
>>>> Hi,
>>>>
>>>> my question is about the same, but more general. How can i force
>>>> Oracle to use my prefered way of explain plan and not use CBO's. I
>>>> mean, apart from stored outlines, it somehow seems to complicated.
>>>> I would like to say what order and join types it should use. But,
>>>> try as I might, I many times cannot force Oracle to use my way,
>>>> even though I know it is possible, for I saw this kind of explain
>>>> plan for that specific query..
>>>>
>>>> I tried to use hints like ordered and use_hj etc. Can someone give
>>>> some examples of full set of hints for some simple queries?
>>>>
>>>> Thx,
>>>> rw
>>>>
>>>>> Hi, list friends:
>>>>>     We are using partition to archive history data in our
>>>>> production OLTP database. We get great performance gains(Far less
>>>>> disk io), but we also hit performance trouble sometimes. So I am
>>>>> here ask for your experience. 
>>>>> 
>>>>>     We used local index on all partitioned tables.We add/drop
>>>>> partition monthly to archive the history data.
>>>>> 
>>>>>     But the trouble is, when add/drop partition is being done on
>>>>> the partitioned table, CBO sometimes changed SQL execution path.We
>>>>> implemented partition 2 monthes ago, and in the first time,
>>>>> add/drop partition went on quite smoothly, but in the second time
>>>>> we add/drop partition, two SQL (just TWO SQL) get bad execution
>>>>> path and server load rushed to 10 times(from 2 to 20 in uptime),
>>>>> all waiting for latch free event. It severely affected our
>>>>> application. We are an online system and we do not have scheduled
>>>>> time every month so we have to add/drop partition while db is
>>>>> still running. 
>>>>> 
>>>>>     So, with system still up and running, how do you add/drop
>>>>> partition without changing the SQL execution path? We do not have
>>>>> the time to reanalyze/dbms_stats the tables ,analyze takes hours
>>>>> and if SQL execution path changed, during these time, system is
>>>>> nearly unusable. 
>>>>> 
>>>>>     I tried to import old

>>>> statistics(dbms_stats.import_table_stats),
>>>>> but did not fix the problem.
>>>>>     So, can you share your experience on managing partitioned
>>>>> table? 
>>>>> 
>>>>> Regards
>>>>> 
>>>>> Zhu Chao
>>>>> www.cnoug.org
>>>>> 
>>>>> 
>>>>> 
>>>>> --
>>>>> Please see the official ORACLE-L FAQ: http://www.orafaq.net

>>>> --
>>>> Please see the official ORACLE-L FAQ: http://www.orafaq.net --
>>>> Author: Wartiak Rastislav INET: RWartiak_at_cpoj.cz
>>>>
>>>> Fat City Network Services -- 858-538-5051
http://www.fatcity.com
>>> San Diego, California        -- Mailing list and web hosting
>>> services 
>>> 
>> 
> ---------------------------------------------------------------------
>>> To REMOVE yourself from this mailing list, send an E-Mail message
>>> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>>> the message BODY, include a line containing: UNSUB ORACLE-L (or the
>>> name of mailing list you want to be removed from).  You may also
>>> send the HELP command for other information (like subscribing).
>>> 
>> 
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> 

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gudmundur Bjarni Josepsson
  INET: gbj_at_index.is

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wartiak Rastislav
  INET: RWartiak_at_cpoj.cz

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Dec 02 2003 - 08:59:42 CST

Original text of this message

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