Home » RDBMS Server » Server Administration » Basic Partition compression while run DML statement
Basic Partition compression while run DML statement [message #680040] Mon, 20 April 2020 01:31 Go to next message
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 #680042 is a reply to message #680040] Mon, 20 April 2020 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It depends on your version (query v$version), something you should ALWAYS post with a question.

Re: Basic Partition compression while run DML statement [message #680043 is a reply to message #680042] Mon, 20 April 2020 03:08 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
Hi Michael,

sorry, the version is "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production"
Re: Basic Partition compression while run DML statement [message #680047 is a reply to message #680043] Mon, 20 April 2020 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ALTER TABLE COMPRESS will take a lock on the table which will prevent all DMLs.
But I advise you to test it and not just rely on my opinion.

Re: Basic Partition compression while run DML statement [message #680048 is a reply to message #680047] Mon, 20 April 2020 05:48 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
Hi Michael,

but that should be not a problem.
Even if the ALTER TABLE COMPRESS will take a lock on the table/partion, I think that the ETL process should keep running without getting error and will wait as long as the lock being released.

Thanks.

[Updated on: Mon, 20 April 2020 05:51]

Report message to a moderator

Re: Basic Partition compression while run DML statement [message #680049 is a reply to message #680048] Mon, 20 April 2020 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But the answer to the question:
Quote:
It's possible execute a basic compression statement on a table partition while involved in DML statement?

Is no, you have to wait until the end of the compression.
And on the other side, the compression is not possible if a transaction is going on the table.

Re: Basic Partition compression while run DML statement [message #680050 is a reply to message #680049] Mon, 20 April 2020 06:56 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
ok, thanks.
Re: Basic Partition compression while run DML statement [message #680051 is a reply to message #680050] Mon, 20 April 2020 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
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

Previous Topic: Relink Oracle after OS upgrade
Next Topic: open cursor parameter exceeds but no alarm have been raised
Goto Forum:
  


Current Time: Thu Mar 28 03:37:23 CDT 2024