RE: Query not in code, but has vaguely known WHERE condition
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Jan 2015 21:13:00 +0000
Timur,
Thanks for that.
Nice to know.
There may be a couple of other examples of a similar trick - I feel I've seen
the pattern elsewhere before but never chased it down.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Timur Akhmadeev [timur.akhmadeev@xxxxxxxxx]
Sent: 26 January 2015 17:55
To: martin.klier@xxxxxxxxxxxxxxxxx
Cc: Jonathan Lewis; l flatz; Oracle-L Freelists
Subject: Re: Query not in code, but has vaguely known WHERE condition
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<mailto: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<UrlBlockedError.aspx>>
To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx<UrlBlockedError.aspx>>
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<UrlBlockedError.aspx>
[oracle-l-bounce@xxxxxxxxxxxxx<UrlBlockedError.aspx>] on behalf of Martin Klier
[martin.klier@xxxxxxxxxxxxxxxxx<UrlBlockedError.aspx>]
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
Other related posts:
