DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: analyze compute stats

  1. #1
    Join Date
    Feb 2007
    Posts
    212

    analyze compute stats

    Hi Friends,

    I forgot the syntax on how to analyze and compute stats on all tables/indexes
    in one's schema. Can u help pls ....thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    look up dbms_stats

  3. #3
    Join Date
    Jan 2007
    Posts
    231
    there is no syntax to forget..

    you can use dbms_utility package..

    Eg.,

    EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');
    EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_rows => 100);
    EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by jennifer2007
    I forgot the syntax on how to analyze and compute stats on all tables/indexes
    You forgot to mention you also forgot the syntax on how to google or metalink it
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Quote Originally Posted by ams-jamali
    there is no syntax to forget..

    you can use dbms_utility package..

    Eg.,

    EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');
    EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_rows => 100);
    EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);
    ugh no way should you use dbms_utility, not at all

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by ams-jamali
    EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');
    Ugh, dbms_utility.

    I feel more secure in my job today.
    Jeff Hunter

  7. #7
    Join Date
    Jan 2007
    Posts
    231
    Dave..,

    can you please explain(if it is not secure) why i should not use dbms.utility package to find entire schema stats.
    (by god's graze till now i have used only on testDB but not in prod)
    Last edited by ams-jamali; 05-31-2007 at 07:21 AM. Reason: increase details

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    DBMS_UTILITY.ANALYZE_SCHEMA got desupported in Ora9i
    The only supported way to generate stats in Ora9i and upper is DBMS_STATS
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Jan 2007
    Posts
    231
    only now i came to know it is desupported.

    Thanks PavB

  10. #10
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    To add more points for clarity:

    DBMS_STATS was available from 8i onwards apart from ANALYZE/DBMS_UTILITY. Oracle recommended to use DBMS_STATS as this collects statistics globally and more accurate and also the ability to parallelise statistics collection. These are not possible in ANALYZE and it collects statistics at the lowest level.

    Optimizer uses global statistics to gather access path.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width