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)
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.
call count cpu elapsed disk query current rows
Com a execução da consulta para criar os índices de acordo com as chaves estrangeiras foi recomendada a criação do seguinte índice:
Após criado o índices executamos novamente a mesma operação de delete.
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.
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.
Grande Henchen, bom post heim?
ResponderExcluiruahuahuahuhua
Obs.: vou responder agora seu email.
Abraços meu amigo.
capin