Olá pessoal, o objetivo aqui é (era) bem breve. Vou mostrar o que acontece durante uma operação de rebuild online de um índice. Poucos DBAs se dão conta disso, mas durante esta operação um índice auxiliar (entre outra coisas) é criado para armazenar os dados transientes enquanto a aplicação continua a rodar normalmente.
Observe que para a tabela ORDER_ITEMS temos apenas 3 índices cujos nomes estão listados na query:
SYS@orcl > select index_name, table_name from dba_indexes where owner='SOE' and table_name='ORDER_ITEMS'; INDEX_NAME TABLE_NAME ------------------------------ ------------------------------ ITEM_ORDER_IX ORDER_ITEMS ITEM_PRODUCT_IX ORDER_ITEMS ORDER_ITEMS_PK ORDER_ITEMS
Observe também que temos 27 segmentos do tipo INDEX que pertencem ao SOE:
SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type='INDEX' order by 1; SEGMENT_NAME SEGMENT_TYPE SIZE_MB ------------------------------ ------------------ ---------- ADDRESS_CUST_IX INDEX 33.9375 ADDRESS_PK INDEX 32.8125 CARDDETAILS_CUST_IX INDEX 35.875 CARD_DETAILS_PK INDEX 32.875 CUSTOMERS_PK INDEX 21.6875 CUST_ACCOUNT_MANAGER_IX INDEX 23.8125 CUST_DOB_IX INDEX 26.1875 CUST_EMAIL_IX INDEX 50.375 CUST_FUNC_LOWER_NAME_IX INDEX 37.5625 INVENTORY_PK INDEX 18 INV_PRODUCT_IX INDEX 15.625 INV_WAREHOUSE_IX INDEX 16.5 ITEM_ORDER_IX INDEX 161.25 ITEM_PRODUCT_IX INDEX 107.1875 ORDER_ITEMS_PK INDEX 122 ORDER_PK INDEX 46.1875 ORD_CUSTOMER_IX INDEX 39.3125 ORD_ORDER_DATE_IX INDEX 43.1875 ORD_SALES_REP_IX INDEX 30.8125 ORD_WAREHOUSE_IX INDEX 44.375 PRD_DESC_PK INDEX .0625 PRODUCT_INFORMATION_PK INDEX .0625 PROD_CATEGORY_IX INDEX .0625 PROD_NAME_IX INDEX .125 PROD_SUPPLIER_IX INDEX .0625 WAREHOUSES_PK INDEX .0625 WHS_LOCATION_IX INDEX .0625 27 rows selected.
Em outra sessão executo a operação de rebuild online do índice ORDER_ITEMS_PK:
SYS@orcl > alter index SOE.ORDER_ITEMS_PK rebuild online;
Observe que um novo segmento do tipo INDEX foi criado com o nome aleatório SYS_IOT_TOP_117714. Este é o índice que está armazenando os dados transientes referentes a operações DMLs que atualizaram o índice que está sendo reconstruído:
SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type='INDEX' order by 1; SEGMENT_NAME SEGMENT_TYPE SIZE_MB ------------------------------ ------------------ ---------- ADDRESS_CUST_IX INDEX 33.9375 ADDRESS_PK INDEX 32.8125 CARDDETAILS_CUST_IX INDEX 35.875 CARD_DETAILS_PK INDEX 32.875 CUSTOMERS_PK INDEX 21.6875 CUST_ACCOUNT_MANAGER_IX INDEX 23.8125 CUST_DOB_IX INDEX 26.1875 CUST_EMAIL_IX INDEX 50.375 CUST_FUNC_LOWER_NAME_IX INDEX 37.5625 INVENTORY_PK INDEX 18 INV_PRODUCT_IX INDEX 15.625 INV_WAREHOUSE_IX INDEX 16.5 ITEM_ORDER_IX INDEX 161.25 ITEM_PRODUCT_IX INDEX 107.1875 ORDER_ITEMS_PK INDEX 122 ORDER_PK INDEX 46.1875 ORD_CUSTOMER_IX INDEX 39.3125 ORD_ORDER_DATE_IX INDEX 43.1875 ORD_SALES_REP_IX INDEX 30.8125 ORD_WAREHOUSE_IX INDEX 44.375 PRD_DESC_PK INDEX .0625 PRODUCT_INFORMATION_PK INDEX .0625 PROD_CATEGORY_IX INDEX .0625 PROD_NAME_IX INDEX .125 PROD_SUPPLIER_IX INDEX .0625 SYS_IOT_TOP_117714 INDEX 3.125 WAREHOUSES_PK INDEX .0625 WHS_LOCATION_IX INDEX .0625 28 rows selected.
A operação de rebuild online é concluída na outra sessão:
Index altered.
Veja que o índice SYS_IOT_TOP_117714 deixa de existir após concluir o Merge dos dados:
SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type='INDEX' order by 1; SEGMENT_NAME SEGMENT_TYPE SIZE_MB ------------------------------ ------------------ ---------- ADDRESS_CUST_IX INDEX 33.9375 ADDRESS_PK INDEX 32.8125 CARDDETAILS_CUST_IX INDEX 35.875 CARD_DETAILS_PK INDEX 32.875 CUSTOMERS_PK INDEX 21.6875 CUST_ACCOUNT_MANAGER_IX INDEX 23.8125 CUST_DOB_IX INDEX 27.1875 CUST_EMAIL_IX INDEX 50.375 CUST_FUNC_LOWER_NAME_IX INDEX 37.5625 INVENTORY_PK INDEX 18 INV_PRODUCT_IX INDEX 15.625 INV_WAREHOUSE_IX INDEX 16.5 ITEM_ORDER_IX INDEX 161.25 ITEM_PRODUCT_IX INDEX 107.1875 ORDER_ITEMS_PK INDEX 120 ORDER_PK INDEX 47.1875 ORD_CUSTOMER_IX INDEX 41.3125 ORD_ORDER_DATE_IX INDEX 43.1875 ORD_SALES_REP_IX INDEX 30.8125 ORD_WAREHOUSE_IX INDEX 44.375 PRD_DESC_PK INDEX .0625 PRODUCT_INFORMATION_PK INDEX .0625 PROD_CATEGORY_IX INDEX .0625 PROD_NAME_IX INDEX .125 PROD_SUPPLIER_IX INDEX .0625 WAREHOUSES_PK INDEX .0625 WHS_LOCATION_IX INDEX .0625 27 rows selected.
Algumas percepções durante o rebuild online
Notei ainda mais alguns segmentos criados durante o rebuild online do índice em questão…
Antes de iniciar a operação, listei todos os segmentos exceto os do tipo PARTITION:
SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type not like '%PARTITION' order by 1; SEGMENT_NAME SEGMENT_TYPE SIZE_MB ------------------------------ ------------------ ---------- ADDRESS_CUST_IX INDEX 72.9375 ADDRESS_PK INDEX 64.8125 CARDDETAILS_CUST_IX INDEX 39.875 CARD_DETAILS_PK INDEX 64.875 CUSTOMERS_PK INDEX 42.6875 CUST_ACCOUNT_MANAGER_IX INDEX 28.8125 CUST_DOB_IX INDEX 47.1875 CUST_EMAIL_IX INDEX 61.375 CUST_FUNC_LOWER_NAME_IX INDEX 45.5625 INVENTORIES TABLE 80 INVENTORY_PK INDEX 18 INV_PRODUCT_IX INDEX 15.625 INV_WAREHOUSE_IX INDEX 16.5 ITEM_ORDER_IX INDEX 193.25 ITEM_PRODUCT_IX INDEX 187.1875 ORDERENTRY_METADATA TABLE .0625 ORDER_ITEMS_PK INDEX 160 ORDER_PK INDEX 62.1875 ORD_CUSTOMER_IX INDEX 64.3125 ORD_ORDER_DATE_IX INDEX 62.1875 ORD_SALES_REP_IX INDEX 30.8125 ORD_WAREHOUSE_IX INDEX 60.375 PRD_DESC_PK INDEX .0625 PRODUCT_DESCRIPTIONS TABLE .3125 PRODUCT_INFORMATION TABLE .25 PRODUCT_INFORMATION_PK INDEX .0625 PROD_CATEGORY_IX INDEX .0625 PROD_NAME_IX INDEX .125 PROD_SUPPLIER_IX INDEX .0625 WAREHOUSES TABLE .0625 WAREHOUSES_PK INDEX .0625 WHS_LOCATION_IX INDEX .0625 32 rows selected.
Veja que temos 32 segmentos do SOE no total, sem contar os particionados.
Iniciei o rebuild online e fui acompanhando.
No primeiro momento é criado o índice SYS_IOT_TOP_117714:
SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type not like '%PARTITION' order by 1; SEGMENT_NAME SEGMENT_TYPE SIZE_MB ------------------------------ ------------------ ---------- ADDRESS_CUST_IX INDEX 72.9375 ADDRESS_PK INDEX 64.8125 CARDDETAILS_CUST_IX INDEX 40.875 CARD_DETAILS_PK INDEX 64.875 CUSTOMERS_PK INDEX 42.6875 CUST_ACCOUNT_MANAGER_IX INDEX 29.8125 CUST_DOB_IX INDEX 47.1875 CUST_EMAIL_IX INDEX 62.375 CUST_FUNC_LOWER_NAME_IX INDEX 46.5625 INVENTORIES TABLE 80 INVENTORY_PK INDEX 18 INV_PRODUCT_IX INDEX 15.625 INV_WAREHOUSE_IX INDEX 16.5 ITEM_ORDER_IX INDEX 193.25 ITEM_PRODUCT_IX INDEX 195.1875 ORDERENTRY_METADATA TABLE .0625 ORDER_ITEMS_PK INDEX 160 ORDER_PK INDEX 62.1875 ORD_CUSTOMER_IX INDEX 64.3125 ORD_ORDER_DATE_IX INDEX 63.1875 ORD_SALES_REP_IX INDEX 30.8125 ORD_WAREHOUSE_IX INDEX 61.375 PRD_DESC_PK INDEX .0625 PRODUCT_DESCRIPTIONS TABLE .3125 PRODUCT_INFORMATION TABLE .25 PRODUCT_INFORMATION_PK INDEX .0625 PROD_CATEGORY_IX INDEX .0625 PROD_NAME_IX INDEX .125 PROD_SUPPLIER_IX INDEX .0625 SYS_IOT_TOP_117714 INDEX .0625 WAREHOUSES TABLE .0625 WAREHOUSES_PK INDEX .0625 WHS_LOCATION_IX INDEX .0625 33 rows selected.
Logo mais um outro segmento é criado, este do tipo TEMPORARY, com o nome 10.256074:
SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type not like '%PARTITION' order by 1; SEGMENT_NAME SEGMENT_TYPE SIZE_MB ------------------------------ ------------------ ---------- 10.256074 TEMPORARY 0 ADDRESS_CUST_IX INDEX 72.9375 ADDRESS_PK INDEX 64.8125 CARDDETAILS_CUST_IX INDEX 40.875 CARD_DETAILS_PK INDEX 64.875 CUSTOMERS_PK INDEX 42.6875 CUST_ACCOUNT_MANAGER_IX INDEX 29.8125 CUST_DOB_IX INDEX 47.1875 CUST_EMAIL_IX INDEX 62.375 CUST_FUNC_LOWER_NAME_IX INDEX 46.5625 INVENTORIES TABLE 80 INVENTORY_PK INDEX 18 INV_PRODUCT_IX INDEX 15.625 INV_WAREHOUSE_IX INDEX 16.5 ITEM_ORDER_IX INDEX 193.25 ITEM_PRODUCT_IX INDEX 195.1875 ORDERENTRY_METADATA TABLE .0625 ORDER_ITEMS_PK INDEX 160 ORDER_PK INDEX 62.1875 ORD_CUSTOMER_IX INDEX 64.3125 ORD_ORDER_DATE_IX INDEX 63.1875 ORD_SALES_REP_IX INDEX 30.8125 ORD_WAREHOUSE_IX INDEX 61.375 PRD_DESC_PK INDEX .0625 PRODUCT_DESCRIPTIONS TABLE .3125 PRODUCT_INFORMATION TABLE .25 PRODUCT_INFORMATION_PK INDEX .0625 PROD_CATEGORY_IX INDEX .0625 PROD_NAME_IX INDEX .125 PROD_SUPPLIER_IX INDEX .0625 SYS_IOT_TOP_117714 INDEX .125 WAREHOUSES TABLE .0625 WAREHOUSES_PK INDEX .0625 WHS_LOCATION_IX INDEX .0625 34 rows selected.
Agora os segmentos SYS_IOT_TOP_117714 e 10.256074 são removidos e dois novos segmentos do tipo TEMPORARY são criados 10.256050 e 10.256058:
SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type not like '%PARTITION' order by 1; SEGMENT_NAME SEGMENT_TYPE SIZE_MB ------------------------------ ------------------ ---------- 10.256050 TEMPORARY .25 10.256058 TEMPORARY 160 ADDRESS_CUST_IX INDEX 72.9375 ADDRESS_PK INDEX 64.8125 CARDDETAILS_CUST_IX INDEX 40.875 CARD_DETAILS_PK INDEX 64.875 CUSTOMERS_PK INDEX 42.6875 CUST_ACCOUNT_MANAGER_IX INDEX 29.8125 CUST_DOB_IX INDEX 47.1875 CUST_EMAIL_IX INDEX 62.375 CUST_FUNC_LOWER_NAME_IX INDEX 46.5625 INVENTORIES TABLE 80 INVENTORY_PK INDEX 18 INV_PRODUCT_IX INDEX 15.625 INV_WAREHOUSE_IX INDEX 16.5 ITEM_ORDER_IX INDEX 193.25 ITEM_PRODUCT_IX INDEX 195.1875 ORDERENTRY_METADATA TABLE .0625 ORDER_ITEMS_PK INDEX 160 ORDER_PK INDEX 62.1875 ORD_CUSTOMER_IX INDEX 64.3125 ORD_ORDER_DATE_IX INDEX 63.1875 ORD_SALES_REP_IX INDEX 30.8125 ORD_WAREHOUSE_IX INDEX 61.375 PRD_DESC_PK INDEX .0625 PRODUCT_DESCRIPTIONS TABLE .3125 PRODUCT_INFORMATION TABLE .25 PRODUCT_INFORMATION_PK INDEX .0625 PROD_CATEGORY_IX INDEX .0625 PROD_NAME_IX INDEX .125 PROD_SUPPLIER_IX INDEX .0625 WAREHOUSES TABLE .0625 WAREHOUSES_PK INDEX .0625 WHS_LOCATION_IX INDEX .0625 34 rows selected.
No fim da operação de rebuild online tudo volta ao estado inicial:
SYS@orcl > select segment_name, segment_type, bytes/1024/1024 size_mb from dba_segments where owner='SOE' and segment_type not like '%PARTITION' order by 1; SEGMENT_NAME SEGMENT_TYPE SIZE_MB ------------------------------ ------------------ ---------- ADDRESS_CUST_IX INDEX 72.9375 ADDRESS_PK INDEX 64.8125 CARDDETAILS_CUST_IX INDEX 40.875 CARD_DETAILS_PK INDEX 64.875 CUSTOMERS_PK INDEX 42.6875 CUST_ACCOUNT_MANAGER_IX INDEX 29.8125 CUST_DOB_IX INDEX 47.1875 CUST_EMAIL_IX INDEX 64.375 CUST_FUNC_LOWER_NAME_IX INDEX 47.5625 INVENTORIES TABLE 80 INVENTORY_PK INDEX 18 INV_PRODUCT_IX INDEX 15.625 INV_WAREHOUSE_IX INDEX 16.5 ITEM_ORDER_IX INDEX 193.25 ITEM_PRODUCT_IX INDEX 203.1875 ORDERENTRY_METADATA TABLE .0625 ORDER_ITEMS_PK INDEX 160 ORDER_PK INDEX 62.1875 ORD_CUSTOMER_IX INDEX 64.3125 ORD_ORDER_DATE_IX INDEX 72.1875 ORD_SALES_REP_IX INDEX 30.8125 ORD_WAREHOUSE_IX INDEX 64.375 PRD_DESC_PK INDEX .0625 PRODUCT_DESCRIPTIONS TABLE .3125 PRODUCT_INFORMATION TABLE .25 PRODUCT_INFORMATION_PK INDEX .0625 PROD_CATEGORY_IX INDEX .0625 PROD_NAME_IX INDEX .125 PROD_SUPPLIER_IX INDEX .0625 WAREHOUSES TABLE .0625 WAREHOUSES_PK INDEX .0625 WHS_LOCATION_IX INDEX .0625 32 rows selected.
Mais uma coisa que notei é que um objeto do tipo TABLE, no meu caso com o nome SYS_JOURNAL_117616, é criado no dicionário de dados durante toda a operação e ele existe a todo o tempo juntamente com o índice, pois ambos formam uma IOT:
SYS@orcl > select owner, object_name, object_type, created
from dba_objects
where created > to_date('10/08/2017 05:00','dd/mm/yyyy hh24:mi');
OWNER OBJECT_NAME OBJECT_TYPE CREATED
---------- ------------------------------ ----------------------- ---------
SOE SYS_IOT_TOP_117714 INDEX 10-AUG-17
SOE SYS_JOURNAL_117616 TABLE 10-AUG-17
2 rows selected.
Confirmando então a IOT:
SYS@orcl > select index_name, table_name, index_type from dba_indexes where owner='SOE' and index_name like 'SYS%'; INDEX_NAME TABLE_NAME INDEX_TYPE ------------------------------ ------------------------------ --------------------------- SYS_IOT_TOP_117714 SYS_JOURNAL_117616 IOT - TOP 1 row selected.
Uma investigação que achei legal de fazer. O objetivo era só mostrar que um índice auxiliar era criado durante o rebuild online e acabei descobrindo mais coisas. 😀
Neste teste utilizei a versão 12.1.0.2 do Oracle Database, então pode haver divergências para outras versões.
Durante todo o teste o Swingbench ficou em execução para gerar carga e contenções no ambiente.
Operações de rebuild online só podem ser realizadas no Oracle Database Enterprise Edition. No caso de rebuild comum as demais sessões que tentarem utilizar a tabela na qual o índice está sendo reconstruído ficarão aguardando a operação concluir e apresentarão o evento “enq: TM – contention”.
Por hoje é isso. Se gostaram, inscrevam-se no blog e em caso de dúvidas, elogios ou correções (sim, porque essas foram as minhas observações) deixe um comentário.
Abraços e até mais,
Franky