Gain on the roundabout, but lose on the swings

Everyone likes to put their foot to the floor — but blocking other traffic just slows everyone else down.

An example of a simple bulk load into an existing table is as follows:

INSERT INTO &tbl_1
SELECT * FROM &tbl_2;

The simple bulk load can occur while the application is being used by business users. A datapump import can also do essentially the same task from either a network link, or a dump file set.

As for a bulk load, the data pump import can also use the following access methods:

  • Conventional load.
  • Direct path load.

The datapump import can load data while the application is in use.

The question is, could there be a performance impact to the application during a direct path load, compared to a conventional load?

Impact on the application during a conventional load

The default access method for a datapump import is to use the direct path. The datapump import will also not attempt to load tables that already exists.

Consequently, to perform a conventional load into existing tables, the datapump import statement will need the following clauses:

  • table_exists_action=append
  • access_method=conventional

So to perform a conventional load into an existing schema, the full datapump import statement will be:

> impdp system table_exists_action=append access_method=conventional parfile=d:\importdp.par

The head of the logfile of the datapump import is below:

Import: Release 12.2.0.1.0 — Production on Sun Nov 4 20:42:15 2018
Copyright © 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0–64bit Production
Database Directory Object has defaulted to: “DATA_PUMP_DIR”.
Master table “SYSTEM”.”IMPDP” successfully loaded/unloaded
Starting “SYSTEM”.”IMPDP”: system/******** table_exists_action=append access_method=conventional parfile=d:\importdp.par
Processing object type SCHEMA_EXPORT/USER
..

Now switch from command line mode, to interactive mode with a ctrl+c, then exit interactive mode:

Import> exit_client

Despite exiting the datapump client, the datapump import will continue to operate and is shown using DBA_DATAPUMP_JOBS dictionary view:

-— show the current datapump jobs
col owner_name format a10
col job_name format a10
col operation format a10
col job_mode format a10
col state format a10
SELECT * FROM DBA_DATAPUMP_JOBS;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE     DEGREE
—————————— ———————— ———————-- -------- --------- ------
SYSTEM IMPDP IMPORT FULL EXECUTING 1
SQL>

Proving the datapump import access method is conventional is easy by querying the transaction statistics over time via an SQLPLUS client:

col name format a40
SELECT name, status, noundo, used_ublk, log_io, phy_io
FROM v$transaction;
NAME                            STATUS NOU USED_UBLK  LOG_IO PHY_IO
———————————————————————-------- ------ --- --------- ------- ------
ACTIVE NO 1492 239773 128
SQL> /
NAME                            STATUS NOU USED_UBLK  LOG_IO PHY_IO
———————————————————————-------- ------ --- --------- ------- ------
ACTIVE NO 5532 883356 193
SQL> /
NAME                            STATUS NOU USED_UBLK  LOG_IO PHY_IO
———————————————————————-------- ------ --- --------- ------- ------
ACTIVE NO 7890 1257154 217
SQL>

So the transaction statistics show there are rapidly increasing undo blocks being used, and this behaviour is consistent with an access method that is conventional.

The quantity of redo records generated by the session will also hint to an access method that is either the direct path or conventional:

col name format a30
SELECT sid, name, TRUNC(value/1024/1024,1) "MB"
FROM v$sesstat ss
INNER JOIN v$statname sn
ON (ss.statistic# = sn.statistic#)
WHERE name IN ('redo size for direct writes', —- direct path only
'redo size') —- conventional plus direct path
AND sid IN (SELECT sid
FROM v$session
WHERE program='impdp.exe' or module like 'Data Pump%')
ORDER BY sid, name;
SID NAME                             MB
——— ——————————------------------ ------
16 redo size .3
16 redo size for direct writes .1
139 redo size 403.4
139 redo size for direct writes .5
271 redo size .8
271 redo size for direct writes 0
SQL> /
SID NAME                             MB
——— ——————————------------------ ------
16 redo size .3
16 redo size for direct writes .1
139 redo size 2015
139 redo size for direct writes .5
271 redo size .8
271 redo size for direct writes 0
SQL> /
SID NAME                             MB
——— ——————————------------------ ------
16 redo size .3
16 redo size for direct writes .1
139 redo size 4024.7
139 redo size for direct writes .5
271 redo size .8
271 redo size for direct writes 0
SQL >

The session statistics show that the ‘redo size for direct writes’ is insignificant, while the ‘redo size’ metric is steadily increasing.

This phenomena suggests that conventional redo records are being created.

To simulate an application being used by business users, additional sessions need to be created while the datapump import is loading.

So three sessions are created via SQLPLUS, and insert, update, and delete statements are executed in each one.

Using the SET TRANSACTION NAME statement, the transactions will be named:

  • small conventional bulk load
  • update
  • delete

The naming of the three transactions will make it easier to identify them, as the conventional load from the datapump import will not be named.

Querying the transaction statistics for the four sessions (including the load from the datapump import) will give some indication of the performance:

col name format a40
SELECT name, status, noundo, used_ublk, log_io, phy_io
FROM v$transaction;
NAME                            STATUS NOU USED_UBLK  LOG_IO PHY_IO
———————————————————————-------- ------ --- --------- ------- ------small conventional bulk load ACTIVE NO 53 25901 407
delete ACTIVE NO 2813 603415 13420
update ACTIVE NO 533 142307 65112
ACTIVE NO 17309 2811209 159
SQL> /
NAME                            STATUS NOU USED_UBLK  LOG_IO PHY_IO
———————————————————————-------- ------ --- --------- ------- ------
small conventional bulk load ACTIVE NO 241 103226 583
delete ACTIVE NO 3132 708728 26231
update ACTIVE NO 977 250572 97026
ACTIVE NO 20429 3300952 214
SQL> /
NAME                            STATUS NOU USED_UBLK  LOG_IO PHY_IO
———————————————————————-------- ------ --- --------- ------- ------
small conventional bulk load ACTIVE NO 484 206412 746
delete ACTIVE NO 5206 1186624 26845
update ACTIVE NO 1078 284712 131124
ACTIVE NO 34612 5602518 304
SQL>

All the transaction statistics are increasing, which suggests no transaction from one session is blocking the other transactions in the other sessions.

A more graphical way to monitor blocking transactions (or sessions) is from the SQL Developer client from the ‘instance viewer’ menu:

The SQL Developer client shows that there several active sessions, and no sessions are being blocked.

Waiting and blocking sessions can also be determined by querying the dictionary views from an SQLPLUS client:

-— determine if a session is waiting due to a block
col lock_type format a15
col lock_id1 format 999999
col lock_id2 format 999999
select * from dba_waiters;
no rows selected
SQL>

So there are no sessions waiting for locks.

-— determine if a session is blocking
col mode_held format a10
col mode_requested format a10
col lock_id1 format a10
col lock_id2 format a10
col blocking_others format a15
col lock_type format a15
select *
from dba_lock
where blocking_others='Blocking'
order by session_id;
no rows selected
SQL>

There is no session blocking any other session, which is what we would expect if no session is waiting for a lock.

Without blocking of other sessions, there is lower chance of a datapump import using a conventional access method from impacting the performance of the application.

Impact on the application during a direct path load

To determine the possible impact of a direct path load impacting an application the datapump import will be repeated.

This time the ‘access_method=conventional’ clause will be disregarded, so that the direct path will be used.

> impdp system table_exists_action=append parfile=d:\importdp.par

The head of the logfile of the datapump import is below:

Import: Release 12.2.0.1.0 — Production on Sun Nov 4 22:51:17 2018
Copyright © 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0–64bit Production
Database Directory Object has defaulted to: “DATA_PUMP_DIR”.
Master table “SYSTEM”.”IMPDP” successfully loaded/unloaded
Starting “SYSTEM”.”IMPDP”: system/******** table_exists_action=append parfile=d:\importdp.par
Processing object type SCHEMA_EXPORT/USER
..

Now switch from command line mode, to interactive mode with a ctrl+c, then exit interactive mode:

Import> exit_client

The datapump import will continue to operate and is shown using DBA_DATAPUMP_JOBS:

-— show the current datapump jobs
col owner_name format a10
col job_name format a10
col operation format a10
col job_mode format a10
col state format a10
SELECT * FROM DBA_DATAPUMP_JOBS;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE     DEGREE
—————————— ———————— ———————-- -------- --------- ------
SYSTEM IMPDP IMPORT FULL EXECUTING 1
SQL>

Querying the transaction statistics will also determine whether the direct path or conventional access method is being used:

col name format a40
SELECT name, status, noundo, used_ublk, log_io, phy_io
FROM v$transaction;
NAME                            STATUS NOU USED_UBLK  LOG_IO PHY_IO
———————————————————————-------- ------ --- --------- ------- ------
ACTIVE NO 1 12406 12478
SQL> /
NAME                            STATUS NOU USED_UBLK  LOG_IO PHY_IO
———————————————————————-------- ------ --- --------- ------- ------
ACTIVE NO 1 15442 18108
SQL> /
NAME                            STATUS NOU USED_UBLK  LOG_IO PHY_IO
———————————————————————-------- ------ --- --------- ------- ------
ACTIVE NO 1 22537 26684
SQL>

There are few undo blocks being used which suggests that the access method is using the direct path.

The session statistics should also indicate whether the access method is conventional, or the direct path:

col name format a30
SELECT sid, name, TRUNC(value/1024/1024,1) "MB"
FROM v$sesstat ss
INNER JOIN v$statname sn
ON (ss.statistic# = sn.statistic#)
WHERE name IN ('redo size for direct writes', -— direct path only
'redo size') — conventional plus direct path
and sid IN (SELECT sid
FROM v$session
WHERE program='impdp.exe' or module like 'Data Pump%')
ORDER BY sid, name;
SID NAME                             MB
——— ——————————------------------ ------
16 redo size .3
16 redo size for direct writes .1
142 redo size 211.4
142 redo size for direct writes 209.8
379 redo size .8
379 redo size for direct writes 0
SQL> /
SID NAME                             MB
——— ——————————------------------ ------
16 redo size .3
16 redo size for direct writes .1
142 redo size 949.1
142 redo size for direct writes 947.3
379 redo size .8
379 redo size for direct writes 0
SQL> /
SID NAME                             MB
——— ——————————------------------ ------
16 redo size .3
16 redo size for direct writes .1
142 redo size 1668.1
142 redo size for direct writes 1666.2
379 redo size .8
379 redo size for direct writes 0
SQL>

The session statistics show that the ‘redo size for direct writes’ is increasing significantly, so the access method is the direct path.

Again, to simulate an application being used by business users, additional sessions need to be created while the datapump import is loading.

While the direct path load is in progress, three sessions are created in an SQLPLUS client, and insert, update, and delete statements are executed in each one.

In total, there are four sessions (including the datapump import) and the corresponding transaction statistics are shown below:

col name format a40
SELECT name, status, noundo, used_ublk, log_io, phy_io
FROM v$transaction;
NAME                            STATUS NOU USED_UBLK  LOG_IO PHY_IO
———————————————————————-------- ------ --- --------- ------- ------
ACTIVE NO 1 12406 12478
SQL> /
NAME                            STATUS NOU USED_UBLK  LOG_IO PHY_IO
———————————————————————-------- ------ --- --------- ------- ------
ACTIVE NO 1 60309 82231
SQL> /
NAME                            STATUS NOU USED_UBLK  LOG_IO PHY_IO
———————————————————————-------- ------ --- --------- ------- ------
ACTIVE NO 4 127661 164033
SQL>

Now there is a problem.

Previously with the conventional access method, there were four transactions in progress at the same time.

However now there is just a single transaction despite four statements executing!

Graphically the SQL Developer can show the status of the sessions.

The SQL Developer shows that there are several active sessions, but three sessions being blocked.

The DBA_WAITERS dictionary view will also indicate which sessions are waiting:

-— determine if a session is waiting due to a block
col lock_type format a15
col lock_id1 format 999999
col lock_id2 format 999999
select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUE
——————————————— ——————————————- --------- --------- ----------
141 278 DML Exclusive Row-X (SX)
146 278 DML Exclusive Row-X (SX)
271 278 DML Exclusive Row-X (SX)
SQL>

Previously, for the conventional access method, no sessions were being blocked, but now there are three sessions affected.

The blocking session is also easy to determine from the DBA_LOCK dictionary view.

-— determine if a session is blocking
col mode_held format a10
col mode_requested format a10
col lock_id1 format a10
col lock_id2 format a10
col blocking_others format a15
col lock_type format a15
select *
from dba_lock
where blocking_others='Blocking'
order by session_id;
SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE BLOCKING_OTHERS
—————————— ————————— ————————- ---------- ---------------
142 DML Exclusive None Blocking
SQL>

Session 142 is the direct path load, so now the datapump import is having a detrimental effect on the application.

Oracle also makes available a script UTLLOCKT, to show the blockers and waiters in a tree format:

-— show all the blocking and requested locks
@?\RDBMS\ADMIN\utllockt
WAITING_SESSION LOCK_TYPE MODE_REQUE MODE_HELD LOCK_ID1 LOCK_ID2
——————————————— ————————- ---------- --------- -------- --------
142 None
141 DML Row-X (SX) Exclusive 87471 0
146 DML Row-X (SX) Exclusive 87471 0
271 DML Row-X (SX) Exclusive 87471 0
SQL>

Now that we know there are three sessions being blocked by a direct path load, so the next question is, what is the impact?

By querying the transaction enqueue statistics, the duration of the blocking caused by the direct path load can be determined.

column DECODE(lmode,0,'none',1,'null',2,'rowS',3,'rowX',4,'share',5,'S/RowX',6,'excl',lmode) form A7 heading lock
column DECODE(request,0,'none',1,'null',2,'rowS',3,'rowX',4,'share',5,'S/RowX',6,'excl',request) form A7 heading request
select
sid, type,
DECODE(lmode,0,'none', 1,'null', 2,'rowS', 3,'rowX', 4,'share', 5,'S/RowX', 6,'excl',lmode),
DECODE(request,0,'none', 1,'null', 2,'rowS', 3,'rowX', 4,'share', 5,'S/RowX', 6,'excl',request),
ctime "time(sec)"
from V$TRANSACTION_ENQUEUE a;
sid TY lock request time(sec)
——— —— ———— —------ ---------
142 TX excl none 7
SQL> /
sid TY lock request time(sec)
——— —— ———— —------ ---------
142 TX excl none 103
SQL> /
sid TY lock request time(sec)
——— —— ———— —------ ---------
142 TX excl none 172
SQL>

The larger the direct path load, the longer the blocking lock will be held. In this case, the duration of the blocking lock is increasing.

Effectively this means the functions of the application will be blocked, with the flow on effect that the business users will be left waiting.

Only when the direct path load for that table finishes, will the blocking lock be released, and the application return to normal performance expectations.

See Also

Data Concurrency and Consistency

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/data-concurrency-and-consistency.html#GUID-E8CBA9C5-58E3-460F-A82A-850E0152E95C

Loading using the datapump import

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/datapump-import-utility.html#GUID-D11E340E-14C6-43B8-AB09-6335F0C1F71B

About the Author

Paul Guerin is an international consultant that specialises in Oracle database performance. Paul is based from a global delivery center in South East Asia, but has clients from Australia, Europe, Asia, and North America. Moreover, he has presented at some of the world’s leading Oracle conferences, including Oracle Open World 2013. His work has also been featured in the IOUG Best Practices Tip Booklet of 2015, and also in the Oracle Technology Network (OTN). He is a DBA OCP, and continues to be a participant of the Oracle ACE program.