How to check optimizer statistics preferences at the table level

Sometimes we may have performance issues and we want to check the optimizer statistics preferences at the table level. let’s check out the queries for that

How to check optimizer stats preferences at the table level

set pages 200 lines 200
col owner for a16
col table_name for a32
col preference_value for a50
select owner, table_name, PREFERENCE_NAME, PREFERENCE_VALUE
from dba_tab_stat_prefs
where owner='&table_owner'
and table_name = '&table_name'
order by 1,2,3;

or

we can use the DBMS_STATS.GET_PREFS procedure also

SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'SCOTT', 'EMP')
FROM   DUAL;

we can give the preference name and find the value set

Let’s see an example

First, we will set the preference at the table level

EXEC DBMS_STATS.SET_TABLE_PREFS ('SCOTT', 'EMP', 'incremental', 'false');

Now let’s see the value from the above queries

SELECT DBMS_STATS.GET_PREFS ('incremental', 'SCOTT', 'EMP') FROM DUAL;
_____
FALSE

How to check optimizer stats preferences for all the tables in a schema

set pages 200 lines 200 
col owner for a16 
col table_name for a32 
col preference_value for a50 
select owner, table_name, PREFERENCE_NAME, PREFERENCE_VALUE from dba_tab_stat_prefs where owner='&table_owner' 
order by 1,2,3;

I hope the post on How to check optimizer statistics preferences at the table level is helpful and useful. Please do provide the feedback

Related Articles

How to set table level preference setting
How to set Schema level preference setting
PREFERENCE_OVERRIDES_PARAMETER in Oracle
How to delete Optimizer preference
Optimizer statistics preferences in Oracle