• 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 » PREFERENCE_OVERRIDES_PARAMETER in Oracle

PREFERENCE_OVERRIDES_PARAMETER in Oracle

April 1, 2023 by techgoeasy Leave a Comment

Table of Contents

  • What is PREFERENCE_OVERRIDES_PARAMETER
  • How to set at the table level
  • How to set at the global level
  • How to find the set value

What is PREFERENCE_OVERRIDES_PARAMETER

  • We discussed optimizer statistics preferences in the previous blog post and we know that that got overwritten by the values given explicitly in the dbms_stats procedure
  • Oracle Database 12 Release 2 includes a new DBMS_STATS preference called PREFERENCE_OVERRIDES_PARAMETER
  • When this preference is set to TRUE, it allows preference settings to override DBMS_STATS parameter values

Suppose you want all the users to use AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT. So we can set PREFERENCE_OVERRIDES_PARAMETER to true at the global level. Then whatever users gave, will be ignored. it means that this best-practice setting will be used even if existing manual statistics-gathering procedures use a different parameter setting

Or

Suppose you want to fix the ESTIMATE_PERCENT for a particular table and it should not get changed by the parameter in dbms_stats, then it can be used

How to set at the table level

exec dbms_stats.set_table_prefs(
ownname=>'&owner',
tabname=>'&table_name',
pname=>'PREFERENCE_OVERRIDES_PARAMETER',
pvalue=>'TRUE') ;
end;
/

How to set at the global level

EXEC DBMS_STATS.SET_GLOBAL_PREFS ('PREFERENCE_OVERRIDES_PARAMETER', 'TRUE');

How to find the set value

Global Level
select dbms_stats.get_prefs('PREFERENCE_OVERRIDES_PARAMETER') from dual;
Table level
select dbms_stats.get_prefs('PREFERENCE_OVERRIDES_PARAMETER','SCOTT','EMP') from dual;

I hope you like this article.

How to set table level preference setting
How to set Schema level preference setting
How to delete Optimizer preference
Optimizer statistics preferences in Oracle
How do I drop the histogram on a column and prevent to generate in future

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

  • Top Queries on Sql plan Management
  • Move SQL Baseline from One Database to Another in Oracle
  • How to drop the sql baseline in Oracle
  • how to create sql baseline from AWR repository without STS
  • how to create sql baseline from Sql tuning set in Oracle

Copyright © 2023 : TechGoEasy

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