ABCD
1
Command
2
1EXEC dbms_stats.SET_GLOBAL_PREFS('AUTOSTATS_TARGET', 'AUTO');
3
2EXEC dbms_stats.SET_DATABASE_PREFS('STALE_PERCENT', '15');
4
3EXEC dbms_stats.GATHER_DATABASE_STATS(degree => 4, cascade => TRUE);
5
4EXEC dbms_stats.GATHER_SCHEMA_STATS(ownname => 'SCOTT', degree => 4, cascade => TRUE);
6
5EXEC dbms_stats.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'EMP', degree => 4, cascade => TRUE);
7
8
9
AOSCMoidIITDocID-875: My notes on "Automatic Optimizer Statistics Collection" (AOSC - DBMS_STATS)
https://www.mydbanotes.com/2013/01/my-notes-on-11g-automatic-statistics.html
10
11
12
13
14
PreferenceDescriptionDefault (11gR2)Scope
15
CASCADEDetermines if index stats should be gathered for the current table (TRUE, FALSE, AUTO_CASCADE).DBMS_STATS.AUTO_CASCADEG, D, S, T
16
DEGREEDegree of parallelism (integer or DEFAULT_DEGREE).DBMS_STATS.DEFAULT_DEGREEG, D, S, T
17
ESTIMATE_PERCENTPercentage of rows to sample when gathering stats (0.000001-100 or AUTO_SAMPLE_SIZE).DBMS_STATS.AUTO_SAMPLE_SIZEG, D, S, T
18
METHOD_OPTControls column statistics collection and histogram creation.FOR ALL COLUMNS SIZE AUTOG, D, S, T
19
NO_INVALIDATEDetermines if dependent cursors should be invalidated as a result of new stats on objects (TRUE, FALSE or AUTO_INVALIDATE).DBMS_STATS.AUTO_INVALIDATEG, D, S, T
20
AUTOSTATS_TARGETDetermines which objects have stats gathered (ALL, ORACLE, AUTO).AUTOG
21
GRANULARITYThe granularity of stats to be collected on partitioned objects (ALL, AUTO, DEFAULT, GLOBAL, 'GLOBAL AND PARTITION', PARTITION, SUBPARTITION).AUTOG, D, S, T
22
PUBLISHDetermines if gathered stats should be published immediately or left in a pending state (TRUE, FALSE).TRUEG, D, S, T
23
INCREMENTALDetermines whether incremental stats will be used for global statistics on partitioned objects, rather than generated using table scans (TRUE, FALSE).FALSEG, D, S, T
24
CONCURRENTShould objects statistics be gathered on multiple objects at once, or one at a time (MANUAL, AUTOMATIC, ALL, OFF).OFFG
25
GLOBAL_TEMP_TABLE_STATSShould stats on global temporary tables be session-specific or shared between sessions (SHARED, SESSION).SESSIONG, D, S
26
INCREMENTAL_LEVELWhich level of synopses should be collected for incremental partitioned statistics (TABLE, PARTITION).PARTITIONG, D, S, T
27
INCREMENTAL_STALENESSHow is staleness of partition statistics determined (USE_STALE_PERCENT, USE_LOCKED_STATS, NULL).NULLG, D, S, T
28
TABLE_CACHED_BLOCKSThe number of blocks cached in the buffer cache during calculation of index cluster factor. Jonathan Lewis recommends "16" as a sensible value.1G, D, S, T
29
OPTIONSUsed for the OPTIONS parameter of the GATHER_TABLE_STATS procedure (GATHER, GATHER AUTO).GATHERG, D, S, T
30
Source: https://oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics
31
32
MoidIITDocID-875: My notes on "Automatic Optimizer Statistics Collection" (AOSC - DBMS_STATS)
https://www.mydbanotes.com/2013/01/my-notes-on-11g-automatic-statistics.html
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100