How to find and enable/disable parallel DML, DDL, Query?

In the view v$session there are columns that can tell if parallel DDL, DML, Query is enabled/disabled.
PDML_ENABLED and PDML_STATUS – indicates is Parallel DML operation is enabled/disabled, the default is DISABLED.
PDDL_STATUS – Indicates if parallel DDL is enabled/disabled, by the default value is ENABLED
PQ_STATUS – Indicates if parallel query is enabled/disabled, the default value is ENABLED

The values can set at session level.

Example:
SQL> select PDML_ENABLED, PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$session where sid = (select sid from v$mystat
where rownum = 1);

PDM PDML_STA PDDL_STA PQ_STATU
— ——– ——– ——–
NO DISABLED ENABLED ENABLED

-- enables parallel DML
SQL> alter session enable parallel dml;

Session altered.

-- Display parallel DML is set to enabled
SQL> select PDML_ENABLED, PDML_STATUS FROM V$session where sid = (select sid from v$mystat where rownum = 1);

PDM PDML_STA
— ——–
YES ENABLED

-- To disable parallel DML
SQL> alter session disable parallel dml;

Session altered.

-- To disable parallel DDL
SQL> alter session disable parallel ddl;

Session altered.

-- To disable parallel query
SQL> alter session disable parallel query;

Session altered.

-- Print the parallel DDL, DML and Query for the current session
SQL> select PDML_ENABLED, PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$session where sid = (select sid from v$mystat where rownum = 1);

PDM PDML_STA PDDL_STA PQ_STATU
— ——– ——– ——–
NO DISABLED DISABLED DISABLED

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