Table Statistics

Table Statistics

I am a newbie to Oracle.

I'm selecting all tables from my schema with owner 'PRASHANT'.
like this
          SELECT
               TABLE_NAME
          FROM
               ALL_TABLES
          WHERE
               owner = 'PRASHANT';

I need a PL/SQL code to process each table..estimate statistics and write statistics report in
another table.

Thanks in advance
LVL 4
prashant_n_mhatreAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ddaConnect With a Mentor Commented:
Also consider this SQL*Plus (not a PL/SQL) example:

set pagesize 0
set feedback off
spool an.sql
select 'analyze table ' || table_name || ' compute statistics;'
   from
      all_tables
   where
      owner = 'PRASHANT';
spool off
@@an.sql
0
 
stmontgoCommented:
dba_tab_columns keeps statistics info, is that what you are looking for?

u can use desc dbms_utility.analyze_schema to analyze all objects in a given schema.


SQL> desc dbms_utility.analyze_schema;
PROCEDURE dbms_utility.analyze_schema
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCHEMA                         VARCHAR2                IN
 METHOD                         VARCHAR2                IN
 ESTIMATE_ROWS                  NUMBER                  IN     DEFAULT
 ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
 METHOD_OPT                     VARCHAR2                IN     DEFAULT
0
 
dbalaskiCommented:
Depending upon your version --

While dbms_utility.analyze_schema  is good,   oracle recommends that you use the DBMS_STATS package for collecting statistics -- especially for use with the COST BASED OPTIMIZER  
(init.ora  parameter     optimizer=    set to either  CHOOSE,  FIRST_ROWS,  ALL_ROWS)...

This package will populate the base tables behind  the ALL_TABLES/DBA_TABLES views (and others as well)...     It's speed is much faster than the dbms_utility.analyze_schema  

You can read about it in the Oracle8i Supplied PL/SQL Packages Reference Release 2 (8.1.6)
chapter 56  DBMS_STATS  
as well as a few other manuals..

hope this helps,
dBalaski

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
prashant_n_mhatreAuthor Commented:
I have already figured out a solution. It is exactly similar to what 'dda' has proposed.

'dbms_utility.analyze_schema' doesn't work. It simply gives error.


0
 
ddaCommented:
Hmm.. Would you tell us what eror are you getting?
0
 
prashant_n_mhatreAuthor Commented:
sorry for not getting back for a long..

It says
SP2-0734: unknown command beginning "dbms_utili..."
0
 
ddaCommented:
Add 'exec' before 'dbms_utility':

SQL>exec dbms_utility.analyze_schema(....)
0
 
MoondancerCommented:
Please update this question if more is needed, or award th expert who helped you.  If a point split award is in order, let us know details.

Thank you,
Moondancer
Community Support Moderator @ Experts Exchange
0
 
prashant_n_mhatreAuthor Commented:
Please split points like:

dda : 30
stmontgo : 20
dbalaski : 20

Thanks Everybody !!!
0
 
MoondancerCommented:
Point split transactions have now been completed to award experts as follows.  Please comment in your respective
link below to complete.

dda : 30   Awarded in the primary question
Points for  stmontgo : 20   http://www.experts-exchange.com/jsp/qManageQuestion.jsp?qid=20164785
Points for  dbalaski : 20    http://www.experts-exchange.com/jsp/qManageQuestion.jsp?qid=20164787

Thank you,

Mondancer
Community Support Moderator @ Experts Exchange
0
All Courses

From novice to tech pro — start learning today.