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

9.4.4) Retrieveing statistics with DBMS_STATS

DBMS_STATS.GET_TABLE_STATS

Get table statistics.

Call syntax

dbms_stats.get_table_stats(ownname, tabname, 
	partname, stattab, statid, numrows,
	numblks, avgrlen, statown);

e.g.: getting table statistics data.

SQL> declare
  2    l_numrows  number;
  3    l_numblks  number;
  4    l_avgrlen  number;
  5  begin
  6    dbms_stats.get_table_stats(ownname => 'SYS', tabname => 'AM01', 
  7                       numrows => l_numrows, numblks => l_numblks, avgrlen => l_avgrlen);
  8    dbms_output.put_line('No. of rows: ' || l_numrows);
  9    dbms_output.put_line('No. of blks: ' || l_numblks);
 10    dbms_output.put_line('Avg row length: ' || l_avgrlen);
 11  end;
 12  /
No. of rows: 4106860
No. of blks: 6219
Avg row length: 3

PL/SQL procedure successfully completed.

DBMS_STATS.GET_COLUMN_STATS

Get column statistics present in the dictionary.

Call syntax

dbms_stats.get_column_stats(ownname, tabname, colname, partname, stattab, statid, 
           distcnt, density, nullcnt, srec, avgclen, statown);

e.g.: getting statistics for a column.

SQL> declare
  2    l_distcnt number;
  3    l_density number;
  4    l_nullcnt number;
  5    l_srec   dbms_stats.statrec;
  6    l_avgclen number;
  7  begin
  8    dbms_stats.get_column_stats(ownname => 'SYS', tabname => 'AM01',
  9               colname => 'COL1', distcnt => l_distcnt, density => l_density,
 10               nullcnt => l_nullcnt, srec => l_srec, avgclen => l_avgclen);
 11    dbms_output.put_line('No. of distinct values: ' || l_distcnt);
 12    dbms_output.put_line('Density: ' || l_density);
 13    dbms_output.put_line('Count of nulls: ' || l_nullcnt);
 14    dbms_output.put_line('Avg. column length: ' || l_avgclen);
 15  end;
 16  /
No. of distinct values: 2
Density: .5
Count of nulls: 0
Avg. column length: 3

PL/SQL procedure successfully completed.

DBMS_STATS.GET_INDEX_STATS

Get index statistics.

Call syntax

dbms_stats.get_index_stats(ownname, indname, partname, stattab, statid, 
		numrows, numlblks, numdist, avglblk, avgdblk, 
		clstfct, indlevel, statown);

e.g.: getting an index statistics.

SQL> declare
  2    l_numrows   number;
  3    l_numlblks  number;
  4    l_numdist   number;
  5    l_avglblk   number;
  6    l_avgdblk   number;
  7    l_clstfct   number;
  8    l_indlevel  number;
  9  begin
 10    dbms_stats.get_index_stats(ownname => 'SYS', indname => 'AM01_N1',
 11                   numrows => l_numrows, numlblks => l_numlblks,
 12                   numdist => l_numdist, avglblk => l_avglblk,
 13                   avgdblk => l_avgdblk, clstfct => l_clstfct,
 14                   indlevel => l_indlevel);
 15    dbms_output.put_line('No. of rows: ' || l_numrows);
 16    dbms_output.put_line('No. of blks: ' || l_numlblks);
 17    dbms_output.put_line('No. of distinct values: ' || l_numdist);
 18    dbms_output.put_line('Avg leaf blocks for distinct keys: ' || l_avglblk);
 19    dbms_output.put_line('Avg data blocks pointed to in the table: ' || l_avgdblk);
 20    dbms_output.put_line('Clustering factor: ' || l_clstfct);
 21    dbms_output.put_line('Index height: ' || l_indlevel);
 22  end;
 23  /
No. of rows: 3819711
No. of blks: 11092
No. of distinct values: 1
Avg leaf blocks for distinct keys: 11092
Avg data blocks pointed to in the table: 14616
Clustering factor: 14616
Index height: 2

PL/SQL procedure successfully completed.