DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: COMPUTE statement performance

  1. #1
    Join Date
    Mar 2002
    Posts
    10

    Question

    How is the performanced of the Orcle "COMPUTE" statement? My table consists of 1mill records and would like to know if this will slow down other processes going on on the db and if it will take 15mins, 30mis or how long.

    Thanks.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sure, it takes resources, it will slow other things down. How much it will slow things down depends on what type of transactions you have, how much your temp space is used, how your physical structure is laid out, how much box you have, etc., etc.

    I've computed statisics on a 1M row 2 column table on a Sun E5500 with 6x400Mhz processors, 6G RAM, 12 FC connected 0+1 filesystems in about 2 minutes.

    I've computed statistics on a 1M row 120 column table on a Dell 1x233Mhz processor with 128M RAM and 1 5400RPM 6.4G disk in 2 days.
    Jeff Hunter

  3. #3
    Join Date
    Jan 2002
    Posts
    148
    if u have time constraint then why dont u make use of dbms_stats package.

    you may want to fire dbms_stats package for that table with just 10 percent stats and parallel clause to around 4-8 ( if u have ).

    then it should come out pretty fast over simple serial compute command.

    ** dbms_stats starts with 8i i guess
    Thanx
    Jr.

  4. #4
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    If you mean the SQL*PLUS Compute (rather than the analyze compute) the overhead is negligible. It depends more on the efficiency of the SQL statement.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  5. #5
    Join Date
    Jan 2002
    Posts
    148
    Originally posted by jrpm
    If you mean the SQL*PLUS Compute (rather than the analyze compute) the overhead is negligible. It depends more on the efficiency of the SQL statement.
    Sorry about that.
    i mean compute in 'Analyze...Compute' syntax , and i guess
    the question was not related to Statistics collection....
    really sorry about this.



    Jr.

    [Edited by Jr on 03-26-2002 at 02:05 PM]

  6. #6
    Join Date
    Mar 2002
    Posts
    10
    Originally posted by Jr
    Originally posted by jrpm
    If you mean the SQL*PLUS Compute (rather than the analyze compute) the overhead is negligible. It depends more on the efficiency of the SQL statement.
    Sorry about that.
    i mean compute in 'Analyze...Compute' syntax , and i guess
    the question was not related to Statistics collection....
    really sorry about this.



    Jr.

    [Edited by Jr on 03-26-2002 at 02:05 PM]
    One more thing. To calculate the min, max, std, mean...from table1 can I use the following statement:

    ANALYZE TABLE1 COMPUTE STATISTICS

    Not sure what this will give me as I don't have dba privilages.
    Thanks.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    ANALYZE TABLE ... COMPUTE STATISTICS gives the optimizer statistics about the behaviour of the data in the table. It has nothing to do with SQL Functions used to calculate statistical values. See http://technet.oracle.com/docs/produ...ion.htm#998166 for instructions on how to use those functions.
    Jeff Hunter

  8. #8
    Join Date
    Mar 2002
    Posts
    10
    Originally posted by marist89
    ANALYZE TABLE ... COMPUTE STATISTICS gives the optimizer statistics about the behaviour of the data in the table. It has nothing to do with SQL Functions used to calculate statistical values. See http://technet.oracle.com/docs/produ...ion.htm#998166 for instructions on how to use those functions.
    The problem with this is that it is too time consuming. I need a quick way to get the stats.

  9. #9
    Join Date
    Sep 2000
    Posts
    96
    To save some time, why don'tyou use the ANALYZE.....ESTIMATE STATISTICS instead to get the optimize statistics that you are looking for.

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well you can set up incremental statistics gathering, just add monitor attributes to the table you want and schedule a job to gather statistics with dbms_stats, this way you dont have to analyze the whole table (only the first time)

    the other way is set a large sort_area_size in your analyze session to speed up

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