Re: Pigging out on temp table space?

In article <34B18508.C07AF189@garpac.com>, Jacob Salomon <jake@garpac.com> writes >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? > Online is proably using temp tables for sorting purposes. I sorts the data in small 'bickets' then merges each pair of 'buckets' together. Eventually the final merge produces the sorted output. This would explain the overflow tables as sorts >5Mb overflow onto disk. The bulld ones could be index build(?) which again overflow onto disk. Try using SET EXPLAIN ON. Possibly Indexs are being created during a query (I can;t remember the name of these indexes, they appear in set explain output and are dropped afterwards. Using means you need to create the index yourself). >Thanks. -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care