Performance Create table X insert select

Dúvidas, dicas e atualizações sobre o Treinamento Oracle Performance Diagnostics & Tuning.
fabiosegato

Performance Create table X insert select

Post by fabiosegato »

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.

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
********************************************************************************



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
********************************************************************************


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...

portilho
Site Admin
Posts: 502
Joined: Wed May 29, 2013 8:51 am

Re: Performance Create table X insert select

Post by portilho »

Ainda não olhei os planos, mas para mim, um CTAS sempre foi mais rápido que um INSERT AS SELECT.

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
581936

Elapsed: 00:00:02.98
SQL> CREATE TABLE T2 AS SELECT * FROM T;

Table created.

Elapsed: 00:00:17.63
SQL> CREATE TABLE T3 AS SELECT * FROM T WHERE 1=2;

Table created.

Elapsed: 00:00:00.09
SQL> INSERT INTO T3 SELECT * FROM T;

581936 rows created.

Elapsed: 00:00:35.01
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.03
SQL>

portilho
Site Admin
Posts: 502
Joined: Wed May 29, 2013 8:51 am

Re: Performance Create table X insert select

Post by portilho »

É perfeitamente normal existirem dois planos de execução para dois SQLs diferentes, que mesmo que possuam o mesmo objetivo, são textos diferentes. O compilador criará dois planos de execução, que podem ser diferentes.
Você não pode utilizar o de CTAS?

fabiosegato

Re: Performance Create table X insert select

Post by fabiosegato »

Concordo, mas acho estranho esse comportamento do optimizador , o plano do CTAS é igual ao da query quando faz um fetch normal, que também é bem rápido. Só quando é utilizado o INSERT INTO que o plano muda e deixa a operação extremamente lenta. Não faz o menor sentido esse comportamento do optimizador , seria o mais obvio ele utilizar o plano mais eficiente. Estou utilizando o CTAS no processo batch, só não achei que fosse uma solução elegante, fica com cara de gambiarra sem explicação.

portilho
Site Admin
Posts: 502
Joined: Wed May 29, 2013 8:51 am

Re: Performance Create table X insert select

Post by portilho »

Você tem o SQLT instalado? Se sim, me envie um SQLXTRACT dos dois SQLs.

fabiosegato

Re: Performance Create table X insert select

Post by fabiosegato »

Ricardo, não tenho essa ferramenta instalada , aliás não conhecia essa ferramenta.

portilho
Site Admin
Posts: 502
Joined: Wed May 29, 2013 8:51 am

Re: Performance Create table X insert select

Post by portilho »

Então apenas execute eles em Trace 10053 (Trace do CBO), em sessões e Traces separados:
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

E coloque os Traces aqui, como attachment.

fabiosegato

Re: Performance Create table X insert select

Post by fabiosegato »

Ricardo, trace com level 1 não está retornando nenhum informação.

portilho
Site Admin
Posts: 502
Joined: Wed May 29, 2013 8:51 am

Re: Performance Create table X insert select

Post by portilho »

Não gerou um dump? No udump?

fabiosegato

Re: Performance Create table X insert select

Post by fabiosegato »

Gerou sim, eu me confundi achei que você queria o relatório tkprof, esse sim não estava sendo gerado com nenhuma informação
Attachments
INSERT.zip
INSERT
(50.55 KiB) Downloaded 475 times
CTAS.zip
CTAS
(48.53 KiB) Downloaded 572 times

Post Reply