Desempenho: Evitando Produto Cartesiano

Evitando Produto Cartesiano

Como evitar MERGE CARTESIAN JOIN em planos de execução de consultas SQL

Para que se possa efetivamente iniciar o artigo, primeiro uma breve contextualização referente ao real significado de produto (ou plano) cartesiano. Esta condição, do ponto de vista técnico, é utilizada como alternativa de execução para consultas SQL em um banco de dados, em nosso exemplo um RDBMS Oracle, que não possuam restrições seletivas suficientes, tanto em sua cláusula WHERE, quanto na cláusula FROM, e que efetuem buscas por informações, em dois ou mais objetos (tabelas ou views), que não possuam relacionamento entre si, propiciando a formação de um produto cartesiano entre todas as linhas, de todas as tabelas envolvidas na consulta em questão. O resultado da busca sempre será o produto entre a quantidade de linhas de cada tabela mencionada na cláusula SQL.

Desta forma, imagine uma consulta SQL qualquer, buscando dados em uma tabela com 1000 registros, juntamente com uma busca a uma segunda tabela que possua 10000 registros. O retorno seriam absurdos 10 milhões de linhas. Alto custo de processamento (utilização de recursos do servidor), tempo de processamento, utilização excessiva de recursos de comunicação, agilidade, desempenho, entre muitos outros, são fatores extremamente sensíveis a este tipo de situação, que pode vir a ser origem de lentidões generalizadas, interrupções de serviço, indisponibilidades de sistemas e demais incidentes relacionados.

Abaixo um exemplo de consulta SQL sem restrições seletivas nas cláusulas WHERE ou FROM, executada em um banco Oracle:

SQL> select a.language_id, b.document_id from fnd_languages a, fnd_documents_tl b;

LANGUAGE_ID	DOCUMENT_ID
8	13649575
8	13649575
8	13649576
8	13649576
8	13649577
8	13649577
8	13649578
8	13649578
8	13649579

É possível visualizar o plano de execução utilizado pelo otimizador do ORACLE, consultando o SQL_ID, por meio de uma funcionalidade core de um pacote do RDBMS, chamada DBMS_XPLAN.DISPLAY_CURSOR. Para capturarmos o SQL_ID em execução no momento, por qualquer sessão conectada no banco, basta consultar o campo SQL_ID da view de dicionário v$session.

A seguir, o plano de execução utilizado pelo RDBMS para a consulta anterior (contendo uma junção merge join cartesian):

Id	Operation	Name	Rows	Bytes	Cost(%CPU)	Time
0	SELECT STATEMENT				8902K(100)
1	MERGE JOIN CARTESIAN		1214M	11G	8902K (2)	11:21:54
2	TABLE ACCESS FULL	FND_LANGUAGES	39	156	2  (0)	00:00:01
3	BUFFER SORT		31M	178M	8902K (2)	11:21:54
4	TABLE ACCESS FULL	FND_DOCUMENTS_TL	31M	178M	228K (2)	00:17:30

Conforme observa-se claramente, a consulta foi atendida pela utilização de uma junção cartesiana, além de acessos completos as duas tabelas desta instrução SQL. Muitos fatores podem contribuir para utilização de uma junção cartesiana como saída para resolução de buscas SQL em um banco de dados, dos quais pode-se destacar:

• A já mencionada ausência de critérios seletivos suficientes na cláusula WHERE;
• Ausência de índices de busca, que referenciem os campos utilizados para os JOINs em questão;
• Estatísticas não representando a condição atual do banco (desatualizadas).

Um fator decisivo, que não deve ser esquecido, consiste no contexto o qual as consultas estejam vinculadas, pois nem sempre será possível rever, ou alterar as cláusulas seletivas da instrução SQL, ou nem mesmo criar índices, ou até mesmo reunir estatísticas a um percentual que realmente represente o estado atual da distribuição dos dados através do banco. Portanto, o administrador do banco pode, obviamente com o correto embasamento da documentação adequada, utilizar de outros meios para alterar a forma como o otimizador de buscas do Oracle gerencia estas questões de acesso a dados não devidamente relacionados entre si.

Parâmetros envolvidos

O mecanismo de buscas do Oracle possui um otimizador parametrizável, o qual lê parâmetros durante a inicialização da instância, bem como em tempo de execução. No artigo em questão iremos abordar basicamente 2 parâmetros (não documentados) de inicialização, que alteram e muito o comportamento o RDBMS, diante de situações como a descrita acima. São eles: _optimizer_mjc_enabled e _optimizer_cartesian_enabled. Por padrão, eles não possuem valor algum (indefinido), até que o DBA os resolva utilizar:

SQL> alter system set "_optimizer_mjc_enabled"=FALSE scope=memory;

System altered.

SQL> alter system set "_optimizer_cartesian_enabled"=FALSE scope=memory;

System altered.

SQL>

A partir do momento da alteração dos 2 (dois) parâmetros em questão, para que possuam o valor FALSE, tem-se portanto, um cenário diferenciado, onde o RDBMS entende (apenas para esta instância em memória), que não deve utilizar merge join cartesian em seus planos de execução, o que automaticamente fará que o SQL seja executado através de outro SQL_ID, além da utilização de outro plano. A seguir, uma consulta SQL sem critérios seletivos, nas cláusulas WHERE ou FROM:

SQL> select a.language_id, b.document_id from fnd_languages a, fnd_documents_tl b;7

A tabela a seguir, apresenta de forma melhor estruturada, o plano de execução utilizado pelo RDBMS:

Id	Operation	Name	Rows	Bytes	Cost(%CPU)	Time
0	SELECT STATEMENT				8902K(100)
1	NESTED LOOPS		1214M	11G	8902K  (2)	11:21:54
2	TABLE ACCESS FULL	FND_LANGUAGES	39	156	2   (0)	00:00:01
3	TABLE ACCESS FULL	FND_DOCUMENTS_TL	31M	178M	228K  (2)	00:17:30

Desta forma, sem que seja adotado o plano cartesiano, mas sim NESTED LOOPS, as operações efetuadas neste caso foram duas leituras completas nas tabelas em questão (mesmas operações realizadas anteriormente, com o mesmo custo), situação que caso não seja conveniente, pode ser facilmente contornada através da criação de índices de busca.

Considerações

NESTED LOOPS, pode ser melhor compreendido como a junção que combina OUTER-JOIN com INNER-JOIN. Desta forma, para cada linha vinda da tabela “outer”, que atenda aos critérios restritivos da consulta SQL, serão recuperadas também pela consulta todas as linhas da tabela “inner”, que satisfaçam os critérios de seleção. Esta não é considerada a opção mais eficiente de junção entre tabelas extensas. Em situações diferentes das condições utilizadas para escrever este artigo, o ideal seria que o otimizador utilizasse um HASH-JOIN, todavia, para que isso fosse possível, seriam necessárias mudanças significativas na estruturação da consulta. Esta condição é altamente aplicável para consultas SQL muito extensas e que contenham inúmeras comparações entre diferentes objetos fonte de dados (tabelas ou views), como por exemplo, para emissão de relatórios corporativos.

Outro ponto de atenção extremamente relevante: Caso haja índices de busca, correspondentes as colunas que sejam consultadas pela instrução SQL, e que estejam válidos e com estatísticas que representem a situação atual dos objetos (coletadas recentemente), a consulta as tabelas seria efetuada através de seus ROW_ID – fato este que elevaria e muito a expectativa de um bom desempenho.

Conclusão

O presente artigo demonstrou de forma clara e objetiva, um exemplo de situação trivial no dia-a-dia de administradores de bancos de dados que, não possuindo autonomia sobre a modificação de critérios seletivos em instruções SQL não desenvolvidas da forma correta, ou que estejam sendo executadas em ambientes sem os devidos relacionamentos entre tabelas previamente estipulados, acabam enfrentando situações de contenção generalizada, ou até mesmo interrupções de serviços, em seus bancos de dados, produtivos ou de homologação.

Ao alterar parâmetros ocultos do gerenciador de banco de dados Oracle, é importante que fique clara a responsabilidade diante das conseqüências destas alterações, inclusive ao fato de que sua má utilização pode resultar na perda parcial ou total de suporte, em incidentes envolvendo ambientes produtivos, ou até mesmo danos financeiros extremamente sérios. Obviamente, os parâmetros alterados neste artigo, não causariam qualquer dano ao software gerenciador do banco de dados, bem como a integridade das informações armazenadas, pois alteram apenas a forma como as consultas SQL são efetivamente atendidas.