Indexes and the CBO: Indexes vs. Full Table Scans

18 abr

CPU Cost Model – FTS Costings

Introdução

Neste Lab será esclarecido uma dúvida pertinente em vários blogs e fórum relacionados ao Oracle, que é a questão de por que o CBO não escolhe usar um determinado índice.

Os fatores que influenciam o CBO na decisão sobre utilizar um índice, ou executar um Full Table Scan são três:

Seletividade: a fração de linhas retornadas pela consulta em relação aos totais da tabela.
Clustering Factor: o alinhamento dos dados na tabela (que não é ordenada) em relação ao índice (que é ordenado).
Quantos blocos serão desprezados após a aplicação de um filtro utilizado na consulta.

Exemplificando os conceitos

Vamos criar uma tabela no nosso ambiente de testes com 100.000 linhas com uma coluna “ID” indexada que possui 100 valores distintos e distribuídos uniformemente.

[oracle@ORCL oracle]$sqlplus lab/lab123

SQL*Plus: Release 12.2.0.1.0 Production on Ter Abr 16 12:02:30 2019

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Conectado a:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

LAB @ ORCL:> CREATE TABLE DADOS AS SELECT (mod(rownum,100)+1)*10 id, 'Dados Teste' name FROM dual CONNECT BY LEVEL <= 100000;

Tabela criada.

LAB @ ORCL:> CREATE INDEX idx_dados ON DADOS (id);

índice criado.

LAB @ ORCL:>exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'DADOS', cascade=> true, estimate_percent=> null, method_opt=
> 'FOR ALL COLUMNS SIZE 1');

Procedimento PL/SQL concluído com sucesso.

SYS @ ORCL:>select blocks from dba_tables where table_name='DADOS';

BLOCKS
----------
303

Após a criação da tabela e inserção dos dados, foi feito um select para identificar o número de blocos que é de 303.

LAB @ ORCL:>SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR FROM USER_INDEXES;

INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
----------------------------------------------- ---------- ----------- -----------------
IDX_DADOS 1 207 28864

Notamos que o índice tem um blevel de 1, 207 blocos de folha e um fator de cluster (CF) de 28864, que nem se aproxima do número de blocos da tabela.
Identificamos que o CF é tão ruim que o CBO escolherá realizar um FTS do que usar o índice.

LAB @ ORCL:>show parameter db_file_multi;

PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- -----
db_file_multiblock_read_count integer 16

Observe que o db_file_multiblock_read_count é definido como 16.
Por último, iremos verificar as estatísticas de sistema. Vale ressaltar que todos esses valores são bem relevantes ao calcular o custo de um FTS com o modelo de cálculo de custo da CPU. Conforme iremos abordar adiante.

SYS @ ORCL:>select pname, pval1 from sys.aux_stats$ where pname in ('SREADTIM', 'MREADTIM', 'MBRC', 'CPUSPEED');

PNAME PVAL1
------------------------------ ----------
SREADTIM
MREADTIM
CPUSPEED
MBRC

Agora temos todas as informações necessárias para determinar como o CBO tratará as atividades do índice e do FTS nesta tabela.
Estaremos utilizando o modelo de CBO baseado em I/O.

SYS @ ORCL:>alter session set "_optimizer_cost_model" = io;

Sessão alterada.

Iremos executar uma simples consulta para um ID específico. Ou seja, selecionaremos 1% dos dados da tabela (1000 linhas).

SYS @ ORCL:>set autotrace traceonly
SYS @ ORCL:>SELECT * FROM LAB.DADOS WHERE id = 120;

1000 linhas selecionadas.

Plano de Execução
----------------------------------------------------------
Plan hash value: 1548001723

-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 16000 | 31 |
|* 1 | TABLE ACCESS FULL| DADOS | 1000 | 16000 | 31 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=120)

O custo de usar FTS é aproximadamente:
FTS Cost = 1 + ceil(table blocks / effective db file multiblockread count)
FTS Cost = 1 + ceil (303 / 10.40)
FTS Cost = 1 + 29 = 30

O CBO decidiu usar um FTS para selecionar o 1% de linhas, pois tem o menor custo associado.

Vamos então forçar o uso do índice à mesma consulta.

SYS @ ORCL:>select /*+ index(dados) */ * from lab.dados where id=120;

1000 linhas selecionadas.

Plano de Execução
----------------------------------------------------------
Plan hash value: 651663444

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 16000 | 292 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DADOS | 1000 | 16000 | 292 |
|* 2 | INDEX RANGE SCAN | IDX_DADOS | 1000 | | 3 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

Percebemos que o custo do índice de 292 é maior que o custo do FTS de 31.

Para entender melhor, o custo de usar o índice é de aproximadamente:

Seletividade = Densidade = 0,01
Cardinalidade = Nº de linhas x Densidade = 100000 x 0,01 = 1000
Custo do Índice = blevel -1*+
ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)
Custo do Índice = 0 + ceil (0,01 x 207) + (0,01 x 28864)
Custo do Índice = 0 + 2+ 288 = 290

Vamos verificar nos próximos exemplos como as alterações dos parâmetros utilizados anteriormente podem mudar de forma significante o custo do CBO.

SYS @ ORCL:>exec dbms_stats.set_system_stats('SREADTIM','2');

SYS @ ORCL:>exec dbms_stats.set_system_stats('MREADTIM','10');

SYS @ ORCL:>exec dbms_stats.set_system_stats('CPUSPEED','1120');

SYS @ ORCL:>exec dbms_stats.set_system_stats('MBRC','20');

SYS @ ORCL:>alter session set "_optimizer_cost_model" = cpu;

Sessão alterada.


SYS @ ORCL:>select pname, pval1 from sys.aux_stats$ where pname in ('SREADTIM', 'MREADTIM', 'MBRC', 'CPUSPEED');

PNAME PVAL1
------------------------------ ----------
SREADTIM 2
MREADTIM 10
CPUSPEED 1120
MBRC 20

Repetindo as consultas anteriores:

SYS @ ORCL:> SELECT * FROM LAB.DADOS WHERE id = 120;

1000 linhas selecionadas.


Plano de Execução
----------------------------------------------------------
Plan hash value: 1548001723

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 16000 | 87 (12)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DADOS | 1000 | 16000 | 87 (12)| 00:00:01 |
---------------------------------------------------------------------------

SYS @ ORCL:>select /*+ index(dados) */ * from lab.dados where id=120;

1000 linhas selecionadas.


Plano de Execução
----------------------------------------------------------
Plan hash value: 651663444

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 16000 | 292 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DADOS | 1000 | 16000 | 292 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_DADOS | 1000 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Nota: O CBO ainda está escolhendo o FTS devido o CF ser horrível. No entanto, o custo do FTS aumentou significativamente de 31 para 87, porém está bem distante do custo aproximado de 292 de usar o índice.

Calculo do novo custo do FTS, com o CBO baseado em CPU:

Cost of multiblockreads = (BLOCKS/MBRC) x MREADTIM = ceil(303/20) x 10 = 150
Total I/O time =152ms.
Total I/O in single block read time = 152 / 2 sreadtim = 76
CPU contributed to 12% of total time = 87 x 0.12= 11
Total cost of FTS = 76 + 11 = 87

O que devemos fazer para melhorar a nossa consulta?

CREATE TABLE DADOS2 AS SELECT * FROM DADOS ORDER BY 1;

CREATE INDEX idx_dados2 ON DADOS2 (id);

exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'DADOS2', cascade=> true, estimate_percent=> null, method_opt=> 'FOR ALL
COLUMNS SIZE 1');

LAB @ ORCL:>SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR FROM USER_INDEXES;

INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
----------------------------------------------- ---------- ----------- -----------------
IDX_DADOS 1 207 28864
IDX_DADOS2 1 207 289

set autotrace traceonly
SELECT * FROM LAB.DADOS2 WHERE id = 120;

Plano de Execução
----------------------------------------------------------
Plan hash value: 1182451817

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 16000 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DADOS2 | 1000 | 16000 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_DADOS2 | 1000 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Referências bibliográfica:

FOOTE, Richard. © The CBO CPU Costing Model: Indexes vs. Full Table Scans. 2009. Disponível em:
<https://richardfoote.wordpress.com/2009/11/25/the-cbo-cpu-costing-model-indexes-vs-full-table-scans/>. Acesso em: 16 abr. 2019.

PORTILHO, Ricardo. Oracle SQL Tuning: Quando ocorre um Full Table Scan? 2014. Disponível em:
<http://nervinformatica.com.br/blog/index.php/2014/05/28/sql-tuning-quando-ocorre-um-full-table-scan/>. Acesso em: 16 abr. 2019.

Tags:
Log Shipping,

Indexes and the CBO: Indexes vs. Full Table Scans