RE: DBMS_STATS in 8.1.6
We had trouble with analyze during heavy updates. Something to do with SNAPSHOT TOO OLD. The instance froze. It is corrected somewhere in 8.1.7. Oracle will not port the fix to 8.1.6. We schedule the analyze to work at night and no problems. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: Jesse, Rich [SMTP:[EMAIL PROTECTED]] > Sent: Tue, February 26, 2002 12:39 AM > To: Multiple recipients of list ORACLE-L > Subject: DBMS_STATS in 8.1.6 > > Hey all, > > I'm looking to start CBO on an 8.1.6.0.0 DB on Solaris 2.8. But looking > thru Metaclink, I see some potential problems with some of the DBMS_STATS > package in this version, like with GATHER_SCHEMA_STATS. > > Anyone have any suggestions as to "Yay" or "Nay" for this on 8.1.6.0.0? > Unfortunately, this is another 3rd party app which refuses to support any > other version (not sure about patchsets, though) of Oracle, so I'm stuck > here for the time being. I'm leaning heavily towards the cautious route > of > using ANALYZE and reading DBA_TAB_MODIFICATIONS once a week to see if I > should re-ANALYZE. > > TIA, > Rich Jesse System/Database Administrator > [EMAIL PROTECTED] Quad/Tech International, Sussex, WI > USA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jesse, Rich > 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: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= 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: DBMS_STATS in 8.1.6
Does this apply in 8.1.7. I couldn't tell from the doco. Kathy -Original Message- Sent: Tuesday, February 26, 2002 9:28 AM To: Multiple recipients of list ORACLE-L Sorry, I haven't explained myself correctly. When you have first run a dbms_stats to generate on partitioned tables, analyze doesn't generate any. However, this applies for compute statistics. I think this article could explain you better than I: Doc ID: Note:97207.1 Type: PROBLEM Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 24-JAN-2000 Last Revision Date: 28-MAR-2001 Problem Description --- You have run ANALYZE TABLE COMPUTE STATISTICS on a partitioned table and the statistics in dba_tables have not been replaced. Solution Description You ran the dbms_stats package to get statistics on the partitioned table with granularity = ALL. By running the dbms_stats package on the table the global_stats column in dba_tables is set to YES. You need to do the following to delete the stats generated by the dbms_stats package: execute dbms_stats.delete_table_stats(ownname => 'SCOTT', tabname => 'ORDERS', cascade_parts => TRUE); Once you have done that you can run the ANALYZE TABLE and the statistics will populate dba_tables. Explanation --- When analyzing a partitioned table with dbms_stats package, the global_stats flag is set. When it is set in this case you need to use the dbms_stats package to delete the statistics before the ANALYZE command can be used on the table. References -- [BUG:908138] Additional Search Words --- Regards. --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > Thanks for the advice, Christian, but I'm a little > confused. What do you > mean by "don't run statistics with analyze > because it doesn't generate any"? > > Rich Jesse > System/Database Administrator > [EMAIL PROTECTED] Quad/Tech > International, Sussex, WI USA > > > -Original Message- > Sent: Tuesday, February 26, 2002 3:33 AM > To: Multiple recipients of list ORACLE-L > > > Well, it has some bugs with GATHER EMPTY and GATHER > STALE on your release. However, I set it in some > clients and it is working fine. As an advice, don't > use those options, reduce the size of the bucket > just > to generate statistics quickly, lastly if you decide > against DBMS_STATS, don't run statistics with > analyze > because it doesn't generate any. > > I set it with monitoring option and as I said it is > working fine. I was cautious just to exclude tables > that recently been truncated. However, it is working > fine. > > Regards. > --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > > Hey all, > > > > I'm looking to start CBO on an 8.1.6.0.0 DB on > > Solaris 2.8. But looking > > thru Metaclink, I see some potential problems with > > some of the DBMS_STATS > > package in this version, like with > > GATHER_SCHEMA_STATS. > > > > Anyone have any suggestions as to "Yay" or "Nay" > for > > this on 8.1.6.0.0? > > Unfortunately, this is another 3rd party app which > > refuses to support any > > other version (not sure about patchsets, though) > of > > Oracle, so I'm stuck > > here for the time being. I'm leaning heavily > > towards the cautious route of > > using ANALYZE and reading DBA_TAB_MODIFICATIONS > once > > a week to see if I > > should re-ANALYZE. > > > > TIA, > > Rich Jesse > System/Database > > Administrator > > [EMAIL PROTECTED] Quad/Tech > > International, Sussex, WI USA > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jesse, Rich > 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). = Ing. Christian Trassens Senior DBA & Unix Administrator Phone: 0034-699240979 0034-649824704 __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.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 i
RE: DBMS_STATS in 8.1.6
Sorry, I haven't explained myself correctly. When you have first run a dbms_stats to generate on partitioned tables, analyze doesn't generate any. However, this applies for compute statistics. I think this article could explain you better than I: Doc ID: Note:97207.1 Type: PROBLEM Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 24-JAN-2000 Last Revision Date: 28-MAR-2001 Problem Description --- You have run ANALYZE TABLE COMPUTE STATISTICS on a partitioned table and the statistics in dba_tables have not been replaced. Solution Description You ran the dbms_stats package to get statistics on the partitioned table with granularity = ALL. By running the dbms_stats package on the table the global_stats column in dba_tables is set to YES. You need to do the following to delete the stats generated by the dbms_stats package: execute dbms_stats.delete_table_stats(ownname => 'SCOTT', tabname => 'ORDERS', cascade_parts => TRUE); Once you have done that you can run the ANALYZE TABLE and the statistics will populate dba_tables. Explanation --- When analyzing a partitioned table with dbms_stats package, the global_stats flag is set. When it is set in this case you need to use the dbms_stats package to delete the statistics before the ANALYZE command can be used on the table. References -- [BUG:908138] Additional Search Words --- Regards. --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > Thanks for the advice, Christian, but I'm a little > confused. What do you > mean by "don't run statistics with analyze > because it doesn't generate any"? > > Rich Jesse > System/Database Administrator > [EMAIL PROTECTED] Quad/Tech > International, Sussex, WI USA > > > -Original Message- > Sent: Tuesday, February 26, 2002 3:33 AM > To: Multiple recipients of list ORACLE-L > > > Well, it has some bugs with GATHER EMPTY and GATHER > STALE on your release. However, I set it in some > clients and it is working fine. As an advice, don't > use those options, reduce the size of the bucket > just > to generate statistics quickly, lastly if you decide > against DBMS_STATS, don't run statistics with > analyze > because it doesn't generate any. > > I set it with monitoring option and as I said it is > working fine. I was cautious just to exclude tables > that recently been truncated. However, it is working > fine. > > Regards. > --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > > Hey all, > > > > I'm looking to start CBO on an 8.1.6.0.0 DB on > > Solaris 2.8. But looking > > thru Metaclink, I see some potential problems with > > some of the DBMS_STATS > > package in this version, like with > > GATHER_SCHEMA_STATS. > > > > Anyone have any suggestions as to "Yay" or "Nay" > for > > this on 8.1.6.0.0? > > Unfortunately, this is another 3rd party app which > > refuses to support any > > other version (not sure about patchsets, though) > of > > Oracle, so I'm stuck > > here for the time being. I'm leaning heavily > > towards the cautious route of > > using ANALYZE and reading DBA_TAB_MODIFICATIONS > once > > a week to see if I > > should re-ANALYZE. > > > > TIA, > > Rich Jesse > System/Database > > Administrator > > [EMAIL PROTECTED] Quad/Tech > > International, Sussex, WI USA > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jesse, Rich > 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). = Ing. Christian Trassens Senior DBA & Unix Administrator Phone: 0034-699240979 0034-649824704 __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.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 informatio
RE: DBMS_STATS in 8.1.6
Thanks for the advice, Christian, but I'm a little confused. What do you mean by "don't run statistics with analyze because it doesn't generate any"? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Tuesday, February 26, 2002 3:33 AM To: Multiple recipients of list ORACLE-L Well, it has some bugs with GATHER EMPTY and GATHER STALE on your release. However, I set it in some clients and it is working fine. As an advice, don't use those options, reduce the size of the bucket just to generate statistics quickly, lastly if you decide against DBMS_STATS, don't run statistics with analyze because it doesn't generate any. I set it with monitoring option and as I said it is working fine. I was cautious just to exclude tables that recently been truncated. However, it is working fine. Regards. --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > Hey all, > > I'm looking to start CBO on an 8.1.6.0.0 DB on > Solaris 2.8. But looking > thru Metaclink, I see some potential problems with > some of the DBMS_STATS > package in this version, like with > GATHER_SCHEMA_STATS. > > Anyone have any suggestions as to "Yay" or "Nay" for > this on 8.1.6.0.0? > Unfortunately, this is another 3rd party app which > refuses to support any > other version (not sure about patchsets, though) of > Oracle, so I'm stuck > here for the time being. I'm leaning heavily > towards the cautious route of > using ANALYZE and reading DBA_TAB_MODIFICATIONS once > a week to see if I > should re-ANALYZE. > > TIA, > Rich Jesse System/Database > Administrator > [EMAIL PROTECTED] Quad/Tech > International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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: DBMS_STATS in 8.1.6
DBMS_STATS only gathers stats that the optimizer needs. For example, the CHAIN_CNT field is not updated because the optimizer doesn't need it. I hate this because now I don't know if the table has chained or migrated rows. The ANALYZE command still gives you this information. -Original Message- Sent: Tuesday, February 26, 2002 4:33 AM To: Multiple recipients of list ORACLE-L Well, it has some bugs with GATHER EMPTY and GATHER STALE on your release. However, I set it in some clients and it is working fine. As an advice, don't use those options, reduce the size of the bucket just to generate statistics quickly, lastly if you decide against DBMS_STATS, don't run statistics with analyze because it doesn't generate any. I set it with monitoring option and as I said it is working fine. I was cautious just to exclude tables that recently been truncated. However, it is working fine. Regards. --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > Hey all, > > I'm looking to start CBO on an 8.1.6.0.0 DB on > Solaris 2.8. But looking > thru Metaclink, I see some potential problems with > some of the DBMS_STATS > package in this version, like with > GATHER_SCHEMA_STATS. > > Anyone have any suggestions as to "Yay" or "Nay" for > this on 8.1.6.0.0? > Unfortunately, this is another 3rd party app which > refuses to support any > other version (not sure about patchsets, though) of > Oracle, so I'm stuck > here for the time being. I'm leaning heavily > towards the cautious route of > using ANALYZE and reading DBA_TAB_MODIFICATIONS once > a week to see if I > should re-ANALYZE. > > TIA, > Rich Jesse System/Database > Administrator > [EMAIL PROTECTED] Quad/Tech > International, Sussex, WI USA > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jesse, Rich > 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). = Ing. Christian Trassens Senior DBA & Unix Administrator Phone: 0034-699240979 0034-649824704 __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Terrian, Tom 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: DBMS_STATS in 8.1.6
Well, it has some bugs with GATHER EMPTY and GATHER STALE on your release. However, I set it in some clients and it is working fine. As an advice, don't use those options, reduce the size of the bucket just to generate statistics quickly, lastly if you decide against DBMS_STATS, don't run statistics with analyze because it doesn't generate any. I set it with monitoring option and as I said it is working fine. I was cautious just to exclude tables that recently been truncated. However, it is working fine. Regards. --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote: > Hey all, > > I'm looking to start CBO on an 8.1.6.0.0 DB on > Solaris 2.8. But looking > thru Metaclink, I see some potential problems with > some of the DBMS_STATS > package in this version, like with > GATHER_SCHEMA_STATS. > > Anyone have any suggestions as to "Yay" or "Nay" for > this on 8.1.6.0.0? > Unfortunately, this is another 3rd party app which > refuses to support any > other version (not sure about patchsets, though) of > Oracle, so I'm stuck > here for the time being. I'm leaning heavily > towards the cautious route of > using ANALYZE and reading DBA_TAB_MODIFICATIONS once > a week to see if I > should re-ANALYZE. > > TIA, > Rich Jesse System/Database > Administrator > [EMAIL PROTECTED] Quad/Tech > International, Sussex, WI USA > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jesse, Rich > 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). = Ing. Christian Trassens Senior DBA & Unix Administrator Phone: 0034-699240979 0034-649824704 __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.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).
