Informix

Expand all | Collapse all

Temp dbspace usage

  • 1.  Temp dbspace usage

    Posted 23 days ago
    Hello everyone,

           Hope all are safe and healthy in this pandemic.

           How can I check which query/session or activity is using more Temp dbspace. We have total 32 GB temp dbspace (16 temp dbspaces with 2 gb chunk each).

       Currently gets warning message for Temporary dbspace is 94% used  , So willing to dig what type of SQL/session occupying more TEMP DBSPACE.


    Thanks
    Amit Patel

    ------------------------------
    AMIT PATEL
    ------------------------------


  • 2.  RE: Temp dbspace usage

    Posted 22 days ago
    In the later versions of Informix 12, a "sid" column was added to sysmaster:sysptnhdr.  If you have this, then this is the easiest way to tie temp tables to session IDs.

    You can use the sum the nptotal column of sysptnhdr, and group by sid to find the sessions using the most temp space.


    ------------------------------
    Mike Walker
    xDB Systems, Inc
    ------------------------------



  • 3.  RE: Temp dbspace usage

    Posted 20 days ago
    Amit,.

    Run this against the sysmaster database...

    SELECT q1.*,
     (100 * size_kb / dbs_size) :: DECIMAL(5,2) AS percent
    FROM
    (
        SELECT
           t2.owner [1,8],
           t2.dbsname [1,18] AS database,
           t2.tabname [1,22] AS table,
           t3,name [1,10] AS dbspace,
           (CURRENT - DBINFO('utc_to_datetime', ti_created)) :: INTERVAL DAY(4) TO SECOND AS life_time,
           (ti_nptotal * ti_pagesize/1024) :: INT AS size_kb
       FROM
           systabinfo AS t1,
           systabnames AS t2,
           sysdbspaces AS t3
        WHERE t2.partnum = ti_partnum
        AND t3.dbsnum = TRUNC(t2.partnum/1024/1024)
        AND TRUNC(MOD(ti_flags,256)/16) > 0
    ) AS q1,
    (
        SELECT name AS dbspace,
            SUM(chksize * d1.pagesize/1024) AS dbs_size
        FROM
           syschunks AS d1,
           sysdbspaces AS d2
        WHERE d1.dbsnum = d2.dbsnum
        GROUP BY 1
    ) AS q2
    WHERE q1.dbspace = q2.dbspace
    ORDER BY 6 DESC, 5 DESC;
    ...it will tell you which objects are in your temporary dbspace(s) and the percentage of the temporary dspace(s) being consumed by each object contained therein. It might even be one of Art's or Lester's scripts.

    ------------------------------
    Kirit Rana
    ------------------------------



  • 4.  RE: Temp dbspace usage

    Posted 20 days ago

    If you get

     

    9635: An attempt has failed to convert an opaque type into another type without a cast function.

     

    Just remove the order by

     






  • 5.  RE: Temp dbspace usage

    Posted 19 days ago
    just replace Line 9 : t3,name to t3.name and runs fine.

    ------------------------------
    Chaz Hsieh
    ------------------------------



  • 6.  RE: Temp dbspace usage

    Posted 19 days ago
    Multiple ways to fix :-)

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 7.  RE: Temp dbspace usage

    Posted 19 days ago
    Copy and paste error, but yes as Paul states; there are many ways to skin a cat.

    ------------------------------
    Kirit Rana
    ------------------------------



  • 8.  RE: Temp dbspace usage

    Posted 19 days ago
    Glad we could help :-)

    https://www.oninitgroup.com/faq-items/dbspaces-how-to-list-temp-dbspace-contents

    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------



  • 9.  RE: Temp dbspace usage

    Posted 19 days ago





  • 10.  RE: Temp dbspace usage

    Posted 18 days ago
    Hey Doug 

    So that's where I got the script from. I knew it came from somewhere reputable.

    ------------------------------
    Kirit Rana
    ------------------------------



  • 11.  RE: Temp dbspace usage

    Posted 18 days ago
    Just an FYI -- in Informix 14.10.xC6 there ** might be ** something that makes this very easy to accomplish. :) :) :) :)  There are ways right now using brute force SQL operations to get temp table statistics but who knows what the very near future might bring  . . . . . . . . . . .  . .

    ------------------------------
    Carlton Doe
    ------------------------------