Menu de navegação

[vieriParticipante]

Pessoal,

estou com essa consulta e na hora da criação da mview está muito
pesado.

já criei índice e o custo baixou um pouco, mais ainda está pesado.

Alguem ve algo de a normal aqui:
Eu nunca gostei do hash join queria elimina-lo.

Execution Plan
———————————————————-

—————————————————————————————————
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
—————————————————————————————————
| 0 | SELECT STATEMENT | | 32258 | 3024K| | 1366 (1)|
| 1 | HASH UNIQUE | | 32258 | 3024K| 6632K| 1366 (1)|
| 2 | NESTED LOOPS | | 32258 | 3024K| | 652 (2)|
| 3 | HASH JOIN | | 32258 | 2835K| | 651 (2)|
| 4 | VIEW | index$_join$_002 | 37055 | 470K| | 10 (40)|
| 5 | HASH JOIN | | | | | |
| 6 | HASH JOIN | | | | | |
| 7 | INLIST ITERATOR | | | | | |
| 8 | INDEX RANGE SCAN | IDX_CD_CLASSE_MVIEW | 37055 | 470K| | 5 (20)|
| 9 | INDEX FAST FULL SCAN | AK1_SKU | 37055 | 470K| | 3 (0)|
| 10 | INDEX FAST FULL SCAN | IE3_SKU | 37055 | 470K| | 2 (0)|
| 11 | TABLE ACCESS BY INDEX ROWID| TB_PRECO_ITEM | 1 | 63 | | 1 (0)|
| 12 | NESTED LOOPS | | 32258 | 2425K| | 640 (1)|
| 13 | VIEW | VW_NSO_1 | 32258 | 441K| | 317 (1)|
| 14 | HASH GROUP BY | | 32258 | 409K| | 317 (1)|
| 15 | INDEX FULL SCAN | IE2_PRECO_ITEM | 9409K| 116M| | 317 (1)|
| 16 | INDEX RANGE SCAN | IE2_PRECO_ITEM | 1 | | | 1 (0)|
| 17 | INDEX UNIQUE SCAN | PK_PRODUTO | 1 | 6 | | 1 (0)|
—————————————————————————————————

SELECT distinct
p.COD_PRODUTO_INTERNO,
t.COD_FILIAL,
t.DAT_HORA_TABELA,
t.COD_SKU_INTERNO,
t.VAL_PRECO_TABELA,
t.COD_TIPO_ANUNCIO,
t.VAL_PRECO_DE,
t.IND_PRECO_DE_LOJA,
t.PCT_JUROS_SECUNDARIO,
t.VAL_PRECO_LOJA,
t.VAL_DESCONTO_A_VISTA,
t.PCT_DESCONTO_A_VISTA,
t.COD_SKU_BRINDE,
t.QTD_SKU_BRINDE,
t.QTD_PARCELA_EXIBICAO,
t.PCT_JUROS_EXIBICAO,
t.VAL_PARCELA_EXIBICAO,
t.VAL_DESCONTO_PARCELAMENTO,
t.PCT_DESCONTO_PARCELAMENTO,
t.VAL_PRECO_A_VISTA,
t.VAL_DESCONTO_BOLETO,
t.PCT_DESCONTO_BOLETO
FROM
cev.TB_PRECO_ITEM t , cev.TB_SKU sku, cev.TB_PRODUTO p
where t.COD_SKU_INTERNO = sku.COD_SKU_INTERNO
and sku.COD_PRODUTO_INTERNO = p.COD_PRODUTO_INTERNO
and sku.COD_CLASSE IN (‘A’, ‘B’, ‘I’, ‘L’)
AND (t.COD_SKU_INTERNO, t.DAT_HORA_TABELA) IN (SELECT COD_SKU_INTERNO, MAX(DAT_HORA_TABELA)
FROM cev.TB_PRECO_ITEM
GROUP BY COD_SKU_INTERNO)

O mais extrãno é que na hora de criar a mview fica muito lento,
mas se eu fizer um create table as select é bem rápido… já viram algo parecido??

[]s

[David SiqueiraParticipante]

Alguns pontos que eu olharia :
– Estatisticas de tabelas e indices
– Fragmentação de Indices
– Escrita SQL
– Utilização de Hints

Eu testaria essas possibilidades pra ver se conseguiria algum ganho, recentemente tive uma query muito besta , era apenas um SUM em um campo simples, que estava com 3000 de custo, fui olhando e mexendo e consegui baixa-la para 8 de custo.
Bem é isso.

Espero que ajude
Abcs.

David

[vieriParticipante]

O problema é que se eu criar a tabela
na mão com um create table as select(mesmo select da mview),a table é criada
em 7seg, se eu ficar um truncate+insert
roda em 8seg,

mas quando crio a mview, ou peço um dbms_mview.refresh

ele leva 2 horas!!

E habilitando um trace após 30min de execução,
ele fica paradão no parse do SQL…

…

STAT #16 id=1 cnt=0 pid=0 pos=1 obj=0 op=’FOR UPDATE (cr=1 pr=0 pw=0 time=17 us)’
STAT #16 id=2 cnt=0 pid=1 pos=1 obj=165 op=’TABLE ACCESS CLUSTER MLOG$ (cr=1 pr=0 pw=0 time=12 us)’

STAT #16 id=3 cnt=0 pid=2 pos=1 obj=164 op=’INDEX UNIQUE SCAN I_MLOG# (cr=1 pr=0 pw=0 time=7 us)’

PARSING IN CURSOR #17 len=312 dep=1 uid=0 oct=6 lid=0 tim=1213200456455966 hv=2958118544 ad=’7d8b6650′
update /*+ BYPASS_UJVC */ ( select s.status status from snap$ s, snap_reftime$ r where s.sowner = r.sowner and s.vname = r.vname and r.mowner = :1 and r.master = :2 and s.mlink IS NULL and bitand(s.status,16) = 0 and r.instsite =0 and s.instsite =0) v set status = status + 16
END OF STMT
PARSE #17:c=0,e=179,p=0,cr=0,cu=2,mis=1,r=0,dep=1,og=4,tim=1213200456455964
EXEC #17:c=2000,e=1578,p=0,cr=5,cu=0,mis=1,r=0,dep=1,og=4,tim=1213200456457580
STAT #17 id=1 cnt=0 pid=0 pos=1 obj=0 op=’UPDATE SNAP$ (cr=5 pr=0 pw=0 time=232 us)’
STAT #17 id=2 cnt=0 pid=1 pos=1 obj=220 op=’TABLE ACCESS BY INDEX ROWID SNAP_REFTIME$ (cr=5 pr=0 pw=0 time=223 us)’
STAT #17 id=3 cnt=15 pid=2 pos=1 obj=0 op=’NESTED LOOPS (cr=4 pr=0 pw=0 time=1017 us)’
STAT #17 id=4 cnt=7 pid=3 pos=1 obj=212 op=’TABLE ACCESS BY INDEX ROWID SNAP$ (cr=2 pr=0 pw=0 time=158 us)’
STAT #17 id=5 cnt=7 pid=4 pos=1 obj=218 op=’INDEX SKIP SCAN I_SNAP2 (cr=1 pr=0 pw=0 time=125 us)’
STAT #17 id=6 cnt=7 pid=3 pos=2 obj=221 op=’INDEX RANGE SCAN I_SNAP_REFTIME1 (cr=2 pr=0 pw=0 time=47 us)’
STAT #21 id=1 cnt=0 pid=0 pos=1 obj=0 op=’DELETE MV_PRECO_ITEM_PRODUTO_TESTE (cr=3 pr=0 pw=0 time=26 us)’

STAT #21 id=2 cnt=0 pid=1 pos=1 obj=158435 op=’MAT_VIEW ACCESS FULL MV_PRECO_ITEM_PRODUTO_TESTE (cr=3 pr=0 pw=0 time=21 us)’

PARSING IN CURSOR #21 len=1009 dep=1 uid=67 oct=2 lid=67 tim=1213200456467854 hv=336269635 ad=’165e8210′
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO “CEV”.”MV_PRECO_ITEM_PRODUTO_TESTE” SELECT distinct
p.COD_PRODUTO_INTERNO,
t.COD_FILIAL,
t.DAT_HORA_TABELA,
t.COD_SKU_INTERNO,
t.VAL_PRECO_TABELA,
t.COD_TIPO_ANUNCIO,
t.VAL_PRECO_DE,
t.IND_PRECO_DE_LOJA,
t.PCT_JUROS_SECUNDARIO,
t.VAL_PRECO_LOJA,
t.VAL_DESCONTO_A_VISTA,
t.PCT_DESCONTO_A_VISTA,
t.COD_SKU_BRINDE,
t.QTD_SKU_BRINDE,
t.QTD_PARCELA_EXIBICAO,
t.PCT_JUROS_EXIBICAO,
t.VAL_PARCELA_EXIBICAO,
t.VAL_DESCONTO_PARCELAMENTO,
t.PCT_DESCONTO_PARCELAMENTO,
t.VAL_PRECO_A_VISTA,
t.VAL_DESCONTO_BOLETO,
t.PCT_DESCONTO_BOLETO
FROM
cev.TB_PRECO_ITEM t , cev.TB_SKU sku, cev.TB_PRODUTO p
where sku.COD_SKU_INTERNO = t.COD_SKU_INTERNO
and p.COD_PRODUTO_INTERNO = sku.COD_PRODUTO_INTERNO
and sku.COD_CLASSE IN (‘A’, ‘B’, ‘I’, ‘L’)
AND (t.COD_SKU_INTERNO, t.DAT_HORA_TABELA) IN (SELECT COD_SKU_INTERNO, MAX(DAT_HORA_TABELA)
FROM cev.TB_PRECO_ITEM
GROUP BY COD_SKU_INTERNO)
END OF STMT
PARSE #21:c=9998,e=10061,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1213200456467849

repare o time no parse…
o refresh fica paradão ai..

isso ta me cheirando a BUG de mview, ou limitação não tratada
com exceção, ficando em loop…

oque acham?

[vieriParticipante]

reescrevemos a query de outra maneira e funcionou
, fez o refresh em 30seg, mas continuo achando que tem bug
ai!!

[]s

[David SiqueiraParticipante]

Boa Vieri, é como eu disse a esrita sempre traz grandes problemas, vou dar um exemplo que eu peguei, havia uma query que estava com o custo alto só porque o campo de datas estava do lado esquerdo da query, inverti as posições dos campos e valores e a query foi praticamente instantanea, é algo que muitas vezes parece mentira..rss..e soa como BUG também, mais vamo que vamo, parebéns pelo trabalho. Se eu achar algo relacionado posto aqui pra ti.

ABraço.

David

[Rodrigo AlmeidaParticipante]

Vieri,

Uma dúvida, esse banco que tu está mexendo, é uma versão 9i?

Até o Release 9.2.0.5, tem BUG sim na tabela snap$ do dicionário. No release 9.2.0.8 está corrigido isso.

Agora, umas dicas de MV, poderia ver o seguinte:

• Estatísticas das tabelas do SELECT.
• Custo do SELECT
• As opções que está construindo a MV.
• Se está com a opção QUERY REWRITE habilitada na instância.
• Tente fazer um FAST COMPLETE.
• Tente recriar o grupo de atualização do DBMS_REFRESH.
• VEja se a MV está atualizando por ROWID ou PRIMARY KEY.

E depois vai nos passando a informação, MV realmente é sempre problemática.

Abraços,

Rodrigo Almeida

[vieriParticipante]

Fala rodrigo !

Não! O servidor aqui é 10.2.0.3… e é um ambiente bem estável do ponto de
vista de banco, o servidor de aplicação WEB LOG INTEGRATION(alguêm aqui o conhece) ?,
que gera comportamentos muito extranos no Oracle.. em fim mas isso é papoooo pra muito chop e/ou outro post.. rsrs

Quanto o problema da MV,
Apenas reescrevi a query com o in
dentro do join, fazendo que ela trabalhe com uma
quantidade de linhas já filtradas no inicio da query…
removi alguns índice, criei outros, trab em cima do plano,
e ai o refresh ficou legal!

Esse é o script padrão das mviews deste ambiente,
na outra empresa que trabalhei era um pouco diferente,
em um outro ambiente aqui da mesma empresa tb é diferente, parece que da maneira que da certo no inicio eles continuam sempre
da mesma maneira, não há uma reciclagem das nescessidades…

CREATE MATERIALIZED VIEW “xxx”.”xxxx”
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “xxxx”
BUILD DEFERRED
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT ………

[]s e até o próximo desafio.. rsrsrs… esses ultimos meses to foda!! 8)

[vieriParticipante]

to foda não… digitei errado.. tá foda de problemas !!

[]s
vieri

[Rodrigo AlmeidaParticipante]

Fala Vieri,

aheaheahehaeh… a última frase foi de duplo sentido!!!! =D

Cara, eu particularmente, acho um **SACO** trabalhar com MV, ou até mesmo no Advanced Repication, mas, regras de tuning para MV, são várias, e o aconselhável mesmo é partir para uma re-escrita do SQL.

Tem que atualização de MV por PRIMARY KEY ou ROWID faz muita diferença. Mas é isso ae. parabéns.

Abraços,

Rodrigo Almeida

[Autor]

Posts