RE: mapping a GTT to space usage in the Temp Tablespace
Date: Thu, 13 Mar 2014 16:05:59 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DE2DD6_at_exmbx05.thus.corp>
Another little item that may be useful - I've just had an email from Stegan Koehler who's pointed out that Oracle enhanced x$ktsso in 11.2.0.3 to include the SQL_ID (column ktssosqlid) of the statement that caused a temporary object to come into existence.
He's published a note about this here: http://scn.sap.com/community/oracle/blog/2013/06/10/oracle-wrong-sqlid-in-view-vsortusage-vtempsegusage-and-how-to-handle-it-in-11202-or-higher
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: Jonathan Lewis
Sent: 13 March 2014 14:02
To: Hemant-K.Chitale_at_sc.com; ORACLE-L Subject: RE: mapping a GTT to space usage in the Temp Tablespace
v$tempseg_usage is a synonym for v$sort_usage - which 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).
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 |
Corresponding to 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 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
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 reletaive file and block number for the segment - although you have
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: Jonathan Lewis
Sent: 13 March 2014 07:53
To: Hemant-K.Chitale_at_sc.com; ORACLE-L Subject: RE: mapping a GTT to space usage in the Temp Tablespace
My notes don't include an answer to your question, but v$sort_usage includes a join to v$session (x$ksuse) and reports the prev_sql_id (as you observed) so you could pick up the sql_id and child_number by repeating the join (or creating a new view, or querying the x$ - which eventually you may have to do). From there you can access v$sql_plan and then v$sql_workarea_active - and I have a vague memory that there's something in v$sql_workarea active (or the x$) that will let you find the answer your question (possibly by joining to another x$ which isn't usually documented).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Chitale, Hemant K [Hemant-K.Chitale_at_sc.com] Sent: 13 March 2014 06:22
To: ORACLE-L
Subject: mapping a GTT to space usage in the Temp Tablespace
I can identify a Global Temporary Tablespace consuming space in the Temp tablespace by querying V$SORT_USAGE / V$TEMPSEG_USAGE. It appears as a row with CONTENTS=’TEMPORARY’ SEGTYPE= ‘DATA’
If I have multiple GTTs being used by a single session , how do I identify which entry in V$TEMPSEG_USAGE is which GTT ? I Need to “size” the GTTs and cannot use DBA/USER_SEGMENTS.
For example , the session with SERIAL#49681 is currently using 4 GTTs. SQL_ID doesn’t help because it is the SQL_ID of the last SQL from the session.
HEMANT>l
1* select * from v$tempseg_usage where tablespace='HEMANT_TEMP'
HEMANT>/
USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH SQL_ID ------------------------------ ------------------------------ ---------------- ----------- ---------------- ---------- ------------- TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO# ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ---------- HEMANT HEMANT 000000742D7717B8 12387 000000740D009DB0 2978105261 0890n6fss4jxd HEMANT_TEMP TEMPORARY LOB_DATA 1050 1127424 1 128 2 HEMANT HEMANT 000000742CC87A28 49681 0000006EC8CA75B8 4269071746 0j88ahzz79rc2 HEMANT_TEMP TEMPORARY LOB_DATA 1050 1163776 1 128 2 HEMANT HEMANT 000000742CC87A28 49681 0000006EC8CA75B8 4269071746 0j88ahzz79rc2 HEMANT_TEMP TEMPORARY DATA 1050 1162240 544 69632 2 HEMANT HEMANT 000000742D36DE50 8017 0000006ED576DE00 2252965756 2j2xhw634m1vw HEMANT_TEMP TEMPORARY DATA 1038 3917184 1031 131968 1 HEMANT HEMANT 000000742D36DE50 8017 0000006ED576DE00 2252965756 2j2xhw634m1vw HEMANT_TEMP TEMPORARY DATA 1038 4045056 516 66048 1 HEMANT HEMANT 000000742D36DE50 8017 0000006ED576DE00 2252965756 2j2xhw634m1vw HEMANT_TEMP TEMPORARY DATA 1038 4063744 518 66304 1
Hemant K Chitale
This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 13 2014 - 17:05:59 CET