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

Contents

. 0

MySQL standby Creation (Master - Slave Replication). 2

Environment Details: 2

Configuration Steps on Master Node. 2

Step 1: Mandatory  parameters (/etc/my.cnf). 2

Step 2: User Creation. 2

Step 3: Master Status. 3

Configuration Steps on Slave Node. 3

Step 4: Mandatory Parameters (/etc/my.cnf). 3

Step 5: Connection Testing (Slave to Master). 3

Step 6:  Configuration of Slave process. 3

Step 7:  Start Slave. 4

Step 8: Slave Status. 4

Step 9: Test the Replication. 6

Switchover (Slave to Master). 6

    Master Node. 6

    Slave Node. 6

MySQL standby Creation (Master - Slave Replication)

Environment Details:

Master Node	10.0.0.81
Master Node Hostname	mysqlnode1.sukumar.com
Slave Node	10.0.0.82
Slave Node Hostname	mysqlnode2.sukumar.com

Configuration Steps on Master Node

Step 1: Mandatory  parameters (/etc/my.cnf)

                                                                       

Below Listed parameters are mandatory for Master node.  Make sure to set the unique server-id

[mysql@mysqlnode1 ~]$ cat /etc/my.cnf

[mysqld]

log-bin=/var/lib/mysql/mysql-bin

max_binlog_size=4096

binlog_format=row

socket=/var/lib/mysql/mysql.sock

server-id=1

binlog_do_db=demo

binlog-ignore-db=mysql

binlog-ignore-db=test

[client]

socket=/var/lib/mysql/mysql.sock

[mysqld_safe]

err-log=/var/log/mysqld-node1.log

Step 2: User Creation

Connect to Mysql and create dedicated user for replication. Grant the required privileges to connect from slave node.

mysql> grant replication slave on *.* to

    -> 'rep_user'@'10.0.0.82' identified by 'rep_user';

Query OK, 0 rows affected (0.00 sec)

mysql> select user, host from mysql.user

    -> where user='rep_user';

+----------+-----------+

| user     | host      |

+----------+-----------+

| rep_user | 10.0.0.82 |

+----------+-----------+

1 row in set (0.00 sec)

Step 3: Master Status

 Check the master status

mysql> show master status;

+------------------+----------+--------------+----------- --+-----------------+

| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set

+------------------+----------+--------------+--------------+-----------------+

| mysql-bin.000005 |      120 | demo         | mysql,test   |                  

+------------------+----------+--------------+--------------+-----------------+

1 row in set (0.00 sec)

Configuration Steps on Slave Node

Step 4: Mandatory Parameters (/etc/my.cnf)

 Below Listed parameters are mandatory for Slave node.  Make sure to set the unique server-id

mysql@mysqlnode2 ~]$ cat /etc/my.cnf

[mysqld]

log-bin=/var/lib/mysql/mysql2-bin

max_binlog_size=4096

binlog_format=row

socket=/var/lib/mysql/mysql.sock

server-id=2

[client]

socket=/var/lib/mysql/mysql.sock

Step 5: Connection Testing (Slave to Master)

Test the connection from slave node to Master node by using the below command.

mysql@mysqlnode2 ~]$ mysql -u rep_user -h mysqlnode1.sukumar.com -prep_user demo

Step 6:  Configuration of Slave process

This will configure slave and server will remember settings, so this replaces my.cnf settings in latest versions of MySQL server.

Note: Set the values appropriate with respect to the Master Node. Master_log_file and position values should be from master status on master node.

mysql@mysqlnode2 ~]$ mysql -u root -pwelcome123 demo

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.81',

       MASTER_USER='rep_user',

       MASTER_PASSWORD='rep_user',

       MASTER_PORT=3306,

       MASTER_LOG_FILE='mysql-bin.000005',

       MASTER_LOG_POS=120,

       MASTER_CONNECT_RETRY=10;

Step 7:  Start Slave

Start the slave process with the below command.

mysql> start slave;

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

If you are receiving “ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository” error. please reset the slave and proceed with step 6.

mysql> reset slave;

Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.81', MASTER_USER='rep_user', MASTER_PASSWORD='rep_user', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=120, MASTER_CONNECT_RETRY=10;

Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

Step 8: Slave Status

Make sure to check the below parameter status should be "YES" and the remaining values are appropriate.

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.0.0.81

                  Master_User: rep_user

                  Master_Port: 3306

                Connect_Retry: 10

              Master_Log_File: mysql-bin.000005

          Read_Master_Log_Pos: 120

               Relay_Log_File: mysqlnode2-relay-bin.000002

                Relay_Log_Pos: 283

        Relay_Master_Log_File: mysql-bin.000005

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 120

              Relay_Log_Space: 461

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1

                  Master_UUID: 8a701d66-2119-11e3-9ab2-0689f6cf2c77

             Master_Info_File: /var/lib/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

Step 9: Test the Replication

Perform the transactions on master node and slave node will automatically be in sync.

Switchover (Slave to Master)

Master Node

FLUSH LOGS closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file.

FLUSH LOGS;

Slave Node

Stop the slave process and reset the master. This will configure the master and it act according to my.cnf configuration settings.

STOP SLAVE;

RESET MASTER;