Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Location
    VA
    Posts
    14

    Lightbulb Unanswered: Temp Tablespace hit 100%

    We have a job that joins seven tables with GROUP BY and ORDER BY clauses. Database is not big ( < 100 GB) . After running for about 15 mins, the job will fail due to disk full error reported in the temp tablespace which is system-managed. We are using DB2 v9.5 on SUSE Linux.

    I did a snapshot on the tables and noticed one of the static tables which contains < 20 records has the rows_read returns 1321419183. Overflow_accesses returns 0 across all tables. Why such a small table will have so high rows_read count? Tried RUNSTATS, REORG and BIND but did not help. Also, added more indexes on the joined columns. The Temp space did not get filled up as quickly but eventually still hit 100% full.

    For SELECT queries, is setting optimization level higher, such as 7 would be beneficial as opposed to using the default setting which is 5?

    In DB2, do the positions of the small table and large table make any difference?

    E.G. SELECT *
    FROM tableA, tableB
    WHERE
    tableA.col1 = tableB.col1

    OR

    SELECT *
    FROM tableB, tableA
    WHERE
    tableB.col1 = tableA.col1


    Any recommendations or suggestions would be greatly appreciated.
    Thanks in advance for your assistance.

    Clara

  2. #2
    Join Date
    Oct 2007
    Posts
    246
    clara use the optimitization level of query with in 5 like use 1 to 4 and chk.
    chk for any sorting is taking place or is the query is using the proper indexes mentioned
    &
    did u reset ur monitor switches before taking table snapshot tht could contain history of rows read till ur db has recycled.

    Regds
    Paul

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Feed your query into db2advis. 1 extra index can solve everything. No guarantee, but worth investigating.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    GROUP BY and ORDER BY cause sorts, which, if the sorted sets are large enough, can spill to temporary tables. You will need to look at the query explain plan, identify these large sorts, and try to avoid them or move them to a later stage in the plan.

    There are other things that can cause sort spills as well, which will be evident from the explain plan. You should always start query tuning with looking at its plan.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You could, also, put your query on here for suggestions.
    Dave

  6. #6
    Join Date
    Jan 2004
    Location
    VA
    Posts
    14
    Thank you all for your suggestions. Appreciate your help on this...

    I ran db2advis on the problem query and added recommended indexes but still got the same disk full error.

    Did the explain plan as well. What areas should I look out for to identify causes of sort spills?

    Yes, you are right. After restarting the instance, the table snapshot was reset. The latest table snapshot looks clean.

    I wish I could just copy and paste the query here but unfortunately, I am not given the permission to do so

    FYI ... the optimization level is currently set at 3.

    Thanks again,
    Clara

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by clara View Post
    I ran db2advis on the problem query and added recommended indexes but still got the same disk full error.
    Did you restart the system or perform a "flush package cache" after the creation of the index? If not it could be that the new index was ignored and the previous access-path was chosen again
    Quote Originally Posted by clara View Post
    FYI ... the optimization level is currently set at 3.
    Too low. the default is 5 which is good.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by clara View Post
    What areas should I look out for to identify causes of sort spills?
    Code:
    Statement:
    
      select tabname, count(colname)
      from syscat.columns
      group by tabname
      order by 2 desc
    
    
    Section Code Page = 1208
    
    Estimated Cost = 243.215973
    Estimated Cardinality = 928.000000
    
    Access Table Name = SYSIBM.SYSCOLUMNS  ID = 0,6
    |  Index Scan:  Name = SYSIBM.INDCOLUMNS01  ID = 1
    |  |  Regular Index (Not Clustered)
    |  |  Index Columns:
    |  |  |  1: TBCREATOR (Ascending)
    |  |  |  2: TBNAME (Ascending)
    |  |  |  3: NAME (Ascending)
    |  #Columns = 1
    |  #Key Columns = 0
    |  |  Start Key: Beginning of Index
    |  |  Stop Key: End of Index
    |  Index-Only Access
    |  Index Prefetch: Eligible 31
    |  Lock Intents
    |  |  Table: Intent Share
    |  |  Row  : Next Key Share
    |  Sargable Index Predicate(s)
    |  |  Insert Into Sorted Temp Table  ID = t1
    |  |  |  #Columns = 2
    |  |  |  #Sort Key Columns = 1
    |  |  |  |  Key 1: TBNAME (Ascending)
    |  |  |  Sortheap Allocation Parameters:
    |  |  |  |  #Rows     = 928.000000
    |  |  |  |  Row Width = 28
    |  |  |  Piped
    |  |  |  Buffered Partial Aggregation
    Sorted Temp Table Completion  ID = t1
    Access Temp Table  ID = t1
    |  #Columns = 2
    |  Relation Scan
    |  |  Prefetch: Eligible
    Final Aggregation
    |  Group By
    |  Column Function(s)
    Insert Into Sorted Temp Table  ID = t2
    |  #Columns = 2
    |  #Sort Key Columns = 1
    |  |  Key 1: (Descending)
    |  Sortheap Allocation Parameters:
    |  |  #Rows     = 928.000000
    |  |  Row Width = 24
    |  Piped
    Access Temp Table  ID = t2
    |  #Columns = 2
    |  Relation Scan
    |  |  Prefetch: Eligible
    |  Sargable Predicate(s)
    |  |  Return Data to Application
    |  |  |  #Columns = 2
    Return Data Completion
    
    End of section

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Quote Originally Posted by clara View Post
    I ran db2advis on the problem query and added recommended indexes
    Check if you've done runstats on them

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    we can do all the guessing and suggestions you want, but I am betting your SQL is the real culprit. change the names of the columns if you want and post. Have never understood why the SQL itself is secret with some folks out there, when its the data you need to protect.
    You need to limit in someway the amount of data in at least one of those sorts or give the system more disk for the temp space.
    Dave

  11. #11
    Join Date
    Jan 2004
    Location
    VA
    Posts
    14
    Yes, I did runstats. Setting the optimization level to 5 helped and we no longer got the disk full error. Thank you so much for all your valuable inputs!

    Regards,
    Clara

Posting Permissions

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