It's how merge is implemented internally to support 1 odd feature https://timurakhmadeev.wordpress.com/2010/02/15/silent-ora-904-on-merge/ On Monday, January 26, 2015, Martin Klier <martin.klier@xxxxxxxxxxxxxxxxx> wrote: > Hi Jonathan, Lothar, > > seems we have a garbage generator in the database. :) > > In my case, it comes from within a scheduler job and a dynamically > generated program - but that does not matter. MOS confirmed what I felt - > the application will not issue this thingy against the DB, we would have > seen it in the app server logs. > > > > ========================== > > Oracle Support - 16 mins ago [ODM Question] > > Q > -- > When and why does the DB create SQLs like : > > SELECT :"SYS_B_0" > FROM DUAL > WHERE ... > > > A > - > We confirm this SQL statement is related to MERGE SQL statement. > It's an internal SQL command. > > ========================== > > > > What they don't say, why the heck. But maybe it helps you forming a > picture? > > > > -- > Freundliche Grüße / Best regards > > Martin Klier > Performing Databases GmbH > Oracle ACE > ------------------------------ > *From: *"Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx > <javascript:_e(%7B%7D,'cvml','jonathan@xxxxxxxxxxxxxxxxxx');>> > *To: *"Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx > <javascript:_e(%7B%7D,'cvml','oracle-l@xxxxxxxxxxxxx');>> > *Sent: *Monday, January 26, 2015 2:23:36 PM > *Subject: *RE: Query not in code, but has vaguely known WHERE condition > > > Have you lost some of the text in emailing it, or is it really this > garbaged ? > If the latter then perhaps it's appearing because it keeps failing parse > (which still gets it pushed into the shared pool) and is constantly cuasing > breaks and being re-issued. > Pick any session that tries to execute it, and enable trace for a few > minutes to see if that shows you anything showing up just before or after a > call to this thing. > > It won't show up in the AWR because it's always going to cause a syntax > error. > > > Regards > Jonathan Lewis > http://jonathanlewis.wordpress.com > @jloracle > ------------------------------ > *From:* oracle-l-bounce@xxxxxxxxxxxxx > <javascript:_e(%7B%7D,'cvml','oracle-l-bounce@xxxxxxxxxxxxx');> [ > oracle-l-bounce@xxxxxxxxxxxxx > <javascript:_e(%7B%7D,'cvml','oracle-l-bounce@xxxxxxxxxxxxx');>] on > behalf of Martin Klier [martin.klier@xxxxxxxxxxxxxxxxx > <javascript:_e(%7B%7D,'cvml','martin.klier@xxxxxxxxxxxxxxxxx');>] > *Sent:* 26 January 2015 12:53 > *To:* Oracle-L Freelists > *Subject:* Query not in code, but has vaguely known WHERE condition > > 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 > > -- Regards Timur Akhmadeev