Duplicates when Querying V$TEMPSEG_USAGE?

22 03 2010

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


Actions

Information

6 responses

23 03 2010
Andreas Buckenhofer

I’m glad the errata submission on apress.com inspired another informative article. In the meantime, the statement (+ some variations on other suggestions in the book) is in production as part of a DB monitoring tool set :) .

23 03 2010
Charles Hooper

Andreas,

Thanks for stopping by my blog. Great that you found helpful ideas in the book. Do you want to share a description of what you put together for DB monitoring?

24 03 2010
Andreas Buckenhofer

Charles,

our current (first version of) monitoring gathers
1.) CPU / IO / memory: v$osstat, v$filestat/v$tempstat, v$pgastat
2.) transactions / wait events / time model: v$sysmetric (group_id = 2 for user calls, block gets, redo, user tx), $sys_time_model, v$system_event
3.) application-specifics / SQLs: v$tempseg_usage, v$session, v$sql, v$sqlarea and “individual” application instrumentation/logging (e.g. start/end time of ETL loads, start/end time report generation)

Most of the data is collected every minute by a PLSQL package. Analysis is done by data export & Excel.

24 03 2010
Charles Hooper

Andreas,

It sounds like you put together a comprehensive monitoring solution. Thank you for sharing – it is always interesting to hear how other people are tracking performance issues.

In the VBS category on this blog you will find a couple of scripts that tie directly into several chapters of the book. Some of those scripts might give you one or two additional ideas for additional monitoring for version 2 of your DB monitoring setup – web-based drill down into problematic sessions and their SQL statements, for instance.

21 01 2011
Mohammad Illiyaz

lovely insight Charles….you rock !!!!

21 01 2011
Charles Hooper

Mohammad,

Thank you for the comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 148 other followers

%d bloggers like this: