Re: tracking down hidden SQL???

  • From: Laimutis.Nedzinskas@xxxxxx
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 17 Oct 2013 09:22:45 +0300

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

---------------------------------------------------------------------------------

Please consider the environment before printing this e-mail


|------------>
| From:      |
|------------>
  
>----------------------------------------------------------------------------------------------------------------------------------------|
  |Adric Norris <landstander668@xxxxxxxxx>                                      
                                                           |
  
>----------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| To:        |
|------------>
  
>----------------------------------------------------------------------------------------------------------------------------------------|
  |oracle-l <oracle-l@xxxxxxxxxxxxx>                                            
                                                           |
  
>----------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| 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 can’t 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

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





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


Other related posts: