Oracle Database - Statistics - Table

> Database > Oracle Database > Oracle Database - Optimizer Statistics

1 - About

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

3 - Privileges

grant EXECUTE ON DBMS_STATS TO hr;

4 - How to

4.1 - 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;
/

4.2 - 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)
Advertising

4.2.1 - 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;
/

4.2.2 - USER_TABLES

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

4.2.3 - _tab_statistics

SELECT * FROM  all_tab_statistics

4.3 - Set

SET_TABLE_STATS Procedure

4.4 - Delete

DELETE_TABLE_STATS Procedure

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

4.5 - 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;
/
Advertising

4.6 - 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
###############################################################################

4.7 - 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';

5 - Reference

db/oracle/statistics/table.txt · Last modified: 2017/09/06 19:29 by gerardnico