Tag Archives: enable session level

Implementing Parallel DML Operations in Database

Implementing Parallel DML Operations in Database

Parallel DML Operation is disable by default. For enable fire the following command:

ALTER SESSION ENABLE PARALLEL DML;

to force parallel behaviour, regardless of the parallel degree you have placed on an object:

ALTER SESSION FORCE PARALLEL DML;

For session to run parallel DML operations:
You can run parallel session by specifying in hints or table have degree value more than 1. You can use parallel operation on all DML Statements INSERT, UPDATE & DELETE

1. Enable the session for Parallel DML operations:

ALTER SESSION ENABLE PARALLEL DML;

2. Run the parallel operation by specifying HInts in the statements:

INSERT /*+ PARALLEL(DEPT,4) */ INTO DEPT
SELECT /*+ PARALLEL(DEPT_COPY,4) */ * FROM DEPT_COPY;

UPDATE /*+ PARALLEL(EMP,4) */ EMP SET SAL = SAL*1.01 WHERE DEPTNO=10;

DELETE /*+ PARALLEL(EMP,4) */ FROM EMP WHERE DEPTNO=10;

If you want to delete or update large amount of data in bigger table in Enterprise edition then you can speed up with Parallel hints for the operations.

Note: Insert parallel is not work on single insert statement
Table having the trigger is not worked with parallel hints

Advertisements

Trace SQL at session level in Oracle

SQL trace at session level in Oracle

Oracle provide packages DBMS_MONITOR for tracing the SQL at session level. Tracing SQL queries will help to find out issue going with particular SQL and which session is running which SQL queries.

Enable and disable trace for SQL Query running from particular session or user

1. Determine the SID and serial# from following query by username/query.

-- From User
SELECT SID, SERIAL#, USERNAME FROM V$SESSION WHERE USERNAME = 'OE';
-- From SQL Query
set pagesize 100 linesize 132
col username format a10
col sql_text format a60
SELECT USERname, terminal, SID, a.module, SERIAL#, SQL_TEXT
FROM V$SESSION a, V$SQL b
WHERE a.SQL_ADDRESS = b.ADDRESS AND upper(SQL_TEXT) like '%SELECT * from HR.EMP%' AND username IS NOT NULL;

2. Execute the following command to enable the trace for particular SID and serial# number.
First Way:

For Enable:
exec DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27,serial_num => 60 , waits => true , binds => false);
For Disable:
exec DBMS_MONITOR.SESSION_TRACE_DISABLE( session_id => 27,serial_num => 60);

Note:
waits: true means include in trace file
binds: true means include in trace file

Second Way:

For Enable:
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
For Disable:
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

Checked the Enabled Traces in Database

DBA_ENABLED_TRACES view help to know about the trace is still enabled or disable.No row means all trace is disable in database. It is necessary to check because some session may generate lot of log files and may cause space crunch.
Verify the trace is enabled or disable:

SELECT * FROM DBA_ENABLED_TRACES;
SELECT * FROM DBA_ENABLED_AGGREGATIONS ;

Find the user is login in particular time and enable trace for that session

Following queries help us to find out the username “VPPILOT” connected to the database with login_time then we fetch the session id and and enable trace for particular session in between that time period.

Oracle 11g:
SELECT 'exec dbms_monitor.session_trace_enable(session_id=>' ||to_char(sid) ||',serial_num=>'||to_char(serial#)||',binds=>true,waits=>true) /* ' || program || ' - ' || username || ' - ' || to_char(logon_time, 'DD-MM-YYYY HH24:MI' ) ||' */;'
FROM v$session where (lower(program) like 'staff%' or lower(program) like 'vagtplan%' or lower(program) like 'vaktplan%')
and trunc(logon_time) = trunc(sysdate) and to_char(logon_time, 'HH24:MI') between '2000' and '2030' and username = 'VPPILOT';


Oracle 10g
SELECT 'execute dbms_system.set_sql_trace_in_session(' ||to_char(sid) ||','||to_char(serial#)||', true) /* ' || program || ' - ' || username || ' - ' || to_char(logon_time, 'DD-MM-YYYY HH24:MI' ) ||' */;'
FROM v$session where (lower(program) like 'staff%' or lower(program) like 'vagtplan%' or lower(program) like 'vaktplan%')
and trunc(logon_time) = trunc(sysdate) and to_char(logon_time, 'HH24:MI') between '2000' and '2030' and username = 'VPPILOT';