Temporary Segments

A question came up on oracle-l recently about identifying which temporary segment in v$tempseg_usage is associated with which global temporary table (GTT). Here’s my answer:

v$tempseg_usage is a synonym for v$sort_usage – which is the view that I still tend to think of first – and v$sort_usage is a view over x$ktsso, and in x$ktsso the column ktssoobjn is the object number for the definition of the global temporary table (ditto for any indexes on the table). [Addendum: I didn’t point it out in the reply, but v$lock will show a TM lock with id1 set to the table object_id, and id2 set to zero, and two TO locks with the same value for id1, but with id2 set to 1 and 2 respectively.]

If an internal GTT has appeared because of subquery factoring (“with” subquery / CTE) this correspondance is a little broken but if you look in the execution plan (v$sql_plan / dbms_xplan.display_cursor()) for the query you will find lines like:

|  63 |    HASH JOIN                          |                            |
|  64 |     VIEW                              |                            |
|  65 |      TABLE ACCESS FULL                | SYS_TEMP_0FD9D667C_74A306D |
|  66 |     VIEW                              |                            |
|  67 |      TABLE ACCESS FULL                | SYS_TEMP_0FD9D667B_74A306D |

There will be corresponding TO locks in v$lock (note the negative value):

ADDR             KADDR                   SID TY        ID1        ID2
---------------- ---------------- ---------- -- ---------- ----------
000000008ED8EC68 000000008ED8ECC0        143 TO  -40016261          1
000000008ED8F540 000000008ED8F598        143 TO  -40016260          1

And there will be some large values for ktssoobjn in x$ktsso:

  KTSSOBNO  KTSSOEXTS  KTSSOBLKS  KTSSORFNO  KTSSOOBJD  KTSSOOBJN KTSSOTSNUM KTSSOSQLID
---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------
     49792          1        128          1    4244096 4254951035          3 anb4mscz7wz71
     49152          1        128          1    4243456 4254951036          3 anb4mscz7wz71

Check the middle section of the SYS_TEMP name:

0x0FD9D667C = 4254951036

and then spot the arithmetic:

4254951036 + 40016260 = 4294967296 = 0x100000000 = power(2,32)

v$lock.id1 is a 16 bit wrap of x$ktsso.ktssoobjn, so add it to power(2,32) and you can look for it (for the relevant session) in x$ktsso.

For things like sorts we can check v$sql_workarea_active against v$sort_usage / v$tempseg_usage (though you might stick with x$ktsso to be consistent) since both hold the relative file and block number for the segment.

Follow-up

Shortly after posting a reply to the original question I received an email from Stefan Koehler pointing out that in 11.2.0.2 the object x$ktsso was enhanced to include the sql_id of the statement that caused a temporary object to come into existence. (If you check the definition of v$sort_usage/v$tempseg_usage you’ll see that the sql_id that it’s reporting is the prev_sql_id from V$session).  Stefan also gave me a link to a note that he had written on the topic.

It is an oddity of v$sort_usage / v$tempseg_usage that it holds a column called session num which suggests that it ought to be the SID: it isn’t, it’s the v$session.serial#. To connect to the session information it’s probably best to join the session_addr to v$session.saddr).

(Some time later): a little oddity that I tracked down by session_addr and session_num was that an idle M001 (shared MMON slave) session was holding a (small) temporary segment long after it had last executed the statement that had allocated it. The statement in question (with a little cosmetic editing) was:

INSERT INTO WRI$_ADV_ADDM_PDBS (CON_DBID, PDB_NAME) 
SELECT   CON_DBID, SUBSTR(X,20) 
FROM     ( 
         SELECT
                 I.CON_DBID, 
                 MAX(TO_CHAR(I.OPEN_TIME, 'YYYY:MM:DD HH24:MI:SS') || I.PDB_NAME) AS X 
        FROM
                AWR_PDB_PDB_INSTANCE I, 
                AWR_PDB_SNAPSHOT S 
        WHERE 
                S.DBID = :B1 
        AND     S.INSTANCE_NUMBER = I.INSTANCE_NUMBER 
        AND     S.SNAP_ID = :B3 
        AND     I.STARTUP_TIME >= S.STARTUP_TIME 
        AND     I.STARTUP_TIME <= :B2 
        AND     I.CON_DBID <> 0 
        AND     I.CON_DBID <> :B1 
        AND     I.DBID = :B1 
        AND     I.PDB_NAME IS NOT NULL 
        GROUP BY  
                I.CON_DBID 
        )