Re: Query not in code, but has vaguely known WHERE condition
- From: Lothar Flatz <l.flatz@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 26 Jan 2015 14:13:34 +0100
Hi Martin,
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
begin:vcard
fn:Lothar Flatz
n:Flatz;Lothar
email;internet:lflatz@xxxxxxx
tel;cell:+41786264331
version:2.1
end:vcard
Other related posts:
