#Dica - 'shrink_tablespace' Uma New Feature de Oracle 23ai

Uma das novas funcionalidades introduzidas no Oracle Database 23ai é o shrink_tablespace, que simplifica a recuperação de espaço não utilizado nos tablespaces. Este procedimento reorganiza os segmentos que impedem a redução do tamanho do datafile, permitindo otimizar o desempenho do banco de dados.

Ele permite reduzir dinamicamente o tamanho de tablespaces sem a necessidade de recriá-los ou mover dados manualmente, tudo isso de forma online para ambiente EE e offline para Standard Edition.

Antes do Oracle Database 23ai, ao tentar redimensionar o datafile, era comum encontrar o erro "ORA-03297: file contains used data beyond requested RESIZE value", devido à presença de objetos bloqueando a redução. A solução tradicional envolvia reorganizar manualmente esses objetos, um processo trabalhoso e propenso a erros. Com o shrink_tablespace, essa tarefa é automatizada, movendo os segmentos que bloqueiam o final do datafile para o início e, em seguida, redimensionando o arquivo conforme necessário.

É possivel utilizar o shrink_tablespace tanto em tablespaces com BIGFILE quanto SMALLFILE.

Este procedimento utiliza DDL online para reorganizar os objetos dentro do arquivo de dados e, após os objetos terem sido reorganizados com sucesso, ele executa um redimensionamento do arquivo de dados.

Como utilizar o shrink_tablespace

Para simular um ambiente, foi utilizado o swinbench, criando o schema do SOE, com aproximadamente 19GB de dados.

Após sua criação, vou realizar um delete da tabela order_items que tem aproximadamente 5GB.

15:08:43 SQL> SELECT ROUND(SUM(bytes) / POWER(1024, 3), 2)
AS "Total Size (GB)"
FROM dba_segments
WHERE owner ='SOE';15:11:26   2  15:11:26   3  15:11:26   4

Total Size (GB)
---------------
          19.25

15:15:08 SQL> SELECT
    segment_name AS "Table Name",
    ROUND(SUM(bytes) / POWER(1024, 3), 2) AS "Size (GB)"
FROM
    dba_segments
WHERE
    owner = 'SOE'
    AND segment_type = 'TABLE'
GROUP BY
    segment_name
ORDER BY
    "Size (GB)" DESC;15:16:32   2  15:16:32   3  15:16:32   4  15:16:32   5  15:16:32   6  15:16:32   7  15:16:32   8  15:16:32   9  15:16:32  10  15:16:32  11  15:16:32  12

Table Name                      Size (GB)
------------------------------ ----------
ORDER_ITEMS                          5.12
ORDERS                               1.98
CUSTOMERS                             1.5
ADDRESSES                            1.32
CARD_DETAILS                          .92
LOGON                                 .65
INVENTORIES                           .17
PRODUCT_INFORMATION                     0
WAREHOUSES                              0
PRODUCT_DESCRIPTIONS                    0
ORDERENTRY_METADATA                     0

Após o processo de delete, veja que a tabela order_items ela fica zerada. Ja a tablespace continua com praticamente 23GB alocados.

SQL> col "Table Name" for a25
SQL> set lines 210
SQL> SELECT
    segment_name AS "Table Name",
    ROUND(SUM(bytes) / POWER(1024, 3), 2) AS "Size (GB)"
FROM
    dba_segments
WHERE
    owner = 'SOE'
    AND segment_type = 'TABLE'
GROUP BY
    segment_name
ORDER BY
    "Size (GB)" DESC;  2    3    4    5    6    7    8    9   10   11   12

Table Name                 Size (GB)
------------------------- ----------
ORDERS                          1.98
CUSTOMERS                        1.5
ADDRESSES                       1.32
CARD_DETAILS                     .92
LOGON                            .65
INVENTORIES                      .17
ORDER_ITEMS                      .01
PRODUCT_DESCRIPTIONS               0
PRODUCT_INFORMATION                0
WAREHOUSES                         0
ORDERENTRY_METADATA                0

SQL> col tablespace_name format a25
select tablespace_name, blocks, bytes/1024/1024 as size_mb
     from dba_data_files
     where tablespace_name = 'SOE'; SQL>   2    3

TABLESPACE_NAME               BLOCKS    SIZE_MB
------------------------- ---------- ----------
SOE                          3014656      23552

É possível validar que existem vários segmentos free. Essa é a área que será "recuperada".

SQL> col segment_type format a10
SQL> set pagesize 100
SQL> set pages 200
SQL> select block_id begin_block, block_id + blocks - 1 end_block, blocks, bytes/1024/1024 MB,
  2       segment_name, segment_type
     from dba_extents
     where tablespace_name = 'SOE'
         and segment_name='ORDER_ITEMS'
     union all
     select block_id begin_block, block_id + blocks - 1 end_block, blocks, bytes/1024/1024 MB,
     'free' segment_name, null segment_type
     from dba_free_space
     where tablespace_name = 'SO  3  E'
  4    5    6    7    8    9   10   11       order by 1 desc;

BEGIN_BLOCK  END_BLOCK     BLOCKS         MB SEGMENT_NAME                   SEGMENT_TY
----------- ---------- ---------- ---------- ------------------------------ ----------
    2700032    2700159        128          1 free
    2697472    2697599        128          1 free
    2696448    2696575        128          1 free
    2695040    2695167        128          1 free
    2694400    2694527        128          1 free
    2690944    2691071        128          1 free
    2688384    2688511        128          1 free
    2686592    2686719        128          1 free
    2681728    2681855        128          1 free
    2679552    2679679        128          1 free
    2678272    2678399        128          1 free
    2670336    2670463        128          1 free
    2668800    2668927        128          1 free
    2668544    2668671        128          1 free
    2668160    2668287        128          1 free
    2667008    2667135        128          1 free
    2666112    2666879        768          6 free
    2665600    2665983        384          3 free
    2665344    2665471        128          1 free
    2664576    2665215        640          5 free
    2664192    2664447        256          2 free
    2663936    2664063        128          1 free
    2663680    2663807        128          1 free
    2663168    2663295        128          1 free
    2662912    2663039        128          1 free
    2662400    2662783        384          3 free
    2662144    2662271        128          1 free

...

Agora que foi gerada uma fragmentação da tablespace, vamos usar o shrink_tablespace para realizar a resize da mesma.

A execução da API fica: DBMS_SPACE.SHRINK_TABLESPACE(ts_name, shrink_mode, target_size, shrink_result).

Parâmetros:

ts_name: Este é o nome do tablespace que você deseja reduzir.

shrink_mode: O modo de redução possui algumas opções, como TS_MODE_SHRINK, TS_MODE_ANALYZE e TS_MODE_SHRINK_FORCE. Por padrão, está configurado como TS_MODE_SHRINK, que move os objetos online por padrão (exceto para Index-Organized Tables ou IOT). O TS_MODE_SHRINK_FORCE também move os objetos online por padrão, mas, se a movimentação online falhar, tenta uma movimentação offline.

target_size: Este parâmetro permite definir o novo tamanho do datafile do tablespace (em bytes). O padrão é TS_TARGET_MAX_SHRINK.

Parâmetros de saída (shrink_result):

Para shrink_mode TS_MODE_SHRINK (exemplos abaixo):
- O procedimento retorna o número total e o tamanho dos objetos movidos.
- Fornece o tamanho original e o novo tamanho do datafile.
- Você recebe informações sobre o tempo de processamento.
Para shrink_mode TS_MODE_ANALYZE:
- Gera uma lista de objetos que podem ser movidos.
- Exibe o número total e o tamanho dos objetos que podem ser movidos.
- Sugere o tamanho alvo.
- Também fornece o tempo de processamento.

Será executado primeiramente o TS_MODE_ANALYZE para validar o processo. Veja que neste exemplo o analyze retornou que 23 objetos estão aptos para o move, totalizando 10GB e trazendo um target size de 14GB.

SQL> set serveroutput on;
SQL> execute dbms_space.shrink_tablespace('SOE', shrink_mode => DBMS_SPACE.TS_MODE_ANALYZE);
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 23
Total Movable Size(GB): 10.03
Original Datafile Size(GB): 23
Suggested Target Size(GB): 14.61
Process Time: +00 00:00:09.795170

PL/SQL procedure successfully completed.

Agora vamos executar o processo de shrink. Vale destacar que estou realizando o procedimento em um ambiente Standard Edition e por isso ele não vai deixar eu fazer online. Desta forma será preciso passar o parâmetro TS_MODE_SHRINK_FORCE para que faça o processo offline.

Se tentarmos executar sem passar o parâmetro TS_MODE_SHRINK_FORCE, ele vai tentar fazer o online com isso apresentar uma mensagem em tela falando que não foi possível mover.

SQL>
SQL> set serveroutput on;
SQL> execute dbms_space.shrink_tablespace('SOE');
Procedure exited because it can't move an object:
Failed Move DDL: alter table "SOE"."ORDERS" move  online
Failed Reason: ORA-00439: feature not enabled: Online Index Build

-------------------SHRINK RESULT-------------------
Total Moved Objects: 0
Total Moved Size(GB): 0
Original Datafile Size(GB): 23
New Datafile Size(GB): 23
Process Time: +00 00:00:09.876566

PL/SQL procedure successfully completed.

SQL>

Executando o processo com o parâmetro TS_MODE_SHRINK_FORCE, ele vai fazer o processo de reorg offline.

	 
SQL> set serveroutput on;

SQL> execute dbms_space.shrink_tablespace('SOE', shrink_mode => DBMS_SPACE.TS_MODE_SHRINK_FORCE);

-------------------SHRINK RESULT-------------------
Total Moved Objects: 23
Total Moved Size(GB): 10.03
Original Datafile Size(GB): 23
New Datafile Size(GB): 11.63
Process Time: +00 00:06:16.995479

PL/SQL procedure successfully completed.

O processo concluído com sucesso. Foram movidas 23 tabelas, num total de 10GB. Agora o tamanho da tablespace do SOE passa a ser 11,63GB e não mais os 23GB que ela estava ocupando antes.

SQL> SQL> col tablespace_name format a25
select tablespace_name, blocks, bytes/1024/1024 as size_mb
     from dba_data_files
     where tablespace_name = 'SOE'; SQL>   2    3

TABLESPACE_NAME               BLOCKS    SIZE_MB
------------------------- ---------- ----------
SOE                          1523968      11906

Referencias:

Tablespace Shrink in 23ai

PL/SQL Packages and Types Reference

LAMIMDBA

Pesquisar este blog

#Dica - 'shrink_tablespace' Uma New Feature de Oracle 23ai

Como utilizar o shrink_tablespace

Parâmetros de saída (shrink_result):

Comentários

Postar um comentário