Database Reference
In-Depth Information
It's not necessarily a trivial thing deciding which group of columns it's sensible to create an extension on. The
following approach can be used (a full example is available in the
seed_col_usage.sql
script) as of version 11.2.0.2:
1.
Invoke the
seed_col_usage
procedure of the
dbms_stats
package to instruct the query
optimizer to record information about predicates specified in
WHERE
clauses, about
columns referenced in
GROUP BY
clauses, and, from version 11.2.0.3 onward, about the
DISTINCT
operator in
SELECT
clauses. The recording is done either for all SQL statements
of the SQL tuning set specified in the
sqlset_name
and
owner_name
parameters or, as
shown in the following example, for all SQL statements that are hard parsed (no execution
is needed, hence, an
EXPLAIN PLAN
statement is sufficient) over a period of time specified
in seconds by the
time_limit
parameter:
SQL> BEGIN
2 dbms_stats.seed_col_usage(sqlset_name => NULL,
3 owner_name => NULL,
4 time_limit => 30);
5 END;
6 /
Once the recording is over, invoke the
report_col_usage
function of the
dbms_stats
package to report the columns' usage. Each column's utilization pattern is reported. For
example, in the following output, the
val1
and
val2
columns were part of a single-table
predicate based on equalities:
2.
SQL> SELECT dbms_stats.report_col_usage(ownname => user, tabname => 't')
2 FROM dual;
DBMS_STATS.REPORT_COL_USAGE(OWNNAME=>USER,TABNAME=>'T')
------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
........................................................................
