Re: ANALYZE question
Thanks all for your advice. Will check an option to do estimate. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 24, 2002 7:23 PM At the last Open World I attended a couple of sessions where the general advice for 9i DB is to use ANALYZE ESTIMATE without specifying ANY value. A few brief comparision tests did show that it got better results than the alternatives tested. As always, YMMV HTH HAND! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler 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: Yechiel Adar 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: ANALYZE question
An export has default parameter of STATISTICS=ESTIMATE. If such an exported file is imported , the default import parameter ANALYZE=Y will result in the import utility executing the analyze stmts in dump file. -Original Message- Sean Sent: Wednesday, July 24, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ratnesh Kumar Singh 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: ANALYZE question
dbms_stats --- O'Neill, Sean [EMAIL PROTECTED] wrote: Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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). __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: ANALYZE question
No Impact. Analyze would just collect the latest statistics for the concerned table and the next time any query gets fired on this table the optimizer (CBO) would generate the execution plan based on these statistics. I also believe that this would be healthier sign as the CBO is generating plans as per the latest what is available and not on the stale ones. Moreover, it would be good that instead of Using Analyze you should use DBMS_STATS.gether_table_statistics stored procedure as it : 1) DBMS_STATS can run in parallel mode and hence would be faster than Analyze (which is a serial operation) 2) Only if the statistics are stale the DBMS_STATS would execute. 3) You can always Export/Import/Set the statistics from one db to another db. Thanks, Vikas Khanna -Original Message- Sent: Wednesday, July 24, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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: Vikas Khanna 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: ANALYZE question
DBMS_STATS can be used to analyze tables. Dave -Original Message- Sent: Wednesday, July 24, 2002 4:03 AM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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: Farnsworth, Dave 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: ANALYZE question
A question: If analyzing SYS objects is a bad idea, why is it included by default in the analyzing commands (dbms_stats, analyze, dbms_utility.analyze_database)? Please correct me if my assumption is wrong, we had strange behaviour here when SYS objects were analyzed on a development db. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject:RE: ANALYZE question DBMS_STATS can be used to analyze tables. Dave -Original Message- Sent: Wednesday, July 24, 2002 4:03 AM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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: Farnsworth, Dave 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: Boivin, Patrice J 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: ANALYZE question
On Wed, Jul 24, 2002 at 04:48:41AM -0800, Boivin, Patrice J wrote: A question: If analyzing SYS objects is a bad idea, why is it included by default in the analyzing commands (dbms_stats, analyze, dbms_utility.analyze_database)? bug, Doc ID: 203003.996, fixed in 9i...I hate it when that happens. Please correct me if my assumption is wrong, we had strange behaviour here when SYS objects were analyzed on a development db. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject: RE: ANALYZE question DBMS_STATS can be used to analyze tables. Dave -Original Message- Sent: Wednesday, July 24, 2002 4:03 AM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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: Farnsworth, Dave 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: Boivin, Patrice J 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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: ANALYZE question
I get no hits when I try to see bug 203003.996 in Metalink. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 10:55 AM To: Multiple recipients of list ORACLE-L Subject:Re: ANALYZE question This message uses a character set that is not supported by the Internet Service. To view the original message content, open the attached message. If the text doesn't display correctly, save the attachment to disk, and then open it using a viewer that can display the original character set. File: message.txt -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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: ANALYZE question
On the subject on analyzed: We are doing analyze compute statistics and it takes about an hour. Do you know of ways to speed it up? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 24, 2002 3:54 PM On Wed, Jul 24, 2002 at 04:48:41AM -0800, Boivin, Patrice J wrote: A question: If analyzing SYS objects is a bad idea, why is it included by default in the analyzing commands (dbms_stats, analyze, dbms_utility.analyze_database)? bug, Doc ID: 203003.996, fixed in 9i...I hate it when that happens. Please correct me if my assumption is wrong, we had strange behaviour here when SYS objects were analyzed on a development db. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject: RE: ANALYZE question DBMS_STATS can be used to analyze tables. Dave -Original Message- Sent: Wednesday, July 24, 2002 4:03 AM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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: Farnsworth, Dave 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: Boivin, Patrice J 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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
Re: ANALYZE question
On Wed, Jul 24, 2002 at 06:53:34AM -0800, Boivin, Patrice J wrote: I get no hits when I try to see bug 203003.996 in Metalink. I do, try the advanced search, maybe. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 10:55 AM To: Multiple recipients of list ORACLE-L Subject: Re: ANALYZE question This message uses a character set that is not supported by the Internet Service. To view the original message content, open the attached message. If the text doesn't display correctly, save the attachment to disk, and then open it using a viewer that can display the original character set. File: message.txt -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell 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: ANALYZE question
Yechiel Consider ANALYZE TABLE ESTIMATE STATISTICS. If you consider sampling theory, with larger tables you shouldn't need to inspect each row. You can vary the number of rows that are sampled. We had a good discussion on this topic on this list awhile back. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:59 AM To: Multiple recipients of list ORACLE-L On the subject on analyzed: We are doing analyze compute statistics and it takes about an hour. Do you know of ways to speed it up? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 24, 2002 3:54 PM On Wed, Jul 24, 2002 at 04:48:41AM -0800, Boivin, Patrice J wrote: A question: If analyzing SYS objects is a bad idea, why is it included by default in the analyzing commands (dbms_stats, analyze, dbms_utility.analyze_database)? bug, Doc ID: 203003.996, fixed in 9i...I hate it when that happens. Please correct me if my assumption is wrong, we had strange behaviour here when SYS objects were analyzed on a development db. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 24, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject: RE: ANALYZE question DBMS_STATS can be used to analyze tables. Dave -Original Message- Sent: Wednesday, July 24, 2002 4:03 AM To: Multiple recipients of list ORACLE-L Apart from explicity running an ANALYZE command against a table, what, if any, other events/actions can cause an analyze to be run on the table? - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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: Farnsworth, Dave 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: Boivin, Patrice J 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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author
RE: ANALYZE question
I looked into the same thing some time ago. I found Oracle papers that
recommend using estimate 25% on the tables and a full analyze on the
indexes.
I wrote the script below to generate a script with all the analyze
statements I needed for the schema to be analyzed. I added date and time
displays so I could see how far along I am while the script is processing.
I run the output script on a weekly basis.
Ron Smith
set feedback off;
set heading off;
set pagesize 0;
set linesize 120;
set heading off;
set feedback off;
set show off;
spool analyze2.sql
select 'set feedback off;'|| CHR(10) || 'set heading off;' || CHR(10) ||
'set pagesize 0;' || 'set linesize 80;'|| CHR(10) |
| 'set heading off;' || CHR(10) || 'set feedback off;' || CHR(10) || 'spool
analyze_schema2.lst;' from dual
/
select 'select ' || || object_type || ' ' || object_name || ||',
to_char(sysdate, ''MM/DD/ HH24:MI'') from dua
l;'|| CHR(10) || 'analyze '|| object_type || ' ' || owner || '.' ||
object_name || ' estimate statistics sample 25 percent f
or table;' from dba_objects where object_type in('TABLE') and owner = 'PROD'
order by object_type desc
/
select 'select ' || || object_type || ' ' || object_name || ||',
to_char(sysdate, ''MM/DD/ HH24:MI'') from dua
l;'|| CHR(10) || 'analyze '|| object_type || ' ' || owner || '.' ||
object_name || ' compute statistics;' from dba_objects w
here object_type in('INDEX') and owner = 'PROD' order by object_type desc
/
This is what part of the output script looks like:
set feedback off;
set heading off;
set pagesize 0;
set linesize 80;
set heading off;
set feedback off;
spool analyze_schema2.lst;
select 'TABLE DIST', to_char(sysdate, 'MM/DD/ HH24:MI') from dual;
analyze TABLE PROD.DIST estimate statistics sample 25 percent for table;
select 'TABLE EXCEPTIONS', to_char(sysdate, 'MM/DD/ HH24:MI') from dual;
analyze TABLE PROD.EXCEPTIONS estimate statistics sample 25 percent for
table;
Ron Smith
-Original Message-
Sent: Wednesday, July 24, 2002 11:25 AM
To: Multiple recipients of list ORACLE-L
Yechiel
Consider ANALYZE TABLE ESTIMATE STATISTICS.
If you consider sampling theory, with larger tables you shouldn't need to
inspect each row. You can vary the number of rows that are sampled. We had a
good discussion on this topic on this list awhile back.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, July 24, 2002 9:59 AM
To: Multiple recipients of list ORACLE-L
On the subject on analyzed:
We are doing analyze compute statistics and it takes about an hour.
Do you know of ways to speed it up?
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, July 24, 2002 3:54 PM
On Wed, Jul 24, 2002 at 04:48:41AM -0800, Boivin, Patrice J wrote:
A question:
If analyzing SYS objects is a bad idea, why is it included by default in
the
analyzing commands (dbms_stats, analyze, dbms_utility.analyze_database)?
bug, Doc ID: 203003.996, fixed in 9i...I hate it when that happens.
Please correct me if my assumption is wrong, we had strange behaviour
here
when SYS objects were analyzed on a development db.
Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)
Systems Admin Operations | Admin. et Exploit. des systèmes
Technology Services| Services technologiques
Informatics Branch | Direction de l'informatique
Maritimes Region, DFO | Région des Maritimes, MPO
E-Mail: [EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, July 24, 2002 9:09 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: ANALYZE question
DBMS_STATS can be used to analyze tables.
Dave
-Original Message-
Sent: Wednesday, July 24, 2002 4:03 AM
To: Multiple recipients of list ORACLE-L
Apart from explicity running an ANALYZE command against a table, what,
if
any, other events/actions can cause an analyze to be run on the table?
-
Seán O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode]
This message, including attached files, may contain confidential
information and is intended only for the use by the individual
and/or the entity to which it is addressed. Any unauthorized use,
dissemination of, or copying of the information contained herein is
not allowed and may lead to irreparable harm and damage for which
you may be held liable. If you receive this message in error or if
it is intended for someone else please notify the sender by
returning this e-mail immediately and delete the message.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: O'Neill, Sean
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858
Re: ANALYZE question
At the last Open World I attended a couple of sessions where the general advice for 9i DB is to use ANALYZE ESTIMATE without specifying ANY value. A few brief comparision tests did show that it got better results than the alternatives tested. As always, YMMV HTH HAND! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler 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: Analyze Question -- How CBO uses column statistics for non-indexed columns
Hi Larry, I think that the answer is that the statistics are of no use to the CBO until an index is created. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 2 February 2001 11:16 To: Multiple recipients of list ORACLE-L non-indexed columns Listers, 7.3.4.3 database on HP-UX 11.0. In what way do statistics (or lack thereof) on non-indexed columns influence the CBO? I've searched high and wide for any info on this and came up with nothing, and, I can't think of a reason or example off the top of my head. And no, this isn't an OCP, homework (I wish I were that young), or interview question. Simply a question a friend called and asked me. Regards, Larry G. Elkins [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams 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).
