RE: RULE vs. CHOOSE
1)I feel each plan depends on init parameters of Oracle. 2) You can statspack of Oracle. It will give u reports of the performance of the DB. HTH -- On Thu, 2 Aug 2001 13:01:56 VIVEK_SHARMA wrote: > >Qs. Is there any Way to Generate the IDEAL OUTLINE Plans Plans in 1 >Database >& Send it for Import to ALL the Various Sites World-wide ? > >Qs. Is there any PACKAGE etc. which Automatically Causes Statistics to >be ANALYZED in some SMALL proportions (Bit by Bit) while Live Operations > >are in progress ? What would be the Overhead of Such a Package (if >Exists) ? > >Thanks so much for the Help > >> -Original Message- >> From:Gillies, Garry [SMTP:[EMAIL PROTECTED]] >> Sent:Wednesday, August 01, 2001 8:01 PM >> To: Multiple recipients of list ORACLE-L >> Subject: RE: RULE vs. CHOOSE >> >> >> > From: VIVEK_SHARMA [<mailto:[EMAIL PROTECTED]>] >> > Sent: 01 August 2001 08:05 >> > Subject: RULE vs. CHOOSE >> > >> > Qs. Will COST Based Optimizer (CBO) or Rule Based Optimizer (RBO) be >> >> > used for the PARTITIONED Table in the Following Query ? >> > >> > NOTE OPTIMIZER_MODE is Set Explicitly to RULE , Run the >> > Following SQL :- >> > >> > SELECT >> > FROM , >> > where .Column = .Column >> > >> > Assuming .Column is the PARTITION KEY >> > & BOTH & are ANALYZED . >> > >> >> Development stopped on RBO at version 7. Partitioning is a version 8 >> feature. Since RBO has no knowledge of dealing with partitions, CBO >> must be used. >> >> > Qs. Will Only the Respective partition be SCANNED in the Above Query >> ? >> >> No. There is nothing in that query that tells the optimiser that only >> a particular partition will be required. >> >> > Qs. Would there be Any Disadvantage in SETTING OPTIMIZER_MODE=RULE >> for >> > the Above Query ? >> >> It would be ignored. The CBO would be used, defaulting to ALL_ROWS. >> >> > Qs. How is it Best Possible to KEEP the SAME (BEST) Execution Plan >> at >> > Various Sites where the SAME PRODUCT Exists >> >> Yes, but you must be using CBO - see CREATE OUTLINE in SQL reference >> manual. >> >> > Qs. Is there any PACKAGE etc. which Automatically Causes Statistics >> to >> > be ANALYZED in some SMALL proportions (Bit by Bit) while Live >> > Operations >> > are in progress ? What would be the Overhead of Such a Package (if >> > Exists) ? >> >> I do not know. >> > Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: C.S.Venkata Subramanian INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: RULE vs. CHOOSE
Qs. Is there any Way to Generate the IDEAL OUTLINE Plans Plans in 1 Database & Send it for Import to ALL the Various Sites World-wide ? Qs. Is there any PACKAGE etc. which Automatically Causes Statistics to be ANALYZED in some SMALL proportions (Bit by Bit) while Live Operations are in progress ? What would be the Overhead of Such a Package (if Exists) ? Thanks so much for the Help > -Original Message- > From: Gillies, Garry [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, August 01, 2001 8:01 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: RULE vs. CHOOSE > > > > From: VIVEK_SHARMA [<mailto:[EMAIL PROTECTED]>] > > Sent: 01 August 2001 08:05 > > Subject: RULE vs. CHOOSE > > > > Qs. Will COST Based Optimizer (CBO) or Rule Based Optimizer (RBO) be > > > used for the PARTITIONED Table in the Following Query ? > > > > NOTE OPTIMIZER_MODE is Set Explicitly to RULE , Run the > > Following SQL :- > > > > SELECT > > FROM , > > where .Column = .Column > > > > Assuming .Column is the PARTITION KEY > > & BOTH & are ANALYZED . > > > > Development stopped on RBO at version 7. Partitioning is a version 8 > feature. Since RBO has no knowledge of dealing with partitions, CBO > must be used. > > > Qs. Will Only the Respective partition be SCANNED in the Above Query > ? > > No. There is nothing in that query that tells the optimiser that only > a particular partition will be required. > > > Qs. Would there be Any Disadvantage in SETTING OPTIMIZER_MODE=RULE > for > > the Above Query ? > > It would be ignored. The CBO would be used, defaulting to ALL_ROWS. > > > Qs. How is it Best Possible to KEEP the SAME (BEST) Execution Plan > at > > Various Sites where the SAME PRODUCT Exists > > Yes, but you must be using CBO - see CREATE OUTLINE in SQL reference > manual. > > > Qs. Is there any PACKAGE etc. which Automatically Causes Statistics > to > > be ANALYZED in some SMALL proportions (Bit by Bit) while Live > > Operations > > are in progress ? What would be the Overhead of Such a Package (if > > Exists) ? > > I do not know. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: RULE vs. CHOOSE
Title: RE: RULE vs. CHOOSE > From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]] > Sent: 01 August 2001 08:05 > Subject: RULE vs. CHOOSE > > Qs. Will COST Based Optimizer (CBO) or Rule Based Optimizer (RBO) be > used for the PARTITIONED Table in the Following Query ? > > NOTE OPTIMIZER_MODE is Set Explicitly to RULE , Run the > Following SQL :- > > SELECT > FROM , > where .Column = .Column > > Assuming .Column is the PARTITION KEY > & BOTH & are ANALYZED . > Development stopped on RBO at version 7. Partitioning is a version 8 feature. Since RBO has no knowledge of dealing with partitions, CBO must be used. > Qs. Will Only the Respective partition be SCANNED in the Above Query ? No. There is nothing in that query that tells the optimiser that only a particular partition will be required. > Qs. Would there be Any Disadvantage in SETTING OPTIMIZER_MODE=RULE for > the Above Query ? It would be ignored. The CBO would be used, defaulting to ALL_ROWS. > Qs. How is it Best Possible to KEEP the SAME (BEST) Execution Plan at > Various Sites where the SAME PRODUCT Exists Yes, but you must be using CBO - see CREATE OUTLINE in SQL reference manual. > Qs. Is there any PACKAGE etc. which Automatically Causes Statistics to > be ANALYZED in some SMALL proportions (Bit by Bit) while Live > Operations > are in progress ? What would be the Overhead of Such a Package (if > Exists) ? I do not know. All internet traffic to this site is automatically scanned for viruses and vandals.
Re: RULE vs. CHOOSE
Ya I got the name correctly. Thanks for pointing it out. Venkat -- On Wed, 01 Aug 2001 03:01:19 Christian Trassens wrote: >Sorry, but maybe you refer to the outlines and the >parameter use_stored_outlines. Read the notes >1071358.6 and 92202.1 > >Regards. >--- "C.S.Venkata Subramanian" <[EMAIL PROTECTED]> >wrote: >> Use the plan stability of Oracle 8i. In this u have >> to capture plans for the sqls. A init parameter has >> to be added use_plan_stability=true(pls chk with >> manuals for more info). >> >> Ref to Oracle performance tuning manual. It is >> explained well there. >> >> HTH >> Regards >> Venkat >> -- >> >> On Tue, 31 Jul 2001 23:05:27 >> VIVEK_SHARMA wrote: >> > >> >Qs. Will COST Based Optimizer (CBO) or Rule Based >> Optimizer (RBO) be >> >used for the PARTITIONED Table in the Following >> Query ? >> > >> >NOTE OPTIMIZER_MODE is Set Explicitly to RULE , Run >> the Following SQL :- >> > >> >SELECT >> >FROM , >> >where .Column = > Table>.Column >> > >> >Assuming .Column is the PARTITION >> KEY >> >& BOTH & >> are ANALYZED . >> > >> >Qs. Will Only the Respective partition be SCANNED >> in the Above Query ? >> >Qs. Would there be Any Disadvantage in SETTING >> OPTIMIZER_MODE=RULE for >> >the Above Query ? >> > >> >Most of the Tables in the Application may be >> NON-partitioned & Most of >> >the Queries RUN Well in RULE . >> > >> > >> > >> > >> >Qs. How is it Best Possible to KEEP the SAME (BEST) >> Execution Plan at >> >Various Sites where the SAME PRODUCT Exists >> > >> >NOTE - These Sites Belong to DIFFERENT Customers & >> Have Different Total >> >Data Size & Distributions . >> > >> >Current Data Size ranges from 50 to 150 GB but the >> Data is Growing Very >> >Rapidly & may touch 200 GB within the Year . >> > >> >Being a Banking Application Product , it is a MIX >> of OLTP & DSS Types >> >of Transactions . >> > >> >With OPTIMIZER_MODE set to RULE the Queries work >> Quite Well . >> > >> > >> >Qs. Is there any PACKAGE etc. which Automatically >> Causes Statistics to >> >be ANALYZED in some SMALL proportions (Bit by Bit) >> while Live Operations >> >are in progress ? What would be the Overhead of >> Such a Package (if >> >Exists) ? >> > >> > >> >-- >> >Please see the official ORACLE-L FAQ: >> http://www.orafaq.com >> >-- >> >Author: VIVEK_SHARMA >> > INET: [EMAIL PROTECTED] >> > >> >Fat City Network Services-- (858) 538-5051 >> FAX: (858) 538-5051 >> >San Diego, California-- Public Internet >> access / Mailing Lists >> >> >> >To REMOVE yourself from this mailing list, send an >> E-Mail message >> >to: [EMAIL PROTECTED] (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). >> > >> >> >> Get 250 color business cards for FREE! >> http://businesscards.lycos.com/vp/fastpath/ >> -- >> Please see the official ORACLE-L FAQ: >> http://www.orafaq.com >> -- >> Author: C.S.Venkata Subramanian >> INET: [EMAIL PROTECTED] >> >> Fat City Network Services-- (858) 538-5051 FAX: >> (858) 538-5051 >> San Diego, California-- Public Internet >> access / Mailing Lists >> > >> To REMOVE yourself from this mailing list, send an >> E-Mail message >> to: [EMAIL PROTECTED] (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). > > >= >Eng. Christian Trassens >Senior DBA >Systems Engineer >[EMAIL PROTECTED] >[EMAIL PROTECTED] >Phone : 541149816062 > >__ >Do You Yahoo!? >Make international calls for as low as $.04/minute with Yahoo! Messenger >http://phonecard.yahoo.com/ >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Christian Trassens > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (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). > Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: C.S.Venkata Subramanian INET: [EMAIL PROTECTED] Fat City Network Services-- (8
Re: RULE vs. CHOOSE
Sorry, but maybe you refer to the outlines and the parameter use_stored_outlines. Read the notes 1071358.6 and 92202.1 Regards. --- "C.S.Venkata Subramanian" <[EMAIL PROTECTED]> wrote: > Use the plan stability of Oracle 8i. In this u have > to capture plans for the sqls. A init parameter has > to be added use_plan_stability=true(pls chk with > manuals for more info). > > Ref to Oracle performance tuning manual. It is > explained well there. > > HTH > Regards > Venkat > -- > > On Tue, 31 Jul 2001 23:05:27 > VIVEK_SHARMA wrote: > > > >Qs. Will COST Based Optimizer (CBO) or Rule Based > Optimizer (RBO) be > >used for the PARTITIONED Table in the Following > Query ? > > > >NOTE OPTIMIZER_MODE is Set Explicitly to RULE , Run > the Following SQL :- > > > >SELECT > >FROM , > >where .Column = Table>.Column > > > >Assuming .Column is the PARTITION > KEY > >& BOTH & > are ANALYZED . > > > >Qs. Will Only the Respective partition be SCANNED > in the Above Query ? > >Qs. Would there be Any Disadvantage in SETTING > OPTIMIZER_MODE=RULE for > >the Above Query ? > > > >Most of the Tables in the Application may be > NON-partitioned & Most of > >the Queries RUN Well in RULE . > > > > > > > > > >Qs. How is it Best Possible to KEEP the SAME (BEST) > Execution Plan at > >Various Sites where the SAME PRODUCT Exists > > > >NOTE - These Sites Belong to DIFFERENT Customers & > Have Different Total > >Data Size & Distributions . > > > >Current Data Size ranges from 50 to 150 GB but the > Data is Growing Very > >Rapidly & may touch 200 GB within the Year . > > > >Being a Banking Application Product , it is a MIX > of OLTP & DSS Types > >of Transactions . > > > >With OPTIMIZER_MODE set to RULE the Queries work > Quite Well . > > > > > >Qs. Is there any PACKAGE etc. which Automatically > Causes Statistics to > >be ANALYZED in some SMALL proportions (Bit by Bit) > while Live Operations > >are in progress ? What would be the Overhead of > Such a Package (if > >Exists) ? > > > > > >-- > >Please see the official ORACLE-L FAQ: > http://www.orafaq.com > >-- > >Author: VIVEK_SHARMA > > INET: [EMAIL PROTECTED] > > > >Fat City Network Services-- (858) 538-5051 > FAX: (858) 538-5051 > >San Diego, California-- Public Internet > access / Mailing Lists > > > >To REMOVE yourself from this mailing list, send an > E-Mail message > >to: [EMAIL PROTECTED] (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). > > > > > Get 250 color business cards for FREE! > http://businesscards.lycos.com/vp/fastpath/ > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: C.S.Venkata Subramanian > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (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). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: RULE vs. CHOOSE
Use the plan stability of Oracle 8i. In this u have to capture plans for the sqls. A init parameter has to be added use_plan_stability=true(pls chk with manuals for more info). Ref to Oracle performance tuning manual. It is explained well there. HTH Regards Venkat -- On Tue, 31 Jul 2001 23:05:27 VIVEK_SHARMA wrote: > >Qs. Will COST Based Optimizer (CBO) or Rule Based Optimizer (RBO) be >used for the PARTITIONED Table in the Following Query ? > >NOTE OPTIMIZER_MODE is Set Explicitly to RULE , Run the Following SQL :- > >SELECT >FROM , >where .Column = .Column > >Assuming .Column is the PARTITION KEY >& BOTH & are ANALYZED . > >Qs. Will Only the Respective partition be SCANNED in the Above Query ? >Qs. Would there be Any Disadvantage in SETTING OPTIMIZER_MODE=RULE for >the Above Query ? > >Most of the Tables in the Application may be NON-partitioned & Most of >the Queries RUN Well in RULE . > > > > >Qs. How is it Best Possible to KEEP the SAME (BEST) Execution Plan at >Various Sites where the SAME PRODUCT Exists > >NOTE - These Sites Belong to DIFFERENT Customers & Have Different Total >Data Size & Distributions . > >Current Data Size ranges from 50 to 150 GB but the Data is Growing Very >Rapidly & may touch 200 GB within the Year . > >Being a Banking Application Product , it is a MIX of OLTP & DSS Types >of Transactions . > >With OPTIMIZER_MODE set to RULE the Queries work Quite Well . > > >Qs. Is there any PACKAGE etc. which Automatically Causes Statistics to >be ANALYZED in some SMALL proportions (Bit by Bit) while Live Operations >are in progress ? What would be the Overhead of Such a Package (if >Exists) ? > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: VIVEK_SHARMA > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (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). > Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: C.S.Venkata Subramanian INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
