Temp Tablespace hit 100%

clara · #1 (**permalink**) 12-02-10, 23:31

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

Mathew_paul · #2 (**permalink**) 12-03-10, 03:02

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

dr_te_z · #3 (**permalink**) 12-03-10, 03:58

Feed your query into db2advis. 1 extra index can solve everything. No guarantee, but worth investigating.

n_i · #4 (**permalink**) 12-03-10, 09:03

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.

dav1mo · #5 (**permalink**) 12-03-10, 09:43

You could, also, put your query on here for suggestions.
Dave

clara · #6 (**permalink**) 12-03-10, 12:03

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

dr_te_z · #7 (**permalink**) 12-03-10, 12:49

Quote:

Originally Posted by clara

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

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

Too low. the default is 5 which is good.

n_i · #8 (**permalink**) 12-03-10, 13:12

Quote:

Originally Posted by clara

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

db2girl · #9 (**permalink**) 12-03-10, 18:00

Quote:

Originally Posted by clara

I ran db2advis on the problem query and added recommended indexes

Check if you've done runstats on them

dav1mo · #10 (**permalink**) 12-07-10, 08:35

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

clara · #11 (**permalink**) 12-07-10, 21:52

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

Internet Services

Web Development

Digital Marketing

Consumer Tech