A question came up on oracle-l recently about identifying which temporary segment in v$tempseg_usage is associated with which global temporary table. 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.
v$sort_usage sits on top of 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, and id2 set to 1 and 2.]
If an internal GTT has appeared because of subquery factoring this is a little broken, but if you look in the execution plan (v$sql_plan / 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 |
We will see 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 we note 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
Note the middle 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).
The SQL_ID issue is also mentioned in a comment in this article: https://jonathanlewis.wordpress.com/2014/02/16/recursive-subquery-factoring/
Comment by jkstill — March 18, 2014 @ 2:13 am GMT Mar 18,2014 |
[…] 12c the temporary undo segment is reported with segtype = “UNDEFINED” in v$sort_usage/v$tempseg_usage; by 19c this has changed to “TEMP […]
Pingback by 12c Temporary | Oracle Scratchpad — February 26, 2021 @ 11:28 am GMT Feb 26,2021 |