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