Sunday, February 19, 2006
Stored Outlines
Esta semana lidei com um problema cuja solução foi congelar o plano de execução em RBO e passá-lo a CBO. Um de nossos parceiros possui um sistema fechado (pacote), portanto, não podíamos fazer nada com relação às queries. O problema era a ordem com que o result set era gerado. O sistema usa o primeiro registro para habilitar os grupos de contas que o usuário pode ver. Quando analisamos as tabelas, obviamento o CBO usava um plano melhor, porém a ordem do result set vinha alterada e o administrador não podia ver TODAS as contas.
Solução: Stored Outlines.
Stored Outlines é, em minha opinião, uma máquina fotográfica. Ele tira fotos de um plano de execução. Mas para que serve? Eu já tinha me feito essa pergunta. Pensei que nunca iria usar essa feature do Oracle. Para que vou congelar um plano de execução? Mas... eu estava enganado. Melhor! Resolvemos o problema. Nas próximas semanas, o sistema será testado em ambiente de aceite (UAT) e muito provavelmente será implementado em produção.
Isso me ensinou uma lição - nunca é uma palavra que nunca deve ser usada :-)
Demonstrando!
Primeiro vamos preparar uma tabela para exemplo. Sabemos que se um índice cobre 25% da tabela, ele não tem seletividade boa, portanto, não deveria ser considerado pelo otimizador, certo? Sim, se o otimizador for o CBO, caso contrário o RBO pensa: tenho índice, oba! Tenho a solução, vou pelo índice. Então, a tabela abaixo tem 100 mil registros dividos em 25 mil para o campo id (10, 20, 30 e 40). Vou criar um índice nesse campo e veremos os planos.
Ok, vamos analizar agora os planos de execução para RBO e CBO.
SQL> create table t as
2 select case mod(rownum,4)
3 when 0 then 10
4 when 1 then 20
5 when 2 then 30
6 when 3 then 40 end id,
7 rpad('x', 100, 'x') pad
8 from dual connect by level <= 100000 9 / Table created. SQL>
SQL> create index t_idx on t(id);
Index created.
SQL> set autotrace traceonly explain
SQL>
SQL> select * from t where id = 10
2 /
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | T_IDX |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=10)
Note
-----
- rule based optimizer used (consider using cbo)
SQL>
SQL> set autotrace off
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(
3 user, 'T',
4 cascade => true,
5 method_opt => 'for all indexed columns'
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select * from t where id = 10;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26096 | 1401K| 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 26096 | 1401K| 344 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=10)
SQL>
SQL> set autotrace off
SQL>
SQL> exec dbms_stats.delete_table_stats( user, 't' )
PL/SQL procedure successfully completed.
Como demonstrado acima, com o RBO (sem estatísticas) o otimizador escolheu o índice e com o CBO full table scan. A migração do RBO para o CBO é um dos exemplos para o uso do Outlines. Às vezes é necessário congelar um plano do RBO para que seja usado no CBO. Então vamos tirar um foto do plano de execução para a query "select * from t where id = 10"
Seguindo o exemplo, vamos criar o stored outlines.
SQL> create or replace outline rbo_plan
2 for category rbo_plan on
3 select * from t where id = 10;
Outline created.
Pronto, temos a fotogratia. Agora vamos converter a CBO coletando estatísticas, depois vamos usar o explain plan para verificar o plano da primeira query após a coleta. Será Full table scan, porque para que possamos usar a stored outlines, necessitamos alterar a sessão e indicar ao otimizador que existe a foto.
SQL> begin
2 dbms_stats.gather_table_stats(
3 user, 'T',
4 cascade => true,
5 method_opt => 'for all indexed columns'
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where id = 10;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25092 | 1347K| 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 25092 | 1347K| 344 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=10)
SQL>
Acima CBO com full table scan. Abaixo, vamos alterar a sessão para o uso da stored outlines.
SQL> alter session set use_stored_outlines = rbo_plan;
Session altered.
SQL> select * from t where id = 10;
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25092 | 1347K| 1574 (1)| 00:00:19 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 25092 | 1347K| 1574 (1)| 00:00:19 |
|* 2 | INDEX RANGE SCAN | T_IDX | 25092 | | 51 (2)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=10)
Note
-----
- outline "RBO_PLAN" used for this statement
Então, da próxima vez que eu vir uma feature nova, vou pensar mais para dizer que NUNCA usarei.
Labels: how to
