GPO ( Grupo de Profissionais Oracle )
A maior comunidade Oracle do Brasil !

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 selected

Isso 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

Share

You may also like...

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *