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

On Monday, January 26, 2015, Martin Klier <martin.klier_at_performing-db.com> 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_at_jlcomp.demon.co.uk
> <javascript:_e(%7B%7D,'cvml','jonathan_at_jlcomp.demon.co.uk');>>
> *To: *"Oracle-L Freelists" <oracle-l_at_freelists.org
> <javascript:_e(%7B%7D,'cvml','oracle-l_at_freelists.org');>>
> *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
> _at_jloracle
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org
> <javascript:_e(%7B%7D,'cvml','oracle-l-bounce_at_freelists.org');> [
> oracle-l-bounce_at_freelists.org
> <javascript:_e(%7B%7D,'cvml','oracle-l-bounce_at_freelists.org');>] on
> behalf of Martin Klier [martin.klier_at_performing-db.com
> <javascript:_e(%7B%7D,'cvml','martin.klier_at_performing-db.com');>]
> *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

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