Vamos a mais um post sobre uma falha de arquitetura que muitas vezes não é percebida pelo desenvolvedor ou DBAs envolvidos no desenvolvimento de uma nova aplicação, ou mesmo de uma aplicação já operacional.
A algumas semanas estava dando manutenção em um sistema e me chamou a atenção o fato de todas as chaves estrangeiras estarem desabilitadas, com a justificativa de que elas tornavam o sistema muito mais lento.
Não vou nem entrar na discução a respeito da questão sobre performance (ficará para um próximo post, mas caso não se queira fazer uso dos recursos de integridade de dados de um banco de dados Relacional, então não use um banco de dados Relacional, existem diversas opções de
bancos de dados NoSQL disponíveis no mercado e eles também implementam mecanismos de controle de concorrência e integridade de dados).
Bom, voltando ao assunto, no caso em particular percebi que ao habilitar as constraints que a lentidão ocorria durante o delete de registros em algumas tabelas, onde ocorriam diversos "table locks" nas tabelas filhas, e já me ocorreu, "estão faltando índices".
A documentação
do Oracle Database, já fala sobre índices e chaves estrangeiras:
- Evitar contenção completa da tabela (lock table) filha em caso de "delete" na tabela pai;
- O índice permite o lock na tabela filha como "row lock";
- Updates na chave primária da tabela pai também caem nesta mesma situação, mas alertando que alterações de chave primária serem consideradas uma má prática;
- Evita "full table scan" desnecessários na tabela filha;
Os índices apenas podem ser ignorados caso a tabela pai não sofra operações de "update", e nem mesmo de "delete" (caso raro).
A algum tempo atrás eu já havia construido uma consulta para exibir todos os índices não existentes para as FKs criadas no esquema do usuário corrente, já gerando os comandos de criação dos índices. Segue:
set lines 180
set pages 10000
col command for a180
alter session set optimizer_features_enable='10.2.0.1';
SELECT 'CREATE INDEX ' || cons.constraint_name || ' ON ' || cons.table_name || '(' || cons.columns || ') TABLESPACE &tablespace;' command
FROM (SELECT constraint_name, table_name,
LTRIM(MAX(SYS_CONNECT_BY_PATH(column_name,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS columns
FROM (SELECT dcc.constraint_name,
dcc.table_name,
dcc.column_name,
ROW_NUMBER() OVER (PARTITION BY dcc.constraint_name ORDER BY dcc.position) AS curr,
ROW_NUMBER() OVER (PARTITION BY dcc.constraint_name ORDER BY dcc.position) -1 AS prev
FROM user_cons_columns dcc
INNER JOIN user_constraints dc
ON dc.owner = dcc.owner
AND dc.constraint_name = dcc.constraint_name
AND dcc.table_name = dc.table_name
WHERE dc.constraint_type = 'R')
GROUP BY constraint_name, table_name
CONNECT BY prev = PRIOR curr AND constraint_name = PRIOR constraint_name
START WITH curr = 1) cons
WHERE NOT EXISTS (SELECT 1
FROM
(SELECT index_name, table_name,
LTRIM(MAX(SYS_CONNECT_BY_PATH(column_name,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS columns
FROM (SELECT icc.index_name,
icc.table_name,
icc.column_name,
ROW_NUMBER() OVER (PARTITION BY icc.index_name ORDER BY icc.column_position) AS curr,
ROW_NUMBER() OVER (PARTITION BY icc.index_name ORDER BY icc.column_position) -1 AS prev
FROM user_ind_columns icc)
GROUP BY index_name, table_name
CONNECT BY prev = PRIOR curr AND index_name = PRIOR index_name
START WITH curr = 1) indx
WHERE cons.table_name = indx.table_name
AND cons.columns = indx.columns)
ORDER BY cons.table_name;
Prova de conceito
Caso algum developer teimoso ainda queira argumentar a respeito, bom eis então uma prova de conceito sobre o uso de índices em chaves estrangeiras.
Primeiro devemos criar 2 tabelas grandes, onde a tabela BIGTAB2 (com 1 milhão de registros) fará referencia à tabela BIGTAB1 com a clausula "on delete cascade" (para remover automaticamente registros da tabela BIGTAB2 caso o registro pai tenha sido removido na tabela BIGTAB1).
-- Cria tabela PAI
create table bigtab1
as
select rownum id, a.*
from all_objects a
where 1=0;
alter table bigtab1 add primary key (id) using index tablespace USERS;
insert into bigtab1
select rownum,
owner, object_name, subobject_name,
object_id, data_object_id,
object_type, created, last_ddl_time,
timestamp, status, temporary,
generated, secondary
from all_objects;
commit;
execute dbms_stats.gather_table_stats(ownname => 'DBA_RAFAEL', tabname=>'BIGTAB1', cascade=>TRUE);
-- Cria tabela FILHA
create table bigtab2
as
select a.*,
rownum parent_id
from bigtab1 a
where 1=0;
alter table bigtab2 add foreign key (parent_id) references bigtab1(id) on delete cascade;
declare
l_cnt number := 0;
l_rows number := 1000000;
begin
-- Generate Rows
while (l_cnt < l_rows)
loop
insert into bigtab2
select ID+l_cnt, -- Nova chave unica
owner, object_name, subobject_name,
object_id, data_object_id,
object_type, created, last_ddl_time,
timestamp, status, temporary,
generated, secondary,
ID -- Mantem referencia ao pai na tabela bigtab1
from bigtab1
where id <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/
execute dbms_stats.gather_table_stats(ownname => 'DBA_RAFAEL', tabname=>'BIGTAB2', cascade=>TRUE);
Veja que apenas a chave primária, coluna ID, da tabela BIGTAB1 esta indexada.
Delete sem índice na FK
Agora façamos um teste de deleção na tabela BIGTAB1 gerando trace da execução.
ALTER SESSION SET TRACEFILE_IDENTIFIER = "DBA_RAFAEL_1";
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
delete from bigtab1 where id between 1 and 100;
rollback;
Na execução do TKPROF usamos o parâmetro "sys=yes" para que a deleção dos registros na tabela filha seja exibido no arquivo de saída, assim como o "aggregate=yes" para agregar os deletes.
tkprof rddbdev_ora_22603_DBA_RAFAEL_1.trc trace_delete_1.txt explain=DBA_RAFAEL sys=YES aggregate=yes waits=yes sort=prsela,exeela,fchela
Como podemos ver abaixo a deleção da tabela pai possui o tempo total de execução de 0.04 segundos e plano de execução usando INDEX RANGE SCAN da chave primária, tal como já era de se esperar:
delete from bigtab1
where
id between :"SYS_B_0" and :"SYS_B_1"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.04 0 2 517 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.04 0 2 517 100
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 67 (DBA_RAFAEL)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE BIGTAB1 (cr=1520802 pr=0 pw=0 time=7588432 us)
100 FILTER (cr=2 pr=0 pw=0 time=829 us)
100 INDEX RANGE SCAN SYS_C00167097 (cr=2 pr=0 pw=0 time=32 us)(object id 220499)
Se pode perceber o alto custo (CR) e tempo (TIME) associados a operação de DELETE no plano de execução, isto se deve devido ao cascade, uma vez que a deleção dos registros na tabela filha é feito pelo próprio oracle, pois a constraint existe com a clausula "on delete cascade".
Como podemos ver abaixo são feitas mais de 100 execuções (uma para cada chave na tabela pai) do delete, com tempo total de execução de 7.54 segundos, e também podemos observar no plano de execução que é feito um FULL TABLE SCAN na tabel BIGTAB2 a cada execução.
delete from "DBA_RAFAEL"."BIGTAB2"
where
"PARENT_ID" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 7.72 7.54 0 1520800 1688 1500
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 7.72 7.54 0 1520800 1688 1500
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE BIGTAB2 (cr=1520800 pr=0 pw=0 time=7538904 us)
1500 TABLE ACCESS FULL BIGTAB2 (cr=1520800 pr=0 pw=0 time=7149249 us)
Delete com índice na FK
Com a execução da consulta para criar os índices de acordo com as chaves estrangeiras foi recomendada a criação do seguinte índice:
COMMAND
-------------------------------------------------------------------
CREATE INDEX SYS_C00167103 ON BIGTAB2(PARENT_ID) TABLESPACE USERS;
Após criado o índices executamos novamente a mesma operação de delete.
CREATE INDEX SYS_C00167103 ON BIGTAB2(PARENT_ID) TABLESPACE USERS;
execute dbms_stats.gather_table_stats(ownname => 'DBA_RAFAEL', tabname=>'BIGTAB2', cascade=>TRUE);
ALTER SESSION SET TRACEFILE_IDENTIFIER = "DBA_RAFAEL_2";
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
delete from bigtab1 where id between 1 and 100;
rollback;
Agora, com tempo de execução menor, a saída do TKPROF nos aponta um custo e tempo muito menor em ambas as operações de delete.
delete from bigtab1
where
id between :"SYS_B_0" and :"SYS_B_1"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 615 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 615 100
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 67 (DBA_RAFAEL)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE BIGTAB1 (cr=304 pr=0 pw=0 time=50849 us)
100 FILTER (cr=2 pr=0 pw=0 time=229 us)
100 INDEX RANGE SCAN SYS_C00167097 (cr=2 pr=0 pw=0 time=25 us)(object id 220499)
E para o "delete cascade" temos a mesma deleção usando PARENT_ID só que desta vez com uso do índice recem criado, e com uma quantidade muito inferior de LIOs (302) e por consequencia um custo de execução muito menor em relação ao plano de execução do teste anterior com mais 1.5 milhões de LIOs. Diminuindo de 7.54 para 0.04 o tempo de execução do delete na tabela filha.
delete from "DBA_RAFAEL"."BIGTAB2"
where
"PARENT_ID" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.04 0.04 0 302 6226 1500
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.04 0.04 0 302 6226 1500
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE BIGTAB2 (cr=302 pr=0 pw=0 time=42640 us)
1500 INDEX RANGE SCAN SYS_C00167103 (cr=302 pr=0 pw=0 time=3648 us)(object id 220502)
Neste exemplo temos então as evidências da melhoria de performance para menos de 1% do tempo de execução anterior.