Menu de navegação

[vieriParticipante]

Galera,
estou penando para melhorar a consulta de uma desenvolvedora.

A consulta é a seguinte .

SELECT distinct ‘Cadastramento’ as registro,
b.nu_remessa,
a.nu_car_informado,
a.id_motivo_rejeicao,
COUNT (a.nu_car_informado) as quantidade_registros
FROM sdj.leg_contas_correntes a,
sdj.leg_remessas_cc b ,
sdc.vh0007@DBL_SDC_CON_PDC c
WHERE a.nu_car_informado = c.nu_car AND
c.dt_fim_vali=99999999 AND
a.id_motivo_rejeicao = 65 AND
a.cs_validade =1 AND
a.cs_situacao_conta = ‘ATV’ AND
(a.id_conta_corrente_leg = b.id_conta_corrente_leg AND
a.nu_seq_conta_corrente_leg = b.nu_seq_conta_corrente_leg )
GROUP BY a.nu_car_informado,
a.id_motivo_rejeicao,
b.nu_remessa;

este é o plano de execução da consulta :

Plan Table
—————————————————————————————————————–
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
—————————————————————————————————————–
| SELECT STATEMENT | | 1 | 55 | 17 |||
| SORT GROUP BY | | 1 | 55 | 17 |||
| NESTED LOOPS | | 1 | 55 | 13 |||
| MERGE JOIN CARTESIAN | | 1 | 32 | 12 |||
| REMOTE | | 1 | 19 | 2 |||
| BUFFER SORT | | 8K| 104K| 10 |||
| TABLE ACCESS FULL |LEG_REMESSAS_CC | 8K| 104K| 10 |||
| TABLE ACCESS BY INDEX ROWID |LEG_CONTAS_CORRENTES | 1 | 23 | 1 |||
| INDEX UNIQUE SCAN |LGCC_PK | 1 | | |||
—————————————————————————————————————–

ela está levando em media 1:hora para retornar.

Para tentar melhorar
criei o indice :

CREATE INDEX “SDJ”.”LEG_CC_IDX” ON “SDJ”.”LEG_REMESSAS_CC” (id_conta_corrente_leg,nu_seq_conta_corrente_leg, nu_remessa)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “SDJ_IDX_SEC1” ;

Para evitar o TABLE ACCESS FULL
na tabela LEG_REMESSAS_CC

conseguindo diminuir o custo consideravelmente conforme plano abaixo:

Plan Table
—————————————————————————————————————–
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
—————————————————————————————————————–
| SELECT STATEMENT | | 1 | 53 | 10 |||
| SORT GROUP BY | | 1 | 53 | 10 |||
| NESTED LOOPS | | 1 | 53 | 6 |||
| MERGE JOIN CARTESIAN | | 1 | 31 | 5 |||
| REMOTE | | 1 | 19 | 2 |||
| BUFFER SORT | | 8K| 96K| 3 |||
| INDEX FAST FULL SCAN |LEG_CC_IDX | 8K| 96K| 3 |||
| TABLE ACCESS BY INDEX ROWID |LEG_CONTAS_CORRENTES | 1 | 22 | 1 |||
| INDEX UNIQUE SCAN |LGCC_PK | 1 | | |||
—————————————————————————————————————–

12 rows selected.

Elapsed: 00:00:00.01

CUSTO TAMANHO
———- ———-
40 .187788963

porém infelizmente na hora de roda-lá mesmo com o custo estando bem abaixo, ela continua demorando um eternidade.

Ai começei a utilizar o lado negro do tunnig : HINTS

Quando eu mando a consulta “utilizar” optimizer_mode=rule
a consulta me retorna em 3 minutos(ruim + muitoooo melhor)
e o plano é o abaixo.

Plan Table
—————————————————————————————————————–
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
—————————————————————————————————————–
| SELECT STATEMENT | | | | |||
| SORT GROUP BY | | | | |||
| TABLE ACCESS BY INDEX ROWID |LEG_REMESSAS_CC | | | |||
| NESTED LOOPS | | | | |||
| NESTED LOOPS | | | | |||
| REMOTE | | | | |||
| TABLE ACCESS BY INDEX ROWID |LEG_CONTAS_CORRENTES | | | |||
| INDEX RANGE SCAN |LGCC_MTVR_FK_I | | | |||
| INDEX RANGE SCAN |LRCC_LGCC_FK_I | | | |||
—————————————————————————————————————–

12 rows selected.

Elapsed: 00:00:00.02

CUSTO TAMANHO
———- ———-

* não aparece os custos por causa do modo de otimização = “rule”

Alguem tem alguma idéia de como eu melhorar isso ai ?
Tenho consiência que a ORACLE não recomenda uso de hints
nem utilizar o otimizador por regras. Mais não é frustrante isso ?

Solicito apoio de quem puder nessa questão !
Marcio Almeida, ISHI se puderem ajudar…

Abraços para o GPO !!

[vieriParticipante]

desculpe esqueci de colocar o custo do 1° plano…

 CUSTO    TAMANHO

---

   164  .40685463

[]s

[margaridiParticipante]

Com certeza, o seu maior gargalo está no uso do BDLINK (REMOTE no EXPLAIN PLAN).

É comum ter problemas de performance ao se fazer join com tabelas remotas.

Quando possível, o ideal é garantir que a consulta remota seja executada antes de qualquer outra consulta. Podemos forçar isso incluindo-a em uma subquery, por exemplo:


WHERE nu_car_informado in
(select nu.car
from sdc.vh0007@DBL_SDC_CON_PDC c
where c.dt_fim_vali=99999999)


Não entendi o uso da clausula distinct. Ela é mesmo necessária? Tente o utilizar o select abaixo como ponto de partida:


SELECT 'Cadastramento' as registro
, b.nu_remessa
, a.id_motivo_rejeicao
, a.nu_car_informado
, COUNT(1) quantidade_registros
FROM sdj.leg_contas_correntes a
, sdj.leg_remessas_cc b
WHERE a.nu_car_informado in
(select nu.car
from sdc.vh0007@DBL_SDC_CON_PDC c
where c.dt_fim_vali=99999999)
AND a.id_motivo_rejeicao = 65
AND a.cs_validade =1
AND a.cs_situacao_conta = 'ATV'
AND b.id_conta_corrente_leg = a.id_conta_corrente_leg
AND b.nu_seq_conta_corrente_leg = a.nu_seq_conta_corrente_leg
GROUP BY b.nu_remessa
, a.id_motivo_rejeicao
, a.nu_car_informado


Dê um retorno tendo sucesso ou não …

[vieriParticipante]

Margaridi, segue plano da sua consulta abaixo, também demorou muito para executar , nem esperei o termino . : (

SQL> explain plan for
2 SELECT ‘Cadastramento’ as registro
3 , b.nu_remessa
4 , a.id_motivo_rejeicao
5 , a.nu_car_informado
, COUNT(1) quantidade_registros
6 7 FROM sdj.leg_contas_correntes a
8 , sdj.leg_remessas_cc b , sdc.vh0007@DBL_SDC_CON_PDC c
9 WHERE a.nu_car_informado in
10 (select nu_car
11 from sdc.vh0007@DBL_SDC_CON_PDC c
12 where c.dt_fim_vali=99999999)
13 AND a.id_motivo_rejeicao = 65
14 AND a.cs_validade =1
15 AND a.cs_situacao_conta = ‘ATV’
16 AND b.id_conta_corrente_leg = a.id_conta_corrente_leg
AND b.nu_seq_conta_corrente_leg = a.nu_seq_conta_corrente_leg
17 18 GROUP BY b.nu_remessa
19 , a.id_motivo_rejeicao
, a.nu_car_informado ;
20
Explained.

SQL> @plano.sql

Plan Table

| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |

| SELECT STATEMENT | | 1 | 47 | 10 |||
| SORT GROUP BY | | 1 | 47 | 10 |||
| NESTED LOOPS | | 1 | 47 | 8 |||
| MERGE JOIN CARTESIAN | | 1 | 25 | 7 |||
| MERGE JOIN CARTESIAN | | 1 | 13 | 4 |||
| REMOTE | | 1 | | 2 |||
| BUFFER SORT | | 1 | 13 | 2 |||
| VIEW |VW_NSO_1 | 1 | 13 | 2 |||
| SORT UNIQUE | | 1 | 19 | |||
| REMOTE | | 1 | 19 | 2 |||
| BUFFER SORT | | 8K| 96K| 5 |||
| INDEX FAST FULL SCAN |LEG_CC_IDX | 8K| 96K| 3 |||
| TABLE ACCESS BY INDEX ROWID |LEG_CONTAS_CORRENTES | 1 | 22 | 1 |||

| INDEX UNIQUE SCAN |LGCC_PK | 1 | | |||

17 rows selected.

 CUSTO    TAMANHO

---

    56 .187821388

Concordei com você na colocação, que os dados referentes a conultas
distribuidas deve vir 1° na query, realmente tem lógica, porque com os
dados no buffer não seria nescessário, utilizar o dblink novamente.
Evitando o wait….

tem + alguma idéia ?

[Marcio68AlmeidaParticipante]

Bom…
Colocar o select lá na clausula where gera um produto cartesiano.
Pelo que percebi, havia dois produtos cartesianos na primeira query.
Algumas considerações :
1. Trafegar o mínimo de dados possível…
Se você sabe que a coluna registro tem um valor fixo, não tem por que coloca-lo no select, o mesmo para a coluna id_motivo_rejeicao
2. Verifique se todos os relacionamentos estão OK, parece que estão faltando colunas, não conheço a vossa estrutura, mas é bom quebrar a consulta e trazer cada informação individualizada e ir juntando e vendo como a consulta reage.

Veja esta alternativa, qual o plano dela ?
SELECT a.nu_car_informado, b.nu_remessa, COUNT (a.nu_car_informado) as quantidade_registros
FROM sdj.leg_contas_correntes a,
sdj.leg_remessas_cc b ,
(Select nu_car from sdc.vh0007@DBL_SDC_CON_PDC) c
WHERE a.nu_car_informado = c.nu_car AND
c.dt_fim_vali=99999999 AND
a.id_motivo_rejeicao = 65 AND
a.cs_validade =1 AND
a.cs_situacao_conta = 'ATV' AND
(a.id_conta_corrente_leg = b.id_conta_corrente_leg AND
a.nu_seq_conta_corrente_leg = b.nu_seq_conta_corrente_leg )
GROUP BY a.nu_car_informado, b.nu_remessa;


[vieriParticipante]

Segue o plano abaixo, alterei detalhes apenas para trazer os mesmos
registros da query original.
Muito boa à prática de fitrar no próprio FROM, diminuindo o trafégo de
dados na rede.

SQL> explain plan for
2 SELECT distinct ‘Cadastramento’ as registro,
3 b.nu_remessa,
4 a.nu_car_informado,
5 a.id_motivo_rejeicao,
6 COUNT (a.nu_car_informado) as quantidade_registros
7 FROM sdj.leg_contas_correntes a,
8 sdj.leg_remessas_cc b ,
9 (Select nu_car, dt_fim_vali from sdc.vh0007@DBL_SDC_CON_PDC) c
10 WHERE a.nu_car_informado = c.nu_car AND
11 c.dt_fim_vali=99999999 AND
12 a.cs_validade =1 AND
13 a.cs_situacao_conta = ‘ATV’ AND
14 (a.id_conta_corrente_leg = b.id_conta_corrente_leg AND
a.nu_seq_conta_corrente_leg = b.nu_seq_conta_corrente_leg )
GROUP BY a.nu_car_informado,
15 16 17 a.id_motivo_rejeicao,
18 b.nu_remessa ;

Explained.

Elapsed: 00:00:00.06
SQL> @plano.sql

Plan Table

| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |

| SELECT STATEMENT | | 1 | 53 | 10 |||
| SORT GROUP BY | | 1 | 53 | 10 |||
| NESTED LOOPS | | 1 | 53 | 6 |||
| MERGE JOIN CARTESIAN | | 1 | 31 | 5 |||
| REMOTE | | 1 | 19 | 2 |||
| BUFFER SORT | | 8K| 96K| 3 |||
| INDEX FAST FULL SCAN |LEG_CC_IDX | 8K| 96K| 3 |||
| TABLE ACCESS BY INDEX ROWID |LEG_CONTAS_CORRENTES | 1 | 22 | 1 |||

| INDEX UNIQUE SCAN |LGCC_PK | 1 | | |||

12 rows selected.

Elapsed: 00:00:00.02

 CUSTO    TAMANHO

---

    40 .187788963

:/ ainda está muito lento,definitivamente joins remotos não são uma boa prática

[Marcio68AlmeidaParticipante]

Tente criar uma view materializada para resolver o problema do dblink.

[vieriParticipante]

Isso foi a 1° coisa que veio a mente…..
; )

Se alguem souber de boa apostilas de Tunning de SQL ?!!?
Colem o link ai !!!

abraçoosss

[Autor]

Posts