Pigging out on temp table space?
Hi Folks. Long time no post. Note that for this post, fodder for discussion, I turned off the "wrap long lines on send" option because some of the included output is 80 characters wide. My users came complaining to me that a report program they run is running out of temp space. I added temp space but I wanted to monitor what's going on during the query. That the app generated a 10-million line report is besides the point of the subject line. I agree it was too broad a query but it uncovered an interesting issue. I ran the app in one window and, in another window, ran the following pair of queries (in a shell script) at 30 second intervals, appending the output to a file each time: select dbsname[1,10], tabname, hex(partnum) partition, ti_nptotal, ti_npdata, ti_nextns from systabnames n, systabinfo i where n.partnum = i.ti_partnum and owner = user; select sum(ti_nptotal) total_pages, sum(ti_npused) total_used, sum(ti_npdata) total_data, sum(ti_nextns) total_extents from systabnames n, systabinfo i where n.partnum = i.ti_partnum and owner = user; Now here is a sample of the output: Wed Dec 24 12:36:06 EST 1997 dbsname tabname partition ti_nptotal ti_npdata ti_nextns rga tmp 0x00300005 640 634 8 HASHTEMP th_build_ffffffff 0x00300006 404 0 33 HASHTEMP th_build_ffffffff 0x00300007 212 0 33 HASHTEMP th_build_ffffffff 0x00300008 104 0 21 HASHTEMP th_probe_ffffffff 0x00300009 4 0 1 HASHTEMP th_probe_ffffffff 0x0030000A 4 0 1 HASHTEMP th_probe_ffffffff 0x0030000B 4 0 1 HASHTEMP th_probe_ffffffff 0x0030000C 4 0 1 HASHTEMP th_overflow_ffffff 0x0030000D 3776 0 94 HASHTEMP th_overflow_ffffff 0x0030000E 3656 0 93 HASHTEMP th_overflow_ffffff 0x0030000F 3776 0 94 HASHTEMP th_overflow_ffffff 0x00600002 3248 0 3 rga tmp 0x00600004 640 634 9 HASHTEMP th_build_ffffffff 0x00600006 308 0 39 HASHTEMP th_build_ffffffff 0x00600007 200 0 30 HASHTEMP th_build_ffffffff 0x00600008 104 0 21 HASHTEMP th_probe_ffffffff 0x00600009 4 0 1 HASHTEMP th_probe_ffffffff 0x0060000A 4 0 1 HASHTEMP th_probe_ffffffff 0x0060000B 4 0 1 HASHTEMP th_overflow_ffffff 0x0060000C 3648 0 93 HASHTEMP th_overflow_ffffff 0x0060000D 3648 0 93 HASHTEMP th_overflow_ffffff 0x0060000E 3648 0 93 HASHTEMP th_overflow_ffffff 0x0060000F 3648 0 93 rga tmp 0x00700005 640 634 12 HASHTEMP th_build_ffffffff 0x00700006 896 0 34 HASHTEMP th_build_ffffffff 0x00700007 212 0 33 HASHTEMP th_build_ffffffff 0x00700008 104 0 21 HASHTEMP th_build_ffffffff 0x00700009 104 0 21 HASHTEMP th_probe_ffffffff 0x0070000A 4 0 1 HASHTEMP th_probe_ffffffff 0x0070000B 4 0 1 HASHTEMP th_probe_ffffffff 0x0070000C 4 0 1 HASHTEMP th_overflow_ffffff 0x0070000D 3648 0 93 HASHTEMP th_overflow_ffffff 0x0070000E 1664 0 69 HASHTEMP th_overflow_ffffff 0x0070000F 3648 0 93 HASHTEMP th_overflow_ffffff 0x00700010 3720 0 93 total_pages total_used total_data total_extents 46400 1905 1902 1330 OK, notice the sheer number of extents and pages hogged by these temp tables. Note how many pages are actually used for all of these tables. It looks like the process is wasting aloooootta disk space. Or is ti_npused giving me a wrong value? I note that as the query progresses, the number of temp tables dwindles and the total_used count goes up. At no point in the query is more than about 60% of the total pages actually used. The last run of the query, within 30 seconds of the end, look like this: Wed Dec 24 13:49:15 EST 1997 dbsname tabname partition ti_nptotal ti_npdata ti_nextns rga tmp 0x00300005 8936 8739 90 rga tmp 0x00600004 8928 8739 89 SORTTEMP th_tmprun_c61ac760 0x00600041 16528 0 65 rga tmp 0x00700005 8864 8739 87 SORTTEMP th_tmprun_c61ac700 0x00700041 8224 0 43 total_pages total_used total_data total_extents 51480 26223 26217 374 Not the height of efficiency in any case. Can anyone please explain why it needs so many temp tables when they seem to be mostly empty pages? Thanks. -- -- Jake (Never yelled "CROWDED THEATER!" during a fire) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+