TUNING

Roberto Sobrinho

19/09/2024

Como Otimizar o Clustering Factor Usando TABLE_CACHED_BLOCKS

O Clustering Factor no Oracle é uma métrica para determinar a eficiência de um índice em consultas. Ele reflete como os dados de uma tabela estão organizados em relação aos valores do índice. Quando o Clustering Factor é muito alto, o Oracle pode acabar optando por um Full Table Scan em vez de utilizar o índice.

Este post tem como objetivo explicar a técnica de alteração do TABLE_CACHED_BLOCKS para modificar o cálculo do Clustering Factor. No entanto, é importante lembrar que essa não é a única solução e nem a mais recomendada. Existem abordagens mais eficientes e recomendadas para resolver o problema de Full Table Scans e otimizar o desempenho das consultas. O ajuste do TABLE_CACHED_BLOCKS pode ser útil em alguns casos, mas deve ser avaliado com cuidado, considerando outras práticas.

Exemplo Prático de Ajuste do TABLE_CACHED_BLOCKS

1. Criando a Tabela de Teste

Para simular um cenário com Clustering Factor elevado, vamos criar uma tabela desordenada:

CREATE TABLE TB_CLUSTERING_FACTOR_RUIM AS 
SELECT MOD(ROWNUM, 100) AS ID, RPAD(ROWNUM, 100) AS NAME 
  FROM DBA_SOURCE 
 WHERE ROWNUM <= 10000;
 
 Table created.

Aqui estamos criando uma tabela com 10.000 linhas, onde a coluna ID não está ordenada de maneira sequencial. Isso irá gerar um Clustering Factor elevado, já que os dados estarão distribuídos de forma desordenada em vários blocos.

2. Criando o Índice e Coletando Estatísticas

Agora, criamos um índice na coluna ID e coletamos as estatísticas da tabela para garantir que o Oracle tenha informações atualizadas:

CREATE INDEX IDX_CF_RUIM ON TB_CLUSTERING_FACTOR_RUIM(ID);

Index created.

EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TB_CLUSTERING_FACTOR_RUIM',cascade=> true);

PL/SQL procedure successfully completed.

A criação do índice vai permitir que possamos medir o Clustering Factor e observar como ele afeta o plano de execução. Coletar as estatísticas da tabela é essencial para garantir que o Oracle tenha uma visão atualizada da organização dos dados.

3. Visualizando o Clustering Factor

Depois de criar o índice e coletar as estatísticas, podemos verificar o valor do Clustering Factor com a seguinte consulta:

COL index_name FOR A25
COL owner FOR A25
SELECT i.owner,
       i.index_name,
       i.num_rows,
       i.distinct_keys,
       i.clustering_factor,
       s.blocks,
       i.leaf_blocks,
       s.bytes
  FROM dba_indexes i, dba_segments s
 WHERE i.table_name = s.segment_name
   AND i.owner = s.owner
   AND i.table_name = 'TB_CLUSTERING_FACTOR_RUIM'
   AND s.segment_type = 'TABLE';
   
   
OWNER  INDEX_NAME       NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR     BLOCKS LEAF_BLOCKS      BYTES
------ -------------- ---------- ------------- ----------------- ---------- ----------- ----------
HR     IDX_CF_RUIM         10000           100             10000        256          20    2097152

1 row selected.

Essa consulta exibe o Clustering Factor junto com outras informações sobre o índice. No exemplo acima, o Clustering Factor é 10000, o que indica que os dados da tabela estão mal organizados em relação ao índice. Esse valor elevado sugere que o Oracle vai preferir um Full Table Scan.

4. Carregar os Blocos na Memória

SELECT /*+ FULL(TB_CLUSTERING_FACTOR_RUIM) */ *  FROM TB_CLUSTERING_FACTOR_RUIM;

        ID NAME
---------- --------------------------------------------------------------------------------
        97 9997
        98 9998
        99 9999
         0 10000

10000 rows selected.

Esse comando força um Full Table Scan, fazendo com que parte dos blocos da tabela sejam carregados no buffer cache. A quantidade de blocos carregados pode variar conforme o ambiente, dependendo de diversos fatores que não entrarei em detalhes neste post.

5. Verificando Quantos Blocos Estão no Cache

Vamos forçar a leitura dos blocos da tabela para que eles sejam carregados no buffer cache:

SELECT COUNT(*)
  FROM v$bh
 WHERE objd = (SELECT object_id FROM dba_objects WHERE object_name = 'TB_CLUSTERING_FACTOR_RUIM');

        COUNT(*)
----------------
             164

1 row selected

Aqui, estamos verificando o número de blocos da tabela que estão armazenados no buffer cache. Esse valor será utilizado para definir o TABLE_CACHED_BLOCKS. Ao definir esse valor, é uma boa prática adicionar cerca de 10% de margem para garantir que o Oracle tenha uma estimativa mais precisa do uso de cache.

6. Plano de execução antes do ajuste

Antes do ajuste, o Oracle usa um Full Table Scan. Vamos verificar o plano de execução antes e depois do ajuste:

SET AUTOTRACE TRACEONLY EXPLAIN;
SELECT * FROM TB_CLUSTERING_FACTOR_RUIM WHERE ID BETWEEN 10 AND 20;
SET AUTOTRACE OFF;


Execution Plan
----------------------------------------------------------
Plan hash value: 878275316

-----------------------------------------------------------------------------------------------
| Id  | Operation         | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                           |  1210 |   122K|    46   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TB_CLUSTERING_FACTOR_RUIM |  1210 |   122K|    46   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter("ID"<=20 AND "ID">=10)

TABLE ACCESS FULL acontece porque o Clustering Factor está muito alto, o que indica que os dados estão desorganizados em relação ao índice. Quando o Clustering Factor é elevado, o Oracle considera que usar o índice seria ineficiente, já que isso resultaria em muitas leituras dispersas de blocos. Em vez disso, o Otimizador escolhe um Full Table Scan.

7. Alterando o TABLE_CACHED_BLOCKS

Agora, com base no número de blocos em cache (164), configuramos o TABLE_CACHED_BLOCKS com um valor ligeiramente maior, de 180 blocos, incluindo um incremento de 10%:

COL table_cached_blocks FOR A30;                                                                                                                                            
COL table_name FOR A30;                                                                                                                                                     
SELECT table_name,                                                                                                                                                          
       DBMS_STATS.GET_PREFS(ownname=>USER,tabname=>table_name,pname=>'TABLE_CACHED_BLOCKS') TABLE_CACHED_BLOCKS                                                             
  FROM dba_tables                                                                                                                                                           
 WHERE table_name = 'TB_CLUSTERING_FACTOR_RUIM';  
 

TABLE_NAME                     TABLE_CACHED_BLOCKS
------------------------------ ------------------------------
TB_CLUSTERING_FACTOR_RUIM      1

1 row selected.


EXEC DBMS_STATS.SET_TABLE_PREFS(OWNNAME => user, TABNAME => 'TB_CLUSTERING_FACTOR_RUIM', PNAME => 'TABLE_CACHED_BLOCKS', PVALUE => 180);

PL/SQL procedure successfully completed.

SELECT table_name,                                                                                                                                                          
       DBMS_STATS.GET_PREFS(ownname=>USER,tabname=>table_name,pname=>'TABLE_CACHED_BLOCKS') TABLE_CACHED_BLOCKS                                                             
  FROM dba_tables                                                                                                                                                           
 WHERE table_name = 'TB_CLUSTERING_FACTOR_RUIM';  
 
 TABLE_NAME                     TABLE_CACHED_BLOCKS
------------------------------ ------------------------------
TB_CLUSTERING_FACTOR_RUIM      180

Esse comando ajusta o TABLE_CACHED_BLOCKS com o número de blocos atualmente em cache mais uma margem de segurança de 10%. Isso ajudará o Otimizador de Custo do Oracle a fazer uma avaliação mais precisa da eficiência do índice.

8. Coletando Estatísticas do Índice

Após o ajuste, precisamos recolher as estatísticas do índice para aplicar as mudanças:

EXEC DBMS_STATS.GATHER_INDEX_STATS(user, 'IDX_CF_RUIM');

PL/SQL procedure successfully completed.

Coletando as estatísticas do índice para que o Oracle possa recalcular o Clustering Factor com base no novo valor do TABLE_CACHED_BLOCKS

9. Verificando o Novo Clustering Factor

Agora, vamos verificar se o Clustering Factor foi reduzido após o ajuste:

COL index_name FOR A25
COL owner FOR A25
SELECT i.owner,
       i.index_name,
       i.num_rows,
       i.distinct_keys,
       i.clustering_factor,
       s.blocks,
       i.leaf_blocks,
       s.bytes
  FROM dba_indexes i, dba_segments s
 WHERE i.table_name = s.segment_name
   AND i.owner = s.owner
   AND i.table_name = 'TB_CLUSTERING_FACTOR_RUIM'
   AND s.segment_type = 'TABLE';
   
   
OWNER  INDEX_NAME       NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR     BLOCKS LEAF_BLOCKS      BYTES
------ -------------- ---------- ------------- ----------------- ---------- ----------- ----------
HR     IDX_CF_RUIM         10000           100               152        256          20    2097152

1 row selected.

Esse comando verifica o novo Clustering Factor. O ajuste do TABLE_CACHED_BLOCKS foi bem-sucedido, porque o valor do Clustering Factor caiu significativamente (152).

10. Comparando o Plano de Execução

Antes do ajuste, o Oracle estava optando por um Full Table Scan, conforme demosntrado no passo 6. Vamos verificar o plano de execução antes e depois do ajuste.

Plano de Execução Antes do Ajuste:

SET AUTOTRACE TRACEONLY EXPLAIN;
SELECT * FROM TB_CLUSTERING_FACTOR_RUIM WHERE ID BETWEEN 10 AND 20;
SET AUTOTRACE OFF;


Execution Plan
----------------------------------------------------------
Plan hash value: 878275316

-----------------------------------------------------------------------------------------------
| Id  | Operation         | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                           |  1210 |   122K|    46   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TB_CLUSTERING_FACTOR_RUIM |  1210 |   122K|    46   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter("ID"<=20 AND "ID">=10)

Depois do ajuste do TABLE_CACHED_BLOCKS, execute novamente a consulta para comparar o novo plano de execução:

SET AUTOTRACE TRACEONLY EXPLAIN;
SELECT * FROM TB_CLUSTERING_FACTOR_RUIM WHERE ID BETWEEN 10 AND 20;
SET AUTOTRACE OFF;


Execution Plan
----------------------------------------------------------
Plan hash value: 2595843875

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                           |  1210 |   122K|    23   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TB_CLUSTERING_FACTOR_RUIM |  1210 |   122K|    23   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_CF_RUIM               |  1210 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

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

   2 - access("ID">=10 AND "ID"<=20)

O plano de execução agora mostra que o Oracle está preferindo um Index Range Scan, isso indica que o ajuste no TABLE_CACHED_BLOCKS foi eficaz.

Ajustar o parâmetro TABLE_CACHED_BLOCKS é uma maneira simples e eficiente de melhorar o desempenho das consultas no Oracle, especialmente quando o Clustering Factor está alto.

Mas vale lembrar que essa técnica é apenas uma opção entre várias. Em muitos casos, há soluções mais comuns e eficientes que podem ser adotadas, como melhorar a coleta de estatísticas ou reorganizar índices. O TABLE_CACHED_BLOCKS pode ser útil, mas deve ser visto como uma ferramenta complementar dentro de uma estratégia mais ampla de otimização.

🧟‍♀🧟‍♂#20240919 #DBASobrinho #GuinaNãoTinhaDó #BóBó #CaceteDeAgulha #AulaDeViolaoDificil #DebateAmanha #TurmaDoChaves #TheoNoJapones 🧟‍♀🧟‍♂

Compartilhe

Pesquisar

Roberto Sobrinho

Sou Roberto Fernandes Sobrinho, também conhecido como Sobrinho DBA , pós graduado em “Architecture and Database Administration”, entusiasta, dedicado e com 20 anos de experiência com Oracle Database e suas diversas distribuições e variações.