DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: DBMS_STATS package

  1. #1
    Join Date
    Feb 2006
    Posts
    7

    DBMS_STATS package

    Hi!

    I'm using DBMS_STATS in my DB.

    Some tables are very huge, because of which the TEMP space is never enough & the stats do not complete. So part of my DB is analyzed & part isn't.

    I need to know if there is a procedure or such, that I can use to pass the table names to dbms_stats, so I can specify which tables to analyze & which to skip.

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    gather_table_stats

    or make TEMP bigger

  3. #3
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Are you computing or estimating stats?
    Assistance is Futile...

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    Try:

    Code:
    EXEC DBMS_STATS.gather_table_stats('my_schema', 'my_table', estimate_percent => 10, cascade => TRUE);
    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  5. #5
    Join Date
    Feb 2006
    Posts
    7
    I'm computing stats. TEMP is 20GB. Isn't that too much already?

  6. #6
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    how about...
    analyze table EMP compute statistics?
    Is my answer too far and wild...
    Behind The Success And Failure Of A Man Is A Woman

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Quote Originally Posted by oradba88
    I'm computing stats. TEMP is 20GB. Isn't that too much already?
    I dont' think you need to COMPUTE statistics for a large table rather use ESTIMATE with 10% - 20%. See the syntax from Tamil, how to ESTIMATE Statistics. I felt 20GB Temp space is something pretty big.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    He is Tim not Tamil
    mine is simplier...analyze table estimate statistics;
    I'm the woman behind you.
    Last edited by yxez; 10-04-2006 at 04:26 AM.
    Behind The Success And Failure Of A Man Is A Woman

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Quote Originally Posted by yxez
    He is Tim not Tamil
    mine is simplier...analyze table estimate statistics;
    I'm the woman behind you.
    thats a really crap way of doing it which should be outlawed

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by Thomasps
    I dont' think you need to COMPUTE statistics for a large table rather use ESTIMATE with 10% - 20%. See the syntax from Tamil, how to ESTIMATE Statistics. I felt 20GB Temp space is something pretty big.
    Even 10-20% may be over doing it. For many tables you'll get good statistics with 1-5%
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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