Database Reference
In-Depth Information
The default value is
for all columns size auto
(this default value can be changed—see the section
“Configuring the dbms_stats Package” later in this chapter). For simplicity, use either
size skewonly
or
size auto
. If it's too slow or the chosen number of buckets isn't good (or the needed histogram isn't
created at all), manually specify the list of columns. If
NULL
is specified,
for all columns size 1
is used.
COLUMN USaGe hIStOrY
The
dbms_stats
package relies on column usage history to determine on which columns a histogram is useful
for. To gather the history, while generating a new execution plan, the query optimizer tracks which columns are
referenced in the
WHERE
clause and stores the information it finds in the SGA. Then, at regular intervals, the database
engine stores this information in the data dictionary table
col_usage$
. With a query based on internal data dictionary
tables such as the following (which is available in the
col_usage.sql
script), it's possible to know which columns
were referenced in
WHERE
clauses and for which kind of predicates. The
timestamp
column indicates the last usage.
The other columns are counts of the number of hard parses (actually, hard parses providing the same information
and executed in rapid succession aren't counted). Columns that were never referenced in a
WHERE
clause have no
rows in the
col_usage$
table, so all columns in the output except
name
are
NULL
.
SQL> SELECT c.name, cu.timestamp,
2 cu.equality_preds AS equality, cu.equijoin_preds AS equijoin,
3 cu.nonequijoin_preds AS noneequijoin, cu.range_preds AS range,
4 cu.like_preds AS "LIKE", cu.null_preds AS "NULL"
5 FROM sys.col$ c, sys.col_usage$ cu, sys.obj$ o, dba_users u
6 WHERE c.obj# = cu.obj# (+)
7 AND c.intcol# = cu.intcol# (+)
8 AND c.obj# = o.obj#
9 AND o.owner# = u.user_id
10 AND o.name = 'T'
11 AND u.username = user
12 ORDER BY c.col#;
NAME TIMESTAMP EQUALITY EQUIJOIN NONEEQUIJOIN RANGE LIKE NULL
---- --------- -------- -------- ------------ ----- ----- -----
ID 27-MAY-14 1 1 0 0 0 0
VAL1 27-MAY-14 1 0 0 0 0 0
VAL2
VAL3 27-MAY-14 1 1 0 0 0 0
PAD 27-MAY-14 0 0 0 1 0 0
As of version 11.2.0.2, the
report_col_usage
function of the
dbms_stats
package makes the selection of
col_
usage$
information easier. Note that this is same function already discussed in the “extended Statistics” section.
but be aware that if the function
seed_col_usage
isn't used, the report returned by the
report_col_usage
function won't contain information about potential column groups. The following query shows an example and an
excerpt of the output:
SQL> SELECT dbms_stats.report_col_usage(ownname => user, tabname => 't')
2 FROM dual;
