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