Re: tracking down hidden SQL???

From: <Laimutis.Nedzinskas_at_seb.lt>
Date: Thu, 17 Oct 2013 09:22:45 +0300
Message-ID: <OFC385CED9.7B7B2098-ONC2257C07.0022DFC1-C2257C07.00230AF5_at_seb.lt>



audit (DB_EXTENDED) and/or SQL tracing (both intrusive) might help...

Please consider the environment before printing this e-mail

|------------>

| From: |
|------------>

  >----------------------------------------------------------------------------------------------------------------------------------------|
  |Adric Norris <landstander668_at_gmail.com>                                                                                                 |
  >----------------------------------------------------------------------------------------------------------------------------------------|

|------------>
| To: |

|------------>
  >----------------------------------------------------------------------------------------------------------------------------------------|
  |oracle-l <oracle-l_at_freelists.org>                                                                                                       |
  >----------------------------------------------------------------------------------------------------------------------------------------|

|------------>
| Date: |

|------------>
  >----------------------------------------------------------------------------------------------------------------------------------------|
  |2013.10.16 21:48                                                                                                                        |
  >----------------------------------------------------------------------------------------------------------------------------------------|

|------------>

| Subject: |
|------------>
  >----------------------------------------------------------------------------------------------------------------------------------------|
  |tracking down hidden SQL???                                                                                                             |
  >----------------------------------------------------------------------------------------------------------------------------------------|





I'm trying to track down a SQL_ID which is *always* very prominent in the Top Activity view of OEM 12c. When I attempt to drill down into the specific statement, however, it always reports that "The SQL statement is not available". I can see it listed in [g]v$session and ASH, and thereby determine that it's executed very frequently and completes quickly, but for some reason the beastie is never present in [g]v$sql or AWR. I've also tried tracing some of the sessions which frequently show the SQL_ID in question, but couldn't find it listed in any tracefiles either It seems virtually identical to the issue Kerry Osborne blogged about in Hidden
SQL why cant I find my SQL
Text?<
http://kerryosborne.oracle-guy.com/2009/04/hidden-sql-why-cant-i-find-my-sql-text/ >,
with the very significant exception that the SQL_ID is never present in [g]v$open_cursor either. Has anyone else encountered this situation? Any suggestions on where else I can look, to hopefully figure out what the heck it's doing? The database in question is Oracle Enterprise Edition 11.2.0.3.6 64-bit, running on a 3-node Solaris 10 SPARC cluster.

It isn't clear this activity is adversely affecting performance (the vast majority of waits seem to be *SQL*Net break/reset to client*), so this isn't an urgent issue. Mainly it's just annoying that I can't seem to identify anything of significance about what's occurring... wounded pride, and such. (-;

Thanx!

--
"I'm too sexy for my code." -Awk Sed Fred

--
http://www.freelists.org/webpage/oracle-l





--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 17 2013 - 08:22:45 CEST

Original text of this message