Database Reference
In-Depth Information
is used to override a potential lock of the object statistics (the “Locking Object Statistics”
section later in this chapter describes such locks). Its default value is
FALSE
, which means
that locks are honored by default:
dbms_stats.publish_pending_stats(ownname => user, tabname => 'T')
5.
If the test isn't successful, you can delete the pending statistics by calling the
delete_
pending_stats
procedure. If the
tabname
parameter isn't specified or set to
NULL
, pending
statistics for the whole schema specified by the
ownname
parameter are deleted:
dbms_stats.delete_pending_stats(ownname => user, tabname => 'T')
6.
Enable automatic publishing by setting the
publish
preference to
TRUE
. This step is
required to revert the change carried out in step 1:
dbms_stats.set_table_prefs(ownname => user,
tabname => 'T',
pname => 'PUBLISH',
pvalue => 'TRUE')
To execute the procedures
publish_pending_stats
and
delete_pending_stats
, you need to be connected as
owner or have the
analyze any
system privilege.
If you're interested in knowing the values of the pending statistics, the following data dictionary views provide
all the necessary information. For each view, there are
dba
,
all
and, in a 12.1 multitenant environment,
cdb
versions
as well:
•
user_tab_pending_stats
shows pending table statistics.
•
user_ind_pending_stats
shows pending index statistics.
•
user_col_pending_stats
shows pending column statistics.
user_tab_histgrm_pending_stats
shows pending histograms.
The content and structure of these data dictionary views are similar to
user_tab_statistics
,
user_ind_statistics
,
user_tab_col_statistics
, and
user_tab_histograms
, respectively.
•
Working with Partitioned Objects
Gathering object statistics for partitioned tables and indexes poses specific challenges. This section describes what
the challenges are and introduces two techniques to tackle them.
Challenges
The
dbms_stats
package uses two main approaches for gathering object statistics for partitioned tables and indexes:
•
Gather object statistics at the object, partition, and, if available, subpartition level by means of
queries that are independently executed at each level.
•
Gather object statistics at the physical level only (either the partition or subpartition level) and
use those results to derive the object statistics for the other levels.
