Select the desired tables and click Edit Preferences. Leave Table Name blank to see all tables in the schema. To modify table preferences for a table that has preferences set at the table level, do the following (otherwise, skip to the next step):Įnter values in Schema and Table Name. The Object Level Statistics Gathering Preferences page appears. Go to the Optimizer Statistics Console, as explained in " Accessing the Optimizer Statistics Console".Ĭlick Object Level Statistics Gathering Preferences. To set object-level optimizer statistics preferences using Cloud Control: You use SET_DATABASE_PREFS to set the INCREMENTAL preference for all tables in all user-defined schemas to false. You query the INCREMENTAL preference for costs and confirm that it is set to true. You use SET_SCHEMA_PREFS to set the INCREMENTAL preference to true for every table in the sh schema, including costs. You query the INCREMENTAL preference for costs and confirm that it is set to false. You use SET_TABLE_PREFS to set the INCREMENTAL preference to false for the costs table only. You query the INCREMENTAL preference for costs and determine that it is set to true. Table 12-2 Changing Preferences for Statistics Gathering Procedures Action You cannot set the preference INCREMENTAL_LEVEL using SET_GLOBAL_PREFS. You can only set the CONCURRENT preference at the global level. Possible values for AUTOSTATS_TARGET are ALL, ORACLE, and AUTO (default). This additional parameter controls which objects the automatic statistic gathering job running in the nightly maintenance window affects. With SET_GLOBAL_PREFS, you can set a default value for the parameter AUTOSTATS_TARGET. New objects use the SET_GLOBAL_PREFS values for all parameters. Changes made by SET_GLOBAL_PREFS affect any new objects created after it runs. New objects use the GLOBAL_PREF values for all parameters.Īny table that does not have an existing table preference.Īll parameters default to the global setting unless a table preference is set or the parameter is explicitly set in the DBMS_STATS.GATHER_*_STATS statement. Calling SET_DATABASE_PREFS does not affect any new objects created after it has been run. This procedure calls SET_TABLE_PREFS for each table in the specified schema. You can include system-owned schemas such as SYS and SYSTEM by setting the ADD_SYS parameter to true. New tables use the GLOBAL_PREF values for all parameters.Īll user-defined schemas in the database. Calling SET_SCHEMA_PREFS does not affect any new tables created after it has been run. Table 12-1 DBMS_STATS Procedures for Setting Optimizer Statistics Preferences ProcedureĪll existing tables in the specified schema.