Basic Partition compression while run DML statement [message #680040] |
Mon, 20 April 2020 01:31 |
|
dancko
Messages: 108 Registered: June 2013 Location: italy
|
Senior Member |
|
|
Hi to all,
I have to compress partitions on several oracle tables.
This tables are involved in ETL (extract transformation loading) processes which run many times a day.
It's possible execute a basic compression statement on a table partition while involved in DML statement?
Thanks in advance.
[Updated on: Mon, 20 April 2020 02:00] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Basic Partition compression while run DML statement [message #680051 is a reply to message #680050] |
Mon, 20 April 2020 07:45 |
|
Michel Cadot
Messages: 68731 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I made some tests with 2 sessions on SCOTT.
Test 1
1/ SCOTT 1 is compressing EMP table (this lasts 30 seconds)
2/ DBA checks the lock
3/ SCOTT 2 is trying to insert a row
4/ DBA checks the lock
14:31:43 SCOTT 1> alter table emp compress;
Table altered.
14:32:15 SCOTT 1>
DBA> @lock
Sid Statut Utilisateur OS Pid LK Mod W Objet
---------- -------- --------------- ---------- -- --- - ------------------------------------------------------------
144,1905 ACTIVE SCOTT 2564 TM X SCOTT.EMP
TX X _SYSSMU5_863307317$ tx: 0x0005.0010.000100C0
14:31:53 SCOTT 2> insert into emp (empno) values (0);
1 row created.
14:32:15 SCOTT 2>
DBA> @lock
Sid Statut Utilisateur OS Pid LK Mod W Objet
---------- -------- --------------- ---------- -- --- - ------------------------------------------------------------
144,1905 ACTIVE SCOTT 2564 TM X SCOTT.EMP
TX X _SYSSMU5_863307317$ tx: 0x0005.0010.000100C0
19,725 ACTIVE SCOTT 4620 TM RX SCOTT.DEPT
TM RX W SCOTT.EMP:AAAR/OAAAAAAAAAAAA
As you can see after the ALTER TABLE COMPRESS, SCOTT 1 is holding an exclusive lock on the table.
Then, after SCOTT2 tries to insert a row, we see that this session is trying to get a row exclusive lock on the table and is waiting (W flag).
On this SCOTT2 session, we see the insert waits until the compress finish (time 14:32:15) to be able to complete.
Test 2
1/ SCOTT 2 inserts a row and stands by
2/ DBA checks the lock
3/ SCOTT 1 tries to compress EMP table and fails
14:38:30 SCOTT 2> insert into emp (empno) values (0);
1 row created.
14:38:32 SCOTT 2>
DBA> @lock
Sid Statut Utilisateur OS Pid LK Mod W Objet
---------- -------- --------------- ---------- -- --- - ------------------------------------------------------------
19,725 INACTIVE SCOTT 4620 TX X _SYSSMU8_972524987$ tx: 0x0008.0019.0000FEF5
TM RX SCOTT.DEPT
TM RX SCOTT.EMP
14:38:47 SCOTT 1> alter table emp compress;
alter table emp compress
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
As you can see, after SCOTT 2 inserts a row, it holds a row exclusive lock on EMP, then when SCOTT 1 tries to compress, it receives an error as it is unable to acquire its exclusive lock on EMP.
|
|
|
Re: Basic Partition compression while run DML statement [message #680052 is a reply to message #680051] |
Mon, 20 April 2020 08:32 |
|
dancko
Messages: 108 Registered: June 2013 Location: italy
|
Senior Member |
|
|
Ok. Thanks a lot Michael.
It's clear.
Approximatley we can say that ALTER TABLE COMPRESS will takes an exclusive lock on a table and a DML operation (with another session) on the same table waits until compress finish.
Coversely, when a table get exclusive lock for DML operation and we try (with another session) to execute ALTER TABLE COMPRESS statement we get an error of busy resource.
P.S.: I will try, for exercise, to execute a similar example as your.
thanks
[Updated on: Mon, 20 April 2020 08:42] Report message to a moderator
|
|
|