A "Feature Dynamic Sampling" foi disponibilizada a partir da versão Oracle 9iR2. O equívoco mais comum é que ela pode ser utilizada para substituir as estatísticas coletadas pelo pacote DBMS_STATS. O objetivo dela é dar mais opções estatísticas ao otimizador, ela é usada quando as estatísticas regulares não são suficientes…
Como uma consulta pode ter alto desempenho sem dados estatísticos?
O Otimizador do Oracle cria planos de execução com alta performance baseado nas estatísticas dos objetos no banco de dados, com estas informações ele consegue fazer estimativas de acesso para cada uma das operações no plano de execução e a qualidade destas estimativas é que determina o desempenho das instruções SQL, mas o que acontece quando algum objeto envolvido na instrução SQL não possui estatísticas? Desde a versão 9i até a versão 11gR2, quando um dos objetos envolvidos na instrução SQL não possui estatísticas o Otimizador utiliza o recurso DYNAMIC SAMPLING para coletar estatísticas a partir de uma pequena amostra de registros dos objetos durante a montagem do plano de execução.
Este recurso é muito útil, porem ele não resolve problemas relativos a deficiências na coleta de estatísticas como estatísticas desatualizadas e falta de histogramas. Na versão 12c a Oracle implementou uma melhoria no recurso DYNAMIC SAMPLING e agora o Otimizador também consegue contornar problemas de estatísticas desatualizadas e falta de histogramas, neste artigo vamos fazer 2 simulações para demonstrar a evolução do recurso DYNAMIC SAMPLING, a primeira numa base 11gR2 e a segunda numa base 12cR2.
Na primeira simulação vamos verificar a ineficiência do recurso DYNAMIC SAMPLING na versão 11gR2 quando as estatísticas estão desatualizadas ou sem histogramas:
1.1) Criar uma tabela com um índice
1.2) Executar uma consulta na tabela criada sem as estatísticas
1.3) Coletar as estatísticas e executar a consulta novamente
1.4) Inserir registros na tabela para as estatísticas ficarem desatualizadas
1.5) Executar a consulta novamente
Na segunda simulação vamos verificar a melhoria no recurso DYNAMIC SAMPLING na versão 12cR2 quando as estatísticas estão desatualizadas ou sem histogramas :
2.1) Criar uma tabela com um índice
2.2) Executar uma consulta na tabela criada
2.3) Coletar as estatísticas e executar a consulta novamente
2.4) Inserir registros na tabela para as estatísticas ficarem desatualizadas
2.5) Executar a consulta novamente
1.1) Criar uma tabela com um índice
Vamos criar os objetos necessários para realizar a nossa simulação e inicialmente não vamos coletar as estatísticas.
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 | 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 dbtw056 as 2 select 20 as codigo, 3 'DBTimeWizard - Oracle Performance and Tuning' as produto 4 from dual connect by level<=9981; Tabela criada. SQL > SQL > INSERT INTO dbtw056 select rownum, 'DBTimeWizard - Oracle Performance and Tuning' 2 from dual connect by level<=19; 19 linhas criadas. SQL > SQL > COMMIT; Commit concluído. SQL > SQL > SQL > create index dbtw056_codigo_idx on dbtw056(codigo); Índice criado. SQL > SQL > SELECT table_name, stale_stats, last_analyzed 2 FROM dba_tab_statistics 3 WHERE table_name='DBTW056'; TABLE_NAME STA LAST_ANA ------------------------------ --- -------- DBTW056 SQL > |
Observamos acima no comando SELECT na visão DBA_TAB_STATISTICS que as estatísticas não estão disponíveis.
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:
1.2) Executar uma consulta na tabela criada sem as estatísticas
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 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | SQL > ALTER SESSION SET optimizer_dynamic_sampling=11; Sessão alterada. SQL > SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* dbtw-056.1 */ * 2 FROM dbtw056 3 WHERE codigo=11; CODIGO PRODUTO ---------- -------------------------------------------- 11 DBTimeWizard - Oracle Performance and Tuning SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%dbtw-056.1%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 5ychjrxz1g28n 0 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')); antigo 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')) novo 2: FROM TABLE (dbms_xplan.display_cursor ('5ychjrxz1g28n', 0,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------- SQL_ID 5ychjrxz1g28n, child number 0 ------------------------------------- SELECT /* dbtw-056.1 */ * FROM dbtw056 WHERE codigo=:"SYS_B_0" Plan hash value: 1209234527 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID| DBTW056 | 1 | 67 | 1 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | DBTW056_CODIGO_IDX | 1 | 67 | 1 |00:00:00.01 | 3 | ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CODIGO"=:SYS_B_0) Note ----- - dynamic sampling used for this statement (level=11) 23 linhas selecionadas. SQL > |
Observe no plano de execução que o Otimizador estimou que seriam selecionadas 67 linhas na tabela, esta estimativa aproximada foi realizada pelo recurso DYNAMIC SAMPLING pois não havia estatísticas no dicionario de dados e como a quantidade de linhas estimadas e pequena comparada com a quantidade de linhas na tabela (10.000 linhas) o Otimizador decidiu utilizar o índice no plano de execução.
1.3) Coletar as estatísticas e executar a consulta novamente
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 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW056', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE 1',force=> true,degree=> 16); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SELECT column_name AS "NAME", 2 num_distinct AS "#DISTINCT", 3 density AS "DENSITY", 4 num_nulls AS "#NULL", 5 avg_col_len , 6 histogram, 7 num_buckets AS "#BUCKETS" 8 FROM dba_tab_col_statistics 9 WHERE table_name = 'DBTW056'; NAME #DISTINCT DENSITY #NULL AVG_COL_LEN HISTOGRAM #BUCKETS ------------------------------ ---------- ---------- ---------- ----------- --------------- ---------- PRODUTO 1 1 0 45 NONE 1 CODIGO 20 ,05 0 3 NONE 1 SQL > SQL > ALTER SESSION SET optimizer_dynamic_sampling=11; Sessão alterada. SQL > SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* dbtw-056.2 */ * 2 FROM dbtw056 3 WHERE codigo=11; CODIGO PRODUTO ---------- -------------------------------------------- 11 DBTimeWizard - Oracle Performance and Tuning SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%dbtw-056.2%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ djrqpaw9dqaxw 0 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID djrqpaw9dqaxw, child number 0 ------------------------------------- SELECT /* dbtw-056.2 */ * FROM dbtw056 WHERE codigo=:"SYS_B_0" Plan hash value: 3528933912 --------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 84 | |* 1 | TABLE ACCESS FULL| DBTW056 | 1 | 500 | 1 |00:00:00.01 | 84 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("CODIGO"=:SYS_B_0) Note ----- - dynamic sampling used for this statement (level=11) 22 linhas selecionadas. SQL > |
Após a execução da coleta das estatísticas podemos observar no SELECT na visão DBA_TAB_COL_STATISTICS que não existem histogramas nas duas colunas da tabela, observando o plano de execução verificamos que o Otimizador estimou que seriam selecionadas 500 linhas na tabela e por esta razão decidiu fazer uma operação TABLE ACCESS FULL. O Otimizador não utilizou o recurso DYNAMIC SAMPLING pois havia estatísticas da tabela e do índice no dicionario de dados do banco, mas como não havia HISTOGRAMAS o calculo utilizado para estimar o numero de linhas selecionadas foi: NUMERO DE LINHAS NA TABELA / NUMERO DE LINHAS DISTINTAS (10.000 / 20 = 500).
1.4) Inserir registros na tabela para as estatísticas ficarem desatualizadas
Vamos inserir 2.000 linhas na tabela (10%) para forçar uma situação de estatísticas desatualizadas, na sequência vamos executar a consulta novamente e verificar se o recurso DYNAMIC SAMPLING vai ser utilizado.
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 | SQL > INSERT INTO dbtw056 select 15, 'DBTimeWizard - Oracle Performance and Tuning' 2 from dual connect by level<= 2000; 2000 linhas criadas. SQL > SQL > COMMIT; Commit concluído. SQL > SQL > SQL > exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO(); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SELECT table_name, stale_stats, last_analyzed 2 FROM dba_tab_statistics 3 WHERE table_name='DBTW056'; TABLE_NAME STA LAST_ANA ------------------------------ --- -------- DBTW056 YES 15/10/18 1 linha selecionada. SQL > |
Observe no SELECT na visão DBA_TAB_STATISTICS que o campo STALE_STATS esta como YES, isto significa que as estatísticas estão desatualizadas.
1.5) Executar a consulta novamente
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 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | SQL > ALTER SESSION SET optimizer_dynamic_sampling=11; Sessão alterada. SQL > SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* dbtw-056.3 */ * 2 FROM dbtw056 3 WHERE codigo=11; CODIGO PRODUTO ---------- -------------------------------------------- 11 DBTimeWizard - Oracle Performance and Tuning SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%dbtw-056.3%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ gvy2nbd3qys8k 0 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- SQL_ID gvy2nbd3qys8k, child number 0 ------------------------------------- SELECT /* dbtw-056.3 */ * FROM dbtw056 WHERE codigo=:"SYS_B_0" Plan hash value: 3528933912 --------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 99 | |* 1 | TABLE ACCESS FULL| DBTW056 | 1 | 500 | 1 |00:00:00.01 | 99 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("CODIGO"=:SYS_B_0) Note ----- - dynamic sampling used for this statement (level=11) 22 linhas selecionadas. SQL > |
Analisando o plano de execução da consulta podemos constatar que o Otimizador decidiu acessar a tabela utilizando a operação TABLE ACCESS FULL pois a quantidade de linhas estimadas foi 500 novamente, ou seja, quando as estatísticas estão desatualizadas o Otimizador não utiliza o recurso DYNAMIC SAMPLING.
Agora vamos acessar uma base Oracle 12cR2 e repetir o mesmo processo para verificar como o Otimizador vai tratar uma tabela com estatisticas desatualizas e sem histogramas.
2.1) Criar uma tabela com um índice
Vamos criar os objetos necessários para realizar a nossa simulação na base 12cR2 e inicialmente não vamos executar o comando de coleta de estatísticas.
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 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 | SQL > SELECT BANNER FROM V$VERSION where rownum < 2; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 1 linha selecionada. SQL > SQL > create table dbtw056 as 2 select 20 as codigo, 3 'DBTimeWizard - Oracle Performance and Tuning' as produto 4 from dual connect by level<=9981; Tabela criada. SQL > SQL > INSERT INTO dbtw056 select rownum, 'DBTimeWizard - Oracle Performance and Tuning' 2 from dual connect by level<=19; 19 linhas criadas. SQL > SQL > COMMIT; Commit concluído. SQL > SQL > SQL > create index dbtw056_codigo_idx on dbtw056(codigo); Índice criado. SQL > SQL > COL table_name FOR A40; SQL > SELECT table_name, stale_stats, last_analyzed 2 FROM dba_tab_statistics 3 WHERE table_name='DBTW056'; TABLE_NAME STA LAST_ANA ---------------------------------------- --- -------- DBTW056 NO 17/10/18 1 linha selecionada. SQL > SQL > SELECT column_name AS "NAME", 2 num_distinct AS "#DISTINCT", 3 density AS "DENSITY", 4 num_nulls AS "#NULL", 5 avg_col_len , 6 histogram, 7 num_buckets AS "#BUCKETS" 8 FROM dba_tab_col_statistics 9 WHERE table_name = 'DBTW056'; NAME #DISTINCT DENSITY #NULL AVG_COL_LEN HISTOGRAM #BUCKETS ------------------------------ ---------------- ------------ ---------- ----------- --------------- ---------- CODIGO 1 1.000000000 0 3 NONE 1 PRODUTO 1 1.000000000 0 45 NONE 1 2 linhas selecionadas. SQL > SQL > COL index_name FOR A40; SQL > SELECT index_name , 2 blevel , 3 num_rows , 4 distinct_keys , 5 clustering_factor , 6 status, 7 leaf_blocks , 8 last_analyzed 9 FROM dba_indexes 10 WHERE table_name = 'DBTW056' 11 ORDER by owner, index_name; INDEX_NAME BLEVEL NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR STATUS LEAF_BLOCKS LAST_ANA ---------------------------------------- ---------- ---------- ------------- ----------------- ---------- ----------- -------- DBTW056_CODIGO_IDX 1 10000 20 75 VALID 20 17/10/18 1 linha selecionada. SQL > |
Observamos acima no comando SELECT na visão DBA_TAB_STATISTICS que apesar de não executarmos o comando para coleta de estatísticas, elas foram coletadas automaticamente pelo Oracle, porem elas estão imprecisas pois o campo CODIGO informa que existe somente 1 valor distinto quando na verdade são 20. As estatisticas do índice foram coletadas de forma precisa.
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 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | SQL > ALTER SESSION SET optimizer_dynamic_sampling=11; Sessão alterada. SQL > SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* dbtw-056.1 */ * 2 FROM dbtw056 3 WHERE codigo=11; CODIGO PRODUTO ---------- -------------------------------------------- 11 DBTimeWizard - Oracle Performance and Tuning 1 linha selecionada. SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%dbtw-056.1%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ cfp3qwbjnr0kq 0 1 linha selecionada. SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID cfp3qwbjnr0kq, child number 0 ------------------------------------- SELECT /* dbtw-056.1 */ * FROM dbtw056 WHERE codigo=11 Plan hash value: 1201093745 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DBTW056 | 1 | 1 | 1 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | DBTW056_CODIGO_IDX | 1 | 1 | 1 |00:00:00.01 | 3 | -------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CODIGO"=11) Note ----- - dynamic statistics used: dynamic sampling (level=AUTO) 23 linhas selecionadas. SQL > |
Observe no plano de execução que apesar das estatísticas serem imprecisas o Otimizador estimou com precisão que seria selecionada 1 linha na tabela, esta estimativa foi possível pois o recurso DYNAMIC SAMPLING foi utilizado e o Otimizador decidiu utilizar o índice no plano de execução.
2.3) Coletar as estatísticas e executar a consulta novamente
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 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW056', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE 1',force=> true,degree=> 16); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SELECT column_name AS "NAME", 2 num_distinct AS "#DISTINCT", 3 density AS "DENSITY", 4 num_nulls AS "#NULL", 5 avg_col_len , 6 histogram, 7 num_buckets AS "#BUCKETS" 8 FROM dba_tab_col_statistics 9 WHERE table_name = 'DBTW056'; NAME #DISTINCT DENSITY #NULL AVG_COL_LEN HISTOGRAM #BUCKETS ------------------------------ ---------------- ------------ ---------- ----------- --------------- ---------- CODIGO 20 .050000000 0 3 NONE 1 PRODUTO 1 1.000000000 0 45 NONE 1 2 linhas selecionadas. SQL > SQL > ALTER SESSION SET optimizer_dynamic_sampling=11; Sessão alterada. SQL > SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* dbtw-056.2 */ * 2 FROM dbtw056 3 WHERE codigo=11; CODIGO PRODUTO ---------- -------------------------------------------- 11 DBTimeWizard - Oracle Performance and Tuning 1 linha selecionada. SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%dbtw-056.2%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ d4krxbj892rwr 0 1 linha selecionada. SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID d4krxbj892rwr, child number 0 ------------------------------------- SELECT /* dbtw-056.2 */ * FROM dbtw056 WHERE codigo=11 Plan hash value: 1201093745 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DBTW056 | 1 | 1 | 1 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | DBTW056_CODIGO_IDX | 1 | 1 | 1 |00:00:00.01 | 3 | -------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CODIGO"=11) Note ----- - dynamic statistics used: dynamic sampling (level=AUTO) 23 linhas selecionadas. SQL > |
Após a execução da coleta das estatísticas podemos observar no SELECT na visão DBA_TAB_COL_STATISTICS que o numero de linhas distintas agora esta correto (20 linhas) porem não existem histogramas nas duas colunas da tabela, observando o plano de execução verificamos que o Otimizador estimou o numero de linhas de forma precisa novamente e criou um plano de execução com acesso a tabela utilizando o índice. Para estimar a cardinalidade da operação de acesso a tabela corretamente sem a ajuda de HISTOGRAMAS o Otimizador utilizou o recurso DYNAMIC SAMPLING.
2.4) Inserir registros na tabela para as estatísticas ficarem desatualizadas
Vamos executar o comando de coleta de estatísticas novamente para criar um HISTOGRAMA na coluna CÓDIGO da tabela e na sequencia vamos inserir 2.000 linhas na tabela (10%) para forçar uma situação de estatísticas desatualizadas e inserir 4 linhas com o valor 11 na coluna CÓDIGO que esta sendo utilizada no filtro da cláusula WHERE, na sequência vamos executar a consulta novamente e verificar se o recurso DYNAMIC SAMPLING vai ser utilizado.
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 51 52 53 | SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW056', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',force=> true,degree=> 16); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SELECT column_name AS "NAME", 2 num_distinct AS "#DISTINCT", 3 density AS "DENSITY", 4 num_nulls AS "#NULL", 5 avg_col_len , 6 histogram, 7 num_buckets AS "#BUCKETS" 8 FROM dba_tab_col_statistics 9 WHERE table_name = 'DBTW056'; NAME #DISTINCT DENSITY #NULL AVG_COL_LEN HISTOGRAM #BUCKETS ------------------- ---------- ---------- ---------- ----------- --------------- ---------- CODIGO 20 ,00005 0 3 FREQUENCY 20 PRODUTO 1 1 0 45 NONE 1 SQL > SQL > INSERT INTO dbtw056 select 15, 'DBTimeWizard - Oracle Performance and Tuning' 2 from dual connect by level<= 2000; 2000 linhas criadas. SQL > SQL > INSERT INTO dbtw056 select 11, 'DBTimeWizard - Oracle Performance and Tuning' 2 from dual connect by level<= 4; 4 linhas criadas. SQL > SQL > COMMIT; Commit concluído. SQL > SQL > SQL > exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO(); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SELECT table_name, stale_stats, last_analyzed 2 FROM dba_tab_statistics 3 WHERE table_name='DBTW056'; TABLE_NAME STA LAST_ANA ---------------------------------------- --- -------- DBTW056 YES 18/10/18 SQL > |
Observe no select na visão DBA_TAB_COL_STATISTICS que o HISTOGRAMA foi criado na coluna CÓDIGO e no SELECT na visão DBA_TAB_STATISTICS que o campo STALE_STATS esta como YES o que indica que as estatísticas estão desatualizadas.
2.5) Executar a consulta novamente
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 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | SQL > ALTER SESSION SET optimizer_dynamic_sampling=11; Sessão alterada. SQL > SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* dbtw-056.3 */ * 2 FROM dbtw056 3 WHERE codigo=11; CODIGO PRODUTO ---------- -------------------------------------------- 11 DBTimeWizard - Oracle Performance and Tuning 11 DBTimeWizard - Oracle Performance and Tuning 11 DBTimeWizard - Oracle Performance and Tuning 11 DBTimeWizard - Oracle Performance and Tuning 11 DBTimeWizard - Oracle Performance and Tuning SQL > SQL > SQL > column sql_id new_value m_sql_id SQL > column child_number new_value m_child_no SQL > SQL > SELECT sql_id, child_number 2 FROM v$sql 3 WHERE sql_text LIKE '%dbtw-056.3%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ fnytft0gnc6s2 0 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')); antigo 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')) novo 2: FROM TABLE (dbms_xplan.display_cursor ('fnytft0gnc6s2', 0,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------- SQL_ID fnytft0gnc6s2, child number 0 ------------------------------------- SELECT /* dbtw-056.3 */ * FROM dbtw056 WHERE codigo=11 Plan hash value: 1201093745 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 | 5 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DBTW056 | 1 | 5 | 5 |00:00:00.01 | 5 | |* 2 | INDEX RANGE SCAN | DBTW056_CODIGO_IDX | 1 | 5 | 5 |00:00:00.01 | 3 | -------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CODIGO"=11) Note ----- - dynamic statistics used: dynamic sampling (level=AUTO) 23 linhas selecionadas. SQL > |
Analisando o plano de execução acima podemos constatar que o Otimizador estimou com precisão que seriam selecionadas 5 linhas na tabela, esta estimativa não foi obtida no HISTOGRAMA da coluna CÓDIGO pois foram inseridas 4 linhas após a coleta do HISTOGRAMA, ou seja, o recurso DYNAMIC SAMPLING foi utilizado novamente pois as estatísticas estavam desatualizadas.
CONCLUSÃO
O recurso DYNAMIC SAMPLING na versão 12cR2 ficou muito robusto, com ele o Otimizador passou a ser capaz de identificar várias situações onde as estatísticas dos objetos do banco são insuficientes para garantir uma estimativa de cardinalidade satisfatoria. Esta é apenas uma das muitas melhorias implementadas no Oracle 12cR2 visando melhorar a qualidade dos planos de execução gerados pelo Otimizador.
Referências
https://docs.oracle.com/en/database/oracle/oracle-database/12.2
