Oracle Optimizer: Moving to and working with CBO - Part 4 - Page 3

9.4) DBMS_STATS

DBMS_STATS is a package provided for gathering and maintaining statistics in a database and is the recommended way. The following can be done with this package:

• Gathering statistics
• Deleting statistics
• Providing user statistics
• Retrieving statistics
• Exporting and importing statistics

Below is a list of the various routines present in the package. The key parameters are covered for each routine.

9.4.1) Gathering statistics with DBMS_STATS

DBMS_STATS.GATHER_TABLE_STATS

DBMS_STATS.GATHER_TABLE_STATS gathers statistics for a table and its columns, and optionally the associated indexes.

Call syntax

dbms_stats.gather_table_stats(ownname, tabname, partname, 
estimate_percent, block_sample,
method_opt, degree, granularity, cascade, 
stattab, statid, statown);

The first two parameters are mandatory, the rest are defaulted to a value.

• ownname - owner
• tabname - table name
• partname - partition name
• estimate_percent - sample percent ratio
• block_sample - consider random blocks sampling rather than rows sampling. TRUE/FALSE
• method_opt - method options. FOR ALL COLUMNS/FOR ALL INDEXED COLUMNS. Append the phase SIZE 1 if it is required to generate statistics in parallel.
• degree - degree of parallelism.
• granularity - for partitioned tables. DEFAULT/SUBPARTITION/PARTITION/GLOBAL/ALL.
• cascade - gather statistics for indexes also. TRUE/FALSE
• stattab, statid, statown - required for user statistics, covered below in this section.

e.g.: Estimate statistics for a table and its columns

orAP>exec dbms_stats.gather_table_stats(ownname => 'INV', tabname => 'MTL_MATERIAL_TRX', -
> estimate_percent => 5);
PL/SQL procedure successfully completed.

e.g.: Estimate statistics for a table, its columns and indexes.

orAP>exec dbms_stats.gather_table_stats(ownname => 'APPS', tabname => 'AM21', -
> estimate_percent => 5, cascade => true);
PL/SQL procedure successfully completed.

e.g.: Estimate statistics in parallel, the following uses 8 threads to complete the task.

Session - A

orAP>exec dbms_stats.gather_table_stats(ownname => 'INV', tabname => 'MTL_MATERIAL_TRX', -
> estimate_percent => 5, degree => 8);
PL/SQL procedure successfully completed.

Session - B (When the above process is running)

orAP>select * from v$px_process;

SERV|STATUS   |       PID|SPID     |       SID|  SERIAL#

____|_________|__________|_________|__________|__________

P000|IN USE   |        50|9684     |         7|    50586
P001|IN USE   |        65|9686     |        60|    51561
P002|IN USE   |        66|9688     |        17|     2694
P003|IN USE   |        67|9690     |        30|    39243
P004|IN USE   |        68|9692     |        74|    11017
P005|IN USE   |        69|9694     |        48|     4253
P006|IN USE   |        70|9696     |        76|       17
P007|IN USE   |        71|9698     |        68|     1285

8 rows selected.

e.g.: Estimate statistics for columns in a table, this will also generate statistics for tables.

SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'AM01', -
>  estimate_percent => 5, 
method_opt => 'FOR ALL COLUMNS');
PL/SQL procedure successfully completed.

e.g.: The below example allows generation of column statistics in parallel. The degree of the table is initially set to 8 and the "SIZE 1" makes use of this. Refer to the Histogram section below to find out about the SIZE option.

SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'AM01',-
>  estimate_percent => 5, method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

DBMS_STATS.GATHER_INDEX_STATS

DBMS_STATS.GATHER_INDEX_STATS gathers statistics for indexes. Index statistics cannot be generated in parallel. In addition, the block sampling option available in tables is not available for indexes.

Call syntax

dbms_stats.gather_index_stats(ownname, indname, partname, estimate_percent, 
                                     stattab, statid, statown);

e.g.:

orAP>exec dbms_stats.gather_index_stats
	(ownname => 'INV', indname => 'MTL_SYSTEM_ITEMS_JHN99');
PL/SQL procedure successfully completed.

DBMS_STATS.GATHER_SCHEMA_STATS

DBMS_STATS.GATHER_SCHEMA_STATS gathers statistics for a given schema.

Call Syntax

dbms_stats.gather_schema_stats(ownname, estimate_percent, block_sample, 
method_opt, degree, granularity,
cascade, stattab, statid, options, objlist, statown);

• options - object information can be further specified here.
• GATHER - gather statistics for all objects (default).
• GATHER STALE - update statistics for stale objects, identified with the monitoring option.
• GATHER EMPTY - gather statistics for objects without any statistics.
• LIST STALE - return a list of stale objects, this depends on the SMON processing.
• LIST EMPTY - return a list of objects with no statistics.
• GATHER AUTO - same as STALE but will include objects without any statistics.
• objlist - table of type DBMS_STATS.OBJECTTAB, returns an empty or stale list.

e.g.: Gather schema statistics, for tables and indexes at 5% estimate.

SQL> exec dbms_stats.gather_schema_stats(ownname => 'SCOTT', estimate_percent => 5, -
> cascade => true, options => 'GATHER');

e.g.: Gather statistics for objects with no statistics. The cascade option given below does not make a difference as the GATHER EMPTY options generates for all objects without any statistics.

SQL> exec dbms_stats.gather_schema_stats(ownname => 'QP', estimate_percent => 5, -
> cascade => true, options => 'GATHER EMPTY');
PL/SQL procedure successfully completed.

e.g.: To identify a list of objects without any statistics.

orAP>declare
  2    l_owner       varchar2(30) := 'QP';
  3    l_emptylst    dbms_stats.objecttab;
  4  begin
  5    dbms_stats.gather_schema_stats(ownname => l_owner,
  6                options => 'LIST EMPTY', objlist => l_emptylst);
  7    for i in nvl(l_emptylst.first, 0) .. nvl(l_emptylst.last, 0) loop
  8      dbms_output.put_line(l_emptylst(i).objtype || '/' || l_emptylst(i).objname);
  9    end loop;
 10  end;
 11  /
INDEX/AM21_N1
TABLE/AM21

PL/SQL procedure successfully completed.

DBMS_STATS.GATHER_DATABASE_STATS

DBMS_STATS.GATHER_DATABASE_STATS gathers statistics for the complete database. In 8i, this will generate statistics for the SYS schema also. This has been rectified in Oracle 9i. For Oracle 8i, an alternative is to generate statistics for individual schemas or delete SYS schema statistics after generating statistics at the database level.

Call Syntax

 dbms_stats.gather_database_stats(estimate_percent, block_sample, method_opt, degree, granularity,
	cascade, stattab, statid, options, objlist, statown);