Menu de navegação

[fabio segatoParticipante]

Há um bom tempo trabalho com PL/SQL mas nunca tinha visto algo parecido. O problema é basicamente o seguinte , tenho uma query basicamente complexa que deve carregar uma tabela. Quando executo um create table as … a tabela é carregada em 3 segundos, quando troco para um insert select ela demora minutos e minutos sem terminar.

[b]create table etf_ordenado as
select
A.ID
,A.ID_OBJETO
,A.TIPO_DO_OBJETO
,A.ID_ORIGEM
,A.DT_GERACAO_EVENTO
,A.TAX_CODE
,A.BILL_NO_CHAVE_AGP
,A.DT_INICIO
,A.DT_FIM
,A.VALOR
,A.INICIO_COMP_ATUAL
,sem_origem.origem as origem
,com_origem.destino as destino
from
etf a
left join
(
select
a.bill_no_chave_agp
,a.tax_code
,id_objeto
,dt_fim
,row_number() over (partition by bill_no_chave_agp,tax_code,id_objeto order by dt_FIM DESC) origem
from
etf a
where
id_origem is null
and
dt_fim < inicio_comp_atual and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp and (b.id = a.id_origem)) and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp AND (b.id_origem = a.id)) ) sem_origem on a.tax_code = sem_origem.tax_code and a.bill_no_chave_agp = sem_origem.bill_no_chave_agp and a.id_objeto = sem_origem.id_objeto and a.id_origem is null and a.dt_fim < a.inicio_comp_atual and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp and (b.id = a.id_origem) ) and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp AND (b.id_origem = a.id)) and a.dt_fim = sem_origem.dt_fim left join ( select a.bill_no_chave_agp ,a.tax_code ,id_objeto ,id_origem ,dt_fim ,row_number() over (partition by bill_no_chave_agp,tax_code,id_objeto order by dt_FIM DESC) destino from etf a where id_origem is not null and dt_fim < inicio_comp_atual and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp and (b.id = a.id_origem)) and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp AND (b.id_origem = a.id)) ) com_origem on a.tax_code = com_origem.tax_code and a.bill_no_chave_agp = com_origem.bill_no_chave_agp and a.id_objeto = com_origem.id_objeto and a.id_origem = com_origem.id_origem and a.id_origem is not null and a.dt_fim < a.inicio_comp_atual and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp and (b.id = a.id_origem)) and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp AND (b.id_origem = a.id)) and a.dt_fim = com_origem.dt_fim call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.06 3 38 0 0 Execute 1 2.97 3.03 1053 527110 1285 30072 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 2.98 3.09 1056 527148 1285 30072 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 105 Rows Row Source Operation ------- --------------------------------------------------- 0 LOAD AS SELECT (cr=531056 pr=1176 pw=574 time=0 us) 30072 NESTED LOOPS OUTER (cr=527101 pr=1055 pw=0 time=2892062 us cost=1022885 size=4691232 card=30072) 30072 NESTED LOOPS OUTER (cr=219809 pr=1055 pw=0 time=1098167 us cost=511523 size=4300296 card=30072) 30072 TABLE ACCESS FULL ETF (cr=574 pr=570 pw=0 time=58862 us cost=161 size=3909360 card=30072) 16 VIEW (cr=219235 pr=485 pw=0 time=0 us cost=17 size=13 card=1) 16 FILTER (cr=219235 pr=485 pw=0 time=0 us) 16 VIEW PUSHED PREDICATE (cr=27 pr=0 pw=0 time=0 us cost=8 size=61 card=1) 24 WINDOW SORT (cr=27 pr=0 pw=0 time=12 us cost=7 size=124 card=1) 24 NESTED LOOPS ANTI (cr=27 pr=0 pw=0 time=107 us cost=6 size=124 card=1) 24 NESTED LOOPS ANTI (cr=18 pr=0 pw=0 time=100 us cost=4 size=100 card=1) 24 TABLE ACCESS BY INDEX ROWID ETF (cr=9 pr=0 pw=0 time=7 us cost=2 size=76 card=1) 78 INDEX RANGE SCAN IDX_SUM (cr=8 pr=0 pw=0 time=23 us cost=1 size=0 card=1)(object id 76857) 0 TABLE ACCESS BY INDEX ROWID ETF (cr=9 pr=0 pw=0 time=0 us cost=2 size=24 card=1) 236 INDEX RANGE SCAN IDX_SUM (cr=8 pr=0 pw=0 time=144 us cost=1 size=0 card=1)(object id 76857) 0 TABLE ACCESS BY INDEX ROWID ETF (cr=9 pr=0 pw=0 time=0 us cost=2 size=24 card=1) 156 INDEX RANGE SCAN IDX_SUM (cr=8 pr=0 pw=0 time=74 us cost=1 size=0 card=1)(object id 76857) 3 TABLE ACCESS BY INDEX ROWID ETF (cr=109666 pr=485 pw=0 time=0 us cost=3 size=24 card=1) 30295 INDEX RANGE SCAN IDX_SUM (cr=79630 pr=0 pw=0 time=805 us cost=2 size=0 card=1)(object id 76857) 3 TABLE ACCESS BY INDEX ROWID ETF (cr=109542 pr=0 pw=0 time=0 us cost=3 size=24 card=1) 30181 INDEX RANGE SCAN IDX_SUM (cr=79517 pr=0 pw=0 time=359 us cost=2 size=0 card=1)(object id 76857) 15106 VIEW (cr=307292 pr=0 pw=0 time=0 us cost=17 size=13 card=1) 15106 FILTER (cr=307292 pr=0 pw=0 time=0 us) 15106 VIEW PUSHED PREDICATE (cr=88084 pr=0 pw=0 time=0 us cost=8 size=74 card=1) 15126 WINDOW SORT (cr=88084 pr=0 pw=0 time=68 us cost=7 size=124 card=1) 15126 NESTED LOOPS ANTI (cr=88084 pr=0 pw=0 time=846 us cost=6 size=124 card=1) 15126 NESTED LOOPS ANTI (cr=48789 pr=0 pw=0 time=511 us cost=4 size=100 card=1) 15126 TABLE ACCESS BY INDEX ROWID ETF (cr=15808 pr=0 pw=0 time=119 us cost=2 size=76 card=1) 15186 INDEX RANGE SCAN IDX_SUM (cr=15220 pr=0 pw=0 time=137 us cost=1 size=0 card=1)(object id 76857) 0 TABLE ACCESS BY INDEX ROWID ETF (cr=32981 pr=0 pw=0 time=0 us cost=2 size=24 card=1) 15438 INDEX RANGE SCAN IDX_SUM (cr=27735 pr=0 pw=0 time=255 us cost=1 size=0 card=1)(object id 76857) 0 TABLE ACCESS BY INDEX ROWID ETF (cr=39295 pr=0 pw=0 time=0 us cost=2 size=24 card=1) 15438 INDEX RANGE SCAN IDX_SUM (cr=30365 pr=0 pw=0 time=468 us cost=1 size=0 card=1)(object id 76857) 3 TABLE ACCESS BY INDEX ROWID ETF (cr=109666 pr=0 pw=0 time=0 us cost=3 size=24 card=1) 30295 INDEX RANGE SCAN IDX_SUM (cr=79630 pr=0 pw=0 time=561 us cost=2 size=0 card=1)(object id 76857) 3 TABLE ACCESS BY INDEX ROWID ETF (cr=109542 pr=0 pw=0 time=0 us cost=3 size=24 card=1) 30181 INDEX RANGE SCAN IDX_SUM (cr=79517 pr=0 pw=0 time=210 us cost=2 size=0 card=1)(object id 76857) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ Disk file operations I/O 3 0.00 0.00 direct path read 41 0.00 0.00 direct path write 23 0.00 0.00 db file sequential read 486 0.00 0.00 enq: CR - block range reuse ckpt 1 0.00 0.00 log file sync 1 0.00 0.00 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 7.39 7.39 ********************************************************************************[/b] [b]insert into etf_ordenado select A.ID ,A.ID_OBJETO ,A.TIPO_DO_OBJETO ,A.ID_ORIGEM ,A.DT_GERACAO_EVENTO ,A.TAX_CODE ,A.BILL_NO_CHAVE_AGP ,A.DT_INICIO ,A.DT_FIM ,A.VALOR ,A.INICIO_COMP_ATUAL ,sem_origem.origem as origem ,com_origem.destino as destino from etf a left join ( select a.bill_no_chave_agp ,a.tax_code ,id_objeto ,dt_fim ,row_number() over (partition by bill_no_chave_agp,tax_code,id_objeto order by dt_FIM DESC) origem from etf a where id_origem is null and dt_fim < inicio_comp_atual and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp and (b.id = a.id_origem)) and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp AND (b.id_origem = a.id)) ) sem_origem on a.tax_code = sem_origem.tax_code and a.bill_no_chave_agp = sem_origem.bill_no_chave_agp and a.id_objeto = sem_origem.id_objeto and a.id_origem is null and a.dt_fim < a.inicio_comp_atual and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp and (b.id = a.id_origem) ) and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp AND (b.id_origem = a.id)) and a.dt_fim = sem_origem.dt_fim left join ( select a.bill_no_chave_agp ,a.tax_code ,id_objeto ,id_origem ,dt_fim ,row_number() over (partition by bill_no_chave_agp,tax_code,id_objeto order by dt_FIM DESC) destino from etf a where id_origem is not null and dt_fim < inicio_comp_atual and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp and (b.id = a.id_origem)) and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp AND (b.id_origem = a.id)) ) com_origem on a.tax_code = com_origem.tax_code and a.bill_no_chave_agp = com_origem.bill_no_chave_agp and a.id_objeto = com_origem.id_objeto and a.id_origem = com_origem.id_origem and a.id_origem is not null and a.dt_fim < a.inicio_comp_atual and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp and (b.id = a.id_origem)) and NOT EXISTS (select 1 from etf b where b.bill_no_chave_agp = a.bill_no_chave_agp AND (b.id_origem = a.id)) and a.dt_fim = com_origem.dt_fim call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.02 0 35 0 0 Execute 1 289.31 534.47 609756 6421979 1941 7318 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 289.33 534.49 609756 6422014 1941 7318 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 105 Rows Row Source Operation ------- --------------------------------------------------- 0 LOAD TABLE CONVENTIONAL (cr=0 pr=0 pw=0 time=0 us) 7318 NESTED LOOPS OUTER (cr=6419901 pr=609588 pw=609113 time=830628608 us cost=24602795 size=4691232 card=30072) 7319 NESTED LOOPS OUTER (cr=53038 pr=2 pw=0 time=449299 us cost=6269704 size=4300296 card=30072) 7319 TABLE ACCESS FULL ETF (cr=146 pr=2 pw=0 time=40879 us cost=161 size=3909360 card=30072) 16 VIEW (cr=52892 pr=0 pw=0 time=0 us cost=208 size=13 card=1) 16 FILTER (cr=52892 pr=0 pw=0 time=0 us) 16 VIEW (cr=9202 pr=0 pw=0 time=0 us cost=202 size=61 card=1) 256 WINDOW SORT (cr=9202 pr=0 pw=0 time=3424 us cost=202 size=124 card=1) 256 NESTED LOOPS ANTI (cr=9202 pr=0 pw=0 time=1215 us cost=201 size=124 card=1) 256 NESTED LOOPS ANTI (cr=9193 pr=0 pw=0 time=780 us cost=199 size=100 card=1) 256 TABLE ACCESS FULL ETF (cr=9184 pr=0 pw=0 time=345 us cost=161 size=1444 card=19) 0 TABLE ACCESS BY INDEX ROWID ETF (cr=9 pr=0 pw=0 time=0 us cost=2 size=721728 card=30072) 1248 INDEX RANGE SCAN IDX_SUM (cr=8 pr=0 pw=0 time=721 us cost=1 size=0 card=1)(object id 76857) 0 TABLE ACCESS BY INDEX ROWID ETF (cr=9 pr=0 pw=0 time=0 us cost=2 size=721728 card=30072) 928 INDEX RANGE SCAN IDX_SUM (cr=8 pr=0 pw=0 time=497 us cost=1 size=0 card=1)(object id 76857) 3 TABLE ACCESS BY INDEX ROWID ETF (cr=21856 pr=0 pw=0 time=0 us cost=3 size=24 card=1) 7542 INDEX RANGE SCAN IDX_SUM (cr=14573 pr=0 pw=0 time=582 us cost=2 size=0 card=1)(object id 76857) 3 TABLE ACCESS BY INDEX ROWID ETF (cr=21834 pr=0 pw=0 time=0 us cost=3 size=24 card=1) 7428 INDEX RANGE SCAN IDX_SUM (cr=14562 pr=0 pw=0 time=128 us cost=2 size=0 card=1)(object id 76857) 3672 VIEW (cr=6366875 pr=609586 pw=609113 time=0 us cost=610 size=13 card=1) 3672 FILTER (cr=6366875 pr=609586 pw=609113 time=0 us) 3672 VIEW (cr=6323185 pr=609586 pw=609113 time=0 us cost=604 size=148 card=2) 55476408 WINDOW SORT (cr=6324907 pr=609752 pw=609279 time=527952832 us cost=604 size=248 card=2) 55484338 HASH JOIN ANTI (cr=6324907 pr=609752 pw=609279 time=304091072 us cost=603 size=248 card=2) 55484338 HASH JOIN RIGHT ANTI (cr=4216605 pr=334352 pw=333879 time=889163648 us cost=441 size=15500 card=155) 110454456 TABLE ACCESS FULL ETF (cr=2108303 pr=219 pw=0 time=268386656 us cost=161 size=721728 card=30072) 55484338 TABLE ACCESS FULL ETF (cr=2108302 pr=254 pw=0 time=177606240 us cost=162 size=1177772 card=15497) 110454456 TABLE ACCESS FULL ETF (cr=2108302 pr=0 pw=0 time=246603584 us cost=161 size=721728 card=30072) 3 TABLE ACCESS BY INDEX ROWID ETF (cr=21856 pr=0 pw=0 time=0 us cost=3 size=24 card=1) 7542 INDEX RANGE SCAN IDX_SUM (cr=14573 pr=0 pw=0 time=302 us cost=2 size=0 card=1)(object id 76857) 3 TABLE ACCESS BY INDEX ROWID ETF (cr=21834 pr=0 pw=0 time=0 us cost=3 size=24 card=1) 7428 INDEX RANGE SCAN IDX_SUM (cr=14562 pr=0 pw=0 time=176 us cost=2 size=0 card=1)(object id 76857) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ Disk file operations I/O 3 0.00 0.00 db file scattered read 38 0.00 0.00 asynch descriptor resize 7345 0.00 0.03 direct path write temp 66057 1.37 262.30 direct path read temp 66057 0.00 2.34 db file sequential read 4 0.00 0.00 log file sync 1 0.00 0.00 ********************************************************************************[/b] Oq mais me intrigou foi que existe uma mudança no plano de execução de Nested loops para hash join, sendo q a query continua a mesmo, o hash join utiliza área temp isso faz gerar os wait events de direct path write temp e direct path read temp algo que não acontece com a primeira abordagem. A pergunta é. Pq isso acontece e como arrumar isso...

[rmanParticipante]

@fabiosegato

Foi criado CONSTRAINT após o CREATE TABLE AS SELECT? Por exemplo, se foi criado PRIMARY KEY, FOREIGN KEY e INDEX provavelmente isso vai influenciar no plano de execução do INSERT SELECT.

[fabio segatoParticipante]

Não nenhuma constraint, nenhum indice nenhum trigger, ou nenhum recurso do banco como auditoria foram feitos após a criação da tabela.

[Fábio PradoParticipante]

@fabiosegato

Experimente acrescentar o hint APPEND no insert e veja novamente o tempo.

Exemplo:
insert /*+ APPEND */ into etf_ordenado

[]s

[fabio segatoParticipante]

Já tentei a opção ode criar a tabela com nolloging e usar direct path mas mesmo assim o problema continua o mesmo.

[Fábio PradoParticipante]

Dei uma olhada rápida nos passos do plano de execução que vc tirou do trace das instruções SQL e dá para ver que no INSERT o que está fazendo a instrução demorar são vários FTS que ocorrem na tabela ETF. Estes FTS são substituídos por INDEX RANGE SCAN no CTAS, que foi possível através do passo VIEW PUSHED PREDICATE. O por quê do plano de execução ter sido diferente eu não entendi.

Vc executou as 2 instruções SQL conectado com o mesmo usuário, na mesma sessão de BD?

[fabio segatoParticipante]

Exato a mesma instrução no mesmo banco com o mesmo usuário. Só mudando de CTAS para INSERT INTO.

Detalhe fiz esse mesmo teste em duas instâncias diferentes e ambas apresentam o mesmo problema.

[Regis AraujoParticipante]

Caros, boa tarde!

A quanto tempo heim pessoal..!

Bom, não concordo em ser o mesmo comando, pois as opções de CTAS e INSERT fazem parte da instrução, então são 2 comandos distintos.

Fora que internamente o CTAS trabalha bem diferente do Insert as Select.. Tanto que no plano de execução, nota-se que o CTAS faz um LOAD AS SELECT e o Insert as Select faz um LOAD TABLE CONVENTIONAL

Onde o CTAS é um Direct-Path Insert e o LOAD TABLE CONVENTIONAL não é um Direct-Path Insert.. e por isto que o Oracle muda o plano de execução, para trabalhar com Direct Path e sem Direct Path…!

Faça um teste.. coloque os hints /*+ append parallel() */..

• insert /*+ append parallel(etf_ordenado) */ into etf_ordenado *

Vc verá que o tempo irá melhorar e o plano ficará semelhante..!!

Bom.. se alguem tiver outra explicação, ficarei contente em aprender mais..!!

Abraços..!

[fabio segatoParticipante]

Já tentei usar direct path em paralelo , mesmo assim a situação continua a mesma.

[Autor]

Posts