GET_TABLE_STATS procedure - Retrieves table statistics

The GET_TABLE_STATS procedure retrieves table statistics from the system catalog or from specified statistic tables.

Syntax

Parameters

ownname: An input argument of type VARCHAR(128) that specifies the schema of the table.; The argument is case-sensitive.
tabname: An input argument of type VARCHAR(128) that specifies the name of the table.; The argument is case-sensitive.
partname: An input argument of type VARCHAR(128) that specifies the name of table partition.
stattab: An input argument of type VARCHAR(128) that specifies the identifier of the table where the current user statistics are to be saved.; The default value is NULL.; The argument is case-sensitive.
statid: An input argument of type VARCHAR(128) that specifies the identifier with which the statistics within stattab are associated.
numrows: An output argument of type INTEGER that indicates the number of rows in the table.
numblks: An output argument of type INTEGER that indicates the number of pages that the table occupies.
avgrlen: An output argument of type INTEGER that indicates the average row length for the table.
statown: An input argument of type VARCHAR(128) that specifies the schema that contains the stattab input argument.; The default value is NULL.; The argument is case-sensitive.
cachedblk: An output argument of type INTEGER that indicates the average number of pages in the buffer pool for the object.
cachehit: An output argument of type INTEGER that indicates the average cache hit ratio for the object.

Authorization

EXECUTE privilege on the DBMS_STATS module.

Example 1

BEGIN
DECLARE v_numrows INTEGER;
DECLARE v_numblks INTEGER;
DECLARE v_avgrlen INTEGER;
DECLARE v_cachedblk INTEGER;
DECLARE v_cachehit INTEGER;
CALL DBMS_STATS.GET_TABLE_STATS(
CURRENT SCHEMA,
'TEST_TABLE_STATS',
NUMROWS=> v_numrows,
NUMBLKS=> v_numblks,
AVGRLEN=> v_avgrlen,
CACHEDBLK=> v_cachedblk,
CACHEHIT=> v_cachehit);
CALL DBMS_OUTPUT.PUT_LINE('NUMROWS: ' || v_numrows);
CALL DBMS_OUTPUT.PUT_LINE('NUMBLKS: ' || v_numblks);
CALL DBMS_OUTPUT.PUT_LINE('AVGRLEN: ' || v_avgrlen);
CALL DBMS_OUTPUT.PUT_LINE('CACHEDBLK: ' || v_cachedblk);
CALL DBMS_OUTPUT.PUT_LINE('CACHEHIT: ' || v_cachehit);
END;

DB20000I  The SQL command completed successfully.

NUMROWS: 6
NUMBLKS: 2
AVGRLEN: 17
CACHEDBLK: 2
CACHEHIT: 100

Example 2

Note: When the STATTAB parameter is not null, the table statistics are fetched from the STATSTAB table.

BEGIN
DECLARE v_numrows INTEGER;
DECLARE v_numblks INTEGER;
DECLARE v_avgrlen INTEGER;
DECLARE v_cachedblk INTEGER;
DECLARE v_cachehit INTEGER;
CALL DBMS_STATS.GET_TABLE_STATS(CURRENT SCHEMA,
'TEST_TABLE_STATS',
'PART0',
NUMROWS=> v_numrows,
NUMBLKS=> v_numblks,
AVGRLEN=> v_avgrlen,
CACHEDBLK=> v_cachedblk,
CACHEHIT=> v_cachehit,
STATTAB => 'STATSTAB',
STATOWN => CURRENT SCHEMA,
STATID => 'TABLE2_STAT');
CALL DBMS_OUTPUT.PUT_LINE('NUMROWS: ' || v_numrows);
CALL DBMS_OUTPUT.PUT_LINE('NUMBLKS: ' || v_numblks);
CALL DBMS_OUTPUT.PUT_LINE('AVGRLEN: ' || v_avgrlen);
CALL DBMS_OUTPUT.PUT_LINE('CACHEDBLK: ' || v_cachedblk);
CALL DBMS_OUTPUT.PUT_LINE('CACHEHIT: ' || v_cachehit);
END;

DB20000I  The SQL command completed successfully.

NUMROWS: 3
NUMBLKS: 1
AVGRLEN: 17
CACHEDBLK:
CACHEHIT: