Parallel threads on an Oracle database

In this little guide, I will describe how to find out, how many parallel threads are used on an Oracle database. The first place we need to look, is in the v$session view, where all information about sessions are stored.

select *
  from v$session;

The output from the above sql, is all sessions/connections to the database, is listed – active as well as inactive.

With a parallel query, not always all threads are active at the same time, therefore we should not limit to active sessions, as the threads are held even, when the session is inactive. However, we are only interested in parallel sessions, therefore we must look at the PROGRAM column. If the session is part of a parallel query, the program column will look like this oracle@host (P010). The (P010) indicates that it is the 10’th session part of a parallel query. If it is a stand alone session, it will only look like oracle@host. This we can use to identify parallel sessions, with the following SQL.

select *
  from v$session s
 where program like ‘%(P%’;

Now we only needs to filter out, background and oracle sessions.

select *
  from v$session s
 where (s.username is not null)
   and (nvl (s.osuser, 'x') <> 'SYSTEM')
   and (s.type <> 'BACKGROUND')
   and program like '%(P%'

Last we group by username, in order to find out how many parallel threads each username uses.

select username, count(*) user_parallel
  from v$session s
 where (s.username is not null)
   and (nvl (s.osuser, 'x') <> 'SYSTEM')
   and (s.type <> 'BACKGROUND')
   and program like '%(P%'
group by rollup(username)

Leave a Reply Cancel reply