Duplicates when Querying V$TEMPSEG_USAGE?

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.

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

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:

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:

Related

Actions

Information

6 responses

23032010

Andreas Buckenhofer(07:37:53) :

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 :) .

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.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: