Duplicates when Querying V$TEMPSEG_USAGE?

March 22, 2010

If you search through the Oracle documentation, you will find the following in the Oracle Database 9.2 documentation:

V$SESSION is basically an information view used for finding the SID or SADDR of a user. However, it has some columns that change dynamically and are useful for examining a user. For example:

SQL_HASH_VALUE, SQL_ADDRESS: These identify the SQL statement currently being executed by the session. If NULL or 0, then the session is not executing any SQL statement. PREV_HASH_VALUE and PREV_ADDRESS identify the previous statement being executed by the session.

The Oracle Database 10.2 documentation includes the following:

Use the following query to determine, at a point in time, which sessions are waiting for I/O:

SELECT SQL_ADDRESS, SQL_HASH_VALUE
  FROM V$SESSION
 WHERE EVENT LIKE ‘db file%read’;

Again in the Oracle Database 10.2 documentation:

SQL_ADDRESS: Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed
SQL_HASH_VALUE: Used with SQL_ADDRESS to identify the SQL statement that is currently being executed

The Oracle Database 11.2 documentation repeats what is found in the 10.2 documentation:

SQL_ADDRESS: Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed
SQL_HASH_VALUE: Used with SQL_ADDRESS to identify the SQL statement that is currently being executed

Why doesn’t the documentation for 10.2 and 11.2 suggest using the SQL_ID and SQL_CHILD_NUMBER columns to find the SQL statement that is currently being executed?  For that matter, why isn’t there a SQL_CHILD_ADDRESS column in V$SESSION if the documentation suggests using SQL_ADDRESS to find the SQL statement currently being executed?

Recently, an errata was filed for page 188 of the Expert Oracle Practices book by an observant reader named Andreas, who mentioned that the SQL statement found on that page could produce duplicate rows, and offered an improved version of the SQL statement.  The SQL statement from the book follows:

SQL> SELECT /*+ ORDERED */
  2    TU.USERNAME, S.SID, S.SERIAL#, S.SQL_ID, S.SQL_ADDRESS, TU.SEGTYPE, TU.EXTENTS,
  3    TU.BLOCKS, SQL.SQL_TEXT
  4  FROM
  5    V$TEMPSEG_USAGE TU, V$SESSION S, V$SQL SQL
  6  WHERE
  7    TU.SESSION_ADDR=S.SADDR
  8    AND TU.SESSION_NUM=S.SERIAL#
  9    AND S.SQL_ID=SQL.SQL_ID
 10    AND S.SQL_ADDRESS=SQL.ADDRESS;

The sample output included in the book from that SQL statement is this:

USERNAME   SID  SERIAL# SQL_ID        SQL_ADDRESS      SEGTYPE EXTENTS     BLOCKS
-------- ----- -------- ------------- ---------------- ------- ------- ----------
TESTUSER   165     2171 7pqxpw71fkjvj 000000027DB50320 SORT        370      47360

SQL_TEXT
-------------------------------------------------------------------
SELECT * FROM T1,T2 WHERE T1.ID<=100 AND T2.ID<=1000 ORDER BY T1.ID

We also included the execution plan for the SQL statement, but did not show how the execution plan was obtained since that would be discussed later in the chapter:

------------------------------------------------------------------------------------
| Id |Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|  0|SELECT STATEMENT             |        |       |       |   119G(100)|          |
|  1| MERGE JOIN CARTESIAN        |        |  6552G|   643T|   119G  (3)|999:59:59 |
|  2|  TABLE ACCESS BY INDEX ROWID| T1     |  1005K|    50M|  1006K  (1)| 00:07:10 |
|* 3|   INDEX RANGE SCAN          | IND_T1 |  1005K|       |  2293   (9)| 00:00:01 |
|  4|  BUFFER SORT                |        |  6516K|   341M|   119G  (3)|999:59:59 |
|* 5|   TABLE ACCESS FULL         | T2     |  6516K|   341M|   118K  (3)| 00:00:51 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."ID"<=100)
   5 - filter("T2"."ID"<=1000)

Note
-----
   - dynamic sampling used for this statement

What is wrong with the above SQL statement (no, not the one that generated the Cartesian join – the other one that queried V$TEMPSEG_USAGE)?  We did not write the two chapters in linear order, starting at the beginning of chapter 8 and working to the end of chapter 9.  Instead, we outlined each section that would appear in the chapter, and wrote the contents of those sections in spurts.  At the time that the SQL statement which queried V$TEMPSEG_USAGE was written, we had already written the AdaptiveCursorBindTest.sql sample script that is mentioned later in chapter 8.  When the above SQL statement was written, I recall being internally conflicted – why?

• Should the SQL statement instead use the HASH_VALUE, rather than SQL_ID, as suggested by the Oracle 10.2 and 11.2 documentation, which would then allow the query to also work on Oracle 9.2?  Would it be sufficient to just add a note stating that if you are running Oracle 9.2 or earlier, substitute the SQL_HASH_VALUE for SQL_ID?
• Should the SQL statement join to V$SQLAREA to eliminate the problems with multiple child cursors for the same SQL_ID?  But how would we then retrieve the correct execution plan from the server’s memory in the event that the multiple child cursors have different execution plans?
• Should we also use the SQL_CHILD_NUMBER column to join to V$SQL?  But then what about the users still running Oracle 9.2 or earlier?
• Should there be a SQL_CHILD_ADDRESS column in V$SESSION since the SQL_ADDRESS column will probably be the same for all child cursors for a given SQL_ID?
• How do we deal with the output of the AdaptiveCursorBindTest.sql sample script, which showed Oracle 11.1.0.6 producing multiple child cursors with the same CHILD_NUMBER for a give SQL_ID?  That would mean that even if the query included the SQL_CHILD_NUMBER column, there is still a potential for duplicate rows being returned by the query.

The initial plan was to circle back to this section of the chapter and try to describe some of the potential issues with using the SQL statement that queried V$TEMPSEG_USAGE, but that would not be an easy task without the reader first reading the rest of that chapter.  Alas, just days after the final version of the chapters were due to the publisher, and the first drop of ink was spilled on the page of the printed book, we made another pass through the chapter.  When I executed this SQL statement I found that it was listing the same SQL_ID more than once.  But why?  A query of V$SQL_SHARED_CURSOR showed that ROLL_INVALID_MISMATCH was set to Y when a second (or third) child cursor was created.  What does that mean?  Execute a query, collect statistics on the tables or indexes used by the query, wait about 5 hours, re-execute the query, and then check V$SQL_SHARED_CURSOR.  The delay is mentioned here and here, along with a hidden _optimizer_invalidation_period parameter that controls how long after statistics collection is performed, the previously hard parsed cursors that referenced those objects can no longer be used.

In short, we never did circle back to that section of the chapter before the book was printed, so I am happy that Andreas filed the errata report, which allowed us to clarify the situation.  We did join to V$SQL so that with a little more work it would be possible to determine the correct execution plan to pull from memory.  In retrospect, we probably should have just written the SQL statement like the following and provided a quick comment about Oracle Database 9.2.0.8 and earlier, and the potential duplicates in Oracle Database 11.1.0.6 that were demonstrated in the AdaptiveCursorBindTest.sql sample script:

SELECT /*+ ORDERED */
  TU.USERNAME, S.SID, S.SERIAL#, S.SQL_ID, S.SQL_CHILD_NUMBER,
  TU.SEGTYPE, TU.EXTENTS, TU.BLOCKS, SQL.SQL_TEXT
FROM
  V$TEMPSEG_USAGE TU, V$SESSION S, V$SQL SQL
WHERE
  TU.SESSION_ADDR=S.SADDR
  AND TU.SESSION_NUM=S.SERIAL#
  AND S.SQL_ID=SQL.SQL_ID
  AND S.SQL_CHILD_NUMBER=SQL.CHILD_NUMBER
  AND S.SQL_ADDRESS=SQL.ADDRESS;

Without looking at the V$SQL_SHARED_CURSOR view, what are the different reasons for multiple child cursors to be created?  To get you started:

• Collecting object statistics
• Changing the data type of bind variables
• Changing the data length of bind variables
• Changing the optimizer mode (ALL_ROWS, CHOOSE, RULE, FIRST_ROWS, etc.)
• Adaptive cursor sharing (starting with Oracle Database 11.1.0.6)
• Cardinality feedback (starting with Oracle Database 11.2.0.1)

—

I give my thanks to Andreas for inspiring this blog article.

Related blog articles:
SORT_AREA_SIZE Affecting Sorts to Disk with PGA_AGGREGATE_TARGET Set?
SORT_AREA_SIZE Affecting Sorts to Disk with PGA_AGGREGATE_TARGET Set 2?
SQL PLAN_HASH_VALUE Changes for the Same SQL Statement
V$SESSION_LONGOPS – Where’s My SQL Statement