Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Oct 28, 2003

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

By Amar Kumar Padhi



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);


Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM