SAP Basis Tech Supports

Symptom

high enqueue waits after a reorganization/ change of extent size
Other terms

v$lock
ST-lock
enqueue
lock
insert, update , delete
DML
space management
coalesce free space
alter tablespace coalesce
reorganization
next extent size
Reason and Prerequisites

Performance is at a standstill, all workprocesses are occupied, oracle sessions show enqueue waits
there can be different reasons why an enqeue can cause severe performance impacts on the system. In order to find out what could be a possible reason please evaluate your situation with the following questions:
1. Are the enqueues all held for the same table?
in sm50 you would see all statements hanging on the same table?
in st04 –> detailed analysis session -> oracle sessions you would see enqueue waits for these sessions waiting on the same table?
in db01 -> v$lock you would see these sessions waiting on a TX lock – all for the same object?
find out the session id of the lock holder in st04 -> detailed analysis menu -> oracle sessions match this session-id to find out what the session is doing. Very often the session is doing an operational task where the table lock is required, like update statistics; rebuilding an index , ….
should the lock holder switch too fast so that you cannot match it to a session, check whether or not there is a statement that is performing an operational task to that table, e.g. update statistics, rebuilding an index,… and stop this activity.2. are all the statements holding enqueues doing inserts?
It does not matter whether this is for the same or for different tables.
It might be possible that a transaction is performing space management and is therefore holding the ST-lock. Usually this happens soon after activities were performed, that ‘released’ a lot of free extents (e.g. reorganization of objects with many extents check in db02 -> v$lock
are there sessions that are holding a lock of type ST?
find out what session-id it is and match this session-id to st04 -> detail analysis menu -> oracle sessions is the statement shown for this session (either user session or background process) selecting on table fet$ or uet$?
In that case it most likely is coalescing freespace. If you have just done a reorganization on an object this might have feed up a number of extents that where not reused by the reorganization because you either have moved the objects to a different tablespace. If now any other transaction requires the allocation of a new extent for an object in that tablepace; and the extentsize is bigger than a list of extents on the freelist; then a coalesce of freespace will be started by that session in order to build an extent of the requested size. During that whole time of coalescing the ST lock will be held – this then will cause a hang situation for rest of the database.
Here are some checks that you can perform to find out whether this is the case:find out whether there are a lot of free extents on the system:
sqlplus ‘/ as sysdba’
SQL> select count(1) from dba_free_space;
If the value returned is > 10000 it may be the source of your problem.find out which tablespace is the one that has the most free extents
SQL> select tablespace_name, count(1) from dba_free_space
group by tablespace_name;
interesting is the tablespace that has the most extents.look at the extent distribution in that tablespace
SQL> SELECT blocks, count(1) amount FROM dba_free_space
WHERE tablespace_name=’‘
GROUP BY blocks order by blocks desc;
You will often see a big amount at a relatively small number of blocks (very often at 5 blocks which is the default blocksize)If you execute this statement repeatedly, and coalesce is still going on, you can see this number decrease – you can estimate how long it will take until it is finished (and your system will perform normally again without you having to interact) by executing this statement twice in a set period of time and then based on the elapsed time in between the two executions calculate how long it will take until all of these extents are coalesced.Should your estimate be too long to wait for it; you can cancel the job that is doing the coalesce and apply the following changes so that your system will not get stuck on the next session trying to do a coalesce:

Solution

Ways to help the lock up situation of the ST enqueue
1.Option 1:
The only permanent solution will be to do a reorganization of this tablespace to a locally managed tablespace. This problem happening on the system already means that there are objects in that tablespace that consist of a lot of extents, therefore exporting single objects out in general will even worsen the situation for the objects that remain in the tablespace. It is therefore suggested to do a complete tablespace reorganization to LMTS.2.Option 2:
As a short-term solution you may, instead of a proper conversion, use the dbms_space_admin package to perform a conversion from dictionary managed to locally managed. The command to do so is
dbms_space_admin.tablespace_migrate_to_local(’
If already a coalesce was performed on the tablespace and you know how long this took you can estimate the above command to be executed in about twice the time of the coalesce.3.NOTE: we in general highly suggest NOT to use this function at all, but it will be a fast way to get the ST enqueue issue resolved. This type of conversion should ONLY be used in the context of this type of problem, as the result of this only brings you the benefits of not having to wait for a global ST enqueue, but does NOT provide the numerous other benefits of a proper locally managed tablespace.
Therefore, this should only be done as an emergency relief of the problem, and a proper reorganization of the tablespace should be
scheduled in the next possible downtime.4.Option 3:
In order to prevent the sessions from doing space management, you can temporarily adjust the nextextent value of ALL objects in that tablespace to the number of blocks (5 blocks at a 8k blocksize would be 40k) the majority of extents currently has:
SQL>set heading off;
SQL>set feedback off;
SQL>spool allocate.sql
SQL> SELECT’ alter index “‘ || index_name || ‘” storage (next 40k);’
FROM dba_indexes WHERE tablespace_name=’‘;
SQL> spool off;
SQL> @allocate.sql
Please note that this can only be a temporary solution that is implemented until there is enough time to let the coalesce job finish, or reorganize all remaining objects from that tablespace into a new, locally managed one.
Since for the busy objects of that tablespace the 40k extents would generate a lot of activity on the system you can pre-allocate extents for these objects. In order to do that you will have to create a new datafile in that tablespace and ONLY use THIS datafile – if you do this preallocation with ANY other datafile this will lead to the same situation again. Use the following command to do this preallocation:
SQL> alter table
allocate extent (size ,
datafile ‘‘);
Ways to speed up freespace coalesce
use alternate drop commands:instead of dropping the table during the reorg, rename itUse the command
SQL> TRUNCATE TABLE
REUSE STORAGE;gradually reduce the size of the table gradually using the command
SQL> ALTER TABLE
DEALLOCATE UNUSED KEEP M;
repeat this command until the table is completely gone – you may spread this over several days/nights instead of deallocating all at once
e.g. if the table is 100GB deallocate 10GB in 10 occasions:
SQL> ALTER TABLE
DEALLOCATE UNUSED KEEP 900M;
SQL> ALTER TABLE
DEALLOCATE UNUSED KEEP 800M;
SQL> ALTER TABLE
DEALLOCATE UNUSED KEEP 700M;
…
Unfortuantely this procedure only works for tables, not for indexes.ways to prevent this problem from reocurring
use locally managed tablespaceswhen using locally managed tablespaces, each tablespace is performing its own space management; so no global resources are required. With locally managed tablespaces you also leave the extent sizing to oracle; so a situation where a table has hundreds of thousands of extents will not occur. SAP recommends to gradually try moving all objects (starting with the busiest objects first) into locally managed tablespaces. For more information on locally managed tablespaces see note 214995run brconnect -f next to update nextextentsize frequently
this is only required on dictionary managed tablespaces. This will be handled internally by oracle if you use locally managed tablespaces. It is recommended to run brconnct -f next on a monthly basis for established systems;more frequently for systems that just went life.do not reorganize a big amount of tables with lots of extents out of a tablespace. Before reorganizing an object that you suspect having a lot of extents; check how many extents there really are
sqlplus ‘/ as sysdba’
SQL> SELECT segment_name, count(1) FROM dba_extents
WHERE segement_name=;
If you have an object with > 10000 extents, reorganize only this object; then do the coalesce right after that. Only start reorganizing another object if the the temporary segment leftover by your previously reorganized object is gone and your estimate shows that also the coalesce for this object will finish before the end of your planned downtime.
to find out whether there are still temporary objects leftover you can use the following command:
sqlplus ‘/ as sysdba’
SQL> SELECT count(1) FROM dba_extents
WHERE tablespace_name=’
‘
AND segment_type=’TEMPORARY’;
You will see the amount of temporary extents decreasing while the object is deleted. If a coalesce is started prior to all the temporary extents being released, it will only coalesce the objects that are already on the freelist; not the ones that are still allocated by the temporary object. So a coalesce might initially finish successfully, but there still might be a lot of small extents leftover simply because they where not released yet at the time of the coalesce; even though the object may already have been successfully dropped.consider reorganizing the complete tablespace
when reorganizing all objects out of the tablespace; no object will later on try and allocate any of these small extents again. Therefore these leftover objects will not do any harm. Please note, that the drop of the tablespace still will take a very long time since it will first move the extents into temporary extents and the drop will only complete when all temporary extents are gone. (it will tellyou taht you need to specify ‘including contents’ option for the drop command for that very same reason).
So the drop itself might still take a very long time – therefore it is suggested that you , if you dont need the space on the OS, leave the tablespace there (with no objects in it) until you have enough quiet system time to accodate a lengthy drop command.only reorganize a few objects and perform the coalesce after each object. Please note that the coalesce only works on segments, that are already released. Prior to releaseing the segments oracle would convert them to temporary segments and greadually release them. You can check upon these still temporary segments with the following command:
SQL >SELECT count(1) FROM dba_extents
WHERE tablespace_name=’
‘
AND segment_type=’TEMPORARY’;
So an ‘alter tablespace coalesce; will only do a complete cleanup If this statement returned 0 before. You can start issueing the coalesce command before that, it will clean up some extents; but rerun the command until it is once run with 0 temporary segments in that tablespace.