Veja como a rede pode impactar na Performance do Banco

maio 15, 2016 V. J.A. SQL*Net

Existem situações em que os usuários de um sistema reclamam do tempo de resposta de um determinado relatório e ao analisarmos no banco as consultas que geram esse relatório, verificamos que o tempo de reposta do banco esta muito abaixo do tempo informado pelo usuário. A pergunta que surge imediatamente é: Onde esta essa diferença de tempo? a resposta que nos vem a mente é: no tempo de rede.

Mas se analisarmos os gráficos de monitoramento da rede, na maioria da vezes, descobrimos que o tráfego de rede esta baixo e aparentemente ficamos sem resposta para a nossa pergunta.

Neste artigo vamos demonstrar de forma prática como a transferência de dados pela rede pode impactar o tempo de resposta dos usuários mesmo quando o tráfego de rede esta baixo e como podemos resolver este problema.

Conceito de transporte de dados pela rede

A transferência de dados entre dois locais em uma rede são realizadas de forma fragmentada através de pacotes que trafegam de uma localidade para outra. No entanto, os pacotes devem passar através de roteadores que direcionam os pacotes para seus destinos finais. O tempo gasto na passagem pelos roteadores é geralmente muito maior do que o tempo gasto na viagem entre dois pontos do cabo de rede. Além disso, se houver uma demanda maior de pacotes do que a capacidade de processamento imediata do roteador, os pacotes serão enfileirados. Em casos extremos, pode haver perda de pacotes, pois a fila excedeu a capacidade de armazenamento na memoria do roteador e nesses casos os pacotes precisam ser reenviados.

Consequentemente, se o número de pacotes enviados através da rede é excessivo, o roteador vai começar a enfileirar esses pacotes e a latência da rede (tempo necessário para enviar um pacote da origem para o destino) vai aumentar. Podemos chamar essa situação de contenção de rede. O número máximo de pacotes que podem ser enviados de um ponto a outro da rede é conhecido como largura de banda da rede.

Tomando como base tudo que foi dito até agora, fica claro que, para reduzir a contenção de rede temos duas opções:

1) Aumentar a largura de banda da rede;

2) Reduzir o número de pacotes enviados através da rede.

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

https://dbtimewizard.com.br/mentoria

O aumento da largura de banda da rede é um tema complexo, mas reduzir o numero de pacotes que trafegam na rede pode ser realizado de forma simples e vamos ver neste artigo como podemos fazer isso e qual será o ganho de performance.

Simulação do envio de dados pela rede

Para realizar a simulação de uma aplicação solicitando e recebendo informações de uma Banco de dados Oracle, vamos utilizar um programa Java bem simples, mas esse conceito pode ser aplicado em outras linguagens. Esse programa vai enviar uma consulta a partir de uma estação Windows para um banco de dados Oracle 11.2.0.4 em um servidor Linux, utilizando um “Array size” padrão da linguagem Java que são 10 linhas para cada pacote, em seguida vamos alterar no programa o “Array size” para 100 linhas e analisar o resultados em termos de performance.

Programa Java utilizado no simulação

A seguir temos o código fonte do programa Java que vamos utilizar na nossa simulação, na linha 39 temos a instrução “stmt.setFetchSize(100);” que determina a quantidade de linhas que o banco de dados vai enviar em cada pacote pela rede (Arraysize). Na primeira execução esta linha vai ficar como comentário e o Java vai utilizar o padrão “FetchSize” que são 10 linhas. Na segunda execução vamos utilizar a instrução e o “FetchSize” será definido como 100.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

import java.sql.*;
import java.util.*;
import java.text.SimpleDateFormat;

public class OracleJDBC {

private java.util.Date startingDate = null;
private java.util.Date endingDate = null;

public static void main(String[] argv) {

System.out.println("-------- Oracle JDBC Query Test ------------");
Connection connection = null;
Calendar sd = new GregorianCalendar();
Calendar ed = new GregorianCalendar();

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e) {

System.out.println("Where is your Oracle JDBC Driver?");
e.printStackTrace();
return;

}

System.out.println("Oracle JDBC Driver Registered!");

try {

connection = DriverManager.getConnection("jdbc:oracle:thin:@odbsrv11:1521:LAB11","appuser","oracle2015");

Statement stmt = connection.createStatement();

/* stmt.setFetchSize(100); */

String alterQuery = "ALTER SESSION SET statistics_level=ALL";

int result = stmt.executeUpdate(alterQuery);

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
java.util.Date date = new java.util.Date();
System.out.println("Envio da consulta ao banco Oracle: "+dateFormat.format(date));
sd.setTime(date);

ResultSet rset = stmt.executeQuery("select /* dbtw001 */ OBJECT_NAME, OBJECT_TYPE, OWNER, STATUS from DBTW_OBJECTS");
while (rset.next()) {
System.out.println ("OBJECT_NAME: "+rset.getString("OBJECT_NAME")+" OBJECT_TYPE: "+rset.getString("OBJECT_TYPE")+" OWNER: "+rset.getString("OWNER")+" STATUS: "+rset.getString("STATUS"));
}

date = new java.util.Date();
ed.setTime(date);
int resultm = ed.get(Calendar.MINUTE) - sd.get(Calendar.MINUTE);
int results = ed.get(Calendar.SECOND) - sd.get(Calendar.SECOND);
if (results < 0) {
results = results + 60;
}
if (resultm < 0) {
resultm = resultm + 60;
}
System.out.println("Fim do retorno de dados do banco Oracle: "+dateFormat.format(date));
System.out.println("Tempo de duração em MM:SS: "+resultm+":"+results);

stmt.close();

} catch (SQLException e) {

System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;

}

if (connection != null){
System.out.println("End of program");
} else {
System.out.println("Failed to make connection!");
}

}

}

Primeira execução do programa

Na primeira execução do programa como já foi dito será utilizado um FetchSize=10, a seguir podemos verificar as ultimas linhas da console de saída das mensagens do programa:

1
2
3
4

OBJECT_NAME: DESENV_HCL OBJECT_TYPE: DATABASE LINK OWNER: USERTST STATUS: VALID
Fim do retorno de dados do banco Oracle: 2016/05/15 23:00:35
Tempo de duração em MM:SS: 1:14
End of program

Como podemos constatar na penúltima linha, o tempo necessários para transferir todas as linhas solicitadas pelo programa foi 1minuto e 14 segundos.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
2 FROM v$sql
3 WHERE sql_text LIKE '%dbtw001%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
7xjhqvcz6b8y8 0

1 row selected.

SQL >
SQL > SELECT *
2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7xjhqvcz6b8y8, child number 0
-------------------------------------
select /* dbtw001 */ OBJECT_NAME, OBJECT_TYPE, OWNER, STATUS from
DBTW_OBJECTS

Plan hash value: 3768306837

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 476 (100)| | 120K|00:00:00.13 | 13600 |
| 1 | TABLE ACCESS FULL| DBTW_OBJECTS | 1 | 115K| 10M| 476 (1)| 00:00:06 | 120K|00:00:00.13 | 13600 |
----------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

Observando o plano de execução extraído da base, verificamos que a consulta foi executada em 13 centésimos de segundo e foram lidos 13600 Buffers.

Segunda execução do programa

Agora vamos executar o programa Java com a instrução “stmt.setFetchSize(100);”, ou seja o banco de dados vai devolver pela rede para o programa Java 100 linhas em cada pacote, dessa forma conseguiremos reduzir a quantidade de pacotes necessários para transportar os dados e consequentemente vamos ter uma redução no tempo total dessa tarefa:

1
2
3
4

OBJECT_NAME: DESENV_HCL OBJECT_TYPE: DATABASE LINK OWNER: USERTST STATUS: VALID
Fim do retorno de dados do banco Oracle: 2016/05/15 23:03:45
Tempo de duração em MM:SS: 0:31
End of program

Agora podemos verificar na penúltima linha da console de mensagens que o tempo total necessário para transferir as 120 mil linhas do banco para o programa Java caiu para 31 segundos.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
2 FROM v$sql
3 WHERE sql_text LIKE '%dbtw002%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
b3n5wmj3cjucf 0

1 row selected.

SQL >
SQL > SELECT *
2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b3n5wmj3cjucf, child number 0
-------------------------------------
select /* dbtw002 */ OBJECT_NAME, OBJECT_TYPE, OWNER, STATUS from
DBTW_OBJECTS

Plan hash value: 3768306837

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 476 (100)| | 120K|00:00:00.05 | 2904 |
| 1 | TABLE ACCESS FULL| DBTW_OBJECTS | 1 | 115K| 10M| 476 (1)| 00:00:06 | 120K|00:00:00.05 | 2904 |
----------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

No plano de execução verificamos que o tempo de execução da consulta no banco foi 5 centésimos de segundo e a quantidade de Buffers lidos caiu para 2.904.

Para reproduzir essa simulação utilizando outras linguagens, verifique no quadro abaixo como alterar o “ArraySize” de algumas linguagens mais utilizadas no mercado:

Conclusão

Sempre que estamos realizando uma trabalho de ajuste de performance precisamos ter em mente que existem vários componentes envolvidos nesse processo e o banco de dados é apenas um desses componentes, neste artigo mostramos que uma consulta com um tempo de execução excelente no banco de dados não estava atendendo as necessidades do usuário pois o tempo de resposta da rede não era satisfatório. Existem outros componentes alem da rede e do banco que podem impactar o tempo de resposta do usuário, mas isso é assunto para outro artigo.