Watching Paint Dry or V$SQLTEXT_WITH_NEWLINES

How much time to do you spend debugging database application problems? Probably more time than you want. How much of that time is spent capturing the SQL that is being submitted by your application to the database? I’m going to guess that it’s a significant amount. Asssuming you have access to production (or a test environment that adequately replicates production), then you probably want to check out V$SQLTEXT_WITH_NEWLINES.

Before I talk about V$SQLTEXT_WITH_NEWLINES, let’s talk about why a developer would ever have a need to do this.

My first thought as a paranoid DBA is – “Hey, wait a second. This is YOUR code. You already KNOW what it does!” And my response as a developer is “No, I only know what it’s SUPPOSED to do.”

The lazy developers don’t write code. They write code that writes code. Ever heard of Dynamic SQL? How often have you seen some sort of WHILE construct that iterates through a set of data and builds a complex SQL statement that is to be executed later? You think you know what that statement is going to look like, but you won’t know until it’s actually built and executed. In most production environments, the data is not guaranteed to be the same as in DEV or TEST. So how is a developer to know what’s going on when they are brought in to help a DBA fix an issue in PROD?

I need access to V$SESSION and V$SQLTEXT_WITH_NEWLINES please!

With these two views, I can find my session(s) and extract the SQL that is being submitted by my application.

If I know my session ID, SID, then I can find out what my session is doing pretty easily.

V$SQLTEXT_WITH_NEWLINES is an easier-to-read version of V$SQLTEXT. According to Oracle, V$SQLTEXT “…contains the text of SQL statements belonging to shared SQL cursors in the SGA.” SGA is short for System Global Area. One of the components of the SGA is the Shared Pool. In other words, Oracle reserves a bunch of memory for storing queries that are running in case there are other users that might be running those same queries later. These views are letting us see what queries are being ran in the database.

[sql]
SELECT DECODE (Sql_hash_value, 0, Prev_hash_value, Sql_hash_value) Hash, DECODE (Sql_id, NULL, Prev_sql_id, Sql_id) Sql_id
FROM V$session
WHERE Sid = :Sid;

–every statement will have a HASH, but not every statement will have a SQL_ID

SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES WHERE
HASH_VALUE=TO_NUMBER(:hash) ORDER BY PIECE;
[/sql]

Of course many of your Oracle IDEs will have a GUI that gives you a nice read-out of this same information!

But what if you don’t have access to production?

Ask the DBA to do it for you? Do you ask for a TRACE and dig through the reams of data that comes back? If I’m a developer, I ask for a reproducible scenario and wait until the behavior can be replicated in our test environment. Then I wake up from my dream and realize I need to go mess in PROD again.

This again underscores the need to have a very good working relationship with your DBA teams. Be sure you know HOW to ask for what you want. This should include a business reason to backup your request for production data.

Once you faithfully loan your neighbor your power tools a dozen or so times, and they return them promptly and in good shape, then eventually you just give them a key to the garage. Don’t be the bad neighbor that abuses that trust. It just makes it harder for the other ‘good’ neighbors.