Recentemente eu tive um problema com um cliente onde algumas queries usando BIND estavam fazendo plano errado. Depois de comparar o tipo de dado que o otimizador estava recebendo com o que estava na tabela verifiquei que a query estava fazendo um plano extremamente ineficiente devido a uma conversão implícita de dados.
Vamos ver o exemplo abaixo:
1) Criei uma tabela chamada t1
SQL> create table t1 (colvar varchar2(10), colnum number);
2) Vamos preencher dados de testes nessa tabela para realizar algumas queries e ver como essa conversão é perigosa em alguns casos e em outros nem tanto.
SQL> begin
2 for i in 1..1000000 loop
3 insert into t1 values (i, i);
4 end loop;
5 commit;
6* end;
/
3) Vamos coletar estatísticas dessa nova tabela para que o otimizador tenha mais informação para decidir sobre como vai resolver as queries
SQL> exec dbms_stats.gather_table_stats(user, ‘T1′, null, method_opt=>’FOR TABLE FOR ALL COLUMNS SIZE REPEAT’, cascade=>true);;
4) Criei um índice na coluna com tipo de dados varchar e também na coluna numerica, note que apenas tenho 1 ocorrência de valores para cada linha, poderia até criar uma PK nessa coluna.
SQL> create index ix1 on t1(colvar);
SQL> create index ix2 on t1(colnum);
5) Vamos primeiro checar planos de execução em cima do tipo de dados varchar;
SQL> select * from t1 where colvar=1;
Plano de Execuc?o
———————————————————-
Plan hash value: 3617692013
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 12 | 669 (6)| 00:00:09 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 12 | 669 (6)| 00:00:09 |
————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(TO_NUMBER(“COLVAR”)=1)
Estatisticas
———————————————————-
12 recursive calls
0 db block gets
2358 consistent gets
1630 physical reads
0 redo size
410 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
Obs.: Note o que o omitizador do Oracle faz, filter(TO_NUMBER(“COLVAR”)=1), por isso ele não usou o índice.
6) Agora vamos ajudar o otimizador e passar o tipo de dados correto:
SQL> select * from t1 where colvar=’1′;
Plano de Execuc?o
———————————————————-
Plan hash value: 748304776
————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 12 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX1 | 1 | | 1 (0)| 00:00:01 |
————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“COLVAR”=’1′)
Estatisticas
———————————————————-
1 recursive calls
0 db block gets
5 consistent gets
3 physical reads
0 redo size
414 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Obs.: Podemos notar que o Oracle agora decidiu por um plano muito mais tranquilo e fez muito menos esforço para entregar a única linha para o usuário.
7) Agora vamos fazer o mesmo comparativo com o tipo de dados numérico
SQL> select * from t1 where colnum=’1′;
Plano de Execuc?o
———————————————————-
Plan hash value: 3248492971
————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 12 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX2 | 1 | | 1 (0)| 00:00:01 |
————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“COLNUM”=1)
Estatisticas
———————————————————-
1 recursive calls
0 db block gets
5 consistent gets
2 physical reads
0 redo size
414 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
8) Quando passamos o tipo de correto para ajudar o otimizador o Oracle faz exatamente o mesmo plano:
SQL> select * from t1 where colnum=1;
Plano de Execuc?o
———————————————————-
Plan hash value: 3248492971
————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 12 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX2 | 1 | | 1 (0)| 00:00:01 |
————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“COLNUM”=1)
Estatisticas
———————————————————-
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Fica a lição aprendida de que temos que tomar cuidado com conversão implícita de dados, de qualquer maneira, porém, se ela foi feita em uma coluna com tipo de dados numérico o otimizador do Oracle não faz nenhuma besteira. Sabem por que? Simples, em uma coluna com tipo de dados numérico eu só posso gravar number, ao passo que em colunas de dados varchar eu posso gravar number, datas e strings.
Tomem cuidado, pois uma pequena query fazendo uma coisa errada pode significar muito tempo perdido, pois o Oracle teria que sempre ler 23Mb no caso de testes acima para me fornecer a informação de apenas 1 linha.
Att,
Mufalani