Re: Query not in code, but has vaguely known WHERE condition

as always, soe ideas, rather than solutions. what does program_id and PROGRAM_LINE# in v$sql tell you? You can join from v$session to v$process and find the os system process. Based on the nonsense of the query consider VPDB.

You can color a sql statement to show up in AWR. See: http://kerryosborne.oracle-guy.com/2009/04/hidden-sql-why-cant-i-find-my-sql-text/.

Or trace for the statement:
https://blogs.oracle.com/optimizer/entry/how_do_i_capture_a

Regards

Lothar
Am 26.01.15 um 13:53 schrieb Martin Klier:
> Hi listers,
>
> I found a strange SQL in our database, causing Library Cache and
> Cursor Sharing fun of all kinds, but no need to elaborate on that. The
> database is a jungle of dynamic SQL and parsing storms, so we had to
> enable CURSOR_SHARING=SIMILAR to work at all. No value in ranting on that.
>
> What I need to do is, to find where it comes from, to start
> investigating the case.
>
> The SQL text is like that:
>
> SELECT :"SYS_B_0"
> FROM DUAL
> WHERE a.div_id = b.div_id
> AND a.entity_id = b.entity_id
> AND a.attribute_id = :"SYS_B_1"
> AND a.other_id = :"SYS_B_2"
>
>
> We can see it being a top consumer or waiter in currently running /
> active sessions and ASH, but it never shows up in AWR.
> For sure, that's not one of ours, even when ignoring the fact that it
> does not make much sense for itself. All I recognise is the WHERE
> condition - it may match some of our MERGE ON clauses.
>
> So my questions:
>
> 1) How does the DB come to creating this kind of SQL text?
> 2) What's the use of it, the mechanism behind?
> 3) How can I find out where it is triggered?
>
> Thank you all in advance!
> --
> Freundliche Grüße / Best regards
>
> Martin Klier
> Performing Databases GmbH
> Oracle ACE

-- 


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

*Lothar Flatz*
Mag. rer. soc. oec.
Senior Principal Expert


*Diso AG*
Dienstleistungen und Software

Morgenstrasse 1
3073 Gümligen
Switzerland


Phone: 	+41 31 958 90 90
Mobile: 	+41 78 6264331
Fax: 	+41 31 958 90 99



chronoskope





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