Oracle session parameters and MySQL session variables - Oracle to Aurora MySQL Migration Playbook
Oracle usageMySQL usageOracle and MySQL session parameter examples

Oracle session parameters and MySQL session variables

With AWS DMS, you can configure Oracle session parameters and MySQL session variables to optimize performance, control resource usage, and customize database behavior during migration tasks. Oracle session parameters and MySQL session variables are special configuration settings that influence how the database engine operates and processes data. These settings can be crucial for ensuring efficient data transfer, minimizing resource contention, and adhering to organizational policies or regulatory requirements.

Feature compatibility AWS SCT / AWS DMS automation level AWS SCT action code index Key differences

One star feature compatibility

N/A

N/A

SET options are significantly different.

Oracle usage

Certain Oracle database parameters and configuration options are modifiable at the session level using the ALTER SESSION command. However, not all Oracle configuration options and parameters can be modified on a per-session basis. To view a list of all configurable parameters that can be set for the scope of a specific session, query the v$parameter view as shown in the following example.

SELECT NAME, VALUE FROM V$PARAMETER WHERE ISSES_MODIFIABLE='TRUE';

Examples

Change the NLS_LANAUGE codepage parameter of the current session.

alter session set nls_language='SPANISH'

Sesi≤n modificada.

alter session set nls_language='ENGLISH';

Session altered.

alter session set nls_language='FRENCH';

Session modifiΘe.

alter session set nls_language='GERMAN';

Session wurde geΣndert.

Specify the format of date values returned from the database using the NLS_DATE_FORMAT session parameter.

select sysdate from dual;

SYSDATE
SEP-09-17

alter session set nls_date_format='DD-MON-RR';
Session altered.

select sysdate from dual;

SYSDATE
09-SEP-17

alter session set nls_date_format='MM-DD-YYYY';
Session altered.

select sysdate from dual;

SYSDATE
09-09-2017

alter session set nls_date_format='DAY-MON-RR';
Session altered.

For more information, see Changing Parameter Values in a Parameter File in the Oracle documentation.

MySQL usage

MySQL provides session-modifiable parameters configured using the SET SESSION command. Configuration of parameters using SET SESSION is only applicable in the current session. To view the list of parameters that you can set with SET SESSION, see Dynamic System Variables and search for variables with session scope.

Examples of commonly used session parameters:

  • autocommit — Specify if changes take effect immediately or if an explicit COMMIT command is required.

  • character_set_client — Set the character set for the client.

  • default_storage_engine — Set the default storage engine.

  • foreign_key_checks — Set whether or not to run FK checks.

  • innodb_lock_wait_timeout — Set how much time the transaction should wait to acquire a row lock.

Examples

Change the time zone of the connected session.

SELECT now();

now()
2018-02-26 12:13:25

SET SESSION TIME_ZONE = '+10:00';
SELECT now();

now()
2018-02-26 22:14:03

You can also use a time zone name such as Europe/Helsinki instead of +10:00.

Oracle and MySQL session parameter examples

Parameter purpose Oracle MySQL

Configure time and date format

 
ALTER SESSION
SET nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

N/A

Configure the current default schema or database

 
ALTER SESSION
SET current schema='schema_name'

N/A

Generate traces for specific errors

 
ALTER SESSION
SET events '10053 trace name context forever';

N/A

Run trace for a SQL statement

 
ALTER SESSION
SET sql_trace=TRUE;
ALTER SYSTEM
SET EVENTS 'sql_trace [sql:&&sql_id]
    bind=true,
    wait=true';
 
SET GLOBAL general_log = 'ON';

Modify query optimizer cost for index access

 
ALTER SESSION
SET optimizer_index_cost_adj = 50
 
SET SESSION optimizer_switch= ?

You can turn on and off other strategies. For more information, see Switchable Optimizations in the MySQL documentation.

Modify query optimizer row access strategy

 
ALTER SESSION
SET optimizer_mode=all_rows;
 
SET SESSION optimizer_switch= ?

You can turn on and off other strategies. For more information, see Switchable Optimizations in the MySQL documentation.

Memory allocated to sort operations

 
ALTER SESSION
SET sort_area_size=6321;
 
SET SESSION sort_buffer_size=32768;

Memory allocated to hash-joins

 
ALTER SESSION
SET hash_area_sizee= 1048576000;
 
SET SESSION join_buffer_size=1048576000;

For more information, see SET Syntax for Variable Assignment in the MySQL documentation.