cancel
Showing results for 
Search instead for 
Did you mean: 

Shrink versus tradicional reorganization with brtools

former_member209959
Participant
0 Kudos

Hi experts,

We are going to start to use shrink to reorganize certain tables instead the usual reorganization. We read the note 919389 and we made some tests, but we have a doubt to use it in the production systems.

Which method produces more blocks or more performance problems when the tables is being used?

Best Regards.

Accepted Solutions (1)

Accepted Solutions (1)

former_member188883
Active Contributor
0 Kudos

Hi Maximino,

Are you referring to correct SAP note number ??

919389 - Work overview: Incorrect link to documents and so on


Please share correct SAP note number which you referred.


Regards,

Deepak Kori

former_member209959
Participant
0 Kudos

Sorry Deepak, the correct note is 910389 - FAQ: Oracle Segment Shrinking.

Regards.

former_member188883
Active Contributor
0 Kudos

Hi Maximino,

From the SAP note which you shared


  • While online reorganization temporarily requires double the amount of space, segment shrinking manages without additional space requirements.
  • Only a subset of entries is moved if you use segment shrinking, whereas ALL table entries are selected in online reorganization. As a result, the amount of redo log data that accumulates is less if you use segment shrinking than in online reorganization.

Above 2 points clearly states space requirement and performance considerations in both the methods.

Shrinking is much more efficient as compared to reorganization.

Hope this helps.

Regards,

Deepak Kori

former_member209959
Participant
0 Kudos

Hi Deekap and thanks for answering,

I've read this points you refiere and I know shrink is better in space requirements. But ahead in the note:


  • The table is locked against changes

  Even if segment shrinking is as far as possible an online process, a table lock (TM enqueue) may occur in the following situation: If a non-committed change exists for the table, the TM enqueue request of segment shrinking escalates the row-specific TX enqueue to a table-wide TM enqueue. As a result, the table is locked against changes until the uncommitted transaction is completed. If you specify the keyword COMPACT, this problem does not occur because no TM enqueue is requested. Otherwise, you should only execute segment shrinking at times when no long-running transactions that change the underlying table are active.

So, my doubt is about this point, which of the two methods is worst about locking the table while the reorganization or the shrink is being done?

Regards.

former_member188883
Active Contributor
0 Kudos

Hi Maximino,

If you are looking at locking concept , re-organization has an edge against shrinking.

By default, online reorganizations are carried out based on the Oracle package DBMS_REDEFINITION. This means that no access locks occur when segments are copied in the database.

Hope this helps.

Regards,

Deepak Kori

former_member209959
Participant
0 Kudos

Thank, this was my doubt. In a productive system, can it have any influence or negative effects?.

Regards.

former_member188883
Active Contributor
0 Kudos

Hi Maximino,

If you really want to perform any database operation like shrinking or Reorg, my suggestion is to select window where load is very minimal or zero.

This will help in completing the database operation faster and without any hiccups.

Note: Do test the time taken on a Production clone to have proper estimates. This will help in planning your activity on Production.

Regards,

Deepak Kori

former_member209959
Participant
0 Kudos

Ok, I'll try the shrink in our test system first.

Thanks for your help

Regards

Answers (1)

Answers (1)

stefan_koehler
Active Contributor
0 Kudos

Hi Maximino,

i am sorry, but it seems like that the explanation in SAPnote #910389 is plainly wrong and it should be reviewed and corrected by SAP. At first Oracle never ever escalates locks - that's one of the great key concepts of Oracle. Details about this can also be found in the "Oracle documentation - Data Lock Conversion Versus Lock Escalation". Secondly the statement "TM enqueue request of segment shrinking escalates the row-specific TX enqueue to a table-wide TM enqueue" in SAPnote #910389 is also wrong and can be verified easily with a short demo (in my case on Oracle 11.2.0.3.6).


SID 13 holds the TM lock in mode 3 (SX - subexclusive table lock / An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table) in the owner queue after DML, but not commiting. The "shrink space" SQL (SID 11) requests the TM lock in mode 6 (X - exclusive table lock / An X lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table), but can not get it right away due to SID 13 in the owner queue. SID 11 goes to the converter queue in consequence and waits to aquire the TM enqueue in mode 6 (and will get it when the owner queue will become empty). SID 175 is stuck in the waiters queue and waits to get the mode 3 request. The basic effect to the sessions is like described, but the explanation is not correct.

So no lock escalation at all - just the plain lock conversion procedure as Oracle does it at any time and even no TX enqueue involved in that procedure here. By the way a TX enqueue is related to a transaction table slot (id1 representing the undo segment number and slot number, and id2 representing the slot’s sequence number).

Regards

Stefan

P.S.: In addition an explanation about the various TM locks can be found here: Oracle Documentation - Automatic Locks in DML Operations

former_member209959
Participant
0 Kudos

Hi Stefan, and thanks for your deeply explanation.

So, I understand that there are no problems to run shrink in a production system in any time.

Regards.

stefan_koehler
Active Contributor
0 Kudos

Hi Maximino,

well i did not say this .. i said "The basic effect to the sessions is like described, but the explanation is not correct.".

The "shrink space" DDL is an online operation (lock mode 3) until the end when the lock is converted (not escalated - that is the critical point here) to mode 6. You can run into some issues, if you have some long on-going (uncommitted) transactions at this specific point in time. That is also included in my demo.

However DBMS_REDEFINITION.FINISH_REDEF_TABLE also needs a short "lock" at the end (Quote "The original table is locked briefly during this procedure."), even if it is implemented differently.

Regards

Stefan

Former Member
0 Kudos

Stefan Koehler wrote:

The "shrink space" DDL is an online operation (lock mode 3) until the end when the lock is converted (not escalated - that is the critical point here) to mode 6.

What is the difference (technical implementation "under the hood" from Oracle) between lock conversion and escalation? Why you do such emphasis on this?

stefan_koehler
Active Contributor
0 Kudos

Hi Roman,

> What is the difference (technical implementation "under the hood" from Oracle) between lock conversion and escalation?

Oracle itself does not use / need lock escalation, so the only technical implementation is lock conversion. A great detailed explanation of its implementation is published in book "Oracle Core: Essential Internals for DBAs and Developers" by Jonathan Lewis. You can find this chapter (lock implementation starts at page 77) here. Especially the graphic makes it very obvious and you can interpret my previous description of the "shrink space" DDL with it.

> Why you do such emphasis on this?

Because lock escalation can introduce a new level of issues (e.g. deadlocks), which you do not face on Oracle in that context. I am quoting for SQL server RDBMS (knowledge base article #Q323630) which uses lock escalation.


Lock escalation is the process of converting many fine-grained locks (such as row or page locks) into table locks. Microsoft SQL Server dynamically determines when to perform lock escalation. When making this decision, SQL Server takes into account the number of locks that are held on a particular scan, the number of locks that are held by the whole transaction, and the memory that is being used for locks in the system as a whole.

....

Large scans or large numbers of Bookmark Lookups may increase the chance of lock escalation; additionally, it increases the chance of deadlocks, and generally adversely affects concurrency and performance.

Maybe i am a little bit too picky, but this is mixed up a lot

Regards

Stefan

Former Member
0 Kudos

Hi Stefan,

thanks for your reply.

Kind regards,

Roman

former_member209959
Participant
0 Kudos

Thanks Stefen, anyway I've done some reorganizations with shrink in the production system and everything was fine.

Regards.