Pre-Requisite
SQL> sho parameter parallel_degree
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
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.
SQL> sho parameter parallel_servers_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_servers_target integer 16
SQL> sho parameter parallel_max_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 40
Test Table Creation
Created a table test_dop with dba_objects data.
SQL> sho parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string MANUAL
SQL> explain plan for select * from test_dop;
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 381934326
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 298 (0)| 00:00:04 |
| 1 | TABLE ACCESS FULL| TEST_DOP | 1 | 207 | 298 (0)| 00:00:04 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Explain plan with Parallel hint
Check the Explain Plan by passing the Parallel Hint.
SQL> explain plan for select /*+ parallel (test_dop,8) */ * from test_dop;
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 365997929
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 41 (0)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1 | 207 | 41 (0)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 1 | 207 | 41 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST_DOP | 1 | 207 | 41 (0)| 00:00:01 |
---------------------------------------------------------------------------------
- dynamic sampling used for this statement (level=2)
Explain plan with DOP
Check the Explain Plan by enabling the DOP, but received an error.
SQL> alter system set parallel_degree_policy=auto;
System altered.
SQL> sho parameter parallel_degree_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string AUTO
SQl> explain plan for select * from test_dop;
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 381934326
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 298 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_DOP | 1 | 207 | 298 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: skipped because of IO calibrate statistics are missing
Stats Collection
Collect the schema stats and tried again. (Number of Rows has been increased)
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('sukku', 'test_dop');
PL/SQL procedure successfully completed.
SQl> explain plan for select * from test_dop;
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 381934326
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51 | 4590 | 298 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_DOP | 51 | 4590 | 298 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
There is no use of enabling the DOP as it is still skipping it because of IO calibrate statistics are missing.
IO Calibration
Make sure IO_CALIBRATION_STATUS should be READY.
SQL> select status from V$IO_CALIBRATION_STATUS;
STATUS
-------------
NOT AVAILABLE
Make sure the below parameter settings, to make the IO_CALIBRATION_STAUS ready.
disk_asynch_io = true
filesystemio_options = asynch
SQL> sho parameter disk_asynch_io
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean TRUE
SQL> sho parameter filesystemio_options
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
SQL> alter system set filesystemio_options=asynch scope=spfile;
System altered.
Database Restart
Bounce the DB as it is mandatory for this parameter change.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.
SQL> sho parameter filesystemio_options
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string ASYNCH
Use the below PL-SQL code to change the IO_CALIBRATION_STATUS to READY
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
end;
/
Output should be like below.
max_iops = 89
latency = 10
max_mbps = 38
PL/SQL procedure successfully completed.
SQL> select status from v$IO_CALIBRATION_STATUS;
STATUS
-------------
READY
DOP enabled
Without passing the parallel hint, we have achieved the Query parallelism by enabling the DOP.
SQL> explain plan for select * from test_dop;
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 365997929
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51 | 4590 | 166 (0)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 51 | 4590 | 166 (0)| 00:00:01 |
| 3 | PX BLOCK ITERATOR | | 51 | 4590 | 166 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST_DOP | 51 | 4590 | 166 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
Comments
Post a Comment