Solved

DBMS_STAT

Posted on 2002-06-06
8
3,615 Views
Last Modified: 2008-06-27
can any body provide me with a sample use of this package??
0
Comment
Question by:delphipal
8 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 50 total points
Comment Utility
Creation of 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.


Program Notes
-------------

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.


Program
-------


 - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -

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;
/

 - - - - - - - - - - - - - - - -  Code ends here  - - - - - - - - - - - - - - - -


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
.
Report Statistics for a Table, its columns and indexes with DBMS_STATS

Under 7.3.4 and 8.0X versions of Oracle RDBMS, write your own SQL SELECT
Statement to retrieve statistics.
 How can Oracle Enterprise Manager be used to retrieve statistics information ?
------------------------------------------------------------------------------

It is also possible to look at statistics on a particular table or index within
Oracle Enterprise Manager in Schema Manager.
If you edit a table or index, you can see following statistics in the Statistics
Tab :

  Last Analyzed               : LAST_ANALYZED
  Empty Blocks                : EMPTY_BLOCKS
  Average Space               : AVG_SPACE
  Number of rows              : NUM_ROWS
  Sample Size                 : SAMPLE_SIZE
  Average Row Length          : AVG_ROW_LEN
  Continued Row Count         : CHAIN_CNT
  Average Space Freelist Blks : AVG_SPACE_FREELIST_BLOCKS
  No. of Freelist Blks        : NUM_FREELIST_BLOCKS
0
 
LVL 22

Expert Comment

by:Helena Marková
Comment Utility
This is an example from Metalink:

Overview
--------

Creation of 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.

Program Notes
-------------

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.

Caution
-------

The sample program in this article is provided for educational purposes only and is NOT supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Program
-------
 - - - - - - - - - - - - - - - - Code begins here - - - - -
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;
/

 - - - - - - - - - - - - - - - -  Code ends here  - - - - -

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
0
 

Author Comment

by:delphipal
Comment Utility
Hai schwertner,henka

but this shows an error
identifier 'SYS.DBA_TAB_COLUMNS' must be declared
identifier 'SYS.DBA_INDEXES' must be declared
identifier 'SYS.DBA_IND_COLUMNS' must be declared

when i run the query in the cursor separately it runs properly?? can u guess why?

0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
You have to have SYSDBA privelege to run these scripts. User who do not have this priviege can not "see" the tables and views from the SYS schema.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:shyampaliyath
Comment Utility
hai schwertner,

i run this procedure from the  system/manager. i can able to select the DBA_TAB_COLUMNS but cannot run this procedure. system/manager has sysdba rights if not i couldn't select the DBA_TAB_COLUMNS .Am i right??

Thanks in advance
0
 
LVL 1

Expert Comment

by:shyampaliyath
Comment Utility
hai schwertner,

i get the same error as delphipal got.
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
It seems that you have to add also the right to run the procedures. May be you have not this privilege!
0
 
LVL 5

Expert Comment

by:jpkemp
Comment Utility
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept schwertner's comment as answer
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
jpkemp
EE Cleanup Volunteer
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now