Suppose you want to remove the optimizer preference you set on the schema, table, or database, then the below procedure can be used to do that
How to delete table level Optimizer preference
The DBMS_STATS.DELETE_TABLE_PREFS procedure is used to delete the statistics preferences of the specified table in the specified schema.
DBMS_STATS.DELETE_TABLE_PREFS (
ownname IN VARCHAR2,
tabname IN VARCHAR2,
pname IN VARCHAR2); Example
exec DBMS_STATS.DELETE_TABLE_PREFS ( 'SCOTT' ,'EMP','METHOD_OPT');
How to delete schema level Optimizer preference
The DBMS_STATS.DELETE_SCHEMA_PREFS procedure is used to delete the statistics preferences of all the tables owned by the specified owner name.
DBMS_STATS.DELETE_SCHEMA_PREFS (
ownname IN VARCHAR2,
pname IN VARCHAR2)Example
exec DBMS_STATS.DELETE_SCHEMA_PREFS ( 'SCOTT' ,'METHOD_OPT');
How to delete the database level optimizer preference
The DBMS_STATS.DELETE_DATABASE_PREFS procedure is used to delete the statistics preferences of all the tables, excluding the tables owned by Oracle. These tables can by included by passing TRUE for the add_sys parameter.
DBMS_STATS.DELETE_DATABASE_PREFS (
pname IN VARCHAR2,
add_sys IN BOOLEAN DEFAULT FALSE)Example
exec DBMS_STATS.DELETE_DATABASE_PREFS ( 'METHOD_OPT');
How to delete the global optimizer preference
You cannot delete the global optimizer preference. But you can reset all to default using the SET_GLOBAL_PREFS Procedure
DBMS_STATS.RESET_GLOBAL_PREF_DEFAULTS;
It’s important to note that deleting or resetting optimizer preferences should be done with caution, as it can impact the performance of your database. Make sure to test any changes thoroughly before deploying them to a production environment.
Related Articles
How to set table level preference setting: In this post we check out how to set the various optimizer statistics preferences at the table level.
How to set Schema level preference setting: In this post we check out how to set the various optimizer statistics preferences at the Schema level.
PREFERENCE_OVERRIDES_PARAMETER in Oracle
Optimizer statistics preferences in Oracle
STATISTICS_LEVEL in Oracle