History table helper functions
Within the history tables, several of the columns contain
data that use mapped values and bit masks. You can use the following
helper functions within your history queries to return more readable
text values and strings for those internal values:
Function | Description | Return value |
---|---|---|
FORMAT_QUERY_STATUS() | Use this function to display text string versions of the $hist_query_epilog.status column data. | One of the following status values:
|
FORMAT_PLAN_STATUS() | Use this function to display text string versions of the $hist_plan_epilog.status column data. | One of the following status values:
|
FORMAT_TABLE_ACCESS() | Use this function to display text string versions of all bits set in the $hist_table_access.usage column data. | A comma-separated list of one or more of the
following values:
|
FORMAT_COLUMN_ACCESS() | Use this function to display text string versions of all bits set in the $hist_column_access.usage column data. | A comma-separated list of one or more of the
following values:
|
The following sample query shows how you can use these
helper functions.
SELECT
substr (querytext, 1, 50) as QUERY,
format_query_status (status) as status,
tb.tablename,
format_table_access (tb.usage),
co.columnname,
format_column_access (co.usage)
from "$hist_query_prolog_3" qp
inner join
"$hist_query_epilog_3" qe using (npsid, npsinstanceid, opid)
inner join
"$hist_table_access_3" tb using (npsid, npsinstanceid, opid)
inner join
"$hist_column_access_3" co using (npsid, npsinstanceid, opid)
where
exists (select tb.dbname
from "$hist_table_access_3" tb
where tb.npsid = qp.npsid and
tb.npsinstanceid = qp.npsinstanceid and
tb.opid = qp.opid and
tb.tablename in (^nation^, ^orders^, ^part^,
^partsupp^, ^supplier^, ^lineitem^,
^region^))
and tb.tableid = co.tableid;