Database Reference
In-Depth Information
Because the backup table is used to store different kinds of information, most of its columns are generic.
For example, in version 11.2 there are 12 columns to store numeric values (named
n1
. . .
n12
), 5 columns to store
character string values (named
c1
. . .
c5
), 2 columns to store bit string values (named
r1
and
r2
) and 1 column to
store datatime values (named
d1
).
Note that over the years the backup table's structure has changed. As a result, you may need to upgrade the
backup table after upgrading to a new database release or moving a backup table between different database releases.
Otherwise, you might not be able to use the table. For that purpose, the
dbms_stats
package provides the
upgrade_stat_table
procedure. To use it, specify the owner (with the
ownname
parameter) and the name (with the
stattab
parameter) of the backup table. For example:
dbms_stats.upgrade_stat_table(ownname => user,
stattab => 'MYSTATS')
To drop a backup table, the
dbms_stats
package provides the
drop_stat_table
procedure:
dbms_stats.drop_stat_table(ownname => user,
stattab => 'MYSTATS')
You can also drop the backup table with a regular
DROP TABLE
statement.
Configuring the dbms_stats Package
The
dbms_stats
package provides two sets of subprograms for configuring the default values of some of the
parameters described in the preceding section. The first set should be used only in version 10.2. In fact, its
subprograms are obsolete as of version 11.1. Therefore, starting with version 11.1, the subprograms provided by the
second set of subprograms should be used.
The Legacy Way
In version 10.2, you can change the global default values of the parameters
cascade
,
estimate_percent
,
degree
,
method_opt
,
no_invalidate
, and
granularity
. This is possible because the default values aren't hard-coded in the
signature of the procedures, but rather extracted from the data dictionary at runtime. The
set_param
procedure of the
dbms_stats
package is available for setting default values. To execute it, you need the system privileges
analyze any
dictionary
and
analyze any
. The
get_param
function of the
dbms_stats
package is for getting default values. The
following example shows how use them. Note that
pname
is the parameter name and
pval
is the parameter value:
SQL> execute dbms_output.put_line(dbms_stats.get_param(pname => 'CASCADE'))
DBMS_STATS.AUTO_CASCADE
SQL> execute dbms_stats.set_param(pname => 'CASCADE', pval =>'TRUE')
SQL> execute dbms_output.put_line(dbms_stats.get_param(pname => 'CASCADE'))
TRUE
Another parameter that can be set with this method is
autostats_target
. This parameter is used only by the
gather_stats_job
job (described later in this chapter) to determine which objects the gathering of statistics has to
process. Table
8-7
lists the available values. The default value is
auto
.
