Analyze V/S Dbms_stats
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Analyze V/S Dbms_stats

  1. #1
    Join Date
    Nov 2005
    Posts
    32

    Analyze V/S Dbms_stats

    hi

    for optimizer mode-'choose' to have current statistics for tables and indexes which of the two is best ' analyze table (compute/estimate) or dbms_stats.gather_table_stats' whether analyze table (compute/estimate ) gather the statistics for optimizer mode 'choose'.The cost of executing the statement is same in 'plan_table'

    i am working in 7.3.4 , 8.1.7 ,9.2
    guide me for 7.3.4 , 8.1.7 ,9.2



    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    dbms_stats in 8.1.7 / 9.2 as it collects more information than a simple analyze does

    Not sure about 7.3.4

  3. #3
    Join Date
    Nov 2005
    Posts
    32
    does optimizer mode 'choose' use any statistics generated by analyze table/index

    in 7.3.4,8.1.7 and 9.2

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    If the optimizer mode is choose and the table has stats, the CBO will be used

    It 'chooses' which optimizer method to use

  5. #5
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by gurbirbhatia
    hi

    for optimizer mode-'choose' to have current statistics for tables and indexes which of the two is best ' analyze table (compute/estimate) or dbms_stats.gather_table_stats' whether analyze table (compute/estimate ) gather the statistics for optimizer mode 'choose'.The cost of executing the statement is same in 'plan_table'

    i am working in 7.3.4 , 8.1.7 ,9.2
    guide me for 7.3.4 , 8.1.7 ,9.2

    thanks
    DBMS_STATS package can gather global statistics at multiple levels as specified by the granularity parameter.
    ANALYZE command collects statistics only at the lowest level.

    Oracle recommends to use DBMS_STATS mainly to collect optimizer statistics in 8i and 9i. For 8i and below use analyze.
    Remember to monitor the table to keep the stats upto date.
    "What is past is PROLOGUE"

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can use dbms_utility.analyze_schema procedure in Oracle 7.3

    Tamil

  7. #7
    Join Date
    Nov 2005
    Posts
    32
    Thanks tamilselvan

    is there difference b/t analyze table and dbms_utility.analyze_schema procedure in Oracle 7.3
    can i used this procedure for both tables and indexes




    Thanks

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