?
Solved

DBMS_STAT

Posted on 2002-06-06
8
Medium Priority
?
3,623 Views
Last Modified: 2008-06-27
can any body provide me with a sample use of this package??
0
Comment
Question by:delphipal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 200 total points
ID: 7058983
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á
ID: 7058998
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
ID: 7061085
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 48

Expert Comment

by:schwertner
ID: 7061235
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
 
LVL 1

Expert Comment

by:shyampaliyath
ID: 7061616
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
ID: 7061627
hai schwertner,

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

Expert Comment

by:schwertner
ID: 7061718
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
ID: 9025108
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

762 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