Recently we were facing an issue in Oracle where SELECT queries on a particular transaction table was opening multiple sessions in Oracle (both 11g and 12c versions).
We checked in multiple environments and using various IDEs/Tools (Toad, SQL Developer etc) and it occurred everywhere.
We also noticed that this issue was happening with only one particular table alone and irrespective of indexes on table or joins in query.
Finally we figured out the reason for this.
The table was created with parallelism option.This is identified by DOP (Degree of Parallelism) setting. For this we have to check 'degree' column in user_table system view against the table. If it shows a value greater than 1, then it means that table has been created with parallelism option ON and Oracle might open multiple sessions when querying the table.
We checked in multiple environments and using various IDEs/Tools (Toad, SQL Developer etc) and it occurred everywhere.
We also noticed that this issue was happening with only one particular table alone and irrespective of indexes on table or joins in query.
Finally we figured out the reason for this.
The table was created with parallelism option.This is identified by DOP (Degree of Parallelism) setting. For this we have to check 'degree' column in user_table system view against the table. If it shows a value greater than 1, then it means that table has been created with parallelism option ON and Oracle might open multiple sessions when querying the table.