Table of Contents

About

  • Number of rows
  • Number of blocks
  • Average row length
  • Last Analyzed

Privileges

grant execute on DBMS_STATS to hr;

How to

Gather

See the GATHER_TABLE_STATS Procedure for a full description of the parameters

  • In the data dictionary
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS( NULL, tabname=>'DEPARTMENTS',
  estimate_percent=>100 );
END;
/

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS( NULL, tabname=>'DEPARTMENTS',
  estimate_percent=>100, stattab=>'STATTAB', statid=>'myFirstStats' );
END;
/

Get

  • numrows: Number of rows in the table (partition)
  • numblks: Number of blocks the table (partition) occupies
  • avgrlen: Average row length for the table (partition)
  • cachedblk: The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition)
  • cachehit: The average cache hit ratio for the segment (index/table/index partition/table partition)

GET_TABLE_STATS

With the GET_TABLE_STATS procedure: 

DECLARE
   numrows NUMBER; 
   numblks NUMBER;
   avgrlen NUMBER;
   cachedblk   NUMBER;
   cachehit    NUMBER;
   no_statistics	exception;
   PRAGMA EXCEPTION_INIT(no_statistics, -20000);
BEGIN
	
   DBMS_STATS.GET_TABLE_STATS (
   ownname=>null,
   tabname=>'DEPARTMENTS', 
   numrows=>numrows, 
   numblks=>numblks,
   avgrlen=>avgrlen,
   cachedblk=>cachedblk,
   cachehit=>cachehit);
   
   DBMS_OUTPUT.PUT_LINE( '');
   DBMS_OUTPUT.PUT_LINE( 'Statistics with the DBMS_STATS.GET_TABLE_STATS:');
   DBMS_OUTPUT.PUT_LINE( '-----------------------------------------------');
   DBMS_OUTPUT.PUT_LINE( 'numrows:    ' || numrows );
   DBMS_OUTPUT.PUT_LINE( 'numblks:    ' || numblks );
   DBMS_OUTPUT.PUT_LINE( 'avgrlen:    ' || avgrlen );
   DBMS_OUTPUT.PUT_LINE( 'cachedblk:  ' || cachedblk );
   DBMS_OUTPUT.PUT_LINE( 'cachehit:   ' || cachehit );
   
   EXCEPTION
		WHEN no_statistics THEN
			DBMS_OUTPUT.PUT_LINE( 'No Statistics for this table. No Statistics have been gathered ?');
			DBMS_OUTPUT.PUT_LINE( 'ORA-20000: Unable to get values for table DEPARTMENTS');
END;
/

USER_TABLES

SELECT
  NUM_ROWS,
  BLOCKS,
  AVG_ROW_LEN,
  SAMPLE_SIZE,
  LAST_ANALYZED
FROM
  user_tables
WHERE
  table_name LIKE 'DEPARTMENTS';

_tab_statistics

select * from  all_tab_statistics

Set

SET_TABLE_STATS Procedure

Delete

DELETE_TABLE_STATS Procedure 

BEGIN
  DBMS_STATS.DELETE_TABLE_STATS(null,'DEPARTMENTS');
END;
/

Preference / Configuration

Create a Oracle Database - Statistics - stattab (Statistics Table) 

BEGIN
   DBMS_STATS.CREATE_STAT_TABLE(NULL,'tables_prefs');  
   DBMS_STATS.EXPORT_TABLE_PREFS('SH', 'SALES',stattab => 'STAT');
END;
/

Compare

You compare the table statistics with the following diff_table_stats table function: (ie you get also the column statistics)

Function Description
DIFF_TABLE_STATS_IN_HISTORY Compares statistics for a table from two timestamps in past and compare the statistics as of that timestamps
DIFF_TABLE_STATS_IN_PENDING Compares pending statistics and statistics as of a timestamp or statistics from dictionary
DIFF_TABLE_STATS_IN_STATTAB Compares statistics for a table from two different sources (STATTAB)

Example: 

SELECT
  *
FROM
  TABLE( DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB( NULL, 'DEPARTMENTS', stattab1
  =>'STATTAB', stattab2=>'STATTAB', statid1=>'myFirstStats', statid2=>
  'myFirstStats2' ) );

REPORT                                                                           MAXDIFFPCT
-------------------------------------------------------------------------------- ----------
###############################################################################  null

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : DEPARTMENTS
OWNER         : HR
SOURCE A      : User statistics table STATTAB
              : Statid     : MYFIRSTSTATS
              : Owner      : HR
SOURCE B      : User statistics table STATTAB
              : Statid     : MYFIRSTSTATS2
              : Owner      : HR
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

DEPARTMENTS                 T   A   NO_STATS
                                B   27         5          21         27
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

DEPARTMENT_ID   A   NO_STATS
                B   27      .037037037 NO   0       4    C10B  C2034 27
DEPARTMENT_NAME A   NO_STATS
                B   27      .037037037 NO   0       12   41636 54726 27
LOCATION_ID     A   NO_STATS
                B   7       .018518518 YES  0       3    C20F  C21C  27
MANAGER_ID      A   NO_STATS
                B   11      .090909090 NO   16      3    C202  C2030 11
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
...............................................................................


                                INDEX: DEPT_ID_PK
                                .................

DEPT_ID_PK      I   A   NO_STATS
                    B   27      1       27      1     1     1       0   27

                             INDEX: DEPT_LOCATION_IX
                             .......................

DEPT_LOCATION_I I   A   NO_STATS
                    B   27      1       7       1     1     1       0   27
###############################################################################

Lock

execute DBMS_STATS.LOCK_TABLE_STATS('SCHEMA','TABLE_TEST');
execute DBMS_STATS.UNLOCK_TABLE_STATS('SCHEMA','TABLE_TEST');
  • The stattype_locked column gives the lock status
select owner, table_name, stattype_locked from dba_tab_statistics where table_name = 'TABLE_TEST';

Reference