Como pode um plano de execução mudar de repente, quando ninguém fez qualquer alteração no banco de dados? Por nenhuma mudança, queremos dizer que não houve alterações na estrutura das tabelas, não foram adicionados ou alterados índices, nenhuma mudança relativa a "Bind Peeking", sem mudanças de parâmetros do banco, não…
O Otimizador do Oracle precisa da nossa ajuda?
As estatísticas sobre os dados de uma tabela são fundamentais para o Otimizador escolher qual a maneira mais eficiente de acessar esses dados, mas existem informações sobre os dados que não são coletadas de forma automática e portanto não estão disponíveis para o Otimizador sem que haja uma intervenção manual, o que causa em algumas situações a escolha de planos de execução ineficientes. Respondendo a questão proposta no titulo desse artigo: sim, em algumas situações especificas precisamos ajudar o Otimizador, coletando estatísticas adicionais.
Neste artigo vamos abordar as “Extended statistics”, vamos simular uma consulta onde o Otimizador não consegue estimar a cardinalidade da operação de forma correta e como consequência cria um plano de execução ineficiente.
Conceito “Extended statistics”
As “Extended statistics” foram implementadas no Banco Oracle para tratar dois tipos de situações onde as estatísticas existentes não fornecem informações suficientes para o Otimizador fazer uma boa estimativa da cardinalidade:
1. Quando existem múltiplos predicados em diferentes colunas na clausula WHERE de uma instrução SQL para uma tabela e existe algum tipo de relação entre essas colunas, por exemplo quando temos uma tabela de veículos automotores e nela temos duas colunas: Nome do veiculo e Fabricante. Nesse caso existe uma relação entre essas duas colunas pois os registros existentes na tabela de um determinado veiculo só podem ser de um determinado fabricante. (Ex: Uno/Fiat, HB20/Hyndai, Fiesta/Ford,etc..)
2. Quando um predicado utiliza uma expressão, por exemplo uma função do Oracle ou uma função criada pelo usuário.
Simulação “Extended statistics”
A seguir vamos simular a consulta de uma tabela onde duas colunas possuem um relacionamento e em função disso as estatísticas tradicionais não são capazes de fornecer as informações necessárias para o Otimizador fazer uma boa estimativa da cardinalidade da operação, na sequência vamos criar as “Extended statistics” e verificar que com esse tipo de informações o Otimizador vai melhorar significativamente a estimativa da cardinalidade e como consequência melhorar o plano de execução.
Criação da tabela
Primeiro vamos criar a tabela que utilizaremos na consulta da nossa simulação e popular com dados que tenham alguma relação entre duas colunas:
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 95 96 97 98 99 100 | 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 dbtw01 (id number, code1k number, code100 number, text varchar(40) ); Table created. SQL > SQL > DECLARE 2 3 v_id NUMBER := 1; 4 var_x NUMBER := 1; 5 l_num number; 6 7 BEGIN 8 9 FOR var1 IN 1..100 10 LOOP 11 var_x := 1; 12 FOR var2 IN 1..2 13 LOOP 14 l_num := round(dbms_random.value(100,0)); 15 insert into dbtw01 values(v_id, var_x, l_num, 'DBTimeWizard - Performance and Tuning'); 16 v_id := v_id + 1; 17 END LOOP; 18 var_x := 4; 19 FOR var2 IN 1..8 20 LOOP 21 l_num := round(dbms_random.value(100,0)); 22 insert into dbtw01 values(v_id, var_x, l_num, 'DBTimeWizard - Performance and Tuning'); 23 v_id := v_id + 1; 24 END LOOP; 25 var_x := 7; 26 FOR var2 IN 1..50 27 LOOP 28 l_num := round(dbms_random.value(100,0)); 29 insert into dbtw01 values(v_id, var_x, l_num, 'DBTimeWizard - Performance and Tuning'); 30 v_id := v_id + 1; 31 END LOOP; 32 var_x := 11; 33 FOR var2 IN 1..150 34 LOOP 35 l_num := round(dbms_random.value(100,0)); 36 insert into dbtw01 values(v_id, var_x, l_num, 'DBTimeWizard - Performance and Tuning'); 37 v_id := v_id + 1; 38 END LOOP; 39 var_x := 13; 40 FOR var2 IN 1..300 41 LOOP 42 l_num := round(dbms_random.value(100,0))+var_x; 43 insert into dbtw01 values(v_id, var_x, l_num, 'DBTimeWizard - Performance and Tuning'); 44 v_id := v_id + 1; 45 END LOOP; 46 var_x := 17; 47 FOR var2 IN 1..490 48 LOOP 49 l_num := round(dbms_random.value(100,0)); 50 insert into dbtw01 values(v_id, var_x, l_num, 'DBTimeWizard - Performance and Tuning'); 51 v_id := v_id + 1; 52 END LOOP; 53 var_x := 19; 54 FOR var2 IN 1..200 55 LOOP 56 insert into dbtw01 values(v_id, var_x, 35, 'DBTimeWizard - Performance and Tuning'); 57 v_id := v_id + 1; 58 END LOOP; 59 COMMIT; 60 END LOOP; 61 62 END; 63 / PL/SQL procedure successfully completed. SQL > SQL > create index dbtw01_idx on dbtw01(code100, code1k) compress 2; Index created. SQL > SQL > SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW01', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE SKEWONLY'); PL/SQL procedure successfully completed. SQL > SQL > select column_name, num_distinct, histogram from dba_tab_col_statistics where table_name='DBTW01'; COLUMN_NAME NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- TEXT 1 FREQUENCY CODE100 114 FREQUENCY CODE1K 7 FREQUENCY ID 120000 NONE SQL > |
Execução da consulta
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:
Agora vamos executar uma consulta que vai acessar a tabela criada na etapa anterior:
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 | SQL > ALTER SESSION SET statistics_level=ALL; Session altered. SQL > SQL > select /* dbtw101 */ id, code1k, code100, text 2 from dbtw01 3 where code1k=17 4 and code100=35; ID CODE1K CODE100 TEXT ---------- ---------- ---------- ---------------------------------------- 598 17 35 DBTimeWizard - Performance and Tuning 527 17 35 DBTimeWizard - Performance and Tuning 539 17 35 DBTimeWizard - Performance and Tuning .............. 119364 17 35 DBTimeWizard - Performance and Tuning 485 rows selected. 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 '%dbtw101%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 9sscfjtmkf8q2 0 SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 9sscfjtmkf8q2, child number 0 ------------------------------------- select /* dbtw101 */ id, code1k, code100, text from dbtw01 where code1k=:"SYS_B_0" and code100=:"SYS_B_1" Plan hash value: 658621498 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 273 (100)| | 485 |00:00:00.01 | 980 | |* 1 | TABLE ACCESS FULL| DBTW01 | 1 | 8486 | 406K| 273 (1)| 00:00:04 | 485 |00:00:00.01 | 980 | ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("CODE100"=:SYS_B_1 AND "CODE1K"=:SYS_B_0)) 19 rows selected. SQL > |
A consulta retornou 485 linhas e para tal realizou uma operação “TABLE ACCESS FULL”, o Otimizador escolheu esta operação pois ele estimou que teria que acessar 8.486 linhas e para essa quantidade de linhas o acesso utilizando um índice seria mais oneroso.
Utilizando “Extended statistics”
Agora vamos dar uma pequena ajuda ao Otimizador, para tal vamos criar as “Extended statistics” das duas colunas que estão sendo utilizadas na clausula WHERE e executar novamente a mesma consulta:
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 | SQL > SELECT DBMS_STATS.CREATE_EXTENDED_STATS(null,'dbtw01', '(code1k, code100)') FROM DUAL; DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'DBTW01','(CODE1K,CODE100)') --------------------------------------------------------------------------------------------------------------------------------------------------- SYS_STUGYIUDX9_XG#MKTWSLK2DJ54 SQL > SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW01', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE SKEWONLY'); PL/SQL procedure successfully completed. SQL > SQL > select column_name, num_distinct, histogram from dba_tab_col_statistics where table_name='DBTW01'; COLUMN_NAME NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- SYS_STUGYIUDX9_XG#MKTWSLK2DJ54 595 HEIGHT BALANCED TEXT 1 FREQUENCY CODE100 114 FREQUENCY CODE1K 7 FREQUENCY ID 120000 NONE SQL > SQL > select /* dbtw102 */ id, code1k, code100, text 2 from dbtw01 3 where code1k=17 4 and code100=35; ID CODE1K CODE100 TEXT ---------- ---------- ---------- ---------------------------------------- 598 17 35 DBTimeWizard - Performance and Tuning 527 17 35 DBTimeWizard - Performance and Tuning 539 17 35 DBTimeWizard - Performance and Tuning .......... 119364 17 35 DBTimeWizard - Performance and Tuning 485 rows selected. 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 '%dbtw102%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 59v6y95ffxr2y 0 SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 59v6y95ffxr2y, child number 0 ------------------------------------- select /* dbtw102 */ id, code1k, code100, text from dbtw01 where code1k=:"SYS_B_0" and code100=:"SYS_B_1" Plan hash value: 954539352 ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 78 (100)| | 485 |00:00:00.01 | 344 | | 1 | TABLE ACCESS BY INDEX ROWID| DBTW01 | 1 | 148 | 7400 | 78 (0)| 00:00:01 | 485 |00:00:00.01 | 344 | |* 2 | INDEX RANGE SCAN | DBTW01_IDX | 1 | 148 | | 1 (0)| 00:00:01 | 485 |00:00:00.01 | 35 | ------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CODE100"=:SYS_B_1 AND "CODE1K"=:SYS_B_0) 20 rows selected. SQL > |
A consulta retornou 485 linhas novamente e o Otimizador estimou que seriam 148 linhas, essa estimativa esta muito próxima do valor real, para essa quantidade de linhas a utilização de um índice é mais eficiente, por esta razão ele utilizou o índice DBTW01_IDX.
Conclusão
Quando comparamos a quantidade de buffers acessados pelas duas consultas, podemos constatar que a segunda consulta foi mais eficiente, pois enquanto na primeira consulta foram acessados 980 buffers, na segunda a quantidade caiu para 344 buffers.
A falha na estimativa da cardinalidade da operação pelo Otimizador é uma das principais razões que levam o Otimizador a criar planos de execução ineficientes.
As “Extended statistics” podem e devem ser utilizadas sempre que o Otimizador não consegue fazer uma boa estimativa da cardinalidade da operação, pois os predicados presentes na clausula WHERE de uma instrução SQL possuem algum relacionamento ou quando a instrução SQL utiliza função no predicado.
Referências
https://blogs.oracle.com/optimizer/entry/extended_statistics
https://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF94725
