Preference can be any of the following.
DBMS_STATS.SET_TABLE_PREFS documentation -
https://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68674 ;
CASCADE
DEGREE
ESTIMATE_PERCENT
GRANULARITY
INCREMENTAL
INCREMENTAL_LEVEL
INCREMENTAL_STALENESS
METHOD_OPT
NO_INVALIDATE
PUBLISH
STALE_PERCENT
TABLE_CACHED_BLOCKS
OPTIONS
Regards,
Vishal Gupta
From: Bheemsen Aitha <baitha@xxxxxxxxxxxxxxxxx>
Date: Thursday, 22 March 2018 at 21:53
To: "vishal@xxxxxxxxxxxxxxx" <vishal@xxxxxxxxxxxxxxx>,
"jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>,
"christopherdtaylor1994@xxxxxxxxx" <christopherdtaylor1994@xxxxxxxxx>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: RE: Drawing a blank and can't find a quick answer - view for
incremental stats enabled tables?
What’s preference_name in the below query?
BA
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Vishal Gupta
Sent: Thursday, March 22, 2018 1:52 PM
To: jonathan@xxxxxxxxxxxxxxxxxx; christopherdtaylor1994@xxxxxxxxx
Cc: ORACLE-L
Subject: Re: Drawing a blank and can't find a quick answer - view for
incremental stats enabled tables?
You can use the query like below to get table level stats preferences.
-- This query has been taken from DBA_TAB_STAT_PREFS sys view.
-- chgtime column is not exposed in the view, so querying from underlying
-- objects directly
SELECT u.username owner
, o.object_name table_name
, p.pname preference_name
, p.valchar preference_value
, p.chgtime ChangeTime
FROM sys.optstat_user_prefs$ p
, dba_objects o
, dba_users u
WHERE p.obj#=o.object_id
AND u.username=o.owner
AND u.username like UPPER('&&owner') ESCAPE '\'
AND o.object_name like UPPER('&&table_name') ESCAPE '\'
AND UPPER(p.pname) like UPPER('&&preference_name') ESCAPE '\'
ORDER BY u.username
, o.object_name
, p.pname
;
Regards,
Vishal Gupta
From: <oracle-l-bounce@xxxxxxxxxxxxx> on behalf of Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx>
Reply-To: <jonathan@xxxxxxxxxxxxxxxxxx>
Date: Wednesday, 21 March 2018 at 15:35
To: <christopherdtaylor1994@xxxxxxxxx>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Drawing a blank and can't find a quick answer - view for
incremental stats enabled tables?
Check view dba_tab_stat_prefs.
There’s no equivalent user view
Regards
Jonathan Lewis
(From my iPad mini; please excuse typos and auto-correct)
On 21 Mar 2018, at 14:58, Chris Taylor <christopherdtaylor1994@xxxxxxxxx> wrote:
Is there a system view that shows which tables INCREMENTAL stats have been
setup on?
I know I can do:
DBMS_STATS.get_prefs and pass in the table table and owner but surely there's a
view that I can query?
But I can't find it - we're on 12.1.0.2.
Thanks,
Chris
