Temporary tables called th_overflow_ffffff
User observed temporary tables named "th_overflow_ffffff" in HASHTEMP dbspace. Experts explained these are overflow tables created by hash join queries when insufficient memory is available. Recommendations included: optimize queries and create indexes to reduce hash joins, and use SSD drives for temp dbspace. A clarification noted hash joins only create temp tables when memory is insufficient.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I have applications that create several temporary tables. I'm monitoring which
tables are created and I see several tables with tabname =
"th_overflow_ffffff" and dbsname = 'HASHTEMP'
This is the query that I use:
database sysmaster;
select b.dbsname
,b.tabname
,a.ti_nptotal
,a.ti_npused
,a.ti_npdata
,a.ti_nextns
,a.ti_nrows
from systabinfo a, systabnames b
where a.ti_partnum = b.partnum
and (bitval(a.ti_flags, 32) = 1 or bitval(a.ti_flags, 64) = 1 or
bitval(a.ti_flags, 128) = 1)
order by 3 desc
My question is: Why several tables called th_overflow_ffffff are created ?
Thanks in advance.
Roger
These are temp tables created by queries performing hash joins.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Apr 9, 2013 at 11:49 AM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote:
> Hi,
> I have applications that create several temporary tables. I'm monitoring
> which
> tables are created and I see several tables with tabname =
> "th_overflow_ffffff" and dbsname = 'HASHTEMP'
>
> This is the query that I use:
>
> database sysmaster;
> select b.dbsname
> ,b.tabname
> ,a.ti_nptotal
> ,a.ti_npused
> ,a.ti_npdata
> ,a.ti_nextns
> ,a.ti_nrows
> from systabinfo a, systabnames b
> where a.ti_partnum = b.partnum
> and (bitval(a.ti_flags, 32) = 1 or bitval(a.ti_flags, 64) = 1 or
> bitval(a.ti_flags, 128) = 1)
> order by 3 desc>
> My question is: Why several tables called th_overflow_ffffff are created ?
>
> Thanks in advance.
>
> Roger
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c20c9af6950804d9f003bc
You are seeing is for the hash table overflow from the hash join.
In version 12.10 you will be able to run update statistics low on sysmaster
database to get a better and more accurate query plan. In previous
releases all sysmaster tables assumed to have the same number of rows.
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 04/09/2013 08:49:52 AM:
> From: "ROGER VILCA" <rvilca@luzdelsur.com.pe>
> To: ids@iiug.org,
> Date: 04/09/2013 09:03 AM
> Subject: Temporary tables called th_overflow_ffffff [30018]
> Sent by: ids-bounces@iiug.org
>
> Hi,
> I have applications that create several temporary tables. I'm
> monitoring which
> tables are created and I see several tables with tabname =
> "th_overflow_ffffff" and dbsname = 'HASHTEMP'
>
> This is the query that I use:
>
> database sysmaster;
> select b.dbsname
> ,b.tabname
> ,a.ti_nptotal
> ,a.ti_npused
> ,a.ti_npdata
> ,a.ti_nextns
> ,a.ti_nrows
> from systabinfo a, systabnames b
> where a.ti_partnum = b.partnum
> and (bitval(a.ti_flags, 32) = 1 or bitval(a.ti_flags, 64) = 1 or
> bitval(a.ti_flags, 128) = 1)
> order by 3 desc>
> My question is: Why several tables called th_overflow_ffffff are
created ?
>
> Thanks in advance.
>
> Roger
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Ok, that means: a) I could review the queries and create indexes for reduce the amount of hash joins. b) I could increase the size the shared memory (SHMVIRTSIZE) so that the majority of hash joins are running in memory. Please tell me if this statements are correct Thanks in advance Roger
Absolutely to a). On b) hash joins will always create a temp table. What you can do is to use SSD drives for your temp dbspace chunks! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Apr 9, 2013 at 12:45 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote: > Ok, that means: > > a) I could review the queries and create indexes for reduce the amount of > hash > joins. > b) I could increase the size the shared memory (SHMVIRTSIZE) so that the > majority of hash joins are running in memory. > > Please tell me if this statements are correct > Thanks in advance > Roger > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b343d36e8baac04d9f0c75f
In my experience hash joins only create a temp table if they have = insufficient memory to perform the join in memory. j. On Apr 9, 2013, at 1:21 PM, Art Kagel wrote: > Absolutely to a). On b) hash joins will always create a temp table. = What=20 > you can do is to use SSD drives for your temp dbspace chunks!=20 >=20 > Art=20 >=20 > Art S. Kagel=20 > Advanced DataTools (www.advancedatatools.com)=20 > Blog: http://informix-myview.blogspot.com/=20 >=20 > Disclaimer: Please keep in mind that my own opinions are my own = opinions=20 > and do not reflect on my employer, Advanced DataTools, the IIUG, nor = any=20 > other organization with which I am associated either explicitly,=20 > implicitly, or by inference. Neither do those opinions reflect those = of=20 > other individuals affiliated with any entity with which I am = affiliated nor=20 > those of the entities themselves.=20 >=20 > On Tue, Apr 9, 2013 at 12:45 PM, ROGER VILCA = <rvilca@luzdelsur.com.pe>wrote:=20 >=20 >> Ok, that means:=20 >>=20 >> a) I could review the queries and create indexes for reduce the = amount of=20 >> hash=20 >> joins.=20 >> b) I could increase the size the shared memory (SHMVIRTSIZE) so that = the=20 >> majority of hash joins are running in memory.=20 >>=20 >> Please tell me if this statements are correct=20 >> Thanks in advance=20 >> Roger=20 >>=20 >>=20 >>=20 >>=20 > = **************************************************************************= *****=20 >> Forum Note: Use "Reply" to post a response in the discussion forum.=20= >>=20 >>=20 >=20 > --047d7b343d36e8baac04d9f0c75f=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20