Pessoal, para Exemplificar os Conceitos discutidos montei aqui um caso-teste,
empregando paralelismo de pobre, DELETE com array processing e query com bulk
collecting, e com isso (num ambiente DE TESTE, uma VM no meu notebook) Deletei
mais de 7 milhões de linhas (e não linhas quaisquer, mas linhas que não estavam
na tabela de "cópia" dos dados, o que foi a proposição da thread) em minutos...
ÓBVIO ULULANTE que num ambiente real com TOTAL certeza a pessoa vai ter outras
tasks rodando, vai ter alguma concorrência feroz que num ambiente de teste não
temos, E com certeza devem haver índices a atualizar e constraints a checar
(DIFICILMENTE se obtém em Prod um Honesto e Suculento table scan que nem o que
eu obtive, dado que a tabela a deletar no meu exemplo Não Tinha nem índices nem
constraints nem relacionamentos), então CLARO que não se vai obter uma
performance do tipo, mas o case demonstra bem a Eficiência das técnicas ...
E antes de mostrar o Exemplo, FRISO que este Código é APENAS PARA DEMONSTRAÇÃO
: ele NÂO ESTÁ PRONTO para produção , Não prevê SAVE EXCEPTIONS, não prevê a
possibilidade de o intervalo das ROWIDs ter mudado por eventuais DMLs
concorrentes, está limitado na qtdade de recursos de máquina que eu boto no
panelão, então COM CERTEZA ABSOLUTA não recomendo que ninguém o bote nem perto
de produção, mas segue, FOR DEMO ONLY :
=> tenho uma tabela menor com as linhas a preservar :
SYSTEM:@orcl:SQL>select count(*) from TAB_PRESERVE;
COUNT(*)
----------
150006
=> tenho uma tabela Grande que contém os registros a serem deletados (teste com
pouco mais de 7 milhões e meio) :
SYSTEM:@orcl:SQL>select count(*) from TAB_TO_DELETE;
COUNT(*)
----------
7500200
SYSTEM:@orcl:SQL>
=> como vou fazer Paralelismo de pobre (já que pro caso perguntado a pessoa não
tem Enterprise Edition, então neca de parallel sql), eu DIVIDO a tabela em 3
partes, 3 INTERVALOS :
SYSTEM:SQL>get split.sql
1 set verify off
2 define TNAME=&1
3 define CHUNKS=&2
4 --
5 select grp,
6 dbms_rowid.rowid_create( 1, data_object_id, lo_fno, lo_block, 0 )
min_rid,
7 dbms_rowid.rowid_create( 1, data_object_id, hi_fno, hi_block, 10000
) max_rid
8 from (
9 select distinct grp,
10 first_value(relative_fno)
11 over (partition by grp order by relative_fno, block_id
12 rows between unbounded preceding and unbounded following)
lo_fno,
13 first_value(block_id )
14 over (partition by grp order by relative_fno, block_id
15 rows between unbounded preceding and unbounded following)
lo_block,
16 last_value(relative_fno)
17 over (partition by grp order by relative_fno, block_id
18 rows between unbounded preceding and unbounded following) hi_fno,
19 last_value(block_id+blocks-1)
20 over (partition by grp order by relative_fno, block_id
21 rows between unbounded preceding and unbounded following)
hi_block,
22 sum(blocks) over (partition by grp) sum_blocks
23 from (
24 select relative_fno,
25 block_id,
26 blocks,
27 trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
28 (sum(blocks) over ()/&CHUNKS) ) grp
29 from dba_extents
30 where segment_name = upper('&TNAME')
31 and owner = user order by block_id
32 )
33 ),
34* (select data_object_id from user_objects where object_name =
upper('&TNAME') )
SYSTEM:SQL>
SYSTEM:SQL>@split
Informe o valor para 1: TAB_TO_DELETE
Informe o valor para 2: 3
GRP MIN_RID MAX_RID
---------- ------------------ ------------------
0 AAAa1QAAIAAAACAAAA AAAa1QAAIAAAIx/CcQ
1 AAAa1QAAIAAAIyAAAA AAAa1QAAIAAARh/CcQ
2 AAAa1QAAIAAARiAAAA AAAa1QAAIAAAah/CcQ
=> ok... Antes de executar, primeiro vou desabilitar Paralelismo, já que estou
usando um Enterprise Edition onde o recurso existe, de modo a Simular a
situação lá do colega que usa Standard Edition :
-> sess#1 :
SYSTEM#1:SQL>ALTER session disable parallel query;
SessÒo alterada.
SYSTEM#1:SQL>ALTER session disable parallel dml;
SessÒo alterada.
SYSTEM#1:SQL>
-> sess#2 :
SYSTEM#2:SQL>ALTER session disable parallel query;
SessÒo alterada.
SYSTEM#2:SQL>ALTER session disable parallel dml;
SessÒo alterada.
-> sess#3 :
SYSTEM#3:SQL>ALTER session disable parallel query;
SessÒo alterada.
SYSTEM#3:SQL>ALTER session disable parallel dml;
SessÒo alterada.
==> PREPARO cada sessão, alocando o máximo de recursos que posso (mostrarei
aqui da sessão#1, mas fiz para TODAs elas):
SYSTEM#1:SQL>alter session set db_file_multiblock_read_count=128;
SessÒo alterada.
SYSTEM#1:SQL>alter session set workarea_size_policy=MANUAL;
SessÒo alterada.
SYSTEM#1:SQL>alter session set SORT_AREA_SIZE=104857600;
SessÒo alterada.
SYSTEM#1:SQL>alter session set HASH_AREA_SIZE=209715200;
SessÒo alterada.
SYSTEM#1:SQL>set timing on
==> ok , agora é executar a rotina simultaneamente nas 3 sessões, cada uma
lendo partes diferentes da tabela grande e deletando linhas diferentes :
-> exec. na sess#1
SYSTEM#1:SQL>DECLARE
cursor v_cursor is
SELECT -- Cursor#1
a.rowid ROWID_A_DELETAR
FROM tab_to_delete a
WHERE a.rowid between 'AAAa1QAAIAAAACAAAA' and 'AAAa1QAAIAAAIx/CcQ'
AND NOT EXISTS
(select 1 from tab_preserve b where b.object_id = a.object_id);
TYPE t_rowid IS TABLE OF rowid INDEX BY BINARY_INTEGER;
v_cursor_array t_rowid;
v_pass NUMBER := 0;
v_commit NUMBER := 0;
BEGIN
OPEN v_cursor;
LOOP
FETCH v_cursor BULK COLLECT INTO v_cursor_array LIMIT 2000;
EXIT WHEN v_cursor%NOTFOUND;
v_pass := v_pass + 1;
dbms_application_info.set_client_info('Sess#1,Pass#' || v_pass || ' em '
|| to_char(sysdate, 'HH24:MI:SS'));
forall i in v_cursor_array.FIRST..v_cursor_array.LAST DELETE FROM
tab_to_delete WHERE rowid=v_cursor_array(i);
v_pass := v_pass + 1;
v_commit := v_commit + 1;
if v_commit = 100 then
commit;
v_commit := 0;
end if;
END LOOP;
CLOSE v_cursor;
COMMIT;
END;
/
..... rola em background ...
-> exec. na sess#2
SYSTEM#2:SQL>DECLARE
cursor v_cursor is
SELECT -- Cursor#2
a.rowid ROWID_A_DELETAR
FROM tab_to_delete a
WHERE a.rowid between 'AAAa1QAAIAAAIyAAAA' and 'AAAa1QAAIAAARh/CcQ'
AND NOT EXISTS
(select 1 from tab_preserve b where b.object_id = a.object_id);
TYPE t_rowid IS TABLE OF rowid INDEX BY BINARY_INTEGER;
v_cursor_array t_rowid;
v_pass NUMBER := 0;
v_commit NUMBER := 0;
BEGIN
OPEN v_cursor;
LOOP
FETCH v_cursor BULK COLLECT INTO v_cursor_array LIMIT 2000;
EXIT WHEN v_cursor%NOTFOUND;
v_pass := v_pass + 1;
dbms_application_info.set_client_info('Sess#2,Pass#' || v_pass || ' em '
|| to_char(sysdate, 'HH24:MI:SS'));
forall i in v_cursor_array.FIRST..v_cursor_array.LAST DELETE FROM
tab_to_delete WHERE rowid=v_cursor_array(i);
v_pass := v_pass + 1;
v_commit := v_commit + 1;
if v_commit = 100 then
commit;
v_commit := 0;
end if;
END LOOP;
CLOSE v_cursor;
COMMIT;
END;
/
... continua em background ...
-> exec. na sess#3
SYSTEM#3:SQL>DECLARE
cursor v_cursor is
SELECT -- Cursor#3
a.rowid ROWID_A_DELETAR
FROM tab_to_delete a
WHERE a.rowid between 'AAAa1QAAIAAARiAAAA' and 'AAAa1QAAIAAAah/CcQ'
AND NOT EXISTS
(select 1 from tab_preserve b where b.object_id = a.object_id);
TYPE t_rowid IS TABLE OF rowid INDEX BY BINARY_INTEGER;
v_cursor_array t_rowid;
v_pass NUMBER := 0;
v_commit NUMBER := 0;
BEGIN
OPEN v_cursor;
LOOP
FETCH v_cursor BULK COLLECT INTO v_cursor_array LIMIT 2000;
EXIT WHEN v_cursor%NOTFOUND;
v_pass := v_pass + 1;
dbms_application_info.set_client_info('Sess#3,Pass#' || v_pass || ' em '
|| to_char(sysdate, 'HH24:MI:SS'));
forall i in v_cursor_array.FIRST..v_cursor_array.LAST DELETE FROM
tab_to_delete WHERE rowid=v_cursor_array(i);
v_pass := v_pass + 1;
v_commit := v_commit + 1;
if v_commit = 100 then
commit;
v_commit := 0;
end if;
END LOOP;
CLOSE v_cursor;
COMMIT;
END;
/
.... continua em background ...
==> durante a execução, Notar que o progresso está rápido, muitas centenas de
passadas/envios de arrays por minuto :
SYS:AS SYSDBA@orcl:SQL>select client_info from v$session where client_info is
not null;
CLIENT_INFO
----------------------------------------------------------------
Sess#1,Pass#903 em 08:45:02
Sess#2,Pass#111 em 08:46:30
Sess#3,Pass#305 em 08:45:31
... espero um pouquinho e consulto de novo ...
SYS:AS SYSDBA@orcl:SQL>/
CLIENT_INFO
----------------------------------------------------------------
Sess#1,Pass#1233 em 08:48:55
Sess#2,Pass#579 em 08:48:03
Sess#3,Pass#787 em 08:48:24
=> resultado final :
-> sess#1 :
PL/SQL procedure successfully completed.
Elapsed: 00:06:27.65
SYSTEM#1:SQL>
-> sess#2 :
PL/SQL procedure successfully completed.
Elapsed: 00:06:39.93
SYSTEM#2:SQL>
-> sess #3 :
PL/SQL procedure successfully completed.
Elapsed: 00:06:40.34
=====>> EVIDÊNCIA de DELETEs feitos :
SYS:AS SYSDBA@orcl:SQL>select count(*) from system.tab_preserve;
COUNT(*)
----------
150006
SYS:AS SYSDBA@orcl:SQL>select count(*) from system.tab_to_delete a
2* where exists (select 1 from system.tab_preserve b where b.object_id =
a.object_id);
COUNT(*)
----------
150006
SYS:AS SYSDBA@orcl:SQL>
-> veja que havia alguns OBJECT_IDs (a "chave") órfãos :
SYS:AS SYSDBA@orcl:SQL>select count(*) from system.tab_to_delete;
COUNT(*)
----------
152200
SYS:AS SYSDBA@orcl:SQL>select count(*) from system.tab_to_delete a
2 where not exists (select 1 from system.tab_preserve b where b.object_id =
a.object_id);
COUNT(*)
----------
2194
[]s
Chiappa
---Em oracle_br@yahoogrupos.com.br, <jlchia...@yahoo.com.br> escreveu:
Alessandro, Sim, absolutamente não tem o que discutir que cada COMMIT te força
a ESPERAR que os logs sejam salvos em disco, a esperar que as alterações que o
RDBMS faz nas suas tabelas internas se completem e sejam salvas, esperar e
esperar, e assim se deveria comittar o menos possível :
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4951966319022
,
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:6407993912330
e
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:5918938803188
tem testes e demonstrações que exemplificam Claramente...
Note porém que no caso do código do lmarinho, o COMMIT tá depois do DELETE em
array/bulk (DELETE que está logo depois do FORALL), ele Não É linha-a-linha,
não é cada linha que está sendo COMMITada, é o array....
Logicamente, o código do lmarinho vale de exemplo para outros cenários mas
penso ser inadequado pro caso do colega que estava perguntando porque :
a. as linhas a serem deletadas devem ser encontradas via JOIN, portanto seria
necessário um carregamento com BULK COLLECT pro array, para que depois o DELETE
possa as encontrar, provavelmente via rowid
b. os volumes envolvidos inviabilizam se ter um Array de
não-sei-quantos-milhões, ou mesmo centenas e centenas de milhares de linhas,
entãao não daria pra fazer um FORALL DELETE único, julgo que teria que ter um
outro LOOP e commit de tanto em tanto tempo ou de tantas em tantas linhas, para
não esgotar a área de rollback/undo - normalmente se tem uma área de
rollback/undo adequada ao dia-a-dia, e o tal DELETE de várias dezenas de
milhões que o colega queria não é algo rotineiro, provavelmente o que ele tem
hoje não Suporta, por isso que foi recomendado assumir os WAITs e a demora e
comitar múltiplas vezes
[]s
Chiappa
