11g - set_schema_stats only applies to objects already created

From: Reardon, Bruce (RTABBAY) <"Reardon,>
Date: Wed, 26 Aug 2009 16:27:37 +1000
Message-ID: <B1C87DCFE2040D41B6F46ADF9F8E4D9C01BAE791_at_CALBBEX01.cal.riotinto.org>

Just want to share this behaviour I'd observed and now found the explanation for.

If you want to use the built in statistics job in 11g, and decide to let Oracle keep potentially creating histograms for its "internal" objects, you may decide to do something like:
exec dbms_stats.set_schema_prefs ( 'MYUSER' , 'method_opt' , 'FOR ALL COLUMNS SIZE 1' ) If so, take note of
http://optimizermagic.blogspot.com/2009/08/understanding-dbmsstatssetpre fs.html
The set_schema_prefs only applies to objects created prior to running it.

Hope this helps someone.

Bruce Reardon

This email is confidential and may also be privileged. If you are not the intended recipient, please notify us immediately and delete this message from your system without first printing or copying it. Any personal data in this email (including any attachments) must be handled in accordance with the Rio Tinto Group Data Protection Policy and all applicable data protection laws.

Received on Wed Aug 26 2009 - 01:27:37 CDT

Original text of this message