nice ( but not essential ) to know (1)

April 26, 2012 — Leave a comment

if you ever wondered where oracle stores the statistic preferences one can set by using the dbms_stats.set_*_prefs procedures here you go:

global statistic preferences

table which holds the values: sys.optstat_hist_control$

example script to query the settings:

COLUMN SNAME FOR a30;
COLUMN SVAL1 FOR 9999;
COLUMN SVAL2 FOR a20;
COLUMN SPARE1 FOR 999999;
COLUMN SPARE2 FOR 999;
COLUMN SPARE3 FOR 999999;
COLUMN SPARE4 FOR a30;
COLUMN SPARE5 FOR a10;
COLUMN SPARE6 FOR a10;
SELECT sname
, sval1
, to_char(sval2,'DD.MM.YYYY HH24:MI:SS') sval2
, spare1
, spare2
, spare3
, spare4
, spare5
, spare6
FROM sys.optstat_hist_control$
ORDER BY 1
;

procedure which affects the table: dbms_stats.set_global_prefs, e.g.:

exec dbms_stats.set_global_prefs ( 'ESTIMATE_PERCENT', '11');

non-global ( user ) statistic preferences

table which holds the values: sys.optstat_user_prefs$

example script to query the settings:

COLUMN owner FOR a20;
COLUMN object_name FOR a20;
COLUMN object_type FOR a20;
COLUMN pname FOR a30;
COLUMN valnum FOR 99999;
COLUMN valchar FOR a30;
COLUMN chgtime FOR a20;
COLUMN spare1 FOR 999;
SELECT do.owner
, do.object_name
, do.object_type
, o.pname
, o.valnum
, o.valchar
, to_char(o.chgtime,'DD.MM.YYYY HH24:MI:SS') chgtime
, o.spare1
FROM sys.optstat_user_prefs$ o
, dba_objects do
WHERE o.obj# = do.object_id
;

procedures which affects the table: dbms_stats.set_global_prefs, dbms_stats.set_schema_prefs, dbms_stats.set_database_prefs,e.g.:

exec dbms_stats.set_table_prefs ( USER,'T1','GRANULARITY','ALL');
exec dbms_stats.set_schema_prefs ( USER,'ESTIMATE_PERCENT','11' );
exec dbms_stats.set_database_prefs ( 'ESTIMATE_PERCENT', '12', FALSE );
Advertisements

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s