Menu de navegação

[maperesParticipante]

Bom dia a todos.

Estou com um problema na execução de uma instrução select.

Ambiente:

Oracle : 11.2.0.1
SO : Red Hat 6

Instrução SELECT :

SELECT SCCT1.CCST_SEQUENCIA,
SCCT1.SIGLA,
SCCT1.DESCRICAO,
SCCT.CCST_SEQUENCIA AS CC,
SCCT.SIGLA AS SS,
SCCT.DESCRICAO AS DD,
SOLI.ANO_SOLI,
SOLI.TSOL_COD_TIPO_SOLI,
SOLI.NRO_SOLI,
CONCATENA_PROCESSO(SOLI.ANO_SOLI,SOLI.TSOL_COD_TIPO_SOLI,SOLI.NRO_SOLI) AS SOLICITACAO,
TO_CHAR(SOLI.DAT_PREV_RESP,’DD/MM/YYYY’) AS DAT_PREV_RESP,
BAIR.NOM_BAIR,
SOLI.ASSU_COD_ASSU,
ASSU.DES_ASSU,
PESS.NOM_PESS,
PESS.NRO_CPFI,
PESS.DIG_CPFI,
PESS.NRO_RGER,
PESS.TIP_LOGR,
PESS.NOM_LOGR,
PESS.NRO_PRED,
PESS.DES_BAIR,
PESS.COD_CEPE,
CIDA.NOM_CIDA,
ESTA.SIG_ESTA,
SOLI.DES_SOLI,
TCOM.DES_COMU,
FINA.DES_FINA,
MCOM.DES_MEIO_COMU
FROM SOLICITACOES SOLI
LEFT JOIN PESSOAS_PRT PESS
ON SOLI.PSTP_COD_PESS = PESS.COD_PESS
LEFT JOIN CIDADES CIDA
ON PESS.COD_CIDA = CIDA.COD_CIDA
LEFT JOIN ESTADOS ESTA
ON CIDA.COD_ESTA = ESTA.COD_ESTA
LEFT JOIN BAIRROS BAIR
ON SOLI.BAIR_COD_BAIR = BAIR.COD_BAIR
LEFT JOIN ASSUNTOS ASSU
ON SOLI.ASSU_COD_ASSU = ASSU.COD_ASSU
LEFT JOIN SUB_CENTROS_CUSTO_PRT SCCT
ON SOLI.COD_CCUS_UPRO = SCCT.CCST_SEQUENCIA
LEFT JOIN SUB_CENTROS_CUSTO_PRT SCCT1
ON SCCT.SECRETARIA = SCCT1.SECRETARIA
LEFT JOIN TIPO_COMUNICACOES TCOM
ON SOLI.TCOM_TIP_COMU = TCOM.TIP_COMU
LEFT JOIN MEIO_COMUNICACOES MCOM
ON MCOM.TIP_COMU = TCOM.TIP_COMU
LEFT JOIN FINALIDADES FINA
ON MCOM.COD_FINA = FINA.COD_FINA
WHERE (SOLI.DTH_CADT_SOLI >= ’01/01/2009′
AND SOLI.DTH_CADT_SOLI <= '30/01/2009' AND SOLI.PSTP_TIP_PESS = '1') AND (SCCT1.DEPARTAMENTO = 0 AND SCCT1.DIVISAO = 0 AND SCCT1.SETOR = 0 ); ORDER BY CCST_SEQUENCIA ASC,CC ASC; Essa select "estoura" toda a tablespace TEMP. Qto mais aloco espaço na TEMP a mesma estoura toda. Alguém tem alguma sugestão de como melhorar essa select. Atenciosamente Marco Aurelio

[rmanParticipante]

@maperes

A TEMP está sendo consumida devido o ORDER BY, o ORDER BY neste caso é essencial? Faça um teste sem o ORDER BY.

[maperesParticipante]

Olá

Obrigado pelo seu retorno

Eu ja fiz o teste sem o order by, retornou em 3 minutos.

O problema é que o analista xarope aqui, não quer alterar a select na aplicação, mas pelo que vi, acho inviavel rodar essa select dessa maneira.

[rmanParticipante]

@maperes

Quantos registros essa consulta retorna?

[maperesParticipante]

Essa consulta retorna 7939 registros.

Você sabe se tem alguma alternativa pra carregar essa consulta (com order by) no banco, que eu possa estar ajustando.

Ja tentei varias coisas, mas até agora o mesmo comportamento;

[josenizParticipante]

3 minutos me parece uma eternidade.
Qual o valor do parâmetro sort_area_size ?

[maperesParticipante]

Eu tambem acho, o problema aqui, é que os analistas tem uma resistencia imensa em mudar algo nas sqls.

Eu apresentei essa alternativa que melhorou muito, porem acho alto o tempo.

NAME TYPE VALUE

---

sort_area_size integer 1310720

[josenizParticipante]

Sugiro que vc faça um explain da query e analise o resultado.

Mas antes disso…
Uma dúvida: o que a função CONCATENA_PROCESSO faz ???
Aumente o sort_area_size para 2M, apenas em sua sessão, remova a função concatena_processo da lista de campos no SELECT e execute a query com o ORDER BY. Vai que ela é a vilã.

[rmanParticipante]

@maperes

Sem modificar nada no SELECT é complicado…

Qual é o tamanho atual da TEMP? Talvez não vai ter como fugir de aumenta-la.

[maperesParticipante]

O tamanho atual da TEMP é de 9G.

Ela esta criada conforme abaixo :

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME TABLESPACE_NAME

---

TMP_GROUP TEMP
TMP_GROUP TEMP2
TMP_GROUP TEMP3

[rmanParticipante]

@maperes

Aqui tenho TEMP de 30 gb (3 x 10 gb).

[maperesParticipante]

Então eu tinha alocado pra TEMP antes 40G, mesmo assim a TEMP estourou.

Uma outra coisa que percebi é que a select com o order by parece estar num loop gigantesco, algumas vezes eu disparei a mesma e deixei rodando uns minutos, interrompi e quando consultei o numero de registros estava em torno de 50.000.

A consulta sem order by gera 7939 registros.

[rmanParticipante]

@maperes

Coloque uma condição obrigatório para diminuir o número de registros e mantenha o ORDER BY. 7939 registros realmente é muitos registros, se tirar 2 ou 3 relatórios baseado em um filtro vai reduzir os registros.

[Fábio PradoParticipante]

@maperes,

Como o @rman informou, a melhor forma de vc evitar o erro é tirar o ORDER BY da instrução SQL. VC tbém poderia aumentar o tamanho do TEMP para evitá-lo, mas a dica que eu dou nos meus treinamentos de SQL Tuning é realmente tirar ORDER BY do SQL e ordenar os dados na aplicação. Por experiência própria, sei que fica bem mais rápido.

[]s

Fábio Prado
http://www.fabioprado.net

[maperesParticipante]

Olá Fabio

Eu concordo copm você em genero, número e grau, o problema aqui é convencer os desenvolvedores a reverem essa select.

Não tem como fazer milagre, só o banco não aguenta.

[Autor]

Posts