Melhoria Performance

[Inácio G. Ribeiro 0]

Opa, fala pessoal... Estou tentando melhorar a performance de um SELECT porém não estou conseguindo, comecei tentando eliminar os TABLE ACESS FULL, usando hints para forçar o uso de alguns índices, porém não adiantou muito, já criei todos os índices possíveis e também não tive ganhos significativos, alguém tem alguma sugestão?

 

Segue o SELECT original, sem modificações...

 

SELECT
  PREST.ROWID ROW_ID,
  PREST.PREST_COD,
  PREST.PREST_COD_UNI UNIPR_COD_UNIMED,
  PREST.PREST_COD_PESSOA,
  PREST.TIPPR_COD,
  PREST.PREST_DAT_INIC_SERVIC,
  PREST.PREST_DAT_EXCL,
  0 PREST_IND_RECEBE_PLANT_UTI_HR,
  NVL(P1.PRCFG_VAL, 0) AS CD_CLASSIFICADO,
  NVL(P2.PRCFG_VAL, 'N') AS FG_REALIZA_CIRUR_VIDEO,
  NVL(P3.PRCFG_VAL, '0') AS FG_RECEBE_MOVIMENTO,
  PREST.PREST_TIP_ATO,
  NVL((SELECT 'S'
        FROM PARAM_VALOR PV
       WHERE TRIM(PRVAL_DES_VAL) = PREST.PREST_COD
         AND TRIM(PV.PARSI_COD) = 'FX_IND_HOSPITAL_ALTO_CUSTO'),
      'N') AS FG_HOSP_ALTO_CUSTO,
  (SELECT SUBSTR(PES.PES_NOM_COMP, 1, 50)
     FROM PESSOA PES
    WHERE PES.PES_COD = PREST.PREST_COD_PESSOA) NOM_PREST,
  NVL(TO_NUMBER(F_BUSCA_DOC_PESSOA(PREST.PREST_COD_PESSOA, 'CNPJ')), 0) CNPJ_PREST,
  NVL((SELECT 'S'
        FROM PARAM_VALOR PV
       WHERE TRIM(PRVAL_DES_VAL) = PREST.PREST_COD
         AND TRIM(PV.PARSI_COD) = 'FX_IND_PREST_COM_ERRO_133'),
      'N') AS FG_REALIZAR_CONFER, 
  DECODE(PREST.PREST_COD_CGC_CPF_R402,
         NULL,
         DECODE(F_BUSCA_END_PESSOA((F_BUSCA_COD_PESSOA_UNIMED(PREST.PREST_COD_UNI)),
                                   2,
                                   'UF'),
                F_BUSCA_UF_UTLZDA('V_SAPIA_PREST'),

                DECODE(NVL(TRIM(F_BUSCA_VALOR_PARAM_2('CF_TODAS_UNIMEDS_SC_UTILIZAM_A400',
                                                      1)),
                           'N'),
                       'N',
                       DECODE(F_EXISTE_VALOR_PARAM_FIXO('FX_IND_UNIMED_ENVIA_A400',
                                                        PREST.PREST_COD_UNI),
                              'N',
                              0,
                              1),
                       1),
                0),
         0) FG_PREST_EXTERN_NAO_CADAST, 
  NVL(PREST_COD_EXTERN_UNIMED_ORIGEM, 0) PREST_COD_EXTERN_UNIMED_ORIGEM, 
  NVL(PREST_COD_CGC_CPF_R402, 0) PREST_COD_CGC_CPF_R402,
  (SELECT C.CIDAD_COD
     FROM PESSOA_END PE, PESSOA_END_FINALD PF, CEP C
    WHERE PE.PES_COD = PREST.PREST_COD_PESSOA
      AND PE.PES_COD = PF.PES_COD
      AND PE.END_IND = PF.END_IND
      AND PF.ENDF_IND = 'C '
      AND PE.CEP_COD = C.CEP_COD) CD_CIDADE 
   FROM PREST PREST,
        (SELECT DECODE(TRIM(PRCFG_VAL), 'S', 1, 0) PRCFG_VAL, PREST_COD
           FROM PREST_CFG
          WHERE PARSI_COD = RPAD('PREST_IND_CLA', 45, ' ')) P1,
        (SELECT TRIM(PRCFG_VAL) PRCFG_VAL, PREST_COD
           FROM PREST_CFG
          WHERE PARSI_COD = RPAD('PRES_IND_REAL_CIRURG_VÍDEO', 45, ' ')) P2,
        (SELECT DECODE(TRIM(PRCFG_VAL), '1', 1, 2) PRCFG_VAL, PREST_COD
           FROM PREST_CFG
          WHERE PARSI_COD = RPAD('PREST_IND_MVTO', 45, ' ')) P3 
  WHERE PREST.PREST_COD = P1.PREST_COD(+) 
    AND PREST.PREST_COD = P2.PREST_COD(+) 
    AND PREST.PREST_COD = P3.PREST_COD(+) 

 

Desde já, obrigado! :D/>

[Prog 183]

Quanto é "muito lento" aproximadamente?

Com que periodicidade você precisa executar este SELECT?

São dados criticos? Digo, são alterados o tempo todo?

[Motta 645]

Fica difícil analisar uma query deste tamanho mas :

 

subselects como coluna devem ser usados com cuidado

subselects como tabela idem

 

Os subs das tabela p1/2/3 me parecem que poderiam ser substituidos

pela propria tabela (com um where) e as condições

das colunas tratadas por CASE, talvez melhore

[Inácio G. Ribeiro 0]

Quanto é "muito lento" aproximadamente?

Com que periodicidade você precisa executar este SELECT?

São dados criticos? Digo, são alterados o tempo todo?

 

Já chegou a levar 10 minutos, em média leva de 5 até 7 minutos...

 

Não são alterados com frequência, é um select que roda apenas para carregar os dados ao inicializar o sistema, só é carregado novamente quando fechar a sessão. Mesmo que sofra alterações só será carregado em uma nova sessão.

 

Fica difícil analisar uma query deste tamanho mas :

 

subselects como coluna devem ser usados com cuidado

subselects como tabela idem

 

Os subs das tabela p1/2/3 me parecem que poderiam ser substituidos

pela propria tabela (com um where) e as condições

das colunas tratadas por CASE, talvez melhore

 

 

Tentei utilizar uma lógica diferente no caso das tabelas, porém fica mais lento com as tabelas e as condições... pois vou ter de usar mais subselects...

 

No caso dos subselects tentei substituir por functions, mas ficou a mesma coisa :(

[Motta 645]

Algo assim ...

 

SELECT
  ...
  NVL((case when PARSI_COD = RPAD('PREST_IND_CLA', 45, ' ') then P1.PRCFG_VAL else 0 end),0) AS CD_CLASSIFICADO,

  ...
FROM PREST PREST,PREST_CFG
  WHERE PREST.PREST_COD = PREST_CFG.PREST_COD(+) 
    AND (PARSI_COD = RPAD('PREST_IND_CLA', 45, ' ') or
         PARSI_COD = RPAD('PRES_IND_REAL_CIRURG_VÍDEO', 45, ' ') or
         PARSI_COD = RPAD('PREST_IND_MVTO', 45, ' '))

 

Com demais ajustes , não fica + rápido ?

[Prog 183]

Eu vou sugerir que crie uma view materializada, mas isto apenas faz sentido quando os dados não se alteram com tanta frequência.

Pode até ser uma view materializada de parte deste SELECT, aí você tem que avaliar.

 

Você já avaliou os custos com um explain? Esses outer joins são realmente necessários?

Quantas linhas de resultado esse SELECT gera como resultado?

[Inácio G. Ribeiro 0]

Algo assim ...

 

SELECT
  ...
  NVL((case when PARSI_COD = RPAD('PREST_IND_CLA', 45, ' ') then P1.PRCFG_VAL else 0 end),0) AS CD_CLASSIFICADO,

  ...
FROM PREST PREST,PREST_CFG
  WHERE PREST.PREST_COD = PREST_CFG.PREST_COD(+) 
    AND (PARSI_COD = RPAD('PREST_IND_CLA', 45, ' ') or
         PARSI_COD = RPAD('PRES_IND_REAL_CIRURG_VÍDEO', 45, ' ') or
         PARSI_COD = RPAD('PREST_IND_MVTO', 45, ' '))

 

Com demais ajustes , não fica + rápido ?

 

Assim não daria certo, preciso destas informações na mesma linha...

 

NVL(P1.PRCFG_VAL, 0) AS CD_CLASSIFICADO,

NVL(P2.PRCFG_VAL, 'N') AS FG_REALIZA_CIRUR_VIDEO,

NVL(P3.PRCFG_VAL, '0') AS FG_RECEBE_MOVIMENTO,

 

Precisaria utilizar subselects...

[Motta 645]

Troque o P1 P2 P3 pelo respectivo CASE , ou seja, ao ínves de usar 3 tabelas P1 P2 P3 use só a própria PREST_CFG e trate as colunas por CASE.

[Inácio G. Ribeiro 0]

Eu vou sugerir que crie uma view materializada, mas isto apenas faz sentido quando os dados não se alteram com tanta frequência.

Pode até ser uma view materializada de parte deste SELECT, aí você tem que avaliar.

 

Você já avaliou os custos com um explain? Esses outer joins são realmente necessários?

Quantas linhas de resultado esse SELECT gera como resultado?

 

Esse SELECT é de uma view...

 

Você já avaliou os custos com um explain?

Já, porém modificações que fiz por sugestões não melhoraram a performance significativamente.

 

Esses outer joins são realmente necessários?

Sim, não consegui encontrar outra forma mais rápida de buscar estes dados. :(

 

Quantas linhas de resultado esse SELECT gera como resultado?

120.000 ~ 130.000

 

Troque o P1 P2 P3 pelo respectivo CASE , ou seja, ao ínves de usar 3 tabelas P1 P2 P3 use só a própria PREST_CFG e trate as colunas por CASE.

 

 

Vou tentar ;)

[Prog 183]

Estou falando de view materializada, não de view, são coisas diferentes no Oracle.

[Motta 645]

A criação/atualização da snapshot(view mat.) poderia ser num horário wue a máquina estivesse ociosa.

[Inácio G. Ribeiro 0]

Estou falando de view materializada, não de view, são coisas diferentes no Oracle.

 

Não conheço view materializada, irei pesquisar para ver se resolve o meu problema, obrigado pela dica ;)

[Motta 645]

Do "manuel"