Thursday, 26 September 2013

Degree of Parallelism - 11gR2 feature


 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

No comments: