V$SESSION - SQL_ADDRESS for background processes

From: Moore, Paul <Paul.Moore_at_atosorigin.com>
Date: Fri, 17 Apr 2009 16:20:18 +0100
Message-ID: <3CF9EBFD312E1646A8597D4771733527034EFA52_at_UKCWRX003.uk.int.atosorigin.com>

I'm doing a query to join V$SESSION with V$SQLAREA to get the current SQL for each running session. Nothing fancy, it's just a quick view to help diagnose an issue. But I see some odd results, with background processes (specifically, ARC2) running what looks like user SQL (SELECT /*+ FIRST_ROWS */ * FROM user_table_1, user_table_2). I'm tempted to discount these, on the assumption that background processes wouldn't be running user SQL, so presumably SQL_ADDRESS and SQL_HASH_VALUE are meaningless for such processes, but is that a fair assumption? And if not, then why is that SQL present?

It's also interesting to note that EXECUTIONS in V$SQLAREA for that statement is 0. Presumably that implies that even though the statement was parsed, it has never been executed? What does that mean?


Paul Moore
Oracle DBA Support
Atos Origin UK Ltd
Daresbury Court

Atos Origin and Atos Consulting are trading names used by the Atos Origin group. The following trading entities are registered in England and Wales: Atos Origin IT Services UK Limited (registered number 01245534) and Atos Consulting Limited (registered number 04312380). The registered office for each is at 4 Triton Square, Regents Place, London, NW1 3HG.The VAT No. for each is: GB232327983

This e-mail and the documents attached are confidential and intended solely for the addressee, and may contain confidential or privileged information. If you receive this e-mail in error, you are not authorised to copy, disclose, use or retain it. Please notify the sender immediately and delete this email from your systems. As emails may be intercepted, amended or lost, they are not secure. Atos Origin therefore can accept no liability for any errors or their content. Although Atos Origin endeavours to maintain a virus-free network, we do not warrant that this transmission is virus-free and can accept no liability for any damages resulting from any virus transmitted. The risks are deemed to be accepted by everyone who communicates with Atos Origin by email.

Received on Fri Apr 17 2009 - 10:20:18 CDT

Original text of this message