Skip to Main Content
  • Questions
  • Different sql id/sql text showing up in v$session

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: October 13, 2021 - 7:41 am UTC

Last updated: October 20, 2021 - 10:03 am UTC

Version: Oracle 12c

Viewed 1000+ times

You Asked

Hello!

I have a particular ETL job that fires below 4 select queries on a view definition to our Oracle database -

select * from view where mod(id,4) = 1;
select * from view where mod(id,4) = 2;
select * from view where mod(id,4) = 3;
select * from view where mod(id,4) = 0;


However when the queries start executing and I check v$session for the 4 sessions running these 4 queries, I see a common sql id showing up for all these 4 queries and the sql text for this sql id seems completely unfamiliar/unrelated to the queries that are actually being executed. So I am a bit lost as I have not come across such scenario earlier. What could this sql id be and why would it be showing up in v$session for all 4 different queries?

and Chris said...

So what exactly is the SQL you see in v$session?

Many tools will wrap your statement with an outer query to do things like add top-N processing. If you have cursor_sharing = force, or the tool substitutes literals for binds itself this could lead to the effects you're seeing.

Or perhaps the tool is running common pre- or post-statement actions. But I'm just guessing here - it's hard to say without more details of what the job is and the SQL you see.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.