Estimate temp space before running sql?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Estimate temp space before running sql?

  1. #1
    Join Date
    Oct 2002
    Posts
    807

    Estimate temp space before running sql?

    What's a good way to go about estimating the amount the temp space required by a sql (with a bunch of DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS etc) before running it?

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    In 9i, it is simple.

    You can now estimate Sort Area Size (in temp tablespace) needed by a Query that has group by (or order by ) clause.

    delete from plan_table ;

    explain plan for
    select row_id, created_by, conflict_id, x_profile_ind, x_segment, count(*)
    from s_org_ext
    group by row_id, created_by , conflict_id, x_profile_ind, x_segment ;

    select * from table( dbms_xplan.display );

    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------
    ---------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
    ---------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 11M| 222M| | 156K|
    | 1 | SORT GROUP BY | | 11M| 222M| 766M| 156K|
    | 2 | TABLE ACCESS FULL | S_ORG_EXT | 11M| 222M| | 108K|
    ----------------------------------------------------------------------------------------------------

    From the plan, I come to know that 766M temp space is required.

    Tamil

  3. #3
    Join Date
    Oct 2002
    Posts
    807
    Thanks Tamilselvan. I'd also be interested in solutions for 8i, 7.3.4 (if available..that is). Just wondering how to go about making an 'educated guess' actually.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Practically, I do not estimate temp space requirement before I execute a statement. I usually configure 20 GB disk space for the temp tablespace, no matter how much is used by all sessions.

    Still you use 7.3.4 !!!!
    I have Oracle experience from Rel 5 to 9i.
    To me Release 7.3.4 is the best Oracle Release I ever worked.

    To answer your question, you can't do it in 8i, or 7.3.4. Period.

    Tamil

  5. #5
    Join Date
    Oct 2002
    Posts
    807
    Thanks for the response. We are an ASP/hosters and host databases for a bunch of corporations. Sadly, some are still on 7.3.4, 8i.

    Practically, I do not estimate temp space requirement before I execute a statement. I usually configure 20 GB disk space for the temp tablespace, no matter how much is used by all sessions.
    How about 'while' executing the transaction (in either 8i or 7.3.4)? I have a 7.3.4 customer trying to implement some new reporting tool..and is trying to execute a statement (that runs a few pages) across some HUGE tables. The guy ends up blowing the temp space time and again, as expected. I am reluctant to allocate any more space to TEMP, coz in all probability he'll blow it, no matter what.

    I'm having to make do with very limited space on this machine. I'd like to make an educated guess on the amount of space the transaction will require, before asking him to cough up more $$ to buy additional disk, or simply not run the transaction.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ========
    How about 'while' executing the transaction (in either 8i or 7.3.4)? I have a 7.3.4 customer trying to implement some new reporting tool..and is trying to execute a statement (that runs a few pages) across some HUGE tables. The guy ends up blowing the temp space time and again, as expected. I am reluctant to allocate any more space to TEMP, coz in all probability he'll blow it, no matter what
    =======

    You can't do anything. Let the application fail.

    This happened frequently in every project I handled. Basically the SQL needs to be tuned or the amount of rows returned to the client should be restricted. If the app team is not changing the code, you don't have any option.

    Or you can give some quota on temp space to the particular user. This will stop db crash.

    Tamil

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