Tuesday, March 20, 2007
Eficiencia do Otimizador
Na versão 10g, a Oracle aprimorou muito as decisões do otimizador baseado nas informações das estatísticas (CBO) - nem sei porque usamos essa sigla ainda, talvez vício, já que não existe mais o RBO. A capacidade de reescrever as queries para obter melhor resultado através da avaliação de histogramas, índices, etc.
Porém, ainda há situações onde o otimizador não tem muita opção e, portanto, elege um caminho não tão eficiente. Um exemplo que vi outro dia, foi uma query escrita com "OR". Na query existia dois predicados bastante efetivos e eletivos por índice, só quando executados separadamente. Quando postos juntos na mesma query o otimizador simplesmente não soube como resolver e escolheu um FILTER da primeira tabela (full tablescan) e leitura através de índice na segunda (INDEX RANGE SCAN), nada apropriado para o caso.
Gostaria de chamar atenção em um ponto aqui: a importância de conhecer seus dados. O analista conhecia seus dados e sabia que o acesso não estava adequado. Quando ele me procurou, não entendia porque o Oracle não estava trazendo os dados de forma correta. Então reescrevemos a query com UNION ALL.
A query na verdade é bastante simples e vou tentar reproduzir. Primeiro um setup dos dados - T1 e T2.
Vamos ver o que o otimizador conseguiu.
O problema desta query é quando UNIMOS, então vamos tentar um UNION ALL na mão. Um aparte: por que UNION ALL e não UNION? Porque quando usamos o UNION um DISTINCT vem de presente, portanto cuidado!
Vamos ao resultado final!
Ok muito melhor... 643 consistents gets contra quase 160 mil, notem também a mudança de plano de acesso e para finalizar uma olhada no tkprof.
Contra o query reescrita!
Porém, ainda há situações onde o otimizador não tem muita opção e, portanto, elege um caminho não tão eficiente. Um exemplo que vi outro dia, foi uma query escrita com "OR". Na query existia dois predicados bastante efetivos e eletivos por índice, só quando executados separadamente. Quando postos juntos na mesma query o otimizador simplesmente não soube como resolver e escolheu um FILTER da primeira tabela (full tablescan) e leitura através de índice na segunda (INDEX RANGE SCAN), nada apropriado para o caso.
Gostaria de chamar atenção em um ponto aqui: a importância de conhecer seus dados. O analista conhecia seus dados e sabia que o acesso não estava adequado. Quando ele me procurou, não entendia porque o Oracle não estava trazendo os dados de forma correta. Então reescrevemos a query com UNION ALL.
A query na verdade é bastante simples e vou tentar reproduzir. Primeiro um setup dos dados - T1 e T2.
Duas tabelas com certa relação, ambas indexadas e com estatísticas coletadas inclusive com histograma. A query é bem simples, precisamos de todas os nomes da T1 onde obj# esteja entre 59700 e 59800 e que exista na T2 com o dono (owner) = 'SCOTT'. Quando os predicados são executados separadamente, o otimizador sabe exatamente como escolher o caminho mais eficiente, o problema é quando UNIMOS as queries com o "OR".
ops$marcio:LX10G> create table t1 as select * from sys.obj$;
Table created.
ops$marcio:LX10G> create index it1_object_id on t1 ( obj# );
Index created.
ops$marcio:LX10G>
ops$marcio:LX10G> create table t2 as select * from dba_objects;
Table created.
ops$marcio:LX10G> create index it2_object_id on t2 ( object_id );
Index created.
ops$marcio:LX10G> create index it2_owner on t2 ( owner );
Index created.
ops$marcio:LX10G>
ops$marcio:LX10G> begin
2 dbms_stats.gather_table_stats(
3 user,
4 'T1',
5 cascade => true,
6 method_opt => 'for all indexed columns size 10'
7 );
8 dbms_stats.gather_table_stats(
9 user,
10 'T2',
11 cascade => true,
12 method_opt => 'for all indexed columns size 10'
13 );
14 end;
15 /
PL/SQL procedure successfully completed.
ops$marcio:LX10G>
ops$marcio:LX10G> select ( select count(*) from t1 ) cnt_t1,
2 ( select count(*) from t2 ) cnt_t2
3 from dual
4 /
CNT_T1 CNT_T2
------------- -------------
53094 51845
1 row selected.
Essa foi mais ou menos a "pedrinha no sapato" do meu colega analista. A primeira idéia que surgiu foi a substituição do EXISTS por IN, mas o caso não era esse porque além das tabelas possuirem quase a mesma quantidade de linhas, o otimizador tem a capacidade de rescrever queries do tipo EXISTS/IN para obter performance.
select name
from t1
where obj# between 59700 and 59800
or exists (select null
from t2
where t1.obj# = t2.object_id
and t2.owner = 'SCOTT'
)
/
Vamos ver o que o otimizador conseguiu.
A solução para o otimizador foi (1) FILTER, ou seja, (2) leia (full) a t1 e para cada linha procure na t2 (3) faça uma seleção por owner e acesso pelo índice object_id (4). Não está mal! Pensando como o Oracle, da maneira como a query está escrita, ele precisa de TODAS as linhas de T1 e não só o filtro BETWEEN (59700 e 59800), porque existe um OR, portanto ele precisa de outras linhas na condição do predicado.
ops$marcio:LX10G> select name
2 from t1
3 where obj# between 59700 and 59800
4 or exists (select null
5 from t2
6 where t1.obj# = t2.object_id
7 and t2.owner = 'SCOTT'
8 )
9 /
25 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2766739097
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2697 | 59334 | 147 (3)| 00:00:02 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 53096 | 1140K| 147 (3)| 00:00:02 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IT2_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJ#">=59700 AND "OBJ#" <=59800 OR EXISTS (SELECT /*+ */ 0 FROM "T2"
"T2" WHERE "T2"."OBJECT_ID"=:B1 AND "T2"."OWNER"='SCOTT'))
3 - filter("T2"."OWNER"='SCOTT')
4 - access("T2"."OBJECT_ID"=:B1)
E note o esforço para obter a informação. 158733 consistents gets. Um pouco pesado para 25 linhas. Uma passada de olhos no Performance Tuning Guide, na seção 11.5 Developing Efficient SQL Statements existem ótimas dicas de como escrever eficiente SQLs e uma das dicas é: diminuir o workload do Oracle trazendo o mesmo set de linhas. Então vamos reescrever a query para dar mais chances ao otimizador.
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
158733 consistent gets
0 physical reads
...
O problema desta query é quando UNIMOS, então vamos tentar um UNION ALL na mão. Um aparte: por que UNION ALL e não UNION? Porque quando usamos o UNION um DISTINCT vem de presente, portanto cuidado!
select nameAlguns alertas quando reescrever a query, se estamos fazendo um UNION ALL na mão, há que se respeitar o predicado da query inicial, ou seja, não queremos linhas duplicadas. Então basta incluir mais um predicado na segunda query limitando o que já foi feito na primeira
from t1
where obj# between 59700 and 59800
UNION ALL
select name
from t1
where exists ( select null
from t2
where t1.obj# = t2.object_id
and t2.owner = 'SCOTT'
)
and not ( obj# between 59700 and 59800 )
/
( and not ( obj# between 59700 and 59800 ) )outra lembrança importante é: se o campo no NOT tiver a mínima change de conter null, é preciso tratar, no caso seria: nvl(obj#,0) between ...
Vamos ao resultado final!
ops$marcio:LX10G> select name
2 from t1
3 where obj# between 59700 and 59800
4 UNION ALL
5 select name
6 from t1
7 where exists ( select null
8 from t2
9 where t1.obj# = t2.object_id
10 and t2.owner = 'SCOTT'
11 )
12 and not ( obj# between 59700 and 59800 )
13 /
25 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1985880467
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1103 | 35915 | 183 (99)| 00:00:03 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 | 44 | 968 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IT1_OBJECT_ID | 44 | | 2 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT SEMI | | 1059 | 34947 | 180 (3)| 00:00:03 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1058 | 11638 | 32 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IT2_OWNER | 1058 | | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | T1 | 52879 | 1136K| 147 (3)| 00:00:02 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJ#">=59700 AND "OBJ#"<=59800)
4 - access("T1"."OBJ#"="T2"."OBJECT_ID")
6 - access("T2"."OWNER"='SCOTT')
7 - filter("OBJ#"<59700 OR "OBJ#">59800)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
643 consistent gets
0 physical reads
...
Ok muito melhor... 643 consistents gets contra quase 160 mil, notem também a mudança de plano de acesso e para finalizar uma olhada no tkprof.
select name
from t1
where obj# between 59700 and 59800
or exists (select null
from t2
where t1.obj# = t2.object_id
and t2.owner = 'SCOTT'
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.24 3.16 0 158680 0 41
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.24 3.17 0 158680 0 41
Contra o query reescrita!
select name
from t1
where obj# between 59700 and 59800
UNION ALL
select name
from t1
where exists ( select null
from t2
where t1.obj# = t2.object_id
and t2.owner = 'SCOTT'
)
and not ( obj# between 59700 and 59800 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.05 0.05 0 643 0 41
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.05 0.05 0 643 0 41
Labels: Performance
