Session level parallelism

If you’ve got a stack of large I/O operations to perform, you might want to take advantage of the all of the cores on your server. However, if the scripts are already in place, you might not want to be editing them individually to carefully add parallelism. A little known command is that you can set parallelism at session level. For example, we can control the DDL operations as shown below:


SQL> select * from V$PX_SESSION;

no rows selected

SQL> ALTER SESSION FORCE PARALLEL DDL PARALLEL 5;

Session altered.

SQL> create table T as
  2  select d.*
  3  from dba_Objects d, ( select 1 from dual connect by level <= 20 )
  4  /

Table created.

[in another session, whilst the above is running]
SQL> select * from V$PX_SESSION;

SADDR                   SID    SERIAL#      QCSID  QCSERIAL#  QCINST_ID SERVER_GROUP SERVER_SET    SERVER#     DEGREE REQ_DEGREE     CON_ID
---------------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------
000007FFBD785488        252      27078        474      63158          1            1          1       1     5          5          0
000007FFBD9631D8        481       7594        474      63158          1            1          1       2     5          5          0
000007FFBDB38BE8        714      34791        474      63158          1            1          1       3     5          5          0
000007FFBD5A5668         24      50589        474      63158          1            1          1       4     5          5          0
000007FFBD787558        251      46246        474      63158          1            1          1       5     5          5          0
000007FFBD971788        474      63158        474                                                                                 0

6 rows selected.


and similarly when it comes to indexing the table



SQL> create index IX on T ( object_id, owner );

Index created.

[in another session, whilst the above is running]
SQL> select * from V$PX_SESSION;

SADDR                   SID    SERIAL#      QCSID  QCSERIAL#  QCINST_ID SERVER_GROUP SERVER_SET    SERVER#     DEGREE REQ_DEGREE     CON_ID
---------------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------
000007FFBD785488        252      62878        474      12776          1            1          1       1     5          5          0
000007FFBD9631D8        481      38744        474      12776          1            1          1       2     5          5          0
000007FFBDB38BE8        714      30697        474      12776          1            1          1       3     5          5          0
000007FFBD5A5668         24      22049        474      12776          1            1          1       4     5          5          0
000007FFBD787558        251      57640        474      12776          1            1          1       5     5          5          0
000007FFBD967378        479      34266        474      12776          1            1          2       1     5          5          0
000007FFBDB3CD88        712      42043        474      12776          1            1          2       2     5          5          0
000007FFBD5B9E88         14      32512        474      12776          1            1          2       3     5          5          0
000007FFBD776ED8        259      42473        474      12776          1            1          2       4     5          5          0
000007FFBD95F038        483      37664        474      12776          1            1          2       5     5          5          0
000007FFBD971788        474      12776        474                                                                                 0

11 rows selected.



One thought on “Session level parallelism

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s