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 Some Columns FROM Partition Table , NON-Partitioned Table where Partition Table.Column = NON-Partitioned Table.Column Assuming Partition Table.Column is the PARTITION KEY BOTH Partition Table NON-Partitioned Table 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
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 Some Columns FROM Partition Table , NON-Partitioned Table where Partition Table.Column = NON-Partitioned Table.Column Assuming Partition Table.Column is the PARTITION KEY BOTH Partition Table NON-Partitioned Table 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
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 Some Columns FROM Partition Table , NON-Partitioned Table where Partition Table.Column = NON-Partitioned Table.Column Assuming Partition Table.Column is the PARTITION KEY BOTH Partition Table NON-Partitioned Table 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).
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 Some Columns FROM Partition Table , NON-Partitioned Table where Partition Table.Column = NON-Partitioned Table.Column Assuming Partition Table.Column is the PARTITION KEY BOTH Partition Table NON-Partitioned Table 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
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 Some Columns FROM Partition Table , NON-Partitioned Table where Partition Table.Column = NON-Partitioned Table.Column Assuming Partition Table.Column is the PARTITION KEY BOTH Partition Table NON-Partitioned Table 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-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an
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 Some Columns FROM Partition Table , NON-Partitioned Table where Partition Table.Column = NON-Partitioned Table.Column Assuming Partition Table.Column is the PARTITION KEY BOTH Partition Table NON-Partitioned Table 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.
