| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Plan stability
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 >>>
>>>>> 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
>>>>> 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
>>> 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
 |
 |