RE: Insert Cardinalities into the data dictionary directly
If the production environment is already available, you could use DBMS_STATS.EXPORT_TABLE_STATS on the production table and import those stats into your development instance. Regards, Chris Gait On 18 Apr 2001, at 11:00, Murali Vallath wrote: Thanks for the feedback, I am coming from the Oracle Rdb world, where these row counts/cardinialities could be inserted into the data dictionary to simulate the optimizer behaviour similar to a production environment. I see your point, this could be a negative impact to the optimizer. Thanks for the input.] Murali -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Insert Cardinalities into the data dictionary directly
Take a look at dbms_stats.set_column_stats and dbms_stats.set_table_stats. The latter allows you to set the number of rows, the size of the table and such ; the first allows you to set the number of distinct values for a column the number of nulls, etc. I'm not sure what you are trying to accomplish with this. Is simply changing the cardinalities of a the columns enough? What about the distribution of those values? If the changes result in a different query path, and your queries run more slowly, does that mean they will do so when the statistics truly reflect the database. Perhaps they are running more slowly because you lied to the optimizer. "I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment." Cardinality reflects the ratio of distinct values to the total number of values in the database. I can think of scenarios where cardinality might change significantly for a time. But I would think in most cases it would remain fairly constant. FYI, if statements which assert something contrary to fact are subjunctive not conditional. "If this WERE an environment with good cardinalities" Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, April 18, 2001 8:06 AM To: Multiple recipients of list ORACLE-L I am trying again -- To obtain a good analysis of a SQL statement especially in a new development environment, based on the environment that it is to be deployed on, it would good to sometime reflect/simulate the production volume. Is there a way to input table cardinalities directly into the data dictionary so that the Optimizer could be made to act like in production. I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment. My question is this possible from scratch? Regards, Murali Vallath _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. 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: Insert Cardinalities into the data dictionary directly
Thanks for the feedback, I am coming from the Oracle Rdb world, where these row counts/cardinialities could be inserted into the data dictionary to simulate the optimizer behaviour similar to a production environment. I see your point, this could be a negative impact to the optimizer. Thanks for the input.] Murali Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 18 Apr 2001 09:15:23 -0800 Take a look at dbms_stats.set_column_stats and dbms_stats.set_table_stats. The latter allows you to set the number of rows, the size of the table and such ; the first allows you to set the number of distinct values for a column the number of nulls, etc. I'm not sure what you are trying to accomplish with this. Is simply changing the cardinalities of a the columns enough? What about the distribution of those values? If the changes result in a different query path, and your queries run more slowly, does that mean they will do so when the statistics truly reflect the database. Perhaps they are running more slowly because you lied to the optimizer. "I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment." Cardinality reflects the ratio of distinct values to the total number of values in the database. I can think of scenarios where cardinality might change significantly for a time. But I would think in most cases it would remain fairly constant. FYI, if statements which assert something contrary to fact are subjunctive not conditional. "If this WERE an environment with good cardinalities" Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, April 18, 2001 8:06 AM To: Multiple recipients of list ORACLE-L I am trying again -- To obtain a good analysis of a SQL statement especially in a new development environment, based on the environment that it is to be deployed on, it would good to sometime reflect/simulate the production volume. Is there a way to input table cardinalities directly into the data dictionary so that the Optimizer could be made to act like in production. I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment. My question is this possible from scratch? Regards, Murali Vallath _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. 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 your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath 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: Insert Cardinalities into the data dictionary directly
One of interesting uses of this feature is creation of statistics for temporary tables which cannot be done otherwise. It is Tom Kite's idea. Alex Hillman -Original Message- Sent: Wednesday, April 18, 2001 1:15 PM To: Multiple recipients of list ORACLE-L Take a look at dbms_stats.set_column_stats and dbms_stats.set_table_stats. The latter allows you to set the number of rows, the size of the table and such ; the first allows you to set the number of distinct values for a column the number of nulls, etc. I'm not sure what you are trying to accomplish with this. Is simply changing the cardinalities of a the columns enough? What about the distribution of those values? If the changes result in a different query path, and your queries run more slowly, does that mean they will do so when the statistics truly reflect the database. Perhaps they are running more slowly because you lied to the optimizer. "I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment." Cardinality reflects the ratio of distinct values to the total number of values in the database. I can think of scenarios where cardinality might change significantly for a time. But I would think in most cases it would remain fairly constant. FYI, if statements which assert something contrary to fact are subjunctive not conditional. "If this WERE an environment with good cardinalities" Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Wednesday, April 18, 2001 8:06 AM To: Multiple recipients of list ORACLE-L I am trying again -- To obtain a good analysis of a SQL statement especially in a new development environment, based on the environment that it is to be deployed on, it would good to sometime reflect/simulate the production volume. Is there a way to input table cardinalities directly into the data dictionary so that the Optimizer could be made to act like in production. I am aware that if this is was an environment with good cardinalities, these values could be exported and imported into another environment. My question is this possible from scratch? Regards, Murali Vallath _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex 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).
