Evitando Merge Join Cartesian - Oracle Database 12c

Olá pessoal, tudo bem?

Consultas SQL em bancos de dados Oracle que utilizem planos cartesianos, tendem a prejudicar o desempenho de instruções SQL.

O Merge join cartesian é muito caro para o Oracle, e é apropriado em casos raros, como por exemplo, onde uma das tabelas tem um número muito pequeno de linhas.

Consultas SQL que possuam em seus critérios seletivos busca por dados referentes a colunas pertencentes a mais de uma tabela ou views em situações em que não exista qualquer relacionamento entre as tabelas em questão, uma das formas mais comuns adotada pelo otimizador de consultas do banco de dados é executá-la por meio de uma junção cartesiana no plano de execução.

Isso significa que, para cada linha da Tabela 1, todas as linhas da Tabela 2 serão verificadas, e se necessário, retornadas ao processo solicitante da consulta. Esse cenário degrada significativamente o desempenho da consulta.

Quero deixar algumas dicas para se evitar que consultas SQL em bancos de dados Oracle utilizem planos cartesianos como parte de seus planos de execução, o que prejudica a expectativa de desempenho elevando o tempo total e os recursos utilizados de cada execução.

O plano (ou produto) cartesiano, do ponto de vista técnico, é utilizado como alternativa de execução para consultas SQL em um banco de dados 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 fontes de dados (tabelas ou views) que não possuam relacionamento entre si.

Mas como evitar: Merge join cartesian?

Existem várias razões pelas quais seu SQL fará MERGE JOIN CARTESIAN, dentre algumas podemos citar:

• Esqueceu de adicionar uma condição de associação de tabela à cláusula WHERE, (por exemplo, a.primary_key = b.foreign_key);
• Indices de join perdidos;
• Estatísticas de esquema ruim / obsoletas (reanalisar com dbms_stats);

Esteja sempre atento aos itens acima, mas, para exemplificar, vamos a um pequeno teste. Irei considerar neste teste apenas um ajuste a instrução SQL, desconsiderando parâmetros de configurações no banco de dados Oracle.

Vamos considerar em nosso teste a falta de condições de joins entre tabelas:

Vamos lá:

Primeiro criamos duas tabelas. Uma baseada na dba_objects com todas as tabelas do dicionário de dados, e outra tabela baseada na: dba_tables:

Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 

SQL> 
SQL> create table tb_obj as
2  select obj.owner, obj.object_name
3    from dba_objects obj
4   where obj.object_type = 'TABLE';

Table created

SQL> 
SQL> create table tb_table as
2  select t.owner, t.table_name from dba_tables t;

Table created

SQL> 

Veja quantos registros tem em cada uma, neste teste temos poucos registros:

SQL> select count(*) from tb_obj;

COUNT(*)
----------
11779

SQL> select count(*) from tb_table;

COUNT(*)
----------
11713

SQL> 

Certo, vamos imaginar que esquecemos algum join entre as duas tabelas acima propositalmente na montagem do select. Vamos montar um select sem relacionamento entre as tabelas.

Veja o plano de execução, repare o MERGE JOIN CARTESIAN, veja todo o custo que ele tomou para si e qual percentual de CPU utilizado:

SQL> 
SQL> explain plan for
2  select o.owner,
3         o.object_name,
4         t.table_name
5   from tb_obj o, tb_table t;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   137M|  6052M| 97432   (2)|
|   1 |  MERGE JOIN CARTESIAN|          |   137M|  6052M| 97432   (2)|
|   2 |   TABLE ACCESS FULL  | TB_TABLE | 11713 |   217K|    10   (0)|
|   3 |   BUFFER SORT        |          | 11779 |   310K| 97422   (2)|
|   4 |    TABLE ACCESS FULL | TB_OBJ   | 11779 |   310K|     8   (0)|
----------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version

14 rows selected

Ok, agora altere o select incluindo o join (o.object_name = t.table_name) correto entre as tabelas e veja o plano de execução novamente.

Repare que o MERGE JOIN CARTESIAN foi desconsiderado e está sendo realizado uma comparação hash entre as tabelas e que um Full Table Scan está sendo feito também.

Não irei entrar na questão do full table scan neste artigo, mas, veja que o custo total caiu, bem, como o percentual de utilização de CPU. Este plano está melhor que o anterior:

SQL> 
SQL> explain plan for
2  select o.owner,
3         o.object_name,
4         t.table_name
5   from tb_obj o, tb_table t
6   where o.owner = t.owner
7     and o.object_name = t.table_name;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          | 11713 |   617K|    20   (0)|
|   1 |  HASH JOIN         |          | 11713 |   617K|    20   (0)|
|   2 |   TABLE ACCESS FULL| TB_TABLE | 11713 |   308K|    10   (0)|
|   3 |   TABLE ACCESS FULL| TB_OBJ   | 11779 |   310K|    10   (0)|
--------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version

13 rows selected

SQL> 

Abraço,

Ronaldo.