About
- Number of rows
- Number of blocks
- Average row length
- Last Analyzed
Articles Related
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;
/
- In an external stattab table:
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';