-- Parallel execution (processing) allows query to use multiple CPU 
-- and I/O resources attain optimum through put in a single operation. 
-- The parallel processing (MPP) feature is very useful in large 
-- databases.

-- Check the parallel options configured in the database 

SELECT ' '||parameter||' -> '||value db_options 
FROM sys.v_$option 
WHERE INSTR(LOWER(parameter),'parallel') >0
ORDER BY db_options;

-- The DBA can check for parallel execution parameter
SHOW PARAMETER parallel_max_servers

-- The default DOP is 
-- DOP = 2 x number of CPU cores
-- Cluster configuration: 
-- DOP = 2 x number of CPU cores x number of nodes

-- Alter session to enable parallel execution using default DOP 
-- For DML operations
ALTER SESSION ENABLE PARALLEL DML;

-- For DDL operations
ALTER SESSION ENABLE PARALLEL DDL;

-- To query database objects
ALTER SESSION ENABLE PARALLEL QUERY;

-- Using parallel hint in a SQL query with 8-degree parallelism 
-- ALTER SESSION FORCE PARALLEL DML;
-- The above "FORCE" clause can cause parallel hints to be discarded 

SELECT /*+ parallel(t, 8) */
 COUNT(*) data_count          --also can be columns required 
FROM  very_large_table t
;

-- The table can be configured to execute in parallel
ALTER TABLE very_large_table ENABLE PARALLEL;

-- Analyze chunks created by a logged in user
SELECT * FROM user_parallel_execute_chunks;

-- Parallel execution (PX) query
SELECT * FROM  gv$px_process;