Um pouco sobre o CBO
Hoje vou propor um exercício para entendermos um pouco melhor o CBO e a importância das estatísticas.
Para começar vou criar duas tabelas com estrutura e dados iguais.
Ambas não terão PK, pois o foco aqui é lidar com índices não únicos. Neste caso em especial nas duas tabelas a coluna indexada terá sempre 15 correspondências para cada valor.
Para criá-las podemos usar duas formas, uma usando a função CEIL e outra usando a função MOD
CREATE TABLE t01 AS
SELECT TRUNC((ROWNUM-1)/15) n1, TRUNC((ROWNUM-1)/15) n2, LPAD('#',100) vc
FROM dual
CONNECT BY LEVEL <=3000;Table created.
CREATE TABLE t02 AS
SELECT MOD(ROWNUM,200) n1, MOD(ROWNUM,200) n2, LPAD('#',100) vc
FROM dual
CONNECT BY LEVEL <=3000;
Table created.Estão criadas as tabelas, cada uma com 3 mil registros e com os dados exatamente iguais
SELECT count(1)
FROM T01 t;
COUNT(1)
----------
3000
1 row selected.
SELECT count(1)
FROM T02 t;
COUNT(1)
----------
3000
1 row selected.Para comparar os dados vamos utilizar operação de conjuntos com os MULTISET OPERATORS MINUS e UNION
SELECT *
FROM t01
MINUS
SELECT *
FROM t02
UNION (SELECT *
FROM t02
MINUS
SELECT *
FROM t01);
no rows selectedIsso não prova exatamente que temos os mesmos registros nas mesmas quantidades mas se aprofundarmos a comparação veremos que isso ocorre.
Agora criemos os índices não únicos nas colunas n1 das duas tabelas. Depois coletaremos as estatísticas.
CREATE INDEX t01_i ON t01 (n1);Index created.
CREATE INDEX t02_i ON t02 (n1);Index created.
ANALYZE TABLE t01 COMPUTE STATISTICS;Table analyzed.
ANALYZE TABLE t02 COMPUTE STATISTICS;Table analyzed.
Vamos ver como ficaram:
SELECT a.table_name, a.blocks, a.num_rows, a.avg_row_len
FROM user_tables a
WHERE a.table_name LIKE 'T0%';
TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN
------------------ ---------- -----------
T01 54 3000 111
T02 54 3000 111
2 rows selected.
SELECT c.table_name, c.column_name, c.low_value, c.high_value, c.num_distinct
FROM User_Tab_Columns c
WHERE c.table_name LIKE 'T0%'
AND c.column_name = 'N1';
TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_DISTINCT
----------- ------------ ---------- ----------- ------------
T01 N1 80 C20264 200
T02 N1 80 C20264 200
2 rows selected.Podemos ver que as estatísticas estão iguais.
Agora vamos verificar o plano de execução de um simples select nestas tabelas quando passamos um valor (42, por exemplo) para a coluna n1:
EXPLAIN PLAN FOR
SELECT *
FROM T01 t
WHERE t.n1 = 42;
Explained.
SELECT *
FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 4193336401
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1590 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T01 | 15 | 1590 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T01_I | 15 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."N1"=42)Como esperado, temos um acesso por índice.
Vejamos a mesma query feita na segunda tabela.
EXPLAIN PLAN FOR
SELECT *
FROM T02 t
WHERE t.n1 = 42;
Explained.
SELECT *
FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 2967456566
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1590 | 12 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T02 | 15 | 1590 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."N1"=42)Pois é, diferentemente do que ocorreu na primeira tabela, o CBO decidiu por fazer um acesso FULL na tabela.
Repare que em ambas as situações, foram estimadas 15 linhas de retorno, mas, na primeira tabela o custo com acesso por índice foi de 2 e na segunda, o custo do acesso full foi de 12!
Por que será que o otimizador escolheu um plano mais custozo que o primeiro?
No próximo parágrafo começarei a explicar o que aconteceu, portanto se deseja tentar descobrir sozinho o mistério, pare de ler.
Vimos que as tabelas estão iguais e com as estatísticas iguais, mas não comparamos uma estatística importante: a estatística dos índices
SELECT i.table_name,
i.index_name,
i.num_rows,
i.avg_leaf_blocks_per_key,
i.avg_data_blocks_per_key,
i.clustering_factor,
i.distinct_keys
FROM user_indexes i
WHERE i.index_name LIKE 'T0_\_I' ESCAPE '\';
TABLE_NAME INDEX_NAME NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR DISTINCT_KEYS
----------- ----------- ---------- ----------------------- ----------------------- ----------------- -------------
T01 T01_I 3000 1 1 48 200
T02 T02_I 3000 1 15 3000 200
2 rows selected.Percebeu? Veja novamente as colunas AVG_DATA_BLOCKS_PER_KEY e CLUSTERING_FACTOR como se diferem bastante de uma tabela para outra.
Sabe o que estas estatísticas estão dizendo? Para entender melhor, vamos dar uma olhadinha nos registros que tentamos selecionar com uma ajudinha da pseudocoluna ROWID.
E uma ajudinha da API DBMS_ROWID, também. Por que não?
SELECT ROWID, t.n1, dbms_rowid.rowid_block_number(rowid) blk_no
FROM T01 t
WHERE t.n1 = 42;
ROWID N1 BLK_NO
------------------ ---------- ----------
AAADgDAAAAAAAD8AA2 42 252
AAADgDAAAAAAAD8AA3 42 252
AAADgDAAAAAAAD8AA4 42 252
AAADgDAAAAAAAD8AA5 42 252
AAADgDAAAAAAAD8AA6 42 252
AAADgDAAAAAAAD8AA7 42 252
AAADgDAAAAAAAD8AA8 42 252
AAADgDAAAAAAAD8AA9 42 252
AAADgDAAAAAAAD8AA+ 42 252
AAADgDAAAAAAAD8AA/ 42 252
AAADgDAAAAAAAD9AAA 42 253
AAADgDAAAAAAAD9AAB 42 253
AAADgDAAAAAAAD9AAC 42 253
AAADgDAAAAAAAD9AAD 42 253
AAADgDAAAAAAAD9AAE 42 253
15 rows selected.
SELECT ROWID, t.n1, dbms_rowid.rowid_block_number(rowid) blk_no
FROM T02 t
WHERE t.n1 = 42;
ROWID N1 BLK_NO
------------------ ---------- ----------
AAADgEAAAAAAAErAAp 42 299
AAADgEAAAAAAAEuAAy 42 302
AAADgEAAAAAAAExAA7 42 305
AAADgEAAAAAAAE1AAE 42 309
AAADgEAAAAAAAE5AAO 42 313
AAADgEAAAAAAAE8AAY 42 316
AAADgEAAAAAAAE/AAi 42 319
AAADgEAAAAAAAFCAAs 42 322
AAADgEAAAAAAAFFAA1 42 325
AAADgEAAAAAAAFJAA+ 42 329
AAADgEAAAAAAAFNAAH 42 333
AAADgEAAAAAAAFQAAR 42 336
AAADgEAAAAAAAFTAAb 42 339
AAADgEAAAAAAAFWAAl 42 342
AAADgEAAAAAAAFaAAu 42 346
15 rows selected.E aí? Sacou?
O que ocorre é o seguinte:
Na primeira tabela, as correspondências do registros 42 são encontradas em apenas dois blocos distintos (252, 253) e pleas características dos ROWID os registros encontram-se contíguos nos mesmos blocos, um ao lado do outro.
Na segunda tabela, no entanto, cada registro correspondente ao 42 está localizado em blocos distintos, ou seja, esses registros estão espalhados pelo datafile.
Isso aconteceu pela forma como cada uma das tabelas foram criadas. Experimente executar somente as consultas utilizadas nos CTAS.
Legal. Mas como isso afeta a decisão do CBO?
Pois bem, no momento de coletar as estatísticas o Oracle percebeu que na segunda tabela cada chave do índice estava muito espalhada pelos blocos e gravou essa informação no campo CLUSTERING_FACTOR. Gravou também no campo AVG_DATA_BLOCKS_PER_KEY a informação de que cada chave do índice apontava em média para 15 blocos diferentes.
Diante dessa informação o oracle percebeu que seria muito menos esforço ler todos os blocos da tabela, do que ler os blocos do índice e depois ter que localizar os outros 15 blocos para cada registro.
Você pode estar se perguntando: Por que será que ler os 54 blocos da tabela é considerado menos esforço do que ler os 16 blocos via índice?
Boa pergunta. E a resposta é a seguinte:
Para ler os 16 blocos específicos via índice o Oracle utiliza um mecanismo conhecido como DB file sequencial read. Pense nesse mecanismo como alguém que vai comprar poucos tomates e escolhe cuidadosamente cada um deles.
Para ler os 54 blocos via TABLE ACCESS FULL o Oracle utiliza outro mecanismo: o DB file scattered read. Pense nele como alguém que vai comprar 50kg de tomates e simplesmente vai botando as caixas no carrinho.
No DB file scattered read podemos acessar um número maior de blocos por vez, o que acaba resultando num menor esforço total.
Esse “número maior de blocos por vez” pode ser ajustado através do parâmetro db_file_multiblock_read_count, que pode ser alterado em nível de sessão.
Vejamos o que ocorre quando alteramos esse parâmetros e depois testamos os diferentes acessos na nossa tabela problemática.
ALTER SESSION SET db_file_multiblock_read_count = 32;
Session altered.
EXPLAIN PLAN FOR
SELECT /*+ full(t) */*
FROM T02 t
WHERE t.n1 = 42;
Explained.
SELECT *
FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 2967456566
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1590 | 12 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T02 | 15 | 1590 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."N1"=42)
13 rows selected.
EXPLAIN PLAN FOR
SELECT /*+ index(t T02_I)*/*
FROM T02 t
WHERE t.n1 = 42;
Explained.
SELECT *
FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 2254093054
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1590 | 16 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T02 | 15 | 1590 | 16 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T02_I | 15 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."N1"=42)
14 rows selected.
ALTER SESSION SET db_file_multiblock_read_count = 16;
Session altered.
EXPLAIN PLAN FOR
SELECT /*+ full(t) */*
FROM T02 t
WHERE t.n1 = 42;
Explained.
SELECT *
FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 2967456566
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1590 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T02 | 15 | 1590 | 14 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."N1"=42)
13 rows selected.
EXPLAIN PLAN FOR
SELECT /*+ index(t T02_I)*/*
FROM T02 t
WHERE t.n1 = 42;
Explained.
SELECT *
FROM TABLE(dbms_xplan.display())
;
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 2254093054
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1590 | 16 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T02 | 15 | 1590 | 16 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T02_I | 15 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."N1"=42)
14 rows selected.
ALTER SESSION SET db_file_multiblock_read_count = 4;
Session altered.
EXPLAIN PLAN FOR
SELECT /*+ full(t) */*
FROM T02 t
WHERE t.n1 = 42;
Explained.
SELECT *
FROM TABLE(dbms_xplan.display())
;
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 2967456566
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1590 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T02 | 15 | 1590 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."N1"=42)
13 rows selected.
EXPLAIN PLAN FOR
SELECT /*+ index(t T02_I)*/*
FROM T02 t
WHERE t.n1 = 42;
Explained.
SELECT *
FROM TABLE(dbms_xplan.display())
;
PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 2254093054
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 1590 | 16 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T02 | 15 | 1590 | 16 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T02_I | 15 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."N1"=42)
14 rows selected.E então? Curtiu?
Só pra ver se você entendeu, vão aí duas perguntinhas:
1) Qual era o valor do parâmetro db_file_multiblock_read_count setado no início desse post?
2) Qual dos três valores do db_file_multiblock_read_count que usei no final teria permitido ao CBO utilizar o acesso por índice ao invés do FULL? Isso iria ajudar ou atrapalhar a performance?
Por hoje é só
drop table t01;
Table dropped.
drop table t02;
Table dropped.
exit