How to delete Optimizer preference

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.