Using dbms_stats.auto_sample_size in dbms_stats.
Title: Message To all 9i DBA's. I am trying to find out how efficient (or not ) is the option of running dbms_stats with dbms_stats.auto_sample_size. Reading metalink I see a lot of issues with the time it takesto run, and also that sample_size column is always equal to num_rows. Would like to hear from anyone who is using this in a Production environment, tia Yuval. This transmission may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you, [EMAIL PROTECTED], are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. [neumann.webloyalty.com]
Re: Using dbms_stats.auto_sample_size in dbms_stats.
There's some notes on my site (www.oracledba.co.uk) about how it goes about its work. Basically, it will do a slightly more work than you might think - throw a 10046 trace on it and you can see how it does its job. Cheers Connor --- Arnon, Yuval [EMAIL PROTECTED] wrote: To all 9i DBA's. I am trying to find out how efficient (or not ) is the option of running dbms_stats with dbms_stats.auto_sample_size. Reading metalink I see a lot of issues with the time it takes to run, and also that sample_size column is always equal to num_rows. Would like to hear from anyone who is using this in a Production environment, tia Yuval. This transmission may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you, [EMAIL PROTECTED], are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Strange behavior with dbms_stats...
List... SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too). I use this proc to generate statistics: create or replace procedure get_statistics as cursor get_users_list is select username from dba_users where username != 'SYS' and username != 'SYSTEM'; begin for i in get_users_list loop dbms_output.put_line('exec dbms_stats.gather_schema_stats(ownname = '||chr(39)||i.username||chr(39)||', cascade = TRUE);'); end loop; end; / __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Strange behavior with dbms_stats...
List... SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too). I use the proc at the bottom to generate statistics. It seems to work, but if I check statistics with: select owner, table_name, num_rows, blocks, av_row_len, to_char(last_analyzed, 'MM/DD/ HH24:MI:SS') from dba_tables; the tables have the OLD last_analyzed time! but... If I execute ONE by ONE: exec dbms_stats.gather_schema_stats(ownname = 'PERFSTAT', cascade = TRUE); it works fine!!... So, am I doing something wrong? Any help? TIA JL create or replace procedure get_statistics as cursor get_users_list is select username from dba_users where username != 'SYS' and username != 'SYSTEM'; begin for i in get_users_list loop dbms_output.put_line('exec dbms_stats.gather_schema_stats(ownname = '||chr(39)||i.username||chr(39)||', cascade = TRUE);'); end loop; end; / __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Strange behavior with dbms_stats...
after running the block, do you actually spool the output and run that?? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, December 11, 2003 11:09 AM To: Multiple recipients of list ORACLE-L List... SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too). I use the proc at the bottom to generate statistics. It seems to work, but if I check statistics with: select owner, table_name, num_rows, blocks, av_row_len, to_char(last_analyzed, 'MM/DD/ HH24:MI:SS') from dba_tables; the tables have the OLD last_analyzed time! but... If I execute ONE by ONE: exec dbms_stats.gather_schema_stats(ownname = 'PERFSTAT', cascade = TRUE); it works fine!!... So, am I doing something wrong? Any help? TIA JL create or replace procedure get_statistics as cursor get_users_list is select username from dba_users where username != 'SYS' and username != 'SYSTEM'; begin for i in get_users_list loop dbms_output.put_line('exec dbms_stats.gather_schema_stats(ownname = '||chr(39)||i.username||chr(39)||', cascade = TRUE);'); end loop; end; / __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Strange behavior with dbms_stats...
Um...the PL/SQL script at the bottom of your e-mail doesn't DO anything other than output a line. Do you use this in a subsequent command file? Cheers, Mike -Original Message- Sent: Thursday, December 11, 2003 8:09 AM To: Multiple recipients of list ORACLE-L List... SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too). I use the proc at the bottom to generate statistics. It seems to work, but if I check statistics with: select owner, table_name, num_rows, blocks, av_row_len, to_char(last_analyzed, 'MM/DD/ HH24:MI:SS') from dba_tables; the tables have the OLD last_analyzed time! but... If I execute ONE by ONE: exec dbms_stats.gather_schema_stats(ownname = 'PERFSTAT', cascade = TRUE); it works fine!!... So, am I doing something wrong? Any help? TIA JL create or replace procedure get_statistics as cursor get_users_list is select username from dba_users where username != 'SYS' and username != 'SYSTEM'; begin for i in get_users_list loop dbms_output.put_line('exec dbms_stats.gather_schema_stats(ownname = '||chr(39)||i.username||chr(39)||', cascade = TRUE);'); end loop; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Strange behavior with dbms_stats...
The proc generates the 'exec dbms_stats ' statements for all the users. Are yousaving theoutput and running it manually or not.IT would have the same statements that you run one by one. Jose Luis Delgado [EMAIL PROTECTED] wrote: List...SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too).I use the proc at the bottom to generate statistics.It seems to work, but if I check statistics with:select owner, table_name, num_rows, blocks,av_row_len,to_char(last_analyzed, 'MM/DD/ HH24:MI:SS')from dba_tables;the tables have the OLD last_analyzed time!but... If I execute ONE by ONE:exec dbms_stats.gather_schema_stats(ownname ='PERFSTAT', cascade = TRUE);it works fine!!...So, am I doing something wrong?Any help?TIAJLcreate or replace procedure get_statistics ascursor get_users_list isselect usernamefrom dba_userswhere username != 'SYS' and username != 'SYSTEM';beginfor i in get_users_listloopdbms_output.put_line('execdbms_stats.gather_schema_stats(ownname ='||chr(39)||i.username||chr(39)||', cascade =TRUE);');end loop;end;/__Do you Yahoo!?New Yahoo! Photos - easier uploading and sharing.http://photos.yahoo.com/-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Jose Luis DelgadoINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Free Pop-Up Blocker - Get it now
Re: Re: Strange behavior with dbms_stats...
put it into a a dbms_output to see what is passed as variables then wrap it in execute immediate. your doing dynamic pl/sql. i think that will work. From: anu [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 11:54:35 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Strange behavior with dbms_stats... The proc generates the 'exec dbms_stats ' statements for all the users. Are you saving the output and running it manually or not. IT would have the same statements that you run one by one. Jose Luis Delgado [EMAIL PROTECTED] wrote:List... SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too). I use the proc at the bottom to generate statistics. It seems to work, but if I check statistics with: select owner, table_name, num_rows, blocks, av_row_len, to_char(last_analyzed, 'MM/DD/ HH24:MI:SS') from dba_tables; the tables have the OLD last_analyzed time! but... If I execute ONE by ONE: exec dbms_stats.gather_schema_stats(ownname = 'PERFSTAT', cascade = TRUE); it works fine!!... So, am I doing something wrong? Any help? TIA JL create or replace procedure get_statistics as cursor get_users_list is select username from dba_users where username != 'SYS' and username != 'SYSTEM'; begin for i in get_users_list loop dbms_output.put_line('exec dbms_stats.gather_schema_stats(ownname = '||chr(39)||i.username||chr(39)||', cascade = TRUE);'); end loop; end; / __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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!? Free Pop-Up Blocker - Get it now The proc generates the 'exec dbms_stats ' statements for all the users. Are yousaving theoutput and running it manually or not.IT would have the same statements that you run one by one. Jose Luis Delgado [EMAIL PROTECTED] wrote: List...SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too).I use the proc at the bottom to generate statistics.It seems to work, but if I check statistics with:select owner, table_name, num_rows, blocks,av_row_len,to_char(last_analyzed, 'MM/DD/ HH24:MI:SS')from dba_tables;the tables have the OLD last_analyzed time!but... If I execute ONE by ONE:exec dbms_stats.gather_schema_stats(ownname ='PERFSTAT', cascade = TRUE);it works fine!!...So, am I doing something wrong?Any help?TIAJLcreate or replace procedure get_statistics ascursor get_users_list isselect usernamefrom dba_userswhere username != 'SYS' and username != 'SYSTEM';beginfor i in get_users_listloopdbms_output.put_line('execdbms_stats.gather_schema_stats(ownname ='||chr(39)||i.username||chr(39)||', cascade =TRUE);');end loop;end;/__Do you Yahoo!?New Yahoo! Photos - easier uploading and sharing.http://photos.yahoo.com/-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Jose Luis DelgadoINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Do you Yahoo!? Free Pop-Up Blocker - Get it now
RE: Strange behavior with dbms_stats...
Oooppp... Sorry... I sent the testing proc... this is the original... procedure get_statistics as cursor get_users_list is select username from dba_users where username != 'SYS' and username != 'SYSTEM'; begin for i in get_users_list loop dbms_stats.gather_schema_stats(ownname= chr(39)||i.username||chr(39), cascade= TRUE); end loop; end; --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: after running the block, do you actually spool the output and run that?? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, December 11, 2003 11:09 AM To: Multiple recipients of list ORACLE-L List... SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too). I use the proc at the bottom to generate statistics. It seems to work, but if I check statistics with: select owner, table_name, num_rows, blocks, av_row_len, to_char(last_analyzed, 'MM/DD/ HH24:MI:SS') from dba_tables; the tables have the OLD last_analyzed time! but... If I execute ONE by ONE: exec dbms_stats.gather_schema_stats(ownname = 'PERFSTAT', cascade = TRUE); it works fine!!... So, am I doing something wrong? Any help? TIA JL create or replace procedure get_statistics as cursor get_users_list is select username from dba_users where username != 'SYS' and username != 'SYSTEM'; begin for i in get_users_list loop dbms_output.put_line('exec dbms_stats.gather_schema_stats(ownname = '||chr(39)||i.username||chr(39)||', cascade = TRUE);'); end loop; end; / __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
Yes. At least I was before reverting back to ANALYZE. For our little 30GB DB, there's little penalty for non-parallel stats gathering on Saturdays. Then again, one could ANALYZE multiple tables at once to regain some parallelism in stats gathering... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Wednesday, December 03, 2003 10:19 PM To: Multiple recipients of list ORACLE-L Rich, I went thru the archives. Are you using the option 'cascade=true' for gather_table_stats? -Original Message- Jesse, Rich Sent: Wednesday, December 03, 2003 4:21 PM To: Multiple recipients of list ORACLE-L Yes -- same platform and version. If you look on the list archives (fatcity.com, for example) for a subject of Burned by DBMS_STATS **AGAIN** back in April 2003, you may get some insight. There are also known bugs in GATHER_SCHEMA_STATS in 8i. The recommendation is to iteratively call GATHER_TABLE_STATS. GL! Rich -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
Yes -- same platform and version. If you look on the list archives (fatcity.com, for example) for a subject of Burned by DBMS_STATS **AGAIN** back in April 2003, you may get some insight. There are also known bugs in GATHER_SCHEMA_STATS in 8i. The recommendation is to iteratively call GATHER_TABLE_STATS. GL! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Wednesday, December 03, 2003 3:04 PM To: Multiple recipients of list ORACLE-L Hello, Oracle 8.1.7.4 on HP-UX 11i A week ago, we replaced 'analyze table ... estimate statistics sample 10 percent' with dbms_stats.gather_schema_stats('x', estimate_percent=10, cascade=true, degree=4, method_opt=''for all indexed columns size 2') Performace is good against partitioned tables but not for non-partitioned tables. Saw a note in Metalink that its better to do the above with 'cascade=false' and then do a gather_index_stats separately. In my tests, I see that 'analyze' makes the CBO use an index while dbms_stats is making the CBO to use a FTS instead. Have you faced any similar issues? TIA Prakash -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
I think there are various little (hah!) details that change with versions, but unless your analyze command was: analyze table T estimate statistics sample 10 percent for table for indexes for all indexed columns size 2 ; (I may have the sample clause in the wrong place). then the two commands will be generating different things. The default for analyze columns is 'size' 75, which gives Oracle much better precision on range scans with literal values than 'size 2' - which may be a significant part of your problem. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 9:04 PM Hello, Oracle 8.1.7.4 on HP-UX 11i A week ago, we replaced 'analyze table ... estimate statistics sample 10 percent' with dbms_stats.gather_schema_stats('x', estimate_percent=10, cascade=true, degree=4, method_opt=''for all indexed columns size 2') Performace is good against partitioned tables but not for non-partitioned tables. Saw a note in Metalink that its better to do the above with 'cascade=false' and then do a gather_index_stats separately. In my tests, I see that 'analyze' makes the CBO use an index while dbms_stats is making the CBO to use a FTS instead. Have you faced any similar issues? TIA Prakash -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
dbms_stats
Title: RE: analyze after truncate Hello, Oracle 8.1.7.4 on HP-UX 11i A week ago, we replaced 'analyze table ... estimate statistics sample 10 percent' with dbms_stats.gather_schema_stats('x', estimate_percent=10, cascade=true, degree=4, method_opt=''for all indexed columns size 2') Performace is good against partitioned tables but not for non-partitioned tables. Saw a note in Metalink that its better to do the above with 'cascade=false' and then do a gather_index_stats separately. In my tests, I see that 'analyze' makes the CBO use an index while dbms_stats is making the CBO to use a FTS instead. Have you faced any similar issues? TIA Prakash
RE: dbms_stats
Rich, I went thru the archives. Are you using the option 'cascade=true' for gather_table_stats? -Original Message- Jesse, Rich Sent: Wednesday, December 03, 2003 4:21 PM To: Multiple recipients of list ORACLE-L Yes -- same platform and version. If you look on the list archives (fatcity.com, for example) for a subject of Burned by DBMS_STATS **AGAIN** back in April 2003, you may get some insight. There are also known bugs in GATHER_SCHEMA_STATS in 8i. The recommendation is to iteratively call GATHER_TABLE_STATS. GL! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Wednesday, December 03, 2003 3:04 PM To: Multiple recipients of list ORACLE-L Hello, Oracle 8.1.7.4 on HP-UX 11i A week ago, we replaced 'analyze table ... estimate statistics sample 10 percent' with dbms_stats.gather_schema_stats('x', estimate_percent=10, cascade=true, degree=4, method_opt=''for all indexed columns size 2') Performace is good against partitioned tables but not for non-partitioned tables. Saw a note in Metalink that its better to do the above with 'cascade=false' and then do a gather_index_stats separately. In my tests, I see that 'analyze' makes the CBO use an index while dbms_stats is making the CBO to use a FTS instead. Have you faced any similar issues? TIA Prakash -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Bala, Prakash INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
Maybe I didn't make my point clear enough. If you use dbms_stats.gather_table_stats with method_opt=''for all indexed columns size 2' , i.e. any other than size 1, dbms_stats in Oracle 8i will issue an analyze ... command to gather the statistics. Run a sql_trace if you don't believe me. Therefore the results of gathering statistics with dbms_stats are no different than gathering them with the equivalent analyze command. It is different if you use the default method_opt (for all columns size 1). Then gather_table_stats uses its own sql to collect table and column ( num_distinct, min, max, null, avg_col_length) statistics. For index statistics always resorts to the analyze command. That all changes in Oracle 9i. There the gather procedures do their own work and do not use analyze anymore. How did you determine the bucket size of 2? And no, I have not had any issues with analyze for partitioned tables; however, I do not gather histograms blindly on all indexed columns. Only on a few columns with highly skewed data content and some of them are on partitioned tabled. At 09:29 PM 12/3/2003, you wrote: The reason why we switched from 'analyze table .. 10 percent' is because when we partitioned some of the huge tables, the query performance against these tables was really bad. 'Gather_table_stats' with size 2 on indexed columns did a much better job. Have you had issues with 'analyze' against partitioned tables? -Original Message- Wolfgang Breitling Sent: Wednesday, December 03, 2003 5:29 PM To: Multiple recipients of list ORACLE-L In Oracle 8i you may as well stick with analyze since the dbms_stats call you use translates simply into a analyze table ... ESTIMATE statistics sample 10 percent FOR TABLE FOR ALL INDEXES for all indexed columns size 2 Why did you go from a simple analyze to gathering histograms on all indexed columns? I question the rationale of gathering histograms of size 2. Aside from that, I question the rationale of a blanket histogram gathering (regardless of # of buckets) on all indexed or all columns. Histograms are like medicine. In the right (i.e. sparing dose) they are a therapeutic tool. In the wrong, especially too high dose, they become poison. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Running DBMS_STATS causes ORA-3113 error on 9.2.0.3 Solaris 64-bit
We are having issues running dbms_stats.gather_table_stats. We get and ORA-3113 error message . The following can be found in the ALERT file: Errors in file /oracle/admin/VLDB/udump/vldbn1_ora_21486.trc:ORA-07445: exception encountered: core dump [000100E81374] [SIGSEGV] [Address not mapped to object] [0x00058] [] [] I am wondering if anyone else is seeing this and has a work around. Wondering if analyze command is a work around until a fix is posted. Thanks in advance. -Lizz Do you Yahoo!? Exclusive Video Premiere - Britney Spears
Re: DBMS_STATS and CBO
If you want to get the same effect entirely from the SQL prompt (i.e. without having to issue operating-system copy commands), you can first create a tablespace (named DUMMY_TEMP?) with datafiles of the name and size you want for your TEMP tablespace. Then drop the DUMMY_TEMP tablespace and create the TEMP tablespace over the same files, now as tempfiles. Don¹t forget the REUSE clause... on 9/18/03 11:34 AM, Tanel Poder at [EMAIL PROTECTED] wrote: Hm, it's strange. When you create a temp datafile there is some data written to it despite you actually store anything there or not (headers or whatever control structs). So, the tempfile is being used from beginning. The mechanism of creating sparse files is quite simple: just forward seek command is issued on the file, over the current end of file and then one byte (maybe block in Oracle) is written at the end position. That way, if OS filesystem support sparse files, they don't actually allocate space for the empty part. Space is allocated only when something is written to these parts (when reading empty space in sparse file, nulls are retrieved). To avoid any sparse file issues in future, I recommend you to copy the tempfiles to another name using cp, then rename back, that way the file isn't sparse anymore (well, until the tempfile autoextends, then file gets sparse again). And compare file sizes periodically with ls -l and ls -ls. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 8:04 PM At this moment it's 13 Gig. The tempfile was created as 5 Gig, but since it remained unused, it didn't grab any significant disk space. It's possible, that at some time, the free space in the file system fell below 5 Gig. When I tried to create the index, the error occurred immediately. So, the file never tried to grow at all ... ever. The big question is why not? The OS recorded no I/O errors at the time, and we have had no I/O errors on any of the other stuff using the file system. What I am wondering is if there is some IMPLIED disk address assignment that occurs when the tempfile (a sparse file, I assume) is created, and can this assignment be overwritten or screwed up in some way if the tempfile remains unused so that when the tempfile finally wants its space, the filesystem has changed from the time the file was created, and now tempfile can't grab anything. Or do we have some other kind of weirdness going on here? Is this making sense? -Original Message- From: Tanel Poder [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:35 AM To: Multiple recipients of list ORACLE-L Subject: Re: DBMS_STATS and CBO Btw, how much free space do you have in OS where your tempfiles are? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:49 PM (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: DBMS_STATS and CBO
(Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 and CBO
Sorry about the last post. Forgot to change the subject. Duh! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 and CBO
Stephen, I had something similar happen to me. In 8i, Temp files are not fully allocated when they get created. So if you create a temp file of 600M, only a small portion gets immediately allocated. The Temp file grows into the full 600M as needed. They did this to speed up the creation of the Temp files. The problem is that if you fill the disk up with other stuff, then the Temp file cannot grow when it wants to, and you get the error you got. Kinda a subtle little gotcha here. I personally don't like it - just because you can get stung with this much later in the life of the database - like you and I did. So, yeah, I think you understand what's happening. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 18, 2003 11:50 AM To: Multiple recipients of list ORACLE-L (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 and CBO
Btw, how much free space do you have in OS where your tempfiles are? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:49 PM (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 and CBO
At this moment it's 13 Gig. The tempfile was created as 5 Gig, but since it remained unused, it didn't grab any significant disk space. It's possible, that at some time, the free space in the file system fell below 5 Gig. When I tried to create the index, the error occurred immediately. So, the file never tried to grow at all ... ever. The big question is why not? The OS recorded no I/O errors at the time, and we have had no I/O errors on any of the other stuff using the file system. What I am wondering is if there is some IMPLIED disk address assignment that occurs when the tempfile (a sparse file, I assume) is created, and can this assignment be overwritten or screwed up in some way if the tempfile remains unused so that when the tempfile finally wants its space, the filesystem has changed from the time the file was created, and now tempfile can't grab anything. Or do we have some other kind of weirdness going on here? Is this making sense? -Original Message- From: Tanel Poder [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:35 AM To: Multiple recipients of list ORACLE-L Subject: Re: DBMS_STATS and CBO Btw, how much free space do you have in OS where your tempfiles are? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:49 PM (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 and CBO
Hi, I have hit similiar situation in my datawarehouse server. As temp file are sparse, it did not allocate the actual space when it was created. But as you really begin to sort and the filesystem is full , you can get this error. I offlined that tempfile and add another tempfile in another filesystem and everything is ok. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:49 PM (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 and CBO
Tom, Your observation on which platform? On HPUX 11.0 I think it allocates full given physical size of tempfile at the time of creation and it was 8.1.6.2 when I created it 2 years back. I remember that I created 6 files of 501 MB each and it occupied disk space of 3GB+. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 18 Sep 2003 08:19:40 -0800 Stephen, I had something similar happen to me. In 8i, Temp files are not fully allocated when they get created. So if you create a temp file of 600M, only a small portion gets immediately allocated. The Temp file grows into the full 600M as needed. They did this to speed up the creation of the Temp files. The problem is that if you fill the disk up with other stuff, then the Temp file cannot grow when it wants to, and you get the error you got. Kinda a subtle little gotcha here. I personally don't like it - just because you can get stung with this much later in the life of the database - like you and I did. So, yeah, I think you understand what's happening. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 18, 2003 11:50 AM To: Multiple recipients of list ORACLE-L (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 and CBO
Rafiq, SunOS 5.8 Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 18, 2003 1:40 PM To: Multiple recipients of list ORACLE-L Tom, Your observation on which platform? On HPUX 11.0 I think it allocates full given physical size of tempfile at the time of creation and it was 8.1.6.2 when I created it 2 years back. I remember that I created 6 files of 501 MB each and it occupied disk space of 3GB+. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 18 Sep 2003 08:19:40 -0800 Stephen, I had something similar happen to me. In 8i, Temp files are not fully allocated when they get created. So if you create a temp file of 600M, only a small portion gets immediately allocated. The Temp file grows into the full 600M as needed. They did this to speed up the creation of the Temp files. The problem is that if you fill the disk up with other stuff, then the Temp file cannot grow when it wants to, and you get the error you got. Kinda a subtle little gotcha here. I personally don't like it - just because you can get stung with this much later in the life of the database - like you and I did. So, yeah, I think you understand what's happening. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 18, 2003 11:50 AM To: Multiple recipients of list ORACLE-L (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
Re: DBMS_STATS and CBO
Hm, it's strange. When you create a temp datafile there is some data written to it despite you actually store anything there or not (headers or whatever control structs). So, the tempfile is being used from beginning. The mechanism of creating sparse files is quite simple: just forward seek command is issued on the file, over the current end of file and then one byte (maybe block in Oracle) is written at the end position. That way, if OS filesystem support sparse files, they don't actually allocate space for the empty part. Space is allocated only when something is written to these parts (when reading empty space in sparse file, nulls are retrieved). To avoid any sparse file issues in future, I recommend you to copy the tempfiles to another name using cp, then rename back, that way the file isn't sparse anymore (well, until the tempfile autoextends, then file gets sparse again). And compare file sizes periodically with ls -l and ls -ls. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 8:04 PM At this moment it's 13 Gig. The tempfile was created as 5 Gig, but since it remained unused, it didn't grab any significant disk space. It's possible, that at some time, the free space in the file system fell below 5 Gig. When I tried to create the index, the error occurred immediately. So, the file never tried to grow at all ... ever. The big question is why not? The OS recorded no I/O errors at the time, and we have had no I/O errors on any of the other stuff using the file system. What I am wondering is if there is some IMPLIED disk address assignment that occurs when the tempfile (a sparse file, I assume) is created, and can this assignment be overwritten or screwed up in some way if the tempfile remains unused so that when the tempfile finally wants its space, the filesystem has changed from the time the file was created, and now tempfile can't grab anything. Or do we have some other kind of weirdness going on here? Is this making sense? -Original Message- From: Tanel Poder [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:35 AM To: Multiple recipients of list ORACLE-L Subject: Re: DBMS_STATS and CBO Btw, how much free space do you have in OS where your tempfiles are? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:49 PM (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: DBMS_STATS and CBO
Now, THERE is an idea! Thanky. By the way, there is now a Tanel folder in my mailbox. -Original Message- From: Tanel Poder [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 1:35 PM To: Multiple recipients of list ORACLE-L Subject: Re: DBMS_STATS and CBO To avoid any sparse file issues in future, I recommend you to copy the tempfiles to another name using cp, then rename back, -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 and CBO
Tom Thanks. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 18 Sep 2003 09:54:47 -0800 Rafiq, SunOS 5.8 Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 18, 2003 1:40 PM To: Multiple recipients of list ORACLE-L Tom, Your observation on which platform? On HPUX 11.0 I think it allocates full given physical size of tempfile at the time of creation and it was 8.1.6.2 when I created it 2 years back. I remember that I created 6 files of 501 MB each and it occupied disk space of 3GB+. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 18 Sep 2003 08:19:40 -0800 Stephen, I had something similar happen to me. In 8i, Temp files are not fully allocated when they get created. So if you create a temp file of 600M, only a small portion gets immediately allocated. The Temp file grows into the full 600M as needed. They did this to speed up the creation of the Temp files. The problem is that if you fill the disk up with other stuff, then the Temp file cannot grow when it wants to, and you get the error you got. Kinda a subtle little gotcha here. I personally don't like it - just because you can get stung with this much later in the life of the database - like you and I did. So, yeah, I think you understand what's happening. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 18, 2003 11:50 AM To: Multiple recipients of list ORACLE-L (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
DBMS_STATS and CBO
We have a table (monthly fact table) which has 24 partitions and partitioned by month. There is data in only 3 partitions. All the indexes are locally partitioned. In dev db, I analyzed the table and indexes with analyze table..compute statistics. When I query the no. of rows group by month, it returns the query in couple of seconds and does the index scan(bitmap) on month column. I created this table in production db and this time I used the dbms_stats to create the statistics (compute both on table and indexes). When I run this query, it does full table scan. The only way I could make it to use index scan by specifying hints. All the parameters(init.ora) are exactly the same on both databases and it is 8.1.7.4. In prod db, I tried various combinations of optimizer_index_caching, optimizer_index_cost_adj to favor the index scan. There was no use. Then, I did analyze table .. compute statistics on prod table but it was still doing the FTS. I did not delete the stats created by dbms_stats before using analyze table stmnt. Finally, I deleted the stats generated by dbms_stats/analyze table before generating stats again with Analyze table table compute statistics. It is doing the index scan now. I have no clue 1. why it does not use index when I generated the stats w/dbms_stats. 2. why I had to explicitly delete the stats before generating the stats again to make use of the index. Thanks for your help in advance. Thanks. Best Regards, Prasad 860 843 8377 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 and CBO
Thanks Govind for your reply and suggestion. Actually, I did 'FOR ALL COLUMNS SIZE 1' only. Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE); Best Regards, Prasad 860 843 8377 Govind.Arumugam@ alltel.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: DBMS_STATS and CBO .com 08/09/2003 12:14 AM Please respond to ORACLE-L Prasad, We ran into the same problem when we did FOR ALL INDEXED COLUMNS using DBMS_STATS. Then we changed it to run against FOR ALL COLUMNS SIZE 1. Then CBO started to use the indexes. execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE, 'FOR ALL COLUMNS SIZE 1',20,'DEFAULT',TRUE); Here 30 is the sample percent (ie 30%) and 20 is the the parallel degree. Hope this helps. Govind -Original Message- [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 9:24 PM To: Multiple recipients of list ORACLE-L We have a table (monthly fact table) which has 24 partitions and partitioned by month. There is data in only 3 partitions. All the indexes are locally partitioned. In dev db, I analyzed the table and indexes with analyze table..compute statistics. When I query the no. of rows group by month, it returns the query in couple of seconds and does the index scan(bitmap) on month column. I created this table in production db and this time I used the dbms_stats to create the statistics (compute both on table and indexes). When I run this query, it does full table scan. The only way I could make it to use index scan by specifying hints. All the parameters(init.ora) are exactly the same on both databases and it is 8.1.7.4. In prod db, I tried various combinations of optimizer_index_caching, optimizer_index_cost_adj to favor the index scan. There was no use. Then, I did analyze table .. compute statistics on prod table but it was still doing the FTS. I did not delete the stats created by dbms_stats before using analyze table stmnt. Finally, I deleted the stats generated by dbms_stats/analyze table before generating stats again with Analyze table table compute statistics. It is doing the index scan now. I have no clue 1. why it does not use index when I generated the stats w/dbms_stats. 2. why I had to explicitly delete the stats before generating the stats again to make use of the index. Thanks for your help in advance. Thanks. Best Regards, Prasad 860 843 8377 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538
RE: DBMS_STATS and CBO
Prasad, We ran into the same problem when we did FOR ALL INDEXED COLUMNS using DBMS_STATS. Then we changed it to run against FOR ALL COLUMNS SIZE 1. Then CBO started to use the indexes. execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE, 'FOR ALL COLUMNS SIZE 1',20,'DEFAULT',TRUE); Here 30 is the sample percent (ie 30%) and 20 is the the parallel degree. Hope this helps. Govind -Original Message- [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 9:24 PM To: Multiple recipients of list ORACLE-L We have a table (monthly fact table) which has 24 partitions and partitioned by month. There is data in only 3 partitions. All the indexes are locally partitioned. In dev db, I analyzed the table and indexes with analyze table..compute statistics. When I query the no. of rows group by month, it returns the query in couple of seconds and does the index scan(bitmap) on month column. I created this table in production db and this time I used the dbms_stats to create the statistics (compute both on table and indexes). When I run this query, it does full table scan. The only way I could make it to use index scan by specifying hints. All the parameters(init.ora) are exactly the same on both databases and it is 8.1.7.4. In prod db, I tried various combinations of optimizer_index_caching, optimizer_index_cost_adj to favor the index scan. There was no use. Then, I did analyze table .. compute statistics on prod table but it was still doing the FTS. I did not delete the stats created by dbms_stats before using analyze table stmnt. Finally, I deleted the stats generated by dbms_stats/analyze table before generating stats again with Analyze table table compute statistics. It is doing the index scan now. I have no clue 1. why it does not use index when I generated the stats w/dbms_stats. 2. why I had to explicitly delete the stats before generating the stats again to make use of the index. Thanks for your help in advance. Thanks. Best Regards, Prasad 860 843 8377 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 and CBO
Prasad, Make sure that low_value and high_value columns do not have null values ie. generate statistics on all columns. Analyze table generates the correct values for these columns whereas FOR ALL INDEXED columns in DBMS_STATS do not. Once these values are available through FOR ALL COLUMNS SIZE 1 ( ie do not generate histograms ), we seen the queries to be using the indexes without any hints. We ran into this problem when we went to 9i for the first time. select column_name, NUM_DISTINCT , decode(LOW_VALUE ,null,null,'Full') Low , decode(HIGH_VALUE ,null,null,'Full') High , DENSITY , NUM_NULLS , NUM_BUCKETS , LAST_ANALYZED , SAMPLE_SIZE from dba_tab_columns where owner = 'your_owner' and table_name = 'your_table'; Govind -Original Message- Sent: Saturday, August 09, 2003 10:49 AM To: Multiple recipients of list ORACLE-L Hi! Maybe you used analyze command without column analyzing clause, but used dbms_stats package with column analyze clause (for all columns parameter). Or it just could be because dbms_stats calculates some stats somewhat differently (supposedly better), than old analyze command (average column length and some spare columns of hist_head$ have varied in my tests). If you set SIZE to 1, then only column low and high value are stored in histogram. (In hist_head$ instead of histgrm$). As an alternative to Govind's suggestion, you could increase SIZE parameter (maximum is 254) to give CBO better understanding of data distribution. Note that you should be careful with the METHOD_OPT parameter in gather_schema_statistics procedure in version 9i, because if you supply invalid parameter there, then the procedure just does nothing and returns without error! You should verify from last_analyzed column to see whether a segment was actually analyzed or not. (This was tested on 9.2.0.1 on W2k, it might be fixed on newer patch levels). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, August 09, 2003 7:29 AM Thanks Govind for your reply and suggestion. Actually, I did 'FOR ALL COLUMNS SIZE 1' only. Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE); Best Regards, Prasad 860 843 8377 Govind.Arumugam@ alltel.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: DBMS_STATS and CBO .com 08/09/2003 12:14 AM Please respond to ORACLE-L Prasad, We ran into the same problem when we did FOR ALL INDEXED COLUMNS using DBMS_STATS. Then we changed it to run against FOR ALL COLUMNS SIZE 1. Then CBO started to use the indexes. execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE, 'FOR ALL COLUMNS SIZE 1',20,'DEFAULT',TRUE); Here 30 is the sample percent (ie 30%) and 20 is the the parallel degree. Hope this helps. Govind -Original Message- [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 9:24 PM To: Multiple recipients of list ORACLE-L We have a table (monthly fact table) which has 24 partitions and partitioned by month. There is data in only 3 partitions. All the indexes are locally partitioned. In dev db, I analyzed the table and indexes with analyze table..compute statistics. When I query the no. of rows group by month, it returns the query in couple of seconds and does the index scan(bitmap) on month column. I created this table in production db and this time I used the dbms_stats to create the statistics (compute both on table and indexes). When I run this query, it does full table scan. The only way I could make it to use index scan by specifying hints. All the parameters(init.ora) are exactly the same on both databases and it is 8.1.7.4. In prod db, I tried various combinations of optimizer_index_caching, optimizer_index_cost_adj to favor the index scan. There was no use. Then, I did analyze table .. compute statistics on prod table but it was still doing the FTS. I did not delete the stats created by dbms_stats before using analyze table stmnt. Finally, I deleted the stats generated by dbms_stats/analyze table before generating stats again with Analyze table table compute statistics. It is doing the index scan now. I have no clue 1. why it does not use index when I generated the stats w/dbms_stats. 2. why I had to explicitly delete the stats before generating the stats again to make use of the index. Thanks for your help in advance. Thanks. Best Regards, Prasad 860 843 8377 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: DBMS_STATS and CBO
Thanks Govind and Tanel for your replies. Since I was testing both ways (analyze table and dbms_stats) before, I cleared out the stats using analyze table..delete statistics and dbms_stats.delete_table_stats. Then, I generated stats using dbms_stats.gather_table_stats and it is working fine now. Thanks for your help. Best Regards, Prasad 860 843 8377 Govind.Arumugam@ alltel.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: DBMS_STATS and CBO .com 08/09/2003 01:19 PM Please respond to ORACLE-L Prasad, Make sure that low_value and high_value columns do not have null values ie. generate statistics on all columns. Analyze table generates the correct values for these columns whereas FOR ALL INDEXED columns in DBMS_STATS do not. Once these values are available through FOR ALL COLUMNS SIZE 1 ( ie do not generate histograms ), we seen the queries to be using the indexes without any hints. We ran into this problem when we went to 9i for the first time. select column_name, NUM_DISTINCT , decode(LOW_VALUE ,null,null,'Full') Low , decode(HIGH_VALUE ,null,null,'Full') High , DENSITY , NUM_NULLS , NUM_BUCKETS , LAST_ANALYZED , SAMPLE_SIZE from dba_tab_columns where owner = 'your_owner' and table_name = 'your_table'; Govind -Original Message- Sent: Saturday, August 09, 2003 10:49 AM To: Multiple recipients of list ORACLE-L Hi! Maybe you used analyze command without column analyzing clause, but used dbms_stats package with column analyze clause (for all columns parameter). Or it just could be because dbms_stats calculates some stats somewhat differently (supposedly better), than old analyze command (average column length and some spare columns of hist_head$ have varied in my tests). If you set SIZE to 1, then only column low and high value are stored in histogram. (In hist_head$ instead of histgrm$). As an alternative to Govind's suggestion, you could increase SIZE parameter (maximum is 254) to give CBO better understanding of data distribution. Note that you should be careful with the METHOD_OPT parameter in gather_schema_statistics procedure in version 9i, because if you supply invalid parameter there, then the procedure just does nothing and returns without error! You should verify from last_analyzed column to see whether a segment was actually analyzed or not. (This was tested on 9.2.0.1 on W2k, it might be fixed on newer patch levels). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, August 09, 2003 7:29 AM Thanks Govind for your reply and suggestion. Actually, I did 'FOR ALL COLUMNS SIZE 1' only. Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE); Best Regards, Prasad 860 843 8377 Govind.Arumugam@ alltel.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: DBMS_STATS and CBO .com 08/09/2003 12:14 AM Please
Re: DBMS_STATS and CBO
Hi! Maybe you used analyze command without column analyzing clause, but used dbms_stats package with column analyze clause (for all columns parameter). Or it just could be because dbms_stats calculates some stats somewhat differently (supposedly better), than old analyze command (average column length and some spare columns of hist_head$ have varied in my tests). If you set SIZE to 1, then only column low and high value are stored in histogram. (In hist_head$ instead of histgrm$). As an alternative to Govind's suggestion, you could increase SIZE parameter (maximum is 254) to give CBO better understanding of data distribution. Note that you should be careful with the METHOD_OPT parameter in gather_schema_statistics procedure in version 9i, because if you supply invalid parameter there, then the procedure just does nothing and returns without error! You should verify from last_analyzed column to see whether a segment was actually analyzed or not. (This was tested on 9.2.0.1 on W2k, it might be fixed on newer patch levels). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, August 09, 2003 7:29 AM Thanks Govind for your reply and suggestion. Actually, I did 'FOR ALL COLUMNS SIZE 1' only. Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE); Best Regards, Prasad 860 843 8377 Govind.Arumugam@ alltel.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: DBMS_STATS and CBO .com 08/09/2003 12:14 AM Please respond to ORACLE-L Prasad, We ran into the same problem when we did FOR ALL INDEXED COLUMNS using DBMS_STATS. Then we changed it to run against FOR ALL COLUMNS SIZE 1. Then CBO started to use the indexes. execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE, 'FOR ALL COLUMNS SIZE 1',20,'DEFAULT',TRUE); Here 30 is the sample percent (ie 30%) and 20 is the the parallel degree. Hope this helps. Govind -Original Message- [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 9:24 PM To: Multiple recipients of list ORACLE-L We have a table (monthly fact table) which has 24 partitions and partitioned by month. There is data in only 3 partitions. All the indexes are locally partitioned. In dev db, I analyzed the table and indexes with analyze table..compute statistics. When I query the no. of rows group by month, it returns the query in couple of seconds and does the index scan(bitmap) on month column. I created this table in production db and this time I used the dbms_stats to create the statistics (compute both on table and indexes). When I run this query, it does full table scan. The only way I could make it to use index scan by specifying hints. All the parameters(init.ora) are exactly the same on both databases and it is 8.1.7.4. In prod db, I tried various combinations of optimizer_index_caching, optimizer_index_cost_adj to favor the index scan. There was no use. Then, I did analyze table .. compute statistics on prod table but it was still doing the FTS. I did not delete the stats created by dbms_stats before using analyze table stmnt. Finally, I deleted the stats generated by dbms_stats/analyze table before generating stats again with Analyze table table compute statistics. It is doing the index scan now. I have no clue 1. why it does not use index when I generated the stats w/dbms_stats. 2. why I had to explicitly delete the stats before generating the stats again to make use of the index. Thanks for your help in advance. Thanks. Best Regards, Prasad 860 843 8377 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing
DBMS_STATS error
I ran the following and got an error. Any idea? I noticed after I ran it that my DBA_TAB_MODIFICATION view has no records even though I ran gather_schema_stats yesterdays SQL exec dbms_stats.gather_schema_stats(ownname='MASTER_TEST2',estimate_percent=20,degree=16,cascade=true,options='GATH ER EMPTY'); BEGIN dbms_stats.gather_schema_stats(ownname='MASTER_TEST2',estimate_percent=20,degree=16,cascade=true,options='GATHER E MPTY'); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at SYS.DBMS_STATS, line 4609 ORA-06512: at SYS.DBMS_STATS, line 4732 ORA-06512: at SYS.DBMS_STATS, line 4704 ORA-06512: at line 1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 error
why are you so focused on this view: DBA_TAB_MODIFICATIONS It contains info on tables *modified* : columns INSERTS, UPDATES, DELETES give you the number rows inserted or updated or deleted since the last your tables was analyzed Concerning your error, I ran it as SYSTEM and it worked...8.1.7.0 SQL exec dbms_stats.gather_schema_stats(ownname='REGIS',estimate_percent=20,degree= 16,cascade=true,options='GATHER EMPTY' PL/SQL procedure successfully completed. which version of Oracle you are running ? Can you do a: desc dbms_stats ? Regis -Original Message- Sent: Thursday, June 12, 2003 1:49 PM To: Multiple recipients of list ORACLE-L I ran the following and got an error. Any idea? I noticed after I ran it that my DBA_TAB_MODIFICATION view has no records even though I ran gather_schema_stats yesterdays SQL exec dbms_stats.gather_schema_stats(ownname='MASTER_TEST2',estimate_percent=20, degree=16,cascade=true,options='GATH ER EMPTY'); BEGIN dbms_stats.gather_schema_stats(ownname='MASTER_TEST2',estimate_percent=20, degree=16,cascade=true,options='GATHER E MPTY'); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at SYS.DBMS_STATS, line 4609 ORA-06512: at SYS.DBMS_STATS, line 4732 ORA-06512: at SYS.DBMS_STATS, line 4704 ORA-06512: at line 1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: RE: DBMS_STATS error
8.1.7 I ran it again and it worked. I really have no idea why it failed. From: Regis Biassala [EMAIL PROTECTED] Date: 2003/06/12 Thu AM 10:09:52 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: DBMS_STATS error why are you so focused on this view: DBA_TAB_MODIFICATIONS It contains info on tables *modified* : columns INSERTS, UPDATES, DELETES give you the number rows inserted or updated or deleted since the last your tables was analyzed Concerning your error, I ran it as SYSTEM and it worked...8.1.7.0 SQL exec dbms_stats.gather_schema_stats(ownname='REGIS',estimate_percent=20,degree= 16,cascade=true,options='GATHER EMPTY' PL/SQL procedure successfully completed. which version of Oracle you are running ? Can you do a: desc dbms_stats ? Regis -Original Message- Sent: Thursday, June 12, 2003 1:49 PM To: Multiple recipients of list ORACLE-L I ran the following and got an error. Any idea? I noticed after I ran it that my DBA_TAB_MODIFICATION view has no records even though I ran gather_schema_stats yesterdays SQL exec dbms_stats.gather_schema_stats(ownname='MASTER_TEST2',estimate_percent=20, degree=16,cascade=true,options='GATH ER EMPTY'); BEGIN dbms_stats.gather_schema_stats(ownname='MASTER_TEST2',estimate_percent=20, degree=16,cascade=true,options='GATHER E MPTY'); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at SYS.DBMS_STATS, line 4609 ORA-06512: at SYS.DBMS_STATS, line 4732 ORA-06512: at SYS.DBMS_STATS, line 4704 ORA-06512: at line 1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 via dbms_job - syntax question
Thanks for the try Igor but that doesn't work either ERROR at line 1: ORA-06550: line 2, column 24: PLS-00201: identifier 'CASCADE' must be declared ORA-06550: line 1, column 99: Wolfgang's suggestion doesn't work and I have tried every permutation of quotes (including none at all) around the cacscade keyword as per Raj' suggestion with still no success. Thomas's suggestion of using a procedure was my failback position and is probably the way I will have to go. I am still frustrated that I cannot get the syntax to work correctly. Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it fails when I try a dbms_job.run Thanks to everyone anyway. John -Original Message- Sent: 10 June 2003 16:55 To: Multiple recipients of list ORACLE-L This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing parameters to stored procedure, you can not mix and match positional method with naming: either you use formal parameters, or not. It seems like you need naming. I used CHR(39) to get quotes around schema name. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Tuesday, June 10, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Listers, Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs The following syntax works fine to run the procedure interactively execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent =10,cascade=true); but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC ADE=TRUE'');', trunc(sysdate+1)+01/24,'sysdate+7'); end; / The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as Job What == 2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE') ; but that fails to run Has anybody got any ideas. I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 via dbms_job - syntax question SOLVED
Thanks for all the help. The problem was not with my coding but the syntax. In my worked example I had CASCADE=TRUE whereas it should have been CASCADE=TRUE. A subtle difference but one that caused me (and fellow listers) no end of hassle. So all the answers that I said were incorrect were not, they had all replicated my original typo. But one thing I proved was that you can mix and match positional and naming parameters The final working version for anyone who is interested is declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCADE=TRUE);', trunc(sysdate+1)+01/24,'sysdate+7'); end; / Thanks to all John -Original Message- Sent: 11 June 2003 09:24 To: Multiple recipients of list ORACLE-L Thanks for the try Igor but that doesn't work either ERROR at line 1: ORA-06550: line 2, column 24: PLS-00201: identifier 'CASCADE' must be declared ORA-06550: line 1, column 99: Wolfgang's suggestion doesn't work and I have tried every permutation of quotes (including none at all) around the cacscade keyword as per Raj' suggestion with still no success. Thomas's suggestion of using a procedure was my failback position and is probably the way I will have to go. I am still frustrated that I cannot get the syntax to work correctly. Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it fails when I try a dbms_job.run Thanks to everyone anyway. John -Original Message- Sent: 10 June 2003 16:55 To: Multiple recipients of list ORACLE-L This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing parameters to stored procedure, you can not mix and match positional method with naming: either you use formal parameters, or not. It seems like you need naming. I used CHR(39) to get quotes around schema name. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Tuesday, June 10, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Listers, Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs The following syntax works fine to run the procedure interactively execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent =10,cascade=true); but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC ADE=TRUE'');', trunc(sysdate+1)+01/24,'sysdate+7'); end; / The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as Job What == 2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE') ; but that fails to run Has anybody got any ideas. I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: dbms_stats via dbms_job - syntax question
John, My code doesn't work probably because CASCADE = TRUE should actually be: CASCADE = TRUE. So, this should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Wednesday, June 11, 2003 3:24 AM To: Multiple recipients of list ORACLE-L Thanks for the try Igor but that doesn't work either ERROR at line 1: ORA-06550: line 2, column 24: PLS-00201: identifier 'CASCADE' must be declared ORA-06550: line 1, column 99: Wolfgang's suggestion doesn't work and I have tried every permutation of quotes (including none at all) around the cacscade keyword as per Raj' suggestion with still no success. Thomas's suggestion of using a procedure was my failback position and is probably the way I will have to go. I am still frustrated that I cannot get the syntax to work correctly. Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it fails when I try a dbms_job.run Thanks to everyone anyway. John -Original Message- Sent: 10 June 2003 16:55 To: Multiple recipients of list ORACLE-L This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing parameters to stored procedure, you can not mix and match positional method with naming: either you use formal parameters, or not. It seems like you need naming. I used CHR(39) to get quotes around schema name. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Tuesday, June 10, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Listers, Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs The following syntax works fine to run the procedure interactively execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent =10,cascade=true); but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC ADE=TRUE'');', trunc(sysdate+1)+01/24,'sysdate+7'); end; / The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as Job What == 2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE') ; but that fails to run Has anybody got any ideas. I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: dbms_stats via dbms_job - syntax question SOLVED
But one thing I proved was that you can mix and match positional and naming parameters Hmm, I'd still rather use one OR another: you never know if mixing them would work in the next version... Why trying your luck?-:) Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Wednesday, June 11, 2003 4:35 AM To: Multiple recipients of list ORACLE-L Thanks for all the help. The problem was not with my coding but the syntax. In my worked example I had CASCADE=TRUE whereas it should have been CASCADE=TRUE. A subtle difference but one that caused me (and fellow listers) no end of hassle. So all the answers that I said were incorrect were not, they had all replicated my original typo. But one thing I proved was that you can mix and match positional and naming parameters The final working version for anyone who is interested is declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCAD E=TRUE);', trunc(sysdate+1)+01/24,'sysdate+7'); end; / Thanks to all John -Original Message- Sent: 11 June 2003 09:24 To: Multiple recipients of list ORACLE-L Thanks for the try Igor but that doesn't work either ERROR at line 1: ORA-06550: line 2, column 24: PLS-00201: identifier 'CASCADE' must be declared ORA-06550: line 1, column 99: Wolfgang's suggestion doesn't work and I have tried every permutation of quotes (including none at all) around the cacscade keyword as per Raj' suggestion with still no success. Thomas's suggestion of using a procedure was my failback position and is probably the way I will have to go. I am still frustrated that I cannot get the syntax to work correctly. Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it fails when I try a dbms_job.run Thanks to everyone anyway. John -Original Message- Sent: 10 June 2003 16:55 To: Multiple recipients of list ORACLE-L This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing parameters to stored procedure, you can not mix and match positional method with naming: either you use formal parameters, or not. It seems like you need naming. I used CHR(39) to get quotes around schema name. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Tuesday, June 10, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Listers, Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs The following syntax works fine to run the procedure interactively execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent =10,cascade=true); but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC ADE=TRUE'');', trunc(sysdate+1)+01/24,'sysdate+7'); end; / The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as Job What == 2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE') ; but that fails to run Has anybody got any ideas. I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: dbms_stats via dbms_job - syntax question SOLVED
John Then be sure to set the init.ora parameter _do_what_I_mean=true It solved most of my problems. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 11, 2003 4:35 AM To: Multiple recipients of list ORACLE-L Thanks for all the help. The problem was not with my coding but the syntax. In my worked example I had CASCADE=TRUE whereas it should have been CASCADE=TRUE. A subtle difference but one that caused me (and fellow listers) no end of hassle. So all the answers that I said were incorrect were not, they had all replicated my original typo. But one thing I proved was that you can mix and match positional and naming parameters The final working version for anyone who is interested is declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCADE=T RUE);', trunc(sysdate+1)+01/24,'sysdate+7'); end; / Thanks to all John -Original Message- Sent: 11 June 2003 09:24 To: Multiple recipients of list ORACLE-L Thanks for the try Igor but that doesn't work either ERROR at line 1: ORA-06550: line 2, column 24: PLS-00201: identifier 'CASCADE' must be declared ORA-06550: line 1, column 99: Wolfgang's suggestion doesn't work and I have tried every permutation of quotes (including none at all) around the cacscade keyword as per Raj' suggestion with still no success. Thomas's suggestion of using a procedure was my failback position and is probably the way I will have to go. I am still frustrated that I cannot get the syntax to work correctly. Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it fails when I try a dbms_job.run Thanks to everyone anyway. John -Original Message- Sent: 10 June 2003 16:55 To: Multiple recipients of list ORACLE-L This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing parameters to stored procedure, you can not mix and match positional method with naming: either you use formal parameters, or not. It seems like you need naming. I used CHR(39) to get quotes around schema name. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Tuesday, June 10, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Listers, Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs The following syntax works fine to run the procedure interactively execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent =10,cascade=true); but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC ADE=TRUE'');', trunc(sysdate+1)+01/24,'sysdate+7'); end; / The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as Job What == 2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE') ; but that fails to run Has anybody got any ideas. I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
dbms_stats via dbms_job - syntax question
Listers, Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs The following syntax works fine to run the procedure interactively execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10,cascade=true); but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE=TRUE'');', trunc(sysdate+1)+01/24,'sysdate+7'); end; / The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as Job What == 2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE'); but that fails to run Has anybody got any ideas. I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 via dbms_job - syntax question
Title: RE: dbms_stats via dbms_job - syntax question you don't need quotes around cascade=true ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Hallas, John, Tech Dev [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 10, 2003 10:35 AM To: Multiple recipients of list ORACLE-L Subject: dbms_stats via dbms_job - syntax question Listers, Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs The following syntax works fine to run the procedure interactively execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10,cascade=true); but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE=TRUE'');', trunc(sysdate+1)+01/24,'sysdate+7'); end; / The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as Job What == 2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE'); but that fails to run Has anybody got any ideas. I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more John This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: dbms_stats via dbms_job - syntax question
John, I think the easiest way to do this is to create a stored procedure that calls dbms_stats for you. you could then simply run your stored procedure from dbms_jobs. create or replace procedure run_stats is begin dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10 ,cascade=true); end; and then: declare l_job number; begin dbms_job.submit (l_job,'run_stats', trunc(sysdate+1)+01/24,'sysdate+7'); end; / make sense? PS. I personally don't like DBMS_JOBS. I use either Cron, or Tivoli on NT systems to schedule all jobs. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, June 10, 2003 10:35 AM To: Multiple recipients of list ORACLE-L Listers, Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs The following syntax works fine to run the procedure interactively execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10 ,cascade=true); but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE =TRUE'');', trunc(sysdate+1)+01/24,'sysdate+7'); end; / The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as Job What == 2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE'); but that fails to run Has anybody got any ideas. I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 via dbms_job - syntax question
This should work: declare l_job number; begin dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10, block_sample = FALSE, CASCADE = TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7'); end; / When passing parameters to stored procedure, you can not mix and match positional method with naming: either you use formal parameters, or not. It seems like you need naming. I used CHR(39) to get quotes around schema name. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hallas, John, Tech Dev Sent: Tuesday, June 10, 2003 9:35 AM To: Multiple recipients of list ORACLE-L Listers, Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs The following syntax works fine to run the procedure interactively execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent =10,cascade=true); but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC ADE=TRUE'');', trunc(sysdate+1)+01/24,'sysdate+7'); end; / The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as Job What == 2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE') ; but that fails to run Has anybody got any ideas. I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 via dbms_job - syntax question
Why not submit it exactly the same as you do it interactively: declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(ownname=''RPT_3G_MASTER'',estimate_percent=10,cascade=true);', trunc(sysdate+1)+01/24,'sysdate+7'); end; / All you need to do is replace the single quotes around the schema name with double quotes. At 06:34 AM 6/10/2003 -0800, you wrote: Listers, Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs The following syntax works fine to run the procedure interactively execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10,cascade=true); but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script declare l_job number; begin dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE=TRUE'');', trunc(sysdate+1)+01/24,'sysdate+7'); end; / The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as Job What == 2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE'); but that fails to run Has anybody got any ideas. I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: RE: Schedule Analyze using DBMS_STATS ???
There are a Lot of options to DBMS_STATS .. And be Judicious in using the same. Read the docs before attempting it. BTW .. What is the session waiting for .. Just Check v$session_wait. HTH Best Regards, Ganesh R DID : +65-6215-8413 HP : +65-9067-8474 === Live to learn... forget... and learn again. === -Original Message- Keshava Murthy (Cognizant) Sent: Wednesday, June 04, 2003 8:55 PM To: Multiple recipients of list ORACLE-L Hi all, Everyone is recommending DBMS_STATS to be used for computing the statistics. But even after specifying the parallel option , DBMS_STATS is taking lots of time ! in comparison to Analyze... In case of tables with a million records the query just hangs when i use the stats package... Can anybody tell me whts happening ? Regards kesh -Original Message- Sent: Tuesday, June 03, 2003 8:55 PM To: Multiple recipients of list ORACLE-L Sorry JP, I should have clarified that DBMS_STATS is the way to go, when on a 9i or above release. One of the simple factors being that ANALYZE is being deprecated.. There seemed to be a number of bugs/quirks, whatever you want to call them, in certain 8.1.x versions, which are now fixed within 9i.. There's a number of threads about the issues, available in the list archives (you can find them at http://www.faqchest.com), which I dug around - as I remembered seeing a post by Connor McDonald, where he gave away an alternate script to gather stats, here's the mail: http://www.faqchest.com/prgm/oracle-l/ora-02/ora-0210/ora-021011/ora0210 0802 _06150.html I think the general consensus is that DBMS_STATS is quicker. I've never personally done any comparative benchmarks to corroborate this sheep following attitude though.. ;) Regards Mark -Original Message- Sent: 03 June 2003 14:55 To: Mark Leith Cc: [EMAIL PROTECTED] Thanx a lot Mark. Sure , your code has given me a starting point. let me change accordingly to use DBMS_STATSe and give a try :-) So,can i conclude that DBMS_STATS is better than ANALYZE ?! Someone over the list mentioned that DBMS_STATS is slower. is it so ? What is your opinion regarding this ?! just curious to know ! Regards, Jp. 3-6-2003 22:01:32, Mark Leith [EMAIL PROTECTED] wrote: Prem, Use DBMS_JOBS to run the analyze: === undef username undef password grant analyze any to username / connect username/password CREATE or REPLACE PROCEDURE analyze_tables ( v_stat_type IN VARCHAR2 := 'COMPUTE') AS CURSOR c IS SELECT DISTINCT owner FROM all_tables WHERE owner not in ('SYS','SYSTEM'); BEGIN FOR any_row IN c LOOP dbms_utility.analyze_schema( any_row.owner,v_stat_type); END LOOP; END; / === variable jobno number declare jobno number; BEGIN dbms_job.submit(:jobno, 'begin username.analyze_tables; end;', to_date('03jun0304:00','DDMONYYHH24:MI'), 'trunc(sysdate)+(1+(4/24))'); END; === The above will run a COMPUTE analyze on all schemas, except SYS and SYSTEM, at 4:00am every day. Modify it to your own needs, but it should give you a starting point.. I would also recommend using DBMS_STATS to generate your statistics. Have fun! ;0) Mark --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Ganesh Raja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: Schedule Analyze using DBMS_STATS ???
Prem, Use DBMS_JOBS to run the analyze: === undef username undef password grant analyze any to username / connect username/password CREATE or REPLACE PROCEDURE analyze_tables ( v_stat_type IN VARCHAR2 := 'COMPUTE') AS CURSOR c IS SELECT DISTINCT owner FROM all_tables WHERE owner not in ('SYS','SYSTEM'); BEGIN FOR any_row IN c LOOP dbms_utility.analyze_schema( any_row.owner,v_stat_type); END LOOP; END; / === variable jobno number declare jobno number; BEGIN dbms_job.submit(:jobno, 'begin username.analyze_tables; end;', to_date('03jun0304:00','DDMONYYHH24:MI'), 'trunc(sysdate)+(1+(4/24))'); END; === The above will run a COMPUTE analyze on all schemas, except SYS and SYSTEM, at 4:00am every day. Modify it to your own needs, but it should give you a starting point.. I would also recommend using DBMS_STATS to generate your statistics. Have fun! ;0) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Sent: 03 June 2003 12:55 To: Multiple recipients of list ORACLE-L Guys, I would like to scedule the process of analyzing tables/indexes using DBMS_STATS ? Hope someone of u would have a script for the same. can u share with me please ?! BTW,Which is advisable : ANALYZE or DBMS_STATS ? there was a discussion about the same on the list also. but not found any conclusion yet. anu suggestions !!! the ENV is oracle 9.2.0.1/Win2K. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: RE: Schedule Analyze using DBMS_STATS ???
Thanx a lot Mark. Sure , your code has given me a starting point. let me change accordingly to use DBMS_STATSe and give a try :-) So,can i conclude that DBMS_STATS is better than ANALYZE ?! Someone over the list mentioned that DBMS_STATS is slower. is it so ? What is your opinion regarding this ?! just curious to know ! Regards, Jp. 3-6-2003 22:01:32, Mark Leith [EMAIL PROTECTED] wrote: Prem, Use DBMS_JOBS to run the analyze: === undef username undef password grant analyze any to username / connect username/password CREATE or REPLACE PROCEDURE analyze_tables ( v_stat_type IN VARCHAR2 := 'COMPUTE') AS CURSOR c IS SELECT DISTINCT owner FROM all_tables WHERE owner not in ('SYS','SYSTEM'); BEGIN FOR any_row IN c LOOP dbms_utility.analyze_schema( any_row.owner,v_stat_type); END LOOP; END; / === variable jobno number declare jobno number; BEGIN dbms_job.submit(:jobno, 'begin username.analyze_tables; end;', to_date('03jun0304:00','DDMONYYHH24:MI'), 'trunc(sysdate)+(1+(4/24))'); END; === The above will run a COMPUTE analyze on all schemas, except SYS and SYSTEM, at 4:00am every day. Modify it to your own needs, but it should give you a starting point.. I would also recommend using DBMS_STATS to generate your statistics. Have fun! ;0) Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: RE: Schedule Analyze using DBMS_STATS ???
Sorry JP, I should have clarified that DBMS_STATS is the way to go, when on a 9i or above release. One of the simple factors being that ANALYZE is being deprecated.. There seemed to be a number of bugs/quirks, whatever you want to call them, in certain 8.1.x versions, which are now fixed within 9i.. There's a number of threads about the issues, available in the list archives (you can find them at http://www.faqchest.com), which I dug around - as I remembered seeing a post by Connor McDonald, where he gave away an alternate script to gather stats, here's the mail: http://www.faqchest.com/prgm/oracle-l/ora-02/ora-0210/ora-021011/ora02100802 _06150.html I think the general consensus is that DBMS_STATS is quicker. I've never personally done any comparative benchmarks to corroborate this sheep following attitude though.. ;) Regards Mark -Original Message- Sent: 03 June 2003 14:55 To: Mark Leith Cc: [EMAIL PROTECTED] Thanx a lot Mark. Sure , your code has given me a starting point. let me change accordingly to use DBMS_STATSe and give a try :-) So,can i conclude that DBMS_STATS is better than ANALYZE ?! Someone over the list mentioned that DBMS_STATS is slower. is it so ? What is your opinion regarding this ?! just curious to know ! Regards, Jp. 3-6-2003 22:01:32, Mark Leith [EMAIL PROTECTED] wrote: Prem, Use DBMS_JOBS to run the analyze: === undef username undef password grant analyze any to username / connect username/password CREATE or REPLACE PROCEDURE analyze_tables ( v_stat_type IN VARCHAR2 := 'COMPUTE') AS CURSOR c IS SELECT DISTINCT owner FROM all_tables WHERE owner not in ('SYS','SYSTEM'); BEGIN FOR any_row IN c LOOP dbms_utility.analyze_schema( any_row.owner,v_stat_type); END LOOP; END; / === variable jobno number declare jobno number; BEGIN dbms_job.submit(:jobno, 'begin username.analyze_tables; end;', to_date('03jun0304:00','DDMONYYHH24:MI'), 'trunc(sysdate)+(1+(4/24))'); END; === The above will run a COMPUTE analyze on all schemas, except SYS and SYSTEM, at 4:00am every day. Modify it to your own needs, but it should give you a starting point.. I would also recommend using DBMS_STATS to generate your statistics. Have fun! ;0) Mark --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Schedule Analyze using DBMS_STATS ???
Thanks Mark - That is exactly what I was just getting ready to write! (B (BDennis Williams (BDBA, 80%OCP, 100% DBA (BLifetouch, Inc. (B[EMAIL PROTECTED] (B (B (B-Original Message- (BSent: Tuesday, June 03, 2003 8:02 AM (BTo: Multiple recipients of list ORACLE-L (B (B (BPrem, (B (BUse DBMS_JOBS to run the analyze: (B (B=== (B (Bundef username (Bundef password (Bgrant analyze any to username (B/ (B (Bconnect username/password (B (BCREATE or REPLACE PROCEDURE analyze_tables ( (B v_stat_type IN VARCHAR2 := 'COMPUTE') AS (B CURSOR c IS (B SELECT DISTINCT owner (B FROM all_tables (B WHERE owner not in ('SYS','SYSTEM'); (B BEGIN (B FOR any_row IN c LOOP (Bdbms_utility.analyze_schema( (B any_row.owner,v_stat_type); (B END LOOP; (B END; (B/ (B (B=== (B (Bvariable jobno number (Bdeclare jobno number; (B BEGIN (B dbms_job.submit(:jobno, (B'begin username.analyze_tables; end;', (Bto_date('03jun0304:00','DDMONYYHH24:MI'), (B'trunc(sysdate)+(1+(4/24))'); (B END; (B (B=== (B (BThe above will run a COMPUTE analyze on all schemas, except SYS and SYSTEM, (Bat 4:00am every day. Modify it to your own needs, but it should give you a (Bstarting point.. (B (BI would also recommend using DBMS_STATS to generate your statistics. (B (BHave fun! ;0) (B (BMark (B (B=== (B Mark Leith | T: +44 (0)1905 330 281 (B Sales Marketing | F: +44 (0)870 127 5283 (B Cool Tools UK Ltd | E: [EMAIL PROTECTED] (B=== (B http://www.cool-tools.co.uk (B Maximising throughput performance (B (B-Original Message- (BSent: 03 June 2003 12:55 (BTo: Multiple recipients of list ORACLE-L (B (B (BGuys, (B (BI would like to scedule the process of analyzing tables/indexes (Busing DBMS_STATS ? (B (BHope someone of u would have a script for the same. (Bcan u share with me please ?! (B (BBTW,Which is advisable : ANALYZE or DBMS_STATS ? (Bthere was a discussion about the same on the list also. (Bbut not found any conclusion yet. (Banu suggestions !!! (B (Bthe ENV is oracle 9.2.0.1/Win2K. (B (B (B (B (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Prem Khanna J (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing). (B (B--- (BIncoming mail is certified Virus Free. (BChecked by AVG anti-virus system (http://www.grisoft.com). (BVersion: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 (B (B--- (BOutgoing mail is certified Virus Free. (BChecked by AVG anti-virus system (http://www.grisoft.com). (BVersion: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 (B (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Mark Leith (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing). (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: DENNIS WILLIAMS (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing).
RE: RE: Schedule Analyze using DBMS_STATS ???
JP, After you've initially created statistics and set the tables into monitoring mode this script will create a DBMS_JOB that keeps them up to date each morning: set serverout on size 10 prompt Connecting to SYS connect sys@_dbname grant select on dba_tab_modifications to system; grant analyze any to system; prompt Connecting as System @connect system@_dbname create procedure compute_daily_stats is begin for a in (select distinct table_owner from dba_tab_modifications) loop DBMS_STATS.GATHER_SCHEMA_STATS(a.table_owner, NULL,FALSE, 'FOR ALL indexed COLUMNS SIZE 1', NULL,'DEFAULT',TRUE,NULL,NULL,'GATHER STALE','DEFAULT'); end loop; end; / declare jb integer; begin dbms_job.submit(jb, 'compute_daily_stats;',trunc(sysdate+1)+(6/24),'trunc(sysdate+1)+(6/24)',FALSE); dbms_output.put_line('Job is '||jb); end; / Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, June 03, 2003 11:25 AM To: Multiple recipients of list ORACLE-L Sorry JP, I should have clarified that DBMS_STATS is the way to go, when on a 9i or above release. One of the simple factors being that ANALYZE is being deprecated.. There seemed to be a number of bugs/quirks, whatever you want to call them, in certain 8.1.x versions, which are now fixed within 9i.. There's a number of threads about the issues, available in the list archives (you can find them at http://www.faqchest.com), which I dug around - as I remembered seeing a post by Connor McDonald, where he gave away an alternate script to gather stats, here's the mail: http://www.faqchest.com/prgm/oracle-l/ora-02/ora-0210/ora-021011/ora02100802 _06150.html I think the general consensus is that DBMS_STATS is quicker. I've never personally done any comparative benchmarks to corroborate this sheep following attitude though.. ;) Regards Mark -Original Message- Sent: 03 June 2003 14:55 To: Mark Leith Cc: [EMAIL PROTECTED] Thanx a lot Mark. Sure , your code has given me a starting point. let me change accordingly to use DBMS_STATSe and give a try :-) So,can i conclude that DBMS_STATS is better than ANALYZE ?! Someone over the list mentioned that DBMS_STATS is slower. is it so ? What is your opinion regarding this ?! just curious to know ! Regards, Jp. 3-6-2003 22:01:32, Mark Leith [EMAIL PROTECTED] wrote: Prem, Use DBMS_JOBS to run the analyze: === undef username undef password grant analyze any to username / connect username/password CREATE or REPLACE PROCEDURE analyze_tables ( v_stat_type IN VARCHAR2 := 'COMPUTE') AS CURSOR c IS SELECT DISTINCT owner FROM all_tables WHERE owner not in ('SYS','SYSTEM'); BEGIN FOR any_row IN c LOOP dbms_utility.analyze_schema( any_row.owner,v_stat_type); END LOOP; END; / === variable jobno number declare jobno number; BEGIN dbms_job.submit(:jobno, 'begin username.analyze_tables; end;', to_date('03jun0304:00','DDMONYYHH24:MI'), 'trunc(sysdate)+(1+(4/24))'); END; === The above will run a COMPUTE analyze on all schemas, except SYS and SYSTEM, at 4:00am every day. Modify it to your own needs, but it should give you a starting point.. I would also recommend using DBMS_STATS to generate your statistics. Have fun! ;0) Mark --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling
RE: Schedule Analyze using DBMS_STATS ???
This may be a little over-the-top for your needs, but it works for us. We have a standard DBA-level user that we create in every instance. This is a stored procedure within that instance. We schedule it using DBMS_JOB. Typically, we don't analyze every schema every time. Usage patterns vary widely, so I wrote this to be schema-specific. It also creates a log file in the /tmp directory, and sends output to the session with DBMS_OUTPUT. You should be able to Window-icize this procedure - it's written for UNIX. There is an INSERT statement near the bottom, into a table called SEND_EMAIL. This is a table that uses a trigger to send the attached e-mail message. You can take this out if you don't have a similar mechanism. HTH, Mike create or replace procedure gdt_analyze_schema ( p_schema_name IN VARCHAR2 ) AS /* * * * Name: GDT_ANALYZE_SCHEMA * * Author : M. Vergara * * Date: 19-Sept-2002* * Purpose : To compute fresh statistics for a specific schema. The * * notion here is that this procedure will be executed from* * the DBMS_JOB processor. * * * * Change Log: * * Chg# Date Description * * --- -* * * */ -- v_fh UTL_FILE.file_type; v_sdate DATE; v_edate DATE; v_host_name V$INSTANCE.host_name%TYPE; v_logfile_name VARCHAR2(64); /* Error handling variables */ v_error_code NUMBER; v_error_message VARCHAR2( 200 ); -- BEGIN DBMS_OUTPUT.enable( 20 ); DBMS_OUTPUT.put_line( p_schema_name || ' Analysis started...' ); -- SELECT host_name INTO v_host_name FROM v$instance; -- v_sdate:= SYSDATE; v_logfile_name := 'Analyze_' || p_schema_name; v_fh := UTL_FILE.fopen( '/tmp', v_logfile_name, 'a' ); UTL_FILE.put_line( v_fh, '' ); UTL_FILE.put_line( v_fh, p_schema_name || ' Analysis started at ' || TO_CHAR( v_sdate, 'DD-MON- HH24:MI:SS' )); UTL_FILE.fflush( v_fh ); -- UTL_FILE.put_line( v_fh, 'Compute Current Statistics' ); UTL_FILE.fflush( v_fh ); DBMS_UTILITY.analyze_schema( schema = p_schema_name, method = 'COMPUTE' ); -- UTL_FILE.put_line( v_fh, 'Compute Statistics on Indexed Columns' ); UTL_FILE.fflush( v_fh ); DBMS_UTILITY.analyze_schema( schema = p_schema_name, method = 'COMPUTE', method_opt = 'FOR ALL INDEXED COLUMNS' ); -- v_edate := SYSDATE; UTL_FILE.put_line( v_fh, 'Analyze Complete at ' || TO_CHAR( v_edate, 'DD-MON- HH24:MI:SS' )); UTL_FILE.fflush( v_fh ); -- INSERT INTO send_email( msg_to, msg_from, msg_subj, msg_text ) VALUES( 'mvergara', 'dbamon', p_schema_name|| ' Analysis on ' || v_host_name, 'Analyze started at ' || TO_CHAR( v_sdate, 'DD-MON- HH24:MI:SS' ) || ' and completed at ' || TO_CHAR( v_edate, 'DD-MON- HH24:MI:SS' ) || '. Check /tmp/' || v_logfile_name || ' on ' || v_host_name || ' for details.' ); COMMIT; -- UTL_FILE.put_line( v_fh, 'Mail Sent and work committed.' ); UTL_FILE.fflush( v_fh ); UTL_FILE.fclose( v_fh ); -- EXCEPTION WHEN OTHERS THEN v_error_code:= SQLCODE; v_error_message := SUBSTR( SQLERRM, 1, 200 ); ROLLBACK; UTL_FILE.put_line( v_fh, 'Exception Exit.' ); UTL_FILE.put_line( v_fh, 'ERROR! (' || v_error_code || ')' ); UTL_FILE.put_line( v_fh, 'ERROR : ' || v_error_message ); UTL_FILE.fflush( v_fh ); UTL_FILE.fclose( v_fh ); DBMS_OUTPUT.put_line( 'Exception Exit' ); DBMS_OUTPUT.put_line( 'ERROR! (' || v_error_code || ')' ); DBMS_OUTPUT.put_line( 'ERROR : ' || v_error_message ); END; / show errors -Original Message- Sent: Tuesday, June 03, 2003 4:55 AM To: Multiple recipients of list ORACLE-L Guys, I would like to scedule the process of analyzing tables/indexes using DBMS_STATS ? Hope someone of u would have a script for the same. can u share with me please ?! BTW,Which is advisable : ANALYZE or DBMS_STATS ? there was a discussion about the same on the list also. but not found any conclusion yet. anu suggestions !!! the ENV is oracle 9.2.0.1/Win2K. -- Please
RE: RE: Schedule Analyze using DBMS_STATS ???
DBMS_STATS is quicker because it can run in parallel mode. Analyze cannot. (B (B-Original Message- (BSent: Tuesday, June 03, 2003 11:25 AM (BTo: Multiple recipients of list ORACLE-L (B (B (BSorry JP, I should have clarified that DBMS_STATS is the way to go, when on (Ba 9i or above release. One of the simple factors being that ANALYZE is being (Bdeprecated.. There seemed to be a number of bugs/quirks, whatever you want (Bto call them, in certain 8.1.x versions, which are now fixed within 9i.. (B (BThere's a number of threads about the issues, available in the list archives (B(you can find them at http://www.faqchest.com), which I dug around - as I (Bremembered seeing a post by Connor McDonald, where he gave away an alternate (Bscript to gather stats, here's the mail: (B (Bhttp://www.faqchest.com/prgm/oracle-l/ora-02/ora-0210/ora-021011/ora02100802 (B_06150.html (B (BI think the general consensus is that DBMS_STATS is quicker. I've never (Bpersonally done any comparative benchmarks to corroborate this sheep (Bfollowing attitude though.. ;) (B (BRegards (B (BMark (B (B-Original Message- (BSent: 03 June 2003 14:55 (BTo: Mark Leith (BCc: [EMAIL PROTECTED] (B (B (BThanx a lot Mark. (BSure , your code has given me a starting point. (Blet me change accordingly to use DBMS_STATSe and give a try :-) (B (BSo,can i conclude that DBMS_STATS is better than ANALYZE ?! (B (BSomeone over the list mentioned that DBMS_STATS is slower. (Bis it so ? What is your opinion regarding this ?! (Bjust curious to know ! (B (BRegards, (BJp. (B (B (B3-6-2003 22:01:32, "Mark Leith" [EMAIL PROTECTED] wrote: (B (BPrem, (B (BUse DBMS_JOBS to run the analyze: (B (B=== (B (Bundef username (Bundef password (Bgrant analyze any to username (B/ (B (Bconnect username/password (B (BCREATE or REPLACE PROCEDURE analyze_tables ( (B v_stat_type IN VARCHAR2 := 'COMPUTE') AS (B CURSOR c IS (B SELECT DISTINCT owner (B FROM all_tables (B WHERE owner not in ('SYS','SYSTEM'); (B BEGIN (B FOR any_row IN c LOOP (Bdbms_utility.analyze_schema( (B any_row.owner,v_stat_type); (B END LOOP; (B END; (B/ (B (B=== (B (Bvariable jobno number (Bdeclare jobno number; (B BEGIN (B dbms_job.submit(:jobno, (B'begin username.analyze_tables; end;', (Bto_date('03jun0304:00','DDMONYYHH24:MI'), (B'trunc(sysdate)+(1+(4/24))'); (B END; (B (B=== (B (BThe above will run a COMPUTE analyze on all schemas, except SYS and (BSYSTEM, (Bat 4:00am every day. Modify it to your own needs, but it should give (Byou a (Bstarting point.. (B (BI would also recommend using DBMS_STATS to generate your statistics. (B (BHave fun! ;0) (B (BMark (B (B (B (B--- (BIncoming mail is certified Virus Free. (BChecked by AVG anti-virus system (http://www.grisoft.com). (BVersion: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 (B (B--- (BOutgoing mail is certified Virus Free. (BChecked by AVG anti-virus system (http://www.grisoft.com). (BVersion: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 (B (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Mark Leith (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing). (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Mercadante, Thomas F (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing).
RE: Schedule Analyze using DBMS_STATS ???
Title: RE: Schedule Analyze using DBMS_STATS ??? (B (B (B (B (BWe don't analyze schema, we use dbms_stats ... (B (B (BI wrote a package that analyzes everything using dbms_stats and (for my instance) it runs in 10 parallel streams. It splits all the tables in 10 streams so that all of them take approximately same amount of time. (B (BThis package captures the time it takes to analyze each table, everyday and balances the table among 10 parallel streams. (B (BI prefer to use cron, but you can use it with dbms_job as well. (B (B (BCurrently it does only tables (and cascades to indexes automatically). We don't deal with partitions right now, so that needs to be added. After a certain number of rows, it collects stats in parallel too. I am currently working on interfacing it with dba_tab_modifications ... so the logic will decide if the table changes are say 5%, skip the table from stats collections. (B (BRaj (B (BRajendra dot Jamadagni at nospamespn dot com (BAll Views expressed in this email are strictly personal. (BQOTD: Any clod can have facts, having an opinion is an art ! (B (B (B (B-Original Message- (BFrom: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] (BSent: Tuesday, June 03, 2003 11:05 AM (BTo: Multiple recipients of list ORACLE-L (BSubject: RE: Schedule Analyze using DBMS_STATS ??? (B (B (B (BThanks Mark - That is exactly what I was just getting ready to write! (B (B (BDennis Williams (BDBA, 80%OCP, 100% DBA (BLifetouch, Inc. (B[EMAIL PROTECTED] (B (B (B (BThis e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Schedule Analyze using DBMS_STATS ???
Title: RE: Schedule Analyze using DBMS_STATS ??? (B (B (B (B (BThe sample output looks like ... (B (B (B[EMAIL PROTECTED] sys (BSQL*Plus: Release 9.2.0.2.0 - Production on Tue Jun 3 11:57:51 2003 (BCopyright (c) 1982, 2002, Oracle Corporation. All rights reserved. (BConnected. (B11:57:51 SQL select * from system.v_analysis_info; (B (B (BAnalysis Information (B- (BGroup 01 includes 2490 tables, analysis should take approx 01574.93 seconds. (BGroup 02 includes 65 tables, analysis should take approx 01579.41 seconds. (BGroup 03 includes 26 tables, analysis should take approx 01578.01 seconds. (BGroup 04 includes 16 tables, analysis should take approx 01586.48 seconds. (BGroup 05 includes 9 tables, analysis should take approx 01472.85 seconds. (BGroup 06 includes 5 tables, analysis should take approx 01594.39 seconds. (BGroup 07 includes 2 tables, analysis should take approx 01550.56 seconds. (BGroup 08 includes 1 tables, analysis should take approx 01169.40 seconds. (BGroup 09 includes 1 tables, analysis should take approx 01749.20 seconds. (BGroup 10 includes 1 tables, analysis should take approx 01991.25 seconds. (B10 rows selected. (B (B (BRaj (B (BRajendra dot Jamadagni at nospamespn dot com (BAll Views expressed in this email are strictly personal. (BQOTD: Any clod can have facts, having an opinion is an art ! (B (B (B (B-Original Message- (BFrom: Jamadagni, Rajendra (BSent: Tuesday, June 03, 2003 12:02 PM (BTo: '[EMAIL PROTECTED]' (BSubject: RE: Schedule Analyze using DBMS_STATS ??? (B (B (B (BWe don't analyze schema, we use dbms_stats ... (B (B (BI wrote a package that analyzes everything using dbms_stats and (for my instance) it runs in 10 parallel streams. It splits all the tables in 10 streams so that all of them take approximately same amount of time. (B (BThis package captures the time it takes to analyze each table, everyday and balances the table among 10 parallel streams. (B (BI prefer to use cron, but you can use it with dbms_job as well. (B (B (BCurrently it does only tables (and cascades to indexes automatically). We don't deal with partitions right now, so that needs to be added. After a certain number of rows, it collects stats in parallel too. I am currently working on interfacing it with dba_tab_modifications ... so the logic will decide if the table changes are say 5%, skip the table from stats collections. (B (BRaj (B (BRajendra dot Jamadagni at nospamespn dot com (BAll Views expressed in this email are strictly personal. (BQOTD: Any clod can have facts, having an opinion is an art ! (B (B (B (BThis e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Schedule Analyze using DBMS_STATS ???
With some versions, dbms_stats has issues with partitions. Since we use partitioning, we wrote a script to simulate the gather stale functionality. Monitoring is enabled and we do a compute of any table or partition thereof that has more than x% modified. This is essentially what the gather stale command does, but our script has the benefit that we can adjust the percantage if we feel it is necessary. Though in about 1 1/2 years, we haven't felt it neccessary to adjust that number. We choose to do a full COMPUTE since each partition is relatively small and the whole analyze_stale runs well within our maintenance window. We are close to being mostly on 9i and will be looking at dbms_stats to see if we can start using it. Stephen [EMAIL PROTECTED] 06/03/03 09:59AM We don't analyze schema, we use dbms_stats ... I wrote a package that analyzes everything using dbms_stats and (for my instance) it runs in 10 parallel streams. It splits all the tables in 10 streams so that all of them take approximately same amount of time. This package captures the time it takes to analyze each table, everyday and balances the table among 10 parallel streams. I prefer to use cron, but you can use it with dbms_job as well. Currently it does only tables (and cascades to indexes automatically). We don't deal with partitions right now, so that needs to be added. After a certain number of rows, it collects stats in parallel too. I am currently working on interfacing it with dba_tab_modifications ... so the logic will decide if the table changes are say 5%, skip the table from stats collections. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, June 03, 2003 11:05 AM To: Multiple recipients of list ORACLE-L Thanks Mark - That is exactly what I was just getting ready to write! Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Andert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: RE: Schedule Analyze using DBMS_STATS ???
Hi all, Everyone is recommending DBMS_STATS to be used for computing the statistics. But even after specifying the parallel option , DBMS_STATS is taking lots of time ! in comparison to Analyze... In case of tables with a million records the query just hangs when i use the stats package... Can anybody tell me whts happening ? Regards kesh -Original Message- Sent: Tuesday, June 03, 2003 8:55 PM To: Multiple recipients of list ORACLE-L Sorry JP, I should have clarified that DBMS_STATS is the way to go, when on a 9i or above release. One of the simple factors being that ANALYZE is being deprecated.. There seemed to be a number of bugs/quirks, whatever you want to call them, in certain 8.1.x versions, which are now fixed within 9i.. There's a number of threads about the issues, available in the list archives (you can find them at http://www.faqchest.com), which I dug around - as I remembered seeing a post by Connor McDonald, where he gave away an alternate script to gather stats, here's the mail: http://www.faqchest.com/prgm/oracle-l/ora-02/ora-0210/ora-021011/ora02100802 _06150.html I think the general consensus is that DBMS_STATS is quicker. I've never personally done any comparative benchmarks to corroborate this sheep following attitude though.. ;) Regards Mark -Original Message- Sent: 03 June 2003 14:55 To: Mark Leith Cc: [EMAIL PROTECTED] Thanx a lot Mark. Sure , your code has given me a starting point. let me change accordingly to use DBMS_STATSe and give a try :-) So,can i conclude that DBMS_STATS is better than ANALYZE ?! Someone over the list mentioned that DBMS_STATS is slower. is it so ? What is your opinion regarding this ?! just curious to know ! Regards, Jp. 3-6-2003 22:01:32, Mark Leith [EMAIL PROTECTED] wrote: Prem, Use DBMS_JOBS to run the analyze: === undef username undef password grant analyze any to username / connect username/password CREATE or REPLACE PROCEDURE analyze_tables ( v_stat_type IN VARCHAR2 := 'COMPUTE') AS CURSOR c IS SELECT DISTINCT owner FROM all_tables WHERE owner not in ('SYS','SYSTEM'); BEGIN FOR any_row IN c LOOP dbms_utility.analyze_schema( any_row.owner,v_stat_type); END LOOP; END; / === variable jobno number declare jobno number; BEGIN dbms_job.submit(:jobno, 'begin username.analyze_tables; end;', to_date('03jun0304:00','DDMONYYHH24:MI'), 'trunc(sysdate)+(1+(4/24))'); END; === The above will run a COMPUTE analyze on all schemas, except SYS and SYSTEM, at 4:00am every day. Modify it to your own needs, but it should give you a starting point.. I would also recommend using DBMS_STATS to generate your statistics. Have fun! ;0) Mark --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
Schedule Analyze using DBMS_STATS ???
Guys, I would like to scedule the process of analyzing tables/indexes using DBMS_STATS ? Hope someone of u would have a script for the same. can u share with me please ?! BTW,Which is advisable : ANALYZE or DBMS_STATS ? there was a discussion about the same on the list also. but not found any conclusion yet. anu suggestions !!! the ENV is oracle 9.2.0.1/Win2K. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 broken
Interesting, I use dbms_stats with gather stale in 8.1.7.4 it behaves beautifully. Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 11:15 PMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_stats broken Hmmm ... I haven't tried with stale option, but gather-schema works. I know .. I know... this isnot the answer you were looking for ... Raj -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 10:25 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_stats broken Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey.
Re: dbms_stats broken
Koivu, Lisa wrote: Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? Im trying to use dbms_stats gather schema stats with the stale option and it just isnt working in 8.1.7.4. This is documented on Metalink. Id love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. I run this sql out of a python program against a 9.2.0.2.0 database on a Compaq Tru64 server. Works like a charm. The 'gather stale' option works as well. sql = BEGIN DBMS_STATS.GATHER_SCHEMA_STATS( ownname = '%s', options = 'GATHER AUTO', estimate_percent = dbms_stats.auto_sample_size, method_opt = 'for all columns size auto', cascade = true); END; % schema --Glenn Stauffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Glenn Stauffer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 broken
Hey Lisa (Monkey?), Yes, I had a problem with the stale option, too, at least under 8.1.6. It would bomb out with no more rows or something like that. Under 8.1.7.4 on Solaris, I no longer had that problem but I did have some poorer explain plans than with good ol' ANALYZE. See the Burned by DBMS_STATS **AGAIN** thread starting on 04/07/2003. Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Wednesday, May 28, 2003 9:25 PM To: Multiple recipients of list ORACLE-L Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 broken
Darrell, I put dbms_stats into production, on 8.1.7.4, over two months ago have not had a problem. Here is what I did to compute the stats automatically. (BTW: this runs under system) create procedure compute_daily_stats is begin for a in (select distinct table_owner from dba_tab_modifications) loop DBMS_STATS.GATHER_SCHEMA_STATS(a.table_owner, NULL,FALSE, 'FOR ALL indexed COLUMNS SIZE 1', NULL,'DEFAULT',TRUE,NULL,NULL,'GATHER STALE','DEFAULT'); end loop; end; / declare jb integer; begin dbms_job.submit(jb, 'compute_daily_stats;',trunc(sysdate+1)+(6/24),'trunc(sysdate+1)+(6/24)',FALSE); dbms_output.put_line('Job is '||jb); end; / Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 12:30 AM To: Multiple recipients of list ORACLE-L Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 broken
I haven't used it, but here is what we are doing: we created a simple package which loops through dba_tab_modifications and finds itself (the same way as oracle would) which tables are stale: where the sum of inserts, updates and deletes is more than 10% of the number of rows, and than run dbms_stats for those only. That way you have a bit more flexibility (you can for example exclude some tables), makeestimate or calculate decisions on the table basis, etc. Djordje -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 10:25 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_stats broken Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? Im trying to use dbms_stats gather schema stats with the stale option and it just isnt working in 8.1.7.4. This is documented on Metalink. Id love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey.
RE: exp, dbms_stats, RMAN and rollback segments
Oh yeah, for the export consistent=N -Original Message- Sent: Thursday, May 29, 2003 9:14 AM To: '[EMAIL PROTECTED]' A certain alignment of the planets occurred creating a good ole ORA-01555 error... A user level export received the snapshot too old error and terminated. Concurrent to this was an RMAN backup and DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on the same schema being backed up via the user level export. There was no other end user access to the schema data. Since exp got the error I assume it was reading from the rollback segments but why? I'm suspecting dbms_stats. We have ample RBS. Is there any significant undo generated by dbms_stats or RMAN which could create this problem? (Of course we need to improve our job scheduling but that's another issue, the timing of the user level export is application driven and out of our control). Befuddled in Bozeman, Walt and Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 broken
Lisa, I'm still using Analyze so I have no direct experience with this package. That being said, would the SET_TABLE_STATS work for the partitions that haven't been loaded yet? Mike Hand Polaroid Corp. -Original Message- Sent: Thursday, May 29, 2003 10:46 AM To: Multiple recipients of list ORACLE-L Hi Darrell Kirti - It was late last night when I was looking at this. It seems I should be using GATHER EMPTY instead of GATHER STALE. However this will put statistics on partitions with no rows. When I load new partitions tomorrow (they are empty prior to the daily load), the number of rows inserted wouldn't reach 10% change. It would take over a week to reach 10% change in order for GATHER STALE to pick up on this and re-analyze these partitions. I don't want statistics saying there are 0 records in a partition that is indeed loaded. I guess GATHER STALE would be much more useful if the 10% threshold could be modified, and/or the threshold could be partition specific. And Kirti, the bugs I have seen are 1192012, 1890016, 2157655. I thought I was running into 1890016. Looks like I'll have to code around this after all. Darn it all. I really am a monkey. Sheesh Lisa -Original Message- Sent: Thursday, May 29, 2003 12:30 AM To: Multiple recipients of list ORACLE-L Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 broken
Hi Darrell Kirti - It was late last night when I was looking at this. It seems I should be using GATHER EMPTY instead of GATHER STALE. However this will put statistics on partitions with no rows. When I load new partitions tomorrow (they are empty prior to the daily load), the number of rows inserted wouldn't reach 10% change. It would take over a week to reach 10% change in order for GATHER STALE to pick up on this and re-analyze these partitions. I don't want statistics saying there are 0 records in a partition that is indeed loaded. I guess GATHER STALE would be much more useful if the 10% threshold could be modified, and/or the threshold could be partition specific. And Kirti, the bugs I have seen are 1192012, 1890016, 2157655. I thought I was running into 1890016. Looks like I'll have to code around this after all. Darn it all. I really am a monkey. Sheesh Lisa -Original Message- Sent: Thursday, May 29, 2003 12:30 AM To: Multiple recipients of list ORACLE-L Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 broken
Lisa et al, Am I missing something or did someone ever mention that for STALE to work, one needs to set MONITORING on required objects? Straight from the Fine Manual: Enabling Automated Statistics Gathering The GATHER STALE option only gathers statistics for tables that have stale statistics and for which you have enabled the MONITORING attribute. To enable monitoring for tables, use the MONITORING keyword of the CREATE TABLE and ALTER TABLE statements, as described in Designating Tables for Monitoring and Automated Statistics Gathering on page 8-9. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Great, uplifting music (and best of all commercial-free!) - http://www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Darrell Landrum [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 9:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: dbms_stats broken Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: exp, dbms_stats, RMAN and rollback segments
HELP... Has anyone encountered rollback problems while running dbms_stats? -Original Message- Sent: Thursday, May 29, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Oh yeah, for the export consistent=N -Original Message- Sent: Thursday, May 29, 2003 9:14 AM To: '[EMAIL PROTECTED]' A certain alignment of the planets occurred creating a good ole ORA-01555 error... A user level export received the snapshot too old error and terminated. Concurrent to this was an RMAN backup and DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on the same schema being backed up via the user level export. There was no other end user access to the schema data. Since exp got the error I assume it was reading from the rollback segments but why? I'm suspecting dbms_stats. We have ample RBS. Is there any significant undo generated by dbms_stats or RMAN which could create this problem? (Of course we need to improve our job scheduling but that's another issue, the timing of the user level export is application driven and out of our control). Befuddled in Bozeman, Walt and Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
Hi John, Yes, monitoring was set. I wouldn't see anything in *tab_modifications if monitoring wasn't set. Here's a new twist. What percentage are you comfortable with for valid estimates? I attended a seminar given by Jonathan Lewis a few weeks ago, and he stated that adequate statistics can be gathered using 1% sample. That was great news to me, who has time for huge estimates? OK, so I ran dbms_stats.gather_schema_stats using GATHER EMPTY and estimate_percent set to 1. The rowcounts are way wrong. (FACP-LISA)SELECT COUNT(*) 2 FROM VEGAS_MART 3 PARTITION (MAY_28_2003); COUNT(*) -- 603826 (FACP-LISA)select num_rows 2 from dba_tab_partitions 3 where table_name = 'VEGAS_MART' 4 and partition_name = 'MAY_28_2003'; NUM_ROWS -- 595500 (FACP-LISA) And Jonathan if you happen to read this email, if I am mis-stating what you stated in class please correct me. I am on 8.1.7.4 and that may be the difference. -Original Message- Sent: Thursday, May 29, 2003 1:36 PM To: Multiple recipients of list ORACLE-L Lisa et al, Am I missing something or did someone ever mention that for STALE to work, one needs to set MONITORING on required objects? Straight from the Fine Manual: Enabling Automated Statistics Gathering The GATHER STALE option only gathers statistics for tables that have stale statistics and for which you have enabled the MONITORING attribute. To enable monitoring for tables, use the MONITORING keyword of the CREATE TABLE and ALTER TABLE statements, as described in Designating Tables for Monitoring and Automated Statistics Gathering on page 8-9. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Great, uplifting music (and best of all commercial-free!) - http://www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Darrell Landrum [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 9:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: dbms_stats broken Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: dbms_stats broken
Hi Mike, Yes, I could use it... but the GATHER EMPTY option set stats on the empty partitions correctly. I'm more concerned about having old stats on a partition that is now loaded. Thanks -Original Message- Sent: Thursday, May 29, 2003 12:30 PM To: Multiple recipients of list ORACLE-L Lisa, I'm still using Analyze so I have no direct experience with this package. That being said, would the SET_TABLE_STATS work for the partitions that haven't been loaded yet? Mike Hand Polaroid Corp. -Original Message- Sent: Thursday, May 29, 2003 10:46 AM To: Multiple recipients of list ORACLE-L Hi Darrell Kirti - It was late last night when I was looking at this. It seems I should be using GATHER EMPTY instead of GATHER STALE. However this will put statistics on partitions with no rows. When I load new partitions tomorrow (they are empty prior to the daily load), the number of rows inserted wouldn't reach 10% change. It would take over a week to reach 10% change in order for GATHER STALE to pick up on this and re-analyze these partitions. I don't want statistics saying there are 0 records in a partition that is indeed loaded. I guess GATHER STALE would be much more useful if the 10% threshold could be modified, and/or the threshold could be partition specific. And Kirti, the bugs I have seen are 1192012, 1890016, 2157655. I thought I was running into 1890016. Looks like I'll have to code around this after all. Darn it all. I really am a monkey. Sheesh Lisa -Original Message- Sent: Thursday, May 29, 2003 12:30 AM To: Multiple recipients of list ORACLE-L Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: exp, dbms_stats, RMAN and rollback segments
A Large Load or Update Before the Exp ... Delyed Block Clean out is one Reason that really pops out. HTH Best Regards, Ganesh R DID : +65-6215-8413 HP : +65-9067-8474 === Live to learn... forget... and learn again. === -Original Message- Sent: Friday, May 30, 2003 12:15 AM To: Multiple recipients of list ORACLE-L A certain alignment of the planets occurred creating a good ole ORA-01555 error... A user level export received the snapshot too old error and terminated. Concurrent to this was an RMAN backup and DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on the same schema being backed up via the user level export. There was no other end user access to the schema data. Since exp got the error I assume it was reading from the rollback segments but why? I'm suspecting dbms_stats. We have ample RBS. Is there any significant undo generated by dbms_stats or RMAN which could create this problem? (Of course we need to improve our job scheduling but that's another issue, the timing of the user level export is application driven and out of our control). Befuddled in Bozeman, Walt and Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Ganesh Raja INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: exp, dbms_stats, RMAN and rollback segments
Steve, You may have to dig a little further... What happened to those table(s) in that schema prior to starting the export? Heavy DML, may be? This could be a case of 'delayed block cleanout'. Export triggered the cleanout and wanted to access the rollback segments. If no table data was modified after export started reading that table, then there is no need to read RBS info (except for the DBC case, IMO). - Kirti --- Orr, Steve [EMAIL PROTECTED] wrote: Oh yeah, for the export consistent=N -Original Message- Sent: Thursday, May 29, 2003 9:14 AM To: '[EMAIL PROTECTED]' A certain alignment of the planets occurred creating a good ole ORA-01555 error... A user level export received the snapshot too old error and terminated. Concurrent to this was an RMAN backup and DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on the same schema being backed up via the user level export. There was no other end user access to the schema data. Since exp got the error I assume it was reading from the rollback segments but why? I'm suspecting dbms_stats. We have ample RBS. Is there any significant undo generated by dbms_stats or RMAN which could create this problem? (Of course we need to improve our job scheduling but that's another issue, the timing of the user level export is application driven and out of our control). Befuddled in Bozeman, Walt and Steve __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
I wouldn't call 1.4% [ (603826-595500)/603826 ] way wrong. Actually, for a 1% sample I find that pretty good. The problem I found with low sampling percentages is if you have skewed column values. If some values occur very often and others rather seldom, a 1% sample may only encounter the frequently occurring values and none of the infrequently occurring ones and come up with a really way off estimate for num_distinct. At 10:05 AM 5/29/2003 -0800, you wrote: Hi John, Yes, monitoring was set. I wouldn't see anything in *tab_modifications if monitoring wasn't set. Here's a new twist. What percentage are you comfortable with for valid estimates? I attended a seminar given by Jonathan Lewis a few weeks ago, and he stated that adequate statistics can be gathered using 1% sample. That was great news to me, who has time for huge estimates? OK, so I ran dbms_stats.gather_schema_stats using GATHER EMPTY and estimate_percent set to 1. The rowcounts are way wrong. (FACP-LISA)SELECT COUNT(*) 2 FROM VEGAS_MART 3 PARTITION (MAY_28_2003); COUNT(*) -- 603826 (FACP-LISA)select num_rows 2 from dba_tab_partitions 3 where table_name = 'VEGAS_MART' 4 and partition_name = 'MAY_28_2003'; NUM_ROWS -- 595500 (FACP-LISA) And Jonathan if you happen to read this email, if I am mis-stating what you stated in class please correct me. I am on 8.1.7.4 and that may be the difference. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
Lisa, Have a look at ML Note:44961.1 (and maybe Note:114671.1 could also help). The 1% sample may work for larger tables - I am not sure John -Original Message- From: Koivu, Lisa [mailto:[EMAIL PROTECTED] Sent: Thursday, May 29, 2003 11:05 AM To: Multiple recipients of list ORACLE-L Subject: RE: dbms_stats Hi John, Yes, monitoring was set. I wouldn't see anything in *tab_modifications if monitoring wasn't set. Here's a new twist. What percentage are you comfortable with for valid estimates? I attended a seminar given by Jonathan Lewis a few weeks ago, and he stated that adequate statistics can be gathered using 1% sample. That was great news to me, who has time for huge estimates? OK, so I ran dbms_stats.gather_schema_stats using GATHER EMPTY and estimate_percent set to 1. The rowcounts are way wrong. (FACP-LISA)SELECT COUNT(*) 2 FROM VEGAS_MART 3 PARTITION (MAY_28_2003); COUNT(*) -- 603826 (FACP-LISA)select num_rows 2 from dba_tab_partitions 3 where table_name = 'VEGAS_MART' 4 and partition_name = 'MAY_28_2003'; NUM_ROWS -- 595500 (FACP-LISA) And Jonathan if you happen to read this email, if I am mis-stating what you stated in class please correct me. I am on 8.1.7.4 and that may be the difference. -Original Message- Sent: Thursday, May 29, 2003 1:36 PM To: Multiple recipients of list ORACLE-L Lisa et al, Am I missing something or did someone ever mention that for STALE to work, one needs to set MONITORING on required objects? Straight from the Fine Manual: Enabling Automated Statistics Gathering The GATHER STALE option only gathers statistics for tables that have stale statistics and for which you have enabled the MONITORING attribute. To enable monitoring for tables, use the MONITORING keyword of the CREATE TABLE and ALTER TABLE statements, as described in Designating Tables for Monitoring and Automated Statistics Gathering on page 8-9. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Great, uplifting music (and best of all commercial-free!) - http://www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Darrell Landrum [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 9:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: dbms_stats broken Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author
RE: dbms_stats
Hi Wolfgang, In the grand scheme of things, that probably isn't awful. However, if the analyze can't get the row count right (how easy is that?) then how can I trust it to get the rest of the statistics correct? Just my two cents. Thanks for your reply. Lisa -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L I wouldn't call 1.4% [ (603826-595500)/603826 ] way wrong. Actually, for a 1% sample I find that pretty good. The problem I found with low sampling percentages is if you have skewed column values. If some values occur very often and others rather seldom, a 1% sample may only encounter the frequently occurring values and none of the infrequently occurring ones and come up with a really way off estimate for num_distinct. At 10:05 AM 5/29/2003 -0800, you wrote: Hi John, Yes, monitoring was set. I wouldn't see anything in *tab_modifications if monitoring wasn't set. Here's a new twist. What percentage are you comfortable with for valid estimates? I attended a seminar given by Jonathan Lewis a few weeks ago, and he stated that adequate statistics can be gathered using 1% sample. That was great news to me, who has time for huge estimates? OK, so I ran dbms_stats.gather_schema_stats using GATHER EMPTY and estimate_percent set to 1. The rowcounts are way wrong. (FACP-LISA)SELECT COUNT(*) 2 FROM VEGAS_MART 3 PARTITION (MAY_28_2003); COUNT(*) -- 603826 (FACP-LISA)select num_rows 2 from dba_tab_partitions 3 where table_name = 'VEGAS_MART' 4 and partition_name = 'MAY_28_2003'; NUM_ROWS -- 595500 (FACP-LISA) And Jonathan if you happen to read this email, if I am mis-stating what you stated in class please correct me. I am on 8.1.7.4 and that may be the difference. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 broken
I looked at bug 1890016 on MetalClink and I'm confused. Did you run ANALYZE COMPUTE before running DBMS_STATS like the bug's test case? I thought this was a no-no??? If so, I'm wondering if it would it help to ANALYZE DELETE then rerun DBMS_STATS w/EMPTY, insert/update/delete test rows, force an update of DBA_TAB_MONITORING (via SHUTDOWN NORMAL or the new 9i method that escapes me), then try DBMS_STATS w/STALE option. Just a thought... Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Koivu, Lisa [mailto:[EMAIL PROTECTED] Sent: Thursday, May 29, 2003 9:46 AM To: Multiple recipients of list ORACLE-L Subject: RE: dbms_stats broken Hi Darrell Kirti - It was late last night when I was looking at this. It seems I should be using GATHER EMPTY instead of GATHER STALE. However this will put statistics on partitions with no rows. When I load new partitions tomorrow (they are empty prior to the daily load), the number of rows inserted wouldn't reach 10% change. It would take over a week to reach 10% change in order for GATHER STALE to pick up on this and re-analyze these partitions. I don't want statistics saying there are 0 records in a partition that is indeed loaded. I guess GATHER STALE would be much more useful if the 10% threshold could be modified, and/or the threshold could be partition specific. And Kirti, the bugs I have seen are 1192012, 1890016, 2157655. I thought I was running into 1890016. Looks like I'll have to code around this after all. Darn it all. I really am a monkey. Sheesh Lisa -Original Message- Sent: Thursday, May 29, 2003 12:30 AM To: Multiple recipients of list ORACLE-L Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: exp, dbms_stats, RMAN and rollback segments
Hi Kirti, Sounds like you have the same suspicions as do we. So far as we know there isn't any heavy duty DML before the export and there isn't any other activity on the schema other than RMAN and DBMS_STATS. We don't understand how RMAN or DBMS_STATS ***could*** be the culprit but we've seen WEIRDNESS before. We host our webapp and upgrades are customer driven. (Shivers and shudders!) Upgrades often entail database changes so the application shuts out end user access and kicks off an export before changing tables, munging data, updating the code, etc. This process has been fairly automagic and the export went along about 5 minutes before it crapped out. The only known difference between this upgrade run and other successful ones is that dbms_stats was running. So now I've copied the data into our test environment in an effort to duplicate the error and walk through our code. Luckily it's not written in Perl so it won't be too hard to read. :-) Also, Walt found some delayed block cleanout issues on Metalink associated with analyzing indexes. The DBMS_STATS.GATHER_TABLE_STATS(...) routine in question is using the cascade option so I'm beginning to smell smoke. There are a few docs on Metalink about how ORA-01555's can occur even when NO updates are being performed but statistics are being gathered. WEIRDNESS!! See DocID's 367016.995; 17730.996; 45895.1; 89633.996; 61552.1. UNFORTUNATELY... the solutions proposed are not very appealing. Meanwhile we're trying to convince others that Oracle is better than MySQL even though MySQL continuously updates its optimizer statistics and doesn't have problems like this. :-( And proposing a blockout period when customers are not allowed to upgrade while maintenance operations are going on would not be very well received. Especially since it's not an issue with MySQL and how we keep talking about how Oracle is a better 24X7 solution for our 24X7 webapp. Arghh! Big sigh while contemplating a lot of tedious work and getting nowhere... And I had to get up at 2:30 A.M. this morning to help fix the outage. Whine, whine, whine... Steve -Original Message- Sent: Thursday, May 29, 2003 11:51 AM To: [EMAIL PROTECTED] Cc: Orr, Steve Steve, You may have to dig a little further... What happened to those table(s) in that schema prior to starting the export? Heavy DML, may be? This could be a case of 'delayed block cleanout'. Export triggered the cleanout and wanted to access the rollback segments. If no table data was modified after export started reading that table, then there is no need to read RBS info (except for the DBC case, IMO). - Kirti --- Orr, Steve [EMAIL PROTECTED] wrote: Oh yeah, for the export consistent=N -Original Message- Sent: Thursday, May 29, 2003 9:14 AM To: '[EMAIL PROTECTED]' A certain alignment of the planets occurred creating a good ole ORA-01555 error... A user level export received the snapshot too old error and terminated. Concurrent to this was an RMAN backup and DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on the same schema being backed up via the user level export. There was no other end user access to the schema data. Since exp got the error I assume it was reading from the rollback segments but why? I'm suspecting dbms_stats. We have ample RBS. Is there any significant undo generated by dbms_stats or RMAN which could create this problem? (Of course we need to improve our job scheduling but that's another issue, the timing of the user level export is application driven and out of our control). Befuddled in Bozeman, Walt and Steve __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 broken
I haven't read the note yet, but what Rich mentions here may explain why I haven't encountered any problems. I started out testing with copies of prod tables which had no stats at all. So their first stats were gathered with gather_shema_stats. [EMAIL PROTECTED] 05/29/03 03:14PM I looked at bug 1890016 on MetalClink and I'm confused. Did you run ANALYZE COMPUTE before running DBMS_STATS like the bug's test case? I thought this was a no-no??? If so, I'm wondering if it would it help to ANALYZE DELETE then rerun DBMS_STATS w/EMPTY, insert/update/delete test rows, force an update of DBA_TAB_MONITORING (via SHUTDOWN NORMAL or the new 9i method that escapes me), then try DBMS_STATS w/STALE option. Just a thought... Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Koivu, Lisa [mailto:[EMAIL PROTECTED] Sent: Thursday, May 29, 2003 9:46 AM To: Multiple recipients of list ORACLE-L Subject: RE: dbms_stats broken Hi Darrell Kirti - It was late last night when I was looking at this. It seems I should be using GATHER EMPTY instead of GATHER STALE. However this will put statistics on partitions with no rows. When I load new partitions tomorrow (they are empty prior to the daily load), the number of rows inserted wouldn't reach 10% change. It would take over a week to reach 10% change in order for GATHER STALE to pick up on this and re-analyze these partitions. I don't want statistics saying there are 0 records in a partition that is indeed loaded. I guess GATHER STALE would be much more useful if the 10% threshold could be modified, and/or the threshold could be partition specific. And Kirti, the bugs I have seen are 1192012, 1890016, 2157655. I thought I was running into 1890016. Looks like I'll have to code around this after all. Darn it all. I really am a monkey. Sheesh Lisa -Original Message- Sent: Thursday, May 29, 2003 12:30 AM To: Multiple recipients of list ORACLE-L Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: dbms_stats
The answer is as usual it depends. If the table has a reasonably uniform row size and the blocks are approximately evenly utilized, then the analyze can extrapolate the total number of rows fairly accurately even from a small sample. However, if the row size fluctuates wildly, or if the block utilization fluctuates heavily, then you need a larger sample for accurate estimates of the totals. At 12:14 PM 5/29/2003 -0800, you wrote: Hi Wolfgang, In the grand scheme of things, that probably isn't awful. However, if the analyze can't get the row count right (how easy is that?) then how can I trust it to get the rest of the statistics correct? Just my two cents. Thanks for your reply. Lisa Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: exp, dbms_stats, RMAN and rollback segments
When one runs dbms_stats how often is the data committed? Before Oracle exports a table it needs to gather information about it such as the columns comprising it. I wonder if this information also includes data changed by dbms_stats and/or RMAN. We used to have similar problems when exporting while an application was doing quite a few drop tables and CTAS's. However that pretty much went away with 8.1.7. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 29, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Oh yeah, for the export consistent=N -Original Message- Sent: Thursday, May 29, 2003 9:14 AM To: '[EMAIL PROTECTED]' A certain alignment of the planets occurred creating a good ole ORA-01555 error... A user level export received the snapshot too old error and terminated. Concurrent to this was an RMAN backup and DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on the same schema being backed up via the user level export. There was no other end user access to the schema data. Since exp got the error I assume it was reading from the rollback segments but why? I'm suspecting dbms_stats. We have ample RBS. Is there any significant undo generated by dbms_stats or RMAN which could create this problem? (Of course we need to improve our job scheduling but that's another issue, the timing of the user level export is application driven and out of our control). Befuddled in Bozeman, Walt and Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
dbms_stats broken
Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? Im trying to use dbms_stats gather schema stats with the stale option and it just isnt working in 8.1.7.4. This is documented on Metalink. Id love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey.
RE: dbms_stats broken
Hmmm ... I haven't tried with stale option, but gather-schema works. I know .. I know... this isnot the answer you were looking for ... Raj -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 10:25 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_stats broken Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: dbms_stats broken
Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 broken
Hello Lisa Monkey, What platform are you jumping up on? Do you have tables with lots of branches.. er.. partitions? I have been using dbms_stats to collect stale stats on HP-UX 11.0 with a 8.1.7.4 database. These are not partitioned tables. All are being 'monitored', the script to collect stale stats runs every morning as some tables are truncated and reloaded, while others are modified. I get a report of all tables that were found to have 'stale' stats and got analyzed. Here is my today's report : (HRWT is the database, HRW is the schema) df2hptest [HRWT]: more gather_stale_stats_HRWT_HRW.log Connected. Owner HRW : TABLE - ATH_EMPLOYEE Owner HRW : TABLE - CTLR_KEYERS Owner HRW : TABLE - CUR_EMPLOYEES Owner HRW : TABLE - EMERGENCY_CONTACTS Owner HRW : TABLE - EXT_BA_EMPLOYEES Owner HRW : TABLE - EXT_GTE_EMPLOYEES Owner HRW : TABLE - LKP_EMPL_NAME Owner HRW : TABLE - MSS_APPLICATION_DIR Can you please post the Metalink reference about the problem with DBMS_STATS in 8.1.7.4? I know that the global stats (histograms) for partitioned tables is an issue, but would like to know of other problems as well. Thanks. - Kirti --- Koivu, Lisa [EMAIL PROTECTED] wrote: Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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 broken
I don't use it but the other DBA in our group does (gather stale)... and it's working in 9.2.0.1 and 9.2.0.2 --- Koivu, Lisa [EMAIL PROTECTED] wrote: Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Re: DBMS_STATS
Hi, friends: How do you use dbms_stats.gather_schema_stats in OLTP production system? I ever used estimate statistics =20% percent, and some time have serious performance impact while two big table join in my production changed. Later I changed to compute and till now , It is ok. And another question about CBO stability: Do you always analyze your database weekly(or with less data changed, monthly)? Some times because of something wrong, the db is performance poor. While development team will always say: it must be your CBO that is misfunction, repair it! Faint, I think CBO is ok in most case with compute statistics, but that kind of question is really headache, right? How do you friends answer that kind of questions? Sometimes I even want to stop analyze the db weekly.As far as I know, some site like Ebay do not analye database.Can it because they use rbo? And to jeff, for DW application, for indexed columns size 1 is good because it gather histrogram data, but for OLTP system, do you think it is necessary? Friends please share your opinions? Do you use dbms_stats.gather_table_stats, or dbms_stats.gather_schema_stats? My scripts: echo begin to analyze user bidder at `date +%x%T` /export/home/oracle/log/analyze.log sqlplus /nolog EOF connect / as sysdba alter session set sort_area_size=1; alter session set sort_multiblock_read_count =128; execute dbms_stats.gather_schema_stats(ownname='bidder',estimate_percent=99,degree=8,cascade=true,method_opt='for all columns'); quit EOF echo finished analyze user bidder at `date +%x%T` /export/home/oracle/log/analyze.log Thanks. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org(China Oracle User Group) === 2003-02-27 08:09:00 ,you wrote£º=== Terrian, Tom (Contractor) (DAASC) wrote: I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster.Runs in 45 seconds:analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours:execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same?Thanks,Tom Hello Tom We too experienced terrible performance upon first using dbms_stats. There are two things we did that brought the performance in line with the analyze. 1. We set method_opt = 'FOR ALL INDEXED COLUMNS SIZE 1' 2. We set estimate_percent = 15 Hope this helps. BTW: Sun/Solaris 2.6 2.8 Running 8i, 9i, 9.2 5 terabyte db's Jeff = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Re: DBMS_STATS
We run DBMS_STATS.GATHER_TABLE_STATS nightly as cron job using this script to spool the result to a temp file, then use sqlplus to run that file. select 'execute DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','''|| segment_name||''',cascade = TRUE);' from ( select owner,segment_name, sum(bytes) from dba_segments s where segment_type like 'TABLE%' and owner in ('XYZ') and s.partition_name is null group by owner,segment_name order by sum(s.bytes) desc); We use multi-thread (3 threads) to run this script and it cuts the whole time by half. Now it takes about 1.5 hours (our data set is small). For another larger schema, we do this once a month. Guang Mei -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of chao_ping Sent: Friday, February 28, 2003 3:49 AM To: Multiple recipients of list ORACLE-L Subject: Re: Re: DBMS_STATS Hi, friends: How do you use dbms_stats.gather_schema_stats in OLTP production system? I ever used estimate statistics =20% percent, and some time have serious performance impact while two big table join in my production changed. Later I changed to compute and till now , It is ok. And another question about CBO stability: Do you always analyze your database weekly(or with less data changed, monthly)? Some times because of something wrong, the db is performance poor. While development team will always say: it must be your CBO that is misfunction, repair it! Faint, I think CBO is ok in most case with compute statistics, but that kind of question is really headache, right? How do you friends answer that kind of questions? Sometimes I even want to stop analyze the db weekly.As far as I know, some site like Ebay do not analye database.Can it because they use rbo? And to jeff, for DW application, for indexed columns size 1 is good because it gather histrogram data, but for OLTP system, do you think it is necessary? Friends please share your opinions? Do you use dbms_stats.gather_table_stats, or dbms_stats.gather_schema_stats? My scripts: echo begin to analyze user bidder at `date +%x%T` /export/home/oracle/log/analyze.log sqlplus /nolog EOF connect / as sysdba alter session set sort_area_size=1; alter session set sort_multiblock_read_count =128; execute dbms_stats.gather_schema_stats(ownname='bidder',estimate_perc ent=99,degree=8,cascade=true,method_opt='for all columns'); quit EOF echo finished analyze user bidder at `date +%x%T` /export/home/oracle/log/analyze.log Thanks. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org(China Oracle User Group) === 2003-02-27 08:09:00 ,you wrote£º=== Terrian, Tom (Contractor) (DAASC) wrote: I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster.Runs in 45 seconds:analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours:execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same?Thanks,Tom Hello Tom We too experienced terrible performance upon first using dbms_stats. There are two things we did that brought the performance in line with the analyze. 1. We set method_opt = 'FOR ALL INDEXED COLUMNS SIZE 1' 2. We set estimate_percent = 15 Hope this helps. BTW: Sun/Solaris 2.6 2.8 Running 8i, 9i, 9.2 5 terabyte db's Jeff = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
RE: DBMS_STATS
Thanks to Tim Johnston and Jonathan Lewis. They were exactly right! Tom -Original Message- Sent: Tuesday, February 25, 2003 7:49 PM To: Multiple recipients of list ORACLE-L To expand on this, the action level is controlled by the granularity parameter... Granularity of statistics to collect (only pertinent if the table is partitioned). DEFAULT: Gather global- and partition-level statistics. SUBPARTITION: Gather subpartition-level statistics. PARTITION: Gather partition-level statistics. GLOBAL: Gather global statistics. So, in you case, run the following... execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', estimate_percent = 5, granularity = 'PARTITION'); See the supplied package reference for more details... Tim -Original Message- Sent: Tuesday, February 25, 2003 4:50 PM To: Multiple recipients of list ORACLE-L The default action of dbms_stats against a single partition of a partitioned table is much more aggressive than a simple analyze of the partition. At the least, it does a similar analyze of the whole table in order to maintain the global table statistics - you need to set the granularity of the analyze to stop this happening. On a quick test, with sql_trace turned on and using 9.2.0.2, your choice of parameters gave me: Two 5% analyzes of the specified partition, with a small difference relating to one longish varchar() column. One 5% analyze of the whole table. One 50% analyze of the whole table - restricted to a much smaller process of a subset of the columns that omitted the one longish varchar() column. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 25 February 2003 18:12 I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Terrian, Tom (Contractor) (DAASC) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
Terrian, Tom (Contractor) (DAASC) wrote: I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster.Runs in 45 seconds:analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent;Takes over 2 hours:execute dbms_stats.gather_table_stats(ownname => 'LDGADMIN', - tabname => 'LOG_TRANS', - partname => 'LOG_TRANS_20030102', - estimate_percent => 5); Am I missing something? Aren't both commands the same?Thanks,Tom Hello Tom We too experienced terrible performance upon first using dbms_stats. There are two things we did that brought the performance in line with the analyze. 1. We set method_opt = 'FOR ALL INDEXED COLUMNS SIZE 1' 2. We set estimate_percent = 15 Hope this helps. BTW: Sun/Solaris 2.6 2.8 Running 8i, 9i, 9.2 5 terabyte db's Jeff
RE: DBMS_STATS
When is the best time to gather Global Stats? Using the old ANALYZE command, I would analyze each partition as it accumulated data. Now that we have global stats, should I be gather global stats each time I analyze a partition? That would naturally increase the length of time to gather stats. Oracle recommends deleting global stats on partitioned tables in 11i (running on an 8i database). See bde_last_analyzed.sql on MetaLink. In fact, if you read the comments in this script, it implies that you should never gather global stats on a partitioned table. Any thoughts on this? Has anyone had problems with global stats on 9i? Also, does anybody recommend SKEWONLY or AUTO_SAMPLE_SIZE with DBMS_STATS? My testing shows that this causes the analyze to take longer (which is to be expected). I haven't yet determined if its worth the extra time. Thanks, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 02/25/03 07:49PM To expand on this, the action level is controlled by the granularity parameter... Granularity of statistics to collect (only pertinent if the table is partitioned). DEFAULT: Gather global- and partition-level statistics. SUBPARTITION: Gather subpartition-level statistics. PARTITION: Gather partition-level statistics. GLOBAL: Gather global statistics. So, in you case, run the following... execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', estimate_percent = 5, granularity = 'PARTITION'); See the supplied package reference for more details... Tim -Original Message- Sent: Tuesday, February 25, 2003 4:50 PM To: Multiple recipients of list ORACLE-L The default action of dbms_stats against a single partition of a partitioned table is much more aggressive than a simple analyze of the partition. At the least, it does a similar analyze of the whole table in order to maintain the global table statistics - you need to set the granularity of the analyze to stop this happening. On a quick test, with sql_trace turned on and using 9.2.0.2, your choice of parameters gave me: Two 5% analyzes of the specified partition, with a small difference relating to one longish varchar() column. One 5% analyze of the whole table. One 50% analyze of the whole table - restricted to a much smaller process of a subset of the columns that omitted the one longish varchar() column. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 25 February 2003 18:12 I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
Re: DBMS_STATS
There is no generic answer to that question. Global Stats on partitioned tables do take a long time to collect - and you do need them to be reasonable because any query that the optimizer cannot pin down to exactly one partition at parse time is optimised according to the global stats. The best bet is to cheat. If you know your data you can analyze the partitions, but use dbms_stats.set_table_stats et. al. to set the global stats. Run dbms_stats with sql_trace switched on to see what happens with the 'exotic' options. I think Connor has some comments on his website - www.oracledba.co.uk - but the bottom line is don't be surprised if Oracle wastes computer resources when trying to automatic the jobs that require you to know the data. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 26 February 2003 13:23 When is the best time to gather Global Stats? Using the old ANALYZE command, I would analyze each partition as it accumulated data. Now that we have global stats, should I be gather global stats each time I analyze a partition? That would naturally increase the length of time to gather stats. Oracle recommends deleting global stats on partitioned tables in 11i (running on an 8i database). See bde_last_analyzed.sql on MetaLink. In fact, if you read the comments in this script, it implies that you should never gather global stats on a partitioned table. Any thoughts on this? Has anyone had problems with global stats on 9i? Also, does anybody recommend SKEWONLY or AUTO_SAMPLE_SIZE with DBMS_STATS? My testing shows that this causes the analyze to take longer (which is to be expected). I haven't yet determined if its worth the extra time. Thanks, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
DBMS_STATS
Title: Message I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statisticssample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', -estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom
RE: DBMS_STATS
Title: Message May have something to do with bug 2649728, which I just heard about for the first time no more than 10 seconds ago. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 56 Denver - Hotsos Clinic101, Mar 2527 London -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Terrian, Tom (Contractor) (DAASC) Sent: Tuesday, February 25, 2003 12:13 PM To: Multiple recipients of list ORACLE-L Subject: DBMS_STATS I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statisticssample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom
RE: DBMS_STATS
Title: Message ok, I will take a look. thanks -Original Message-From: Cary Millsap [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 1:35 PMTo: Multiple recipients of list ORACLE-LSubject: RE: DBMS_STATS May have something to do with bug 2649728, which I just heard about for the first time no more than 10 seconds ago. Cary MillsapHotsos Enterprises, Ltd.http://www.hotsos.comUpcoming events:- RMOUG Training Days 2003, Mar 5-6 Denver- Hotsos Clinic101, Mar 25-27 London -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Terrian, Tom (Contractor) (DAASC)Sent: Tuesday, February 25, 2003 12:13 PMTo: Multiple recipients of list ORACLE-LSubject: DBMS_STATS I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statisticssample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', -estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom
Re: DBMS_STATS
Title: Message Could it have to do with the fact that ANALYZE is running against a different partition than DBMS_STATS? - Original Message - From: Terrian, Tom (Contractor) (DAASC) To: Multiple recipients of list ORACLE-L Sent: Tuesday, February 25, 2003 11:12 AM Subject: DBMS_STATS I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statisticssample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', -estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom
RE: DBMS_STATS
Reading the bug, I'm frustrated for the user. Anyway, what does Event 10190 do? Or is this a case of File a TAR? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Tuesday, February 25, 2003 12:35 PM To: Multiple recipients of list ORACLE-L May have something to do with bug 2649728, which I just heard about for the first time no more than 10 seconds ago. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- (Contractor) (DAASC) Sent: Tuesday, February 25, 2003 12:13 PM To: Multiple recipients of list ORACLE-L I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
I think since DBMS_STATS also gathers histograms its taking more time Babu Tim Gorman [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] m cc: Sent by: Subject: Re: DBMS_STATS [EMAIL PROTECTED] 02/25/03 02:59 PM Please respond to ORACLE-L Could it have to do with the fact that ANALYZE is running against a different partition than DBMS_STATS? - Original Message - From: Terrian, Tom (Contractor) (DAASC) To: Multiple recipients of list ORACLE-L Sent: Tuesday, February 25, 2003 11:12 AM Subject: DBMS_STATS I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom _ This e-mail transmission and any attachments to it are intended solely for the use of the individual or entity to whom it is addressed and may contain confidential and privileged information. If you are not the intended recipient, your use, forwarding, printing, storing, disseminating, distribution, or copying of this communication is prohibited. If you received this communication in error, please notify the sender immediately by replying to this message and delete it from your computer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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
Check out $ORACLE_HOME/rdbms/mesg/oraus.msg to find out the meaning of any kernel event. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Rich Sent: Tuesday, February 25, 2003 2:19 PM To: Multiple recipients of list ORACLE-L Reading the bug, I'm frustrated for the user. Anyway, what does Event 10190 do? Or is this a case of File a TAR? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Tuesday, February 25, 2003 12:35 PM To: Multiple recipients of list ORACLE-L May have something to do with bug 2649728, which I just heard about for the first time no more than 10 seconds ago. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- (Contractor) (DAASC) Sent: Tuesday, February 25, 2003 12:13 PM To: Multiple recipients of list ORACLE-L I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).