tag:blogger.com,1999:blog-518481768015386858.post5725981618696395192..comments2023-08-06T04:28:52.092-04:00Comments on Karen Morton: Bug alert!Karenhttp://www.blogger.com/profile/03309823327597536648noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-518481768015386858.post-35976206469345376262011-07-25T13:49:25.310-04:002011-07-25T13:49:25.310-04:00The bug doesn't kick in until you have a table...The bug doesn't kick in until you have a table with a significant number of rows. In my case, I found that the table had to be at least 3.25 million rows before the bug occurred. So, your mileage may vary, but since you created what I'd assume is a fairly small table (built on all_objects), it's likely not large enough to meet the conditions of the bug. Also, our environment is 11.2.0.2 on RHEL Linux 5.6 and yours is 11.2.0.1 on Windows. I don't know if the bug affects your environment. See my follow-up post for the link to the patch set fix that was supplied to read more about the bug and determine if your environment is one that is affected.Karenhttps://www.blogger.com/profile/03309823327597536648noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-88491721545517434752011-07-25T10:30:22.389-04:002011-07-25T10:30:22.389-04:00I could not reproduce the same error. See below
B...I could not reproduce the same error. See below<br /><br />Best Regards<br /><br />Mohamed Houri<br /><br />mhouri@mhouri> select * from v$version;<br /><br />BANNER <br />--------------------------------------------------------------------------------<br />Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production <br />PL/SQL Release 11.2.0.1.0 - Production <br />CORE 11.2.0.1.0 Production <br />TNS for 32-bit Windows: Version 11.2.0.1.0 - Production <br />NLSRTL Version 11.2.0.1.0 - Production <br /><br />mhouri@mhouri> create table mho_all_objects as select * from all_objects;<br /><br />Table créée.<br /><br />mhouri@mhouri> ed<br />écrit file afiedt.buf<br /><br /> 1 CREATE INDEX fbi_mho_obj ON mho_all_objects<br /> 2 (<br /> 3 CASE<br /> 4 WHEN object_type = 'TABLE'<br /> 5 THEN NULL<br /> 6 ELSE OBJECT_NAME<br /> 7 END<br /> 8* )<br />mhouri@mhouri> /<br /><br />Index créé.<br /><br />mhouri@mhouri> BEGIN<br /> 2 DBMS_STATS.GATHER_TABLE_STATS(user<br /> 3 ,'MHO_ALL_OBJECTS'<br /> 4 ,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE<br /> 5 ,METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO'<br /> 6 ,cascade => false<br /> 7 ,degree => 2);<br /> 8 END;<br /> 9 /<br /><br />Procédure PL/SQL terminée avec succès.<br /><br />mhouri@mhouri> BEGIN<br /> 2 DBMS_STATS.GATHER_TABLE_STATS( user<br /> 3 ,'MHO_ALL_OBJECTS'<br /> 4 , ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE<br /> 5 , METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO'<br /> 6 , cascade => false<br /> 7 , degree => DBMS_STATS.AUTO_DEGREE);<br /> 8 END;<br /> 9 /<br /><br />Procédure PL/SQL terminée avec succès.Mohamed Hourihttps://www.blogger.com/profile/11687776847553675567noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-62959271684713209102011-07-12T14:42:25.813-04:002011-07-12T14:42:25.813-04:00Doing that actually makes it worse (we've trie...Doing that actually makes it worse (we've tried it). We're trying other alternatives and just heard back from Oracle that they've got a patch that might help us out. As soon as we get it applied and tested, I'll post back here with results.Karenhttps://www.blogger.com/profile/03309823327597536648noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-9533857434883417662011-07-04T11:04:14.794-04:002011-07-04T11:04:14.794-04:00Since you have multiple tables with this problem, ...Since you have multiple tables with this problem, I guess you could parallelize manually, running several tables in parallel, each with degree=>1.<br />Sure it is clunky, but it might just be enough for you to fit into the stats gathering window.<br /><br />Cheers!<br />FladoFladohttps://www.blogger.com/profile/04901763101139511192noreply@blogger.com