Article-ID: Note 130688.1
Circulation: PUBLISHED (EXTERNAL)
Folder: server.DBA.Monitoring
Topic: SQL Scripts
Title: Script: Report Statistics for a Table, its Columns and
Indexes with DBMS_STATS
Document-Type: SCRIPT
Impact: LOW
Skill-Level: NOVICE
Server-Version: 08.01.00 to 09.02.00
Updated-Date: 23-APR-2004 15:58:29
References:
Authors: ELENOC.FR
Attachments: NONE
Content-Type: TEXT/X-HTML
Keywords: DATA_COLLECT; DBPERF; EM; RDBMS;
Products: 0;
Platforms: GENERIC;
Abstract
This script creates a procedure which retrieves all the statistics for a table,
its analyzed columns and its analyzed indexes. This procedure can then be
executed to create an output file which gives results in a formatted report.
Product Name, Product Version
Oracle Server, 8.1 to 9.2.0
Platform Platform Independent
Date Created 15-Dec-2000
Instructions
Execution Environment:
SQL, SQL*Plus
Access Privileges:
Requires DBA access privileges to be executed.
Usage:
sqlplus user/
Instructions:
Copy the script to a file and execute it from SQL*Plus.
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.
Description
This program can be run in any supported RDBMS version from 08.01.XX
on any platform (not before as package SYS.DBMS_STATS did not exist).
The Oracle user which creates the procedures must have following
system privileges:
- select any table
- execute any procedure
- analyze any
The database in which the procedure is created must have been started
with utl_file_dir init.ora parameter set. See comments at the beginning
of the program for more details.
References
Script
rem Creation of procedure selstat_tab_all
rem This procedure retrieves all statistics for a table, all
rem it's analyzed columns and all it's analyzed indexed
rem Owner of the procedure must have select any table privilege
rem Owner of the procedure must have execute any privilege
rem Parameter file init.ora must have utl_file_parameter set
rem Change variable pl_file in this sample code to change
rem directory where output file will be created
rem
CREATE OR REPLACE PROCEDURE selstat_tab_all(in_table_owner VARCHAR2,
in_table_name VARCHAR2) IS
pl_file sys.utl_file.file_type ;
pl_line varchar2 (256);
pl_sqlerrm varchar2(132) ;
-- output variables for table
numrows number ;
numblks number ;
avgrlen number;
--
-- input variables for columns
CURSOR cur_selcolumns IS
Select column_name, decode(nullable,'N', ' (NOT NULL)','') not_null,
num_distinct, num_nulls
from dba_tab_columns
where owner = in_table_owner
and table_name = in_table_name ;
--
-- input variables for indexes
CURSOR cur_selindexes IS
Select owner, index_name
from dba_indexes
where table_owner = in_table_owner
and table_name = in_table_name
and numrows > 0 ;
--
CURSOR cur_selcolindexes(in_index_owner VARCHAR2, in_index_name VARCHAR2) IS
Select column_name
from dba_ind_columns
where index_owner = in_index_owner
and index_name = in_index_name
order by column_position ;
-- output variables for indexes
-- ind_numrows number ;
numlblks number ;
numdist number ;
avglblk number ;
avgdblk number ;
clstfct number ;
indlevel number ;
BEGIN
--
-- prepare output file
--
pl_file := sys.utl_file.fopen('c:\oracle\admin\adm', 'selstaball.log', 'w');
--
-- get table statistics
--
sys.dbms_stats.get_table_stats( in_table_owner, in_table_name,
numrows => numrows,numblks => numblks, avgrlen => avgrlen);
pl_line := 'Table '|| in_table_owner || '.' || in_table_name ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := '- Number of rows : '||numrows ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := '- Number of blocks : '||numblks ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := '- Average row length : '||avgrlen ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := '' ;
sys.utl_file.put_line(pl_file, pl_line );
--
-- get table colums statistics
--
FOR selcolumns IN cur_selcolumns LOOP
pl_line := ' Column ' || selcolumns.column_name || selcolumns.not_null ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := ' - Number of distinct values : ' || selcolumns.num_distinct ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := ' - Number of nulls : ' || selcolumns.num_nulls ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := '' ;
sys.utl_file.put_line(pl_file, pl_line );
END LOOP ;
--
-- get index statistics
--
FOR selindexes IN cur_selindexes LOOP
sys.dbms_stats.get_index_stats( selindexes.owner, selindexes.index_name,
numrows => numrows, numlblks => numlblks,numdist => numdist,
avglblk => avglblk,avgdblk => avgdblk,
clstfct => clstfct,indlevel => indlevel);
--
-- get index colums
--
pl_line := ' Index ' || selindexes.owner||'.'||selindexes.index_name ;
sys.utl_file.put_line(pl_file, pl_line );
FOR selcolindexes IN cur_selcolindexes(selindexes.owner,selindexes.index_name) LOOP
pl_line := ' Column ' ||selcolindexes.column_name ;
sys.utl_file.put_line(pl_file, pl_line );
END LOOP ;
pl_line := ' - Number of rows : '||numrows ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := ' - Number of blocks : '||numlblks ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := ' - Number of disctint values : '||numdist ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := ' - Average leaf blocks per key : '||avglblk ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := ' - Average data blocks per key : '||avgdblk ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := ' - Clustering Factor : '||clstfct ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := ' - Depth of tree : '||indlevel ;
sys.utl_file.put_line(pl_file, pl_line );
pl_line := '' ;
sys.utl_file.put_line(pl_file, pl_line );
END LOOP ;
sys.utl_file.fclose(pl_file) ;
EXCEPTION
WHEN sys.utl_file.INVALID_PATH THEN
pl_line := 'Invalid file name or file location' ;
sys.utl_file.put_line(pl_file, pl_line );
sys.utl_file.fclose(pl_file) ;
WHEN sys.utl_file.INVALID_MODE THEN
pl_line := 'open_mode parameter must be ''a'', ''w'' or ''r''' ;
sys.utl_file.put_line(pl_file, pl_line );
sys.utl_file.fclose(pl_file) ;
WHEN sys.utl_file.INVALID_OPERATION THEN
pl_line := 'File cannot be operated or opened as requested' ;
sys.utl_file.put_line(pl_file, pl_line );
sys.utl_file.fclose(pl_file) ;
WHEN OTHERS THEN
pl_sqlerrm := substr(SQLERRM, 1,132 ) ;
pl_line := 'Error : ' || pl_sqlerrm ;
sys.utl_file.put_line(pl_file, pl_line );
sys.utl_file.fclose(pl_file) ;
END;
/
==============
Sample Output:
==============
SQL > execute selstat_tab_all('CPTA', 'ECRITURES')
Content of output file :
Table CPTA.ECRITURES
- Number of rows : 2556
- Number of blocks : 41
- Average row length : 44
Column ID_ECR (NOT NULL)
- Number of distinct values : 2556
- Number of nulls : 0
Column ID_CPTE (NOT NULL)
- Number of distinct values : 12
- Number of nulls : 0
Column ID_SERVICE (NOT NULL)
- Number of distinct values : 51
- Number of nulls : 0
Column DATE_ECR (NOT NULL)
- Number of distinct values : 1155
- Number of nulls : 0
Column CREDIT
- Number of distinct values : 298
- Number of nulls : 2062
Column DEBIT
- Number of distinct values : 1201
- Number of nulls : 437
Column NO_PAIEMENT
- Number of distinct values : 445
- Number of nulls : 2111
Column ID_RELEVE
- Number of distinct values : 150
- Number of nulls : 56
Column ID_SCE_LETTR
- Number of distinct values : 4
- Number of nulls : 2550
Column DATE_LETTRAGE
- Number of distinct values : 751
- Number of nulls : 56
Column NO_PAIMT_LETTR
- Number of distinct values : 21
- Number of nulls : 2534
Column DATE_PREV_LETTR
- Number of distinct values : 95
- Number of nulls : 1544
Index CPTA.ECR_PK
Column ID_ECR
- Number of rows : 2556
- Number of blocks : 11
- Number of disctint values : 2556
- Average leaf blocks per key : 1
- Average data blocks per key : 1
- Clustering Factor : 661
- Depth of tree : 1
Index CPTA.NDX_ECR_ID_RELEVE
Column ID_RELEVE
- Number of rows : 2500
- Number of blocks : 11
- Number of disctint values : 150
- Average leaf blocks per key : 1
- Average data blocks per key : 2
- Clustering Factor : 360
- Depth of tree : 1