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

Thread: DBMS_STATS vs. ANALYZE

  1. #1
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268

    Question

    Hi all,
    I am looking for papers about DBMS_STATS and ANALYZE comparison.
    Does anybody know the difference between the two (apart from parallel execution in DBMS_STATS) ?

    Thanks,
    R.

  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Simple difference
    Analyze is a command and dbms_stats is procedural.
    You can automate a lot of things with dbms stats.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Also:

    DBMS_STATS allows you transfer stats between servers to give consistant execution plans with differing data.

    DBMS_STATS allows you to gather system statistics (9i) to make the decision process more accurate. In 8i stats such as i/o, CPU and memory usage are estimated.

    Don't get hung up about it. I still use ANALYZE for on-off things. Alot of people still use DBMS_UTILITY.ANALYZE_SCHEMA. They all do essentially the same thing. You should consider moving to DBMS_STATS since it's Oracle's current recommendation.

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

  4. #4
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    Thanks for the notes,
    I know analyze is a command and dbms_stats is a packge.
    My question is:
    beside the desupport for ANALYZE command in the future, and automation in dbms_stats package, are there any major differences ? Why should i use dbms_stats ?

    Thanks.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Apart from the fact that there are tons of things that can be done only by using DBMS_STATS, if we concentrate only on the difference between ANALYZE TABLE(INDEX) versus DBMS_STATS.GATHER_TABLE(INDEX)_STATS:

    - when you are using ESTIMATE, with DBMS_STATS you can specify wheather you want the specified percent to be gathered by random row sampling or random block sampling; With ANALYZE you have no controll on how sampling for the ESTIMATE will be performed
    - on the other hand, with ANALYZE you can choose to estimate based on the specified number of rows, while with DBMS_STATS there is no such option - you can only specify it in a form of percent of total rows

    But neither of the above differences is not of major importance IMHO. The announcement that ANALYZE will be dessuported in the future on the other hand is enough strong argument alone to start getting used to it.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Sep 2002
    Posts
    411
    Could someone please tell me the instruction of how to use DBMS_STATS ???

    sorry, I never used this before.


  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Wink

    when was the last time you analyzed in parallel ?
    I'm stmontgo and I approve of this message

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by mike2000
    Could someone please tell me the instruction of how to use DBMS_STATS ???
    http://otn.oracle.com/docs/products/...eral.htm#11880
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Can we discuss the parallel aspect a little?

    I haven't played with the analyze-type procs from the package yet because my own ANALYZE procs work just fine for me so far

    I have my procs set up so they can be broken up into as many calls as you want that run at the same time. So, you can run 5 jobs, each one pulling from the same list of tables (but not overlapping).

    So, I can be, in this example, analyzing 5 indexes at the exact same time. Since, from my limited understanding, DBMS_STATS will not analyze an index in parallel, I don't see the proc that analyzes the whole schema as sufficient to replace my existing process. Although I could replace my ANALYZE TABLE calls with the the corresponding package calls, of course.

    Thoughts?

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I found that when I used the Parallel features of dbms_analyze it took longer than without parallel. I, too, wrote my own parallel analyze script where I analyze multiple objects (using dbms_stats) at the same time.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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