No artigo "Por que o Otimizador criou um plano de execução ineficiente?" elencamos algumas condições que levam o Otimizador a gerar um plano de execução ineficiente, neste artigo vamos abordar com mais detalhes um desses tópicos, como a repetição de dados não uniformes em uma coluna da tabela podem influenciar…
Como o Hybrid Histogram contribui para deixar o Otimizador mais inteligente?
O Otimizador do Oracle cria planos de execução baseado nas estatísticas dos objetos no banco de dados, a qualidade destas informações é decisiva na precisão das estimativas de acesso das operações no plano de execução e consequentemente determinam o desempenho das instruções SQL. Os Histogramas são parte destas estatísticas e até a versão 11g existiam somente 2 tipos (FREQUENCY e HEIGHT BALANCED), Estes dois tipos de Histogramas não conseguiam prover o Otimizador com estatísticas de qualidade para algumas situações de conjuntos de dados, por esta razão a partir da versão 12c o Banco de dados Oracle passou a utilizar mais dois tipos de Histogramas (TOP FREQUENCY e HYBRID).
Neste artigo vamos mostrar como o novo Histograma HYBRID é mais preciso, para isto vamos comparar a execução de uma consulta com o mesmo conjunto de dados em duas bases, na base 11g a consulta vai utilizar o Histograma HEIGHT BALANCED e na base 19c ela vai utilizar o Histograma HYBRID que ganhou este nome pois ele combina as características dos Histogramas FREQUENCY e HEIGHT BALANCED.
DEMONSTRAÇÃO PRÁTICA
A primeira simulação será realizada numa base 11gR2 onde vamos verificar que o Otimizador utiliza o Histograma HEIGHT BALANCED e faz uma estimativa de cardinalidade imprecisa e como consequência escolhe um método de acesso inadequado:
1.1) Criar uma tabela com um índice
1.2) Executar a consulta na tabela criada
1.3) Executar a consulta novamente incluindo um HINT para corrigir a estimativa de cardinalidade
A segunda simulação será realizada numa base 19c onde vamos verificar que o Otimizador utiliza o Histograma HYBRID o que melhora a estimativa de cardinalidade de acesso a tabela e como consequência será escolhido um plano de execução com um método de acesso mais eficiente.
2.1) Criar uma tabela com um índice
2.2) Executar uma consulta na tabela criada
Base 11gR2
1.1) Criar uma tabela com um índice
Vamos criar os objetos necessários para realizar a nossa simulação e coletar as estatísticas de forma que seja criado o Histograma para a coluna CUST_CITY que é utilizada como filtro na cláusula WHERE.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | SQL > SELECT * FROM V$VERSION where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SQL > SQL > CREATE TABLE dbtw063 2 AS SELECT * 3 FROM sh.customers 4 ORDER BY country_id; Tabela criada. SQL > SQL > CREATE index dbtw063_idx 2 ON dbtw063(cust_city); Índice criado. SQL > SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'DBTW063',method_opt=>'for columns cust_city size 254'); Procedimento PL/SQL concluído com sucesso. SQL > SQL > COL column_name FOR A16; SQL > SQL > SELECT column_name, 2 num_distinct, 3 density, 4 num_nulls, 5 avg_col_len , 6 histogram, 7 num_buckets 8 FROM user_tab_col_statistics 9 WHERE table_name = 'DBTW063' 10 AND column_name = 'CUST_CITY'; COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS AVG_COL_LEN HISTOGRAM NUM_BUCKETS ---------------- ------------ ---------- ---------- ----------- --------------- ----------- CUST_CITY 620 ,002212389 0 10 HEIGHT BALANCED 254 SQL > |
Ajudo DBAs e analistas de sistema a se destacarem em suas empresas
e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:
Observamos acima que a consulta na visão USER_TAB_COL_STATISTICS confirma a criação do histograma HEIGHT BALANCED na coluna CUST_CITY.
1.2) Executar uma consulta na tabela criada
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | SQL > ALTER SESSION SET statistics_level = all; Sessão alterada. SQL > SQL > SELECT cust_city, country_id, count(1) 2 FROM dbtw063 3 WHERE cust_city IN ('Tokyo','Arnhem') 4 GROUP by cust_city, country_id; CUST_CITY COUNTRY_ID COUNT(1) ------------------------------ ---------- ---------- Tokyo 52782 16 Arnhem 52770 17 SQL > SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor(format=>'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID gsp9gna1k97nn, child number 0 ------------------------------------- SELECT cust_city, country_id, count(:"SYS_B_0") FROM dbtw063 WHERE cust_city IN (:"SYS_B_1",:"SYS_B_2") GROUP by cust_city, country_id Plan hash value: 1469487015 ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 1457 | | | | | 1 | HASH GROUP BY | | 1 | 2 | 2 |00:00:00.01 | 1457 | 1301K| 1301K| 599K (0)| |* 2 | TABLE ACCESS FULL| DBTW063 | 1 | 133 | 33 |00:00:00.01 | 1457 | | | | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("CUST_CITY"=:SYS_B_1 OR "CUST_CITY"=:SYS_B_2)) 20 linhas selecionadas. SQL > |
Observe no plano de execução que o Otimizador estimou que seriam selecionadas 133 linhas na tabela (Coluna E-Rows), quando na verdade foram acessadas somente 33 linhas (Coluna A-Rows). Devido a esta estimativa imprecisa o Otimizador decide utilizar a operação TABLE ACCESS FULL para acessar as linhas da tabela, isto tornou o plano de execução ineficiente pois para acessar 33 linhas da tabela ele poderia utilizar o índice que consumiria menos recursos do banco.
1.3) Executar a consulta novamente incluindo um HINT
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | SQL > SELECT /*+ OPT_ESTIMATE(INDEX_SCAN DBTW063 DBTW063_IDX ROWS=33) */ cust_city, country_id, count(1) 2 FROM dbtw063 3 WHERE cust_city IN ('Tokyo','Arnhem') 4 GROUP by cust_city, country_id; CUST_CITY COUNTRY_ID COUNT(1) ------------------------------ ---------- ---------- Tokyo 52782 16 Arnhem 52770 17 SQL > SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor(format=>'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 6rvn8phxqz613, child number 0 ------------------------------------- SELECT /*+ OPT_ESTIMATE(INDEX_SCAN DBTW063 DBTW063_IDX ROWS=33) */ cust_city, country_id, count(:"SYS_B_0") FROM dbtw063 WHERE cust_city IN (:"SYS_B_1",:"SYS_B_2") GROUP by cust_city, country_id Plan hash value: 2102246616 ---------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 31 | | | | | 1 | HASH GROUP BY | | 1 | 2 | 2 |00:00:00.01 | 31 | 1301K| 1301K| 604K (0)| | 2 | INLIST ITERATOR | | 1 | | 33 |00:00:00.01 | 31 | | | | | 3 | TABLE ACCESS BY INDEX ROWID| DBTW063 | 2 | 133 | 33 |00:00:00.01 | 31 | | | | |* 4 | INDEX RANGE SCAN | DBTW063_IDX | 2 | 33 | 33 |00:00:00.01 | 5 | | | | ---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access(("CUST_CITY"=:SYS_B_1 OR "CUST_CITY"=:SYS_B_2)) 23 linhas selecionadas. SQL > |
Incluindo o HINT OPT_ESTIMATE na consulta, nos informamos ao otimizador que a cardinalidade da operação de acesso a tabela usando o índice selecionaria 33 linhas, com isso o Otimizador decide acessar a tabela utilizando o índice e o desempenho da consulta melhora 98% pois na primeira consulta o banco acessou 1.457 blocos para selecionar as 33 linhas e com o HINT ele acessou somente 31 blocos.
Observe que a estimativa de cardinalidade da operação de acesso a tabela continua como 133 linhas, pois não incluímos um HINT para esta operação e o Otimizador continua fazendo a estimativa baseado no Histograma HEIGHT BALANCED.
Base 19c
Agora vamos acessar uma base Oracle 19c e repetir o mesmo processo, mas agora o Otimizador vai utilizar o Histograma Hybrid pois a partir da versão 12c o banco já passou a trabalhar com ele. Nesta simulação a coleta de estatísticas vai gerar um Histograma HYBRID para a coluna presente no filtro da cláusula WHERE da consulta que estamos utilizando.
2.1) Criar uma tabela com um índice
Vamos criar os objetos necessários para realizar a simulação na base 19c e vamos executar o comando de coleta de estatísticas de forma que seja criado o Histograma para a coluna CUST_CITY.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | SQL > SELECT BANNER_FULL FROM V$VERSION where rownum < 2; BANNER_FULL ------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 1 linha selecionada. SQL > SQL > CREATE TABLE dbtw063 2 AS SELECT * 3 FROM sh.customers 4 ORDER BY country_id; Tabela criada. SQL > SQL > CREATE index dbtw063_idx 2 ON dbtw063(cust_city); Índice criado. SQL > SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'DBTW063',method_opt=>'for columns cust_city size 600'); Procedimento PL/SQL concluído com sucesso. SQL > SQL > COL column_name FOR A16; SQL > SQL > SELECT column_name, 2 num_distinct, 3 density, 4 num_nulls, 5 avg_col_len , 6 histogram, 7 num_buckets 8 FROM user_tab_col_statistics 9 WHERE table_name = 'DBTW063' 10 AND column_name = 'CUST_CITY'; COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS AVG_COL_LEN HISTOGRAM NUM_BUCKETS ---------------- ------------ ------------ ---------- ----------- --------------- ----------- CUST_CITY 620 .000927000 0 10 HYBRID 600 1 linha selecionada. SQL > |
Outra mudança ocorrida com os Histogramas a partir da versão 12c é que o numero de Buckets que era limitado em 254 foi expandido para 2048, observe no comando de coleta de estatísticas acima que alterei o parâmetro size para 600 para forçar a criação do Histograma HYBRID.
Também podemos constatar acima que a consulta na visão USER_TAB_COL_STATISTICS confirma a criação do histograma HYBRID na coluna CUST_CITY.
2.2) Executar uma consulta na tabela criada
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | SQL > ALTER SESSION SET statistics_level = all; Sessão alterada. SQL > SQL > SELECT cust_city, country_id, count(1) 2 FROM dbtw063 3 WHERE cust_city IN ('Tokyo','Arnhem') 4 GROUP by cust_city, country_id; CUST_CITY COUNTRY_ID COUNT(1) ------------------------------ ---------- ---------- Tokyo 52782 16 Arnhem 52770 17 2 linhas selecionadas. SQL > SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor(format=>'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 7y7scsjjdz757, child number 0 ------------------------------------- SELECT cust_city, country_id, count(1) FROM dbtw063 WHERE cust_city IN ('Tokyo','Arnhem') GROUP by cust_city, country_id Plan hash value: 646836277 ------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 31 | | | | | 1 | HASH GROUP BY | | 1 | 26 | 2 |00:00:00.01 | 31 | 1282K| 1282K| 663K (0)| | 2 | INLIST ITERATOR | | 1 | | 33 |00:00:00.01 | 31 | | | | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DBTW063 | 2 | 58 | 33 |00:00:00.01 | 31 | | | | |* 4 | INDEX RANGE SCAN | DBTW063_IDX | 2 | 58 | 33 |00:00:00.01 | 5 | | | | ------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access(("CUST_CITY"='Arnhem' OR "CUST_CITY"='Tokyo')) 22 linhas selecionadas. SQL > |
Observe no plano de execução que com a utilização do Histograma HYBRID a estimativa de cardinalidade foi de 58 linhas, bem próximo das 33 linhas que de fato serão acessadas, esta melhora na estimativa fez com que o Otimizador escolhesse acessar as linhas na tabela utilizando a operação INDEX RANGE SCAN que como vimos anteriormente é mais eficiente do que utilizar uma operação TABLE ACCESS FULL.
CONCLUSÃO
A implementação de novos Histogramas a partir da versão 12c aumentou a precisão do Otimizador no calculo das estimativas de cardinalidade para conjunto de dados com repetição não uniformes (SKEWED), com isso o Otimizador passou a escolher planos de execução mais eficientes e consequentemente o desempenho das consultas melhoraram. O outro tipo de Histograma criado a partir da versão 12c é o TOP FREQUENCY que é uma espécie de UPGRADE do Histograma FREQUENCY, mas isto já é assunto para um outro artigo.
REFERÊNCIAS
https://docs.oracle.com/database/121/
