Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369

    Unanswered: Ways to identify tempspace usage from access plan

    The way I know is to look at SORT and TBSCAN above it and see if there is an increase in I/O.

    How else can I tell if the optimizer may need to use tempspace?


    Lots of tempspace is being used during query execution (static sql) and the access plan I have doesn't show any sort spills. Still need to get an explain for the query that is stored in the package (explain I have is for the same query that is stored in the package, but the query was run dynamically when db2exfmt was created). The only join method used is NLJOIN (no HSJOIN).

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I have the package name / section # so I'll get the access plan stored in the package and compare to the one generated when it's run dynamically. The sql is filling up 170GB tempspace filesystem during execution... I'll confirm and recheck everything, but I definetely see it's spilling to disk. If you know of a way to identify tempspace usage from the access plan (other then SORT - TBSCAN), please let me know.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There are a lot of things that can spill beside sorts (e.g. HSJOIN), but I doubt you'll see it in the plan because the query compiler does not care about that - I think it's the interpreter that handles overflows. In other words, there's no information about the temp space use during compilation, only during execution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •