[Oracle] Wrong SQL_ID in view V$SORT_USAGE / V$TEMPSEG_USAGE and how to handle it in 11.2.0.2 or higher

[stefan_koehler]

Introduction

This blog is just a short one (with demo) about a known issue / bug with the views V$SORT_USAGE and V$TEMPSEG_USAGE (both synonyms are referring to view V_$SORT_USAGE). I already replied on a SCN thread and pointing to this issue, but it should be much clearer with a short test case.

Basically this issue happens most of the time by using temporary LOB segments (implicitly or explicitly). Now you maybe wonder - when or in which scenarios does SAP use temporary LOB segments? Let's check the corresponding SAP documentation for this first.

SAPnote #500340 - FAQ: LOBS

17. Where do temporary LOBs come from?

Under some circumstances it can happen that PSAPTEMP is filled with a significant amount of temporary LOBs. This can be verified with the V$TEMPORARY_LOBS view. These temporary LOBs are created in SAP J2EE due to technical reasons (guarantee of atomicity of LOB creation) whenever a new LOB is created. In situations with a high LOB creation rate (e.g. when tables with LOBs are imported in the database) this behaviour can be responsible for a significant filling level or an overflow of PSAPTEMP.

SAPnote #659946 -  FAQ: Temporary tablespaces

Temporary LOBs (for example in J2EE environments)

With BW 2. x, permanent objects are also created in the temporary tablespace in some situations (see Note 216440).

You probably notice this issue in WLS (Oracle WebLogic Server) environments heavily, if JDBC connection pool testing is enabled (WebLogic Documentation). You find temporary LOB segments (if created) with a reference SQL statement like "SELECT 1 from DUAL", which obviously can not be true.

Demo

The following demo is performed with an Oracle database (11.2.0.3.2) on OEL 6.2.

I will create two SQL*Plus sessions. One that is running the application SQL statements (in my case a temporary LOB creation and a SELECT) and one that is used to query the views V$SORT_USAGE and V$TEMPSEG_USAGE.

Initial situation

-- Monitoring session (SID 15)

SYS@T11:15> select * from V$SORT_USAGE;

no rows selected

SYS@T11:15> select * from V$TEMPSEG_USAGE;

no rows selected

Create temporary LOB segment (= application behavior)

-- Working session (SID 138)

SYS@T11:138> declare

  TEST_BLOB BLOB;

begin

  DBMS_LOB.CREATETEMPORARY(TEST_BLOB,TRUE, DBMS_LOB.SESSION);

end;

/

Check views V$SORT_USAGE and V$TEMPSEG_USAGE

-- Monitoring session (SID 15)

SYS@T11:15> select * from V$SORT_USAGE;

SYS@T11:15> select * from V$TEMPSEG_USAGE;

SYS@T11:15> select SQL_TEXT from V$SQL where SQL_ID = 'cqr10p45ppn1j';

.. everything is correct so far. Both views show the correct SQL_ID, if no other SQL was running afterwards. So let's run a simple SELECT in session 138 after the creation of the temporary LOB segment.

Further SQL

-- Working session (SID 138)

SYS@T11:138> select 1 from dual;

Re-check views V$SORT_USAGE and V$TEMPSEG_USAGE

-- Monitoring session (SID 15)

SYS@T11:15> select * from V$SORT_USAGE;

SYS@T11:15> select * from V$TEMPSEG_USAGE;

SYS@T11:15> select SQL_TEXT from V$SQL where SQL_ID = '520mkxqpf15q8';

Now you see the SQL_ID for the simple SELECT, but this obviously can not be true. A SELECT statement like "select 1 from dual" can not force a temporary lob segment creation or even use it. There seems to be something wrong. Kerry Osborne has already described this problem in one of his blog posts (check the reference section).

He also posted a snippet from an Oracle bug description:

"It looks like this really needs a larger change – something like capturing the SQL_ID etc.. at the time that the temp seg gets created and then exposing that information through some new X$ colums in x$ktsso?"

.. and this change was introduced with Oracle version 11.2.0.2, but unfortunately the views were not adjusted until yet.

Use custom query with new column in table x$ktsso

-- Monitoring session (SID 15)

SYS@T11:15> select k.inst_id "INST_ID", ktssoses "SADDR", sid "SID", ktssosno "SERIAL#", username "USERNAME", osuser "OSUSER",

ktssosqlid "SQL_ID", ktssotsn "TABLESPACE", decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",

decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",

ktssofno "SEGFILE#", ktssobno "SEGBLK#", ktssoexts "EXTENTS", ktssoblks "BLOCKS", round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB",

ktssorfno "SEGRFNO#"

from x$ktsso k, v$session s, v$parameter p

where ktssoses = s.saddr and ktssosno = s.serial#  and p.name = 'db_block_size'

order by sid;

Notice how the correct SQL_ID "cqr10p45ppn1j" is returned now (like running the first query on the views V$SORT_USAGE and V$TEMPSEG_USAGE), even if further SQL statements were run afterwards in this session.

** Side note: Implicit temporary LOB creation

In the previous test case we have created a temporary LOB segment explicitly with PL/SQL package DBMS_LOB. However it is also possible that you are creating and using temporary LOB segments implicitly by executing conversion functions like TO_CLOB.

For example:

SQL> SELECT TO_CLOB('TEST TEXT') FROM dual;

Summary

Be careful and think twice, if you notice a temporary space usage by SQL statements that makes no sense at all.

If you have any further questions - please feel free to ask or get in contact directly, if you need assistance by troubleshooting Oracle database issues.

References

• Kerry Osborne - Temp Usage
• Oracle Documentation - View V$TEMPSEG_USAGE
• Sapnote #500340 - FAQ: LOBS
• Sapnote #659946 - FAQ: Temporary tablespaces