Hi,
I am trying to update a table using the parallel hint but the explain plan shows that the DML is not using the hint. I also altered my session to enable parallel DML - ALTER SESSION ENABLE PARALLEL DML;
--- create table script
drop table test_tab;
create table test_tab
as
select rownum num, 'value of session_id' session_id from dual
connect by level <= 1000000;
-- SQL statement
update /*+ PARALLEL(test_tab,4) */ test_tab
set num = num/100, session_id = SYS_CONTEXT('USERENV','SESSIONID');
--Explain Plan
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 920K| 29M| 1130 (1)| 00:00:14 |
| 1 | UPDATE | TEST_TAB | | | | |
| 2 | TABLE ACCESS FULL| TEST_TAB | 920K| 29M| 1130 (1)| 00:00:14 |
-------------------------------------------------------------------------------
-- parallel parameter values
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ----------- --------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 4
parallel_min_percent integer 0
NAME TYPE VALUE
------------------------------------ ----------- --------
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 128
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
-- Database version
11.2.0.2.0
Can you tell me why the explain plan shows that the update operation is not spawning into multiple threads?
Thank you!