0

Recently we upgraded our env from Jdk1.6 to Jdk1.8 and from Jboss server 4.3 to Jboss 7.1.0EAP without any code change. We are facing a peculiar problem where we are getting same SQL_ID having different session id and they are running long and long without any stop. Can anyone tell me what is the cause of the problem. Or any pointer will help what is causing this unending long sessions

enter image description here

sromit
  • 900
  • 3
  • 16
  • 43
  • There is no problem in the fact that two (or more sessions) execute the same statement (with identical `SQL_ID`) the problem is most probably that the *execution plan* is inefficient. See [here](https://stackoverflow.com/a/34975420/4808122) some hints what you should investigate or post to get some advice. – Marmite Bomber Jan 28 '20 at 05:20
  • SQL_ID is a hash value of the SQL text itself. Irrespective of how many times you execute, a SQL with no changes made to its text would always yield the same sql_id in a database. in your scenario, the same SQL is being executed by multiple sessions in parallel. It is a valid scenario unless your business logic expects these SQL's to run in a silo. – Krishna Jan 28 '20 at 06:15

1 Answers1

3

That same SQL_IDs is just two sessions are performing the same query. It happens when the query is literally identical. Normally they are looking like this

select * from some_table where some_column = :some_value;

Have a look at ":" before some_value. This is what they call bind variable.

So if your app running this query twice (even when the parameter values are different) you'll get the same sql_id using across the sessions.

Perhaps the bind variables capture feature from

select * from v$sql_bind_capture where sql_id = 'your_sql_id';

might help you in order to find the values of variables sessions are using in order to perform that long

ekochergin
  • 4,109
  • 2
  • 12
  • 19