• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to delete Optimizer preference

How to delete Optimizer preference

April 11, 2023 by techgoeasy Leave a Comment

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

Table of Contents

  • How to delete table level Optimizer preference
  • How to delete schema level Optimizer preference
  • How to delete the database level optimizer preference
  • How to delete the global optimizer preference

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.

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to set statistics for Table/index/column in Oracle
  • How to delete statistics from Table/index/column in Oracle
  • How to replace sql plan by another sql id sql plan
  • How to get the outline hint in oracle and use for tuning
  • How to implement custom Sql Profile in Oracle

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us
x
x