If Parallel_servers_target is less than parallel_max_servers, parallel statement queuing can occur, if not,
it will not because the parallel_servers_target limit will be reached before Auto DOP queuing logic kicks in.
Test Table Creation
Created a table test_dop with dba_objects data.
Explain plan with Parallel hint
Check the Explain Plan by passing the Parallel Hint.
Explain plan with DOP
Check the Explain Plan by enabling the DOP, but received an error.
Collect the schema stats and tried again. (Number of Rows has been increased)
There is no use of enabling the DOP as it is still skipping it because of IO calibrate statistics are missing.
Make sure IO_CALIBRATION_STATUS should be READY.
Make sure the below parameter settings, to make the IO_CALIBRATION_STAUS ready.
disk_asynch_io = true
filesystemio_options = asynch
Bounce the DB as it is mandatory for this parameter change.
Use the below PL-SQL code to change the IO_CALIBRATION_STATUS to READY
Output should be like below.
max_iops = 89
latency = 10
max_mbps = 38
PL/SQL procedure successfully completed.
Without passing the parallel hint, we have achieved the Query parallelism by enabling the DOP.