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…
Por que minha consulta esta alternando planos de execução diferentes?
A utilização de BIND VARIABLE é muito recomendada quando enviamos instruções SQL para execução no banco de dados Oracle pois esta prática evita que o Otimizador tenha que realizar um quantidade excessiva de HARD PARSE o que evita o consumo desnecessário de CPU. No entanto quando utilizamos BIND VARIABLE num filtro da cláusula WHERE cuja coluna da tabela possui um HISTOGRAM, esta combinação pode levar o Otimizador a criar planos de execução diferentes para garantir o desempenho da instrução SQL dependendo do valor passado na BIND VARIABLE.
Este recurso do Oracle é conhecido como ADAPTIVE CURSOR SHARING e neste artigo vamos simular este mecanismo para que você entenda como ele funciona.
Para realizar esta simulação vamos seguir as etapas abaixo:
1) Criar os objetos que serão utilizados na simulação
2) Executar uma consulta e verificar o processo de PARSE
3) Executar a mesma consulta com valor diferente na BIND VARIABLE
4) Executar a mesma consulta com um terceiro valor na BIND VARIABLE e verificar o processo de PARSE
5) Executar a mesma consulta com o valor da primeira execução na BIND VARIABLE e verificar o processo de PARSE
1) Criar os objetos para a simulação
Vamos criar os objetos necessários para realizar a nossa simulação e 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 42 43 44 | SQL > SELECT * FROM V$VERSION where rownum < 2; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SQL > SQL > drop table dbtw060 purge; Tabela eliminada. SQL > SQL > create table dbtw060 as 2 select case when level <= 99800 then 0 else level-99800 end cod, 3 'DBTimeWizard - Oracle Performance and Tuning' as prod 4 from dual 5 connect by level <= 100000; Tabela criada. SQL > SQL > create index dbtw060_idx on dbtw060(cod); Índice criado. SQL > SQL > exec dbms_stats.gather_table_stats(user, 'DBTW060', method_opt => 'for columns cod size 254', cascade => true); Procedimento PL/SQL concluído com sucesso. SQL > SQL > -- O histograma mostra como o Oracle fez esta distribuição de dados: SQL > SQL > col COLUMN_NAME for a12 SQL > col ENDPOINT_ACTUAL_VALUE for a12 SQL > SQL > select column_name, 2 endpoint_number, 3 endpoint_value, 4 endpoint_number - nvl(endpoint_number_prev,0) row_count 5 from (select column_name, endpoint_number, endpoint_value, lag(endpoint_number,1) over (order by endpoint_number) endpoint_number_prev 6 from user_tab_histograms 7 where table_name='DBTW060' 8 and column_name='COD' |
Na consulta acima na visão USER_TAB_HISTOGRAMS podemos verificar que foi gerado um histograma na coluna COD da tabela DBTW060 e o valor “0” tem 99800 repetições, enquanto os demais valores aparecem somente uma vez.
2) Executar uma consulta e verificar o processo de PARSE
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 > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > variable B1 number; SQL > exec :B1 := 0; Procedimento PL/SQL concluído com sucesso. SQL > SQL > SQL > SELECT /* dbtw-060 */ count(prod) 2 FROM dbtw060 3 WHERE cod=:B1; COUNT(PROD) ----------- 99800 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-060%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 2k4sn0mffnf17 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 ('2k4sn0mffnf17', 0,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 2k4sn0mffnf17, child number 0 ------------------------------------- SELECT /* dbtw-060 */ count(prod) FROM dbtw060 WHERE cod=:B1 Plan hash value: 2118012488 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 725 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 725 | |* 2 | TABLE ACCESS FULL| DBTW060 | 1 | 99800 | 99800 |00:00:00.01 | 725 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("COD"=:B1) 19 linhas selecionadas. SQL > SQL > col SQL_TEXT for a70 SQL > col BIND_SENS for a10 SQL > col BIND_AWARE for a10 SQL > col SHARABLE for a10 SQL > SELECT sql_text, 2 child_number AS child#, 3 executions AS exec, 4 buffer_gets AS buff_gets, 5 is_bind_sensitive AS bind_sens, 6 is_bind_aware AS bind_aware, 7 is_shareable AS sharable 8 FROM v$sql 9 WHERE sql_id='&m_sql_id'; antigo 9: WHERE sql_id='&m_sql_id' novo 9: WHERE sql_id='2k4sn0mffnf17' SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- SELECT /* dbtw-060 */ count(prod) FROM dbtw060 WHERE cod=:B1 0 1 829 Y N Y 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:
Na primeira execução da instrução SQL o Oracle realiza um HARD PARSE e durante este processo ele executa as seguintes tarefas:
1. Gera um plano de execução considerando a cardinalidade do valor informado na BIND VARIABLE (No nosso exemplo o plano de execução faz acesso a tabela usando a operação TABLE ACCESS FULL pois a cardinalidade do valor “0” é 99800 linhas)
2. Marca o curso como BIND-SENSITIVE, um cursor BIND-SENSITIVE é aquele em que o plano de execução ideal depende do valor da BIND VARIABLE
3. Armazena metadados sobre o predicado incluindo a cardinalidade dos valores vinculados
3) Executar a mesma consulta com valor diferente na BIND VARIABLE
Vamos repetir a execução da consulta utilizada anteriormente, só que desta vez vamos passar o valor da BIND VARIABLE como “2”.
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 > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > variable B1 number; SQL > exec :B1 := 2; Procedimento PL/SQL concluído com sucesso. SQL > SQL > SQL > SELECT /* dbtw-060 */ count(prod) 2 FROM dbtw060 3 WHERE cod=:B1; COUNT(PROD) ----------- 1 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-060%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 2k4sn0mffnf17 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 ('2k4sn0mffnf17', 0,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 2k4sn0mffnf17, child number 0 ------------------------------------- SELECT /* dbtw-060 */ count(prod) FROM dbtw060 WHERE cod=:B1 Plan hash value: 2118012488 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 725 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 725 | |* 2 | TABLE ACCESS FULL| DBTW060 | 1 | 99800 | 1 |00:00:00.01 | 725 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("COD"=:B1) 19 linhas selecionadas. SQL > SQL > col SQL_TEXT for a70 SQL > col BIND_SENS for a10 SQL > col BIND_AWARE for a10 SQL > col SHARABLE for a10 SQL > SELECT sql_text, 2 child_number AS child#, 3 executions AS exec, 4 buffer_gets AS buff_gets, 5 is_bind_sensitive AS bind_sens, 6 is_bind_aware AS bind_aware, 7 is_shareable AS sharable 8 FROM v$sql 9 WHERE sql_id='&m_sql_id'; antigo 9: WHERE sql_id='&m_sql_id' novo 9: WHERE sql_id='2k4sn0mffnf17' SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- SELECT /* dbtw-060 */ count(prod) FROM dbtw060 WHERE cod=:B1 0 2 1554 Y N Y SQL > |
Na segunda execução da instrução SQL com outro valor na BIND VARIABLE o Oracle realiza um SOFT PARSE e utiliza o plano de execução gerado na etapa anterior e que ficou armazenado em um cursor na LIBRARY CACHE.
Após a execução da instrução SQL podemos verificar que o cursor BIND-SENSITIVE acumulou as estatísticas das duas execuções.
4) Executar a mesma consulta com um terceiro valor na BIND VARIABLE
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 | SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > variable B1 number; SQL > exec :B1 := 6; Procedimento PL/SQL concluído com sucesso. SQL > SQL > SQL > SELECT /* dbtw-060 */ count(prod) 2 FROM dbtw060 3 WHERE cod=:B1; COUNT(PROD) ----------- 1 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-060%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 2k4sn0mffnf17 0 2k4sn0mffnf17 1 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 ('2k4sn0mffnf17', 1,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 2k4sn0mffnf17, child number 1 ------------------------------------- SELECT /* dbtw-060 */ count(prod) FROM dbtw060 WHERE cod=:B1 Plan hash value: 3248072391 ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | 2 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | 2 | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| DBTW060 | 1 | 1 | 1 |00:00:00.01 | 3 | 2 | |* 3 | INDEX RANGE SCAN | DBTW060_IDX | 1 | 1 | 1 |00:00:00.01 | 2 | 2 | ----------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("COD"=:B1) 20 linhas selecionadas. SQL > SQL > col SQL_TEXT for a70 SQL > col BIND_SENS for a10 SQL > col BIND_AWARE for a10 SQL > col SHARABLE for a10 SQL > SELECT sql_text, 2 child_number AS child#, 3 executions AS exec, 4 buffer_gets AS buff_gets, 5 is_bind_sensitive AS bind_sens, 6 is_bind_aware AS bind_aware, 7 is_shareable AS sharable 8 FROM v$sql 9 WHERE sql_id='&m_sql_id'; antigo 9: WHERE sql_id='&m_sql_id' novo 9: WHERE sql_id='2k4sn0mffnf17' SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- SELECT /* dbtw-060 */ count(prod) FROM dbtw060 WHERE cod=:B1 0 2 1554 Y N N SELECT /* dbtw-060 */ count(prod) FROM dbtw060 WHERE cod=:B1 1 1 3 Y Y Y SQL > |
Na terceira execução da instrução SQL o banco compara as estatísticas da segunda execução com as estatísticas da primeira execução e verifica que a cardinalidade para os valores das BIND VARIABLES informadas foram diferentes e ao verificar que o valor da BIND VARIABLE desta execução tem uma cardinalidade semelhante ao da segunda execução ele decide criar um novo plano de execução que tenha uma desempenho melhor para este valor da BIND VARIABLE.
Quando consultamos a visão dinâmica V$SQL podemos constatar que agora existem dois cursores para a nossa consulta e o campo BIND-AWARE do segundo cursor esta ativo, já o primeiro cursor esta com o campo SHARABLE=N indicando que este cursor não será mais utilizado nas próximas execuções.
5) Executar a mesma consulta com o valor da primeira execução na BIND VARIABLE
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 | SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > variable B1 number; SQL > exec :B1 := 0; Procedimento PL/SQL concluído com sucesso. SQL > SQL > SQL > SELECT /* dbtw-060 */ count(prod) 2 FROM dbtw060 3 WHERE cod=:B1; COUNT(PROD) ----------- 99800 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-060%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ------------- ------------ 2k4sn0mffnf17 0 2k4sn0mffnf17 1 2k4sn0mffnf17 2 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 ('2k4sn0mffnf17', 2,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 2k4sn0mffnf17, child number 2 ------------------------------------- SELECT /* dbtw-060 */ count(prod) FROM dbtw060 WHERE cod=:B1 Plan hash value: 2118012488 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 725 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 725 | |* 2 | TABLE ACCESS FULL| DBTW060 | 1 | 99800 | 99800 |00:00:00.01 | 725 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("COD"=:B1) 19 linhas selecionadas. SQL > SQL > col SQL_TEXT for a70 SQL > col BIND_SENS for a10 SQL > col BIND_AWARE for a10 SQL > col SHARABLE for a10 SQL > SELECT sql_text, 2 child_number AS child#, 3 executions AS exec, 4 buffer_gets AS buff_gets, 5 is_bind_sensitive AS bind_sens, 6 is_bind_aware AS bind_aware, 7 is_shareable AS sharable 8 FROM v$sql 9 WHERE sql_id='&m_sql_id'; antigo 9: WHERE sql_id='&m_sql_id' novo 9: WHERE sql_id='2k4sn0mffnf17' SQL_TEXT CHILD# EXEC BUFF_GETS BIND_SENS BIND_AWARE SHARABLE ---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- SELECT /* dbtw-060 */ count(prod) FROM dbtw060 WHERE cod=:B1 0 2 1554 Y N N SELECT /* dbtw-060 */ count(prod) FROM dbtw060 WHERE cod=:B1 1 1 3 Y Y Y SELECT /* dbtw-060 */ count(prod) FROM dbtw060 WHERE cod=:B1 2 1 725 Y Y Y SQL > |
Na quarta execução da instrução SQL vamos utilizar novamente o valor “0” na BIND VARIABLE, a cardinalidade para este valor não é adequada ao cursor utilizado na execução anterior e como o primeiro cursor foi marcado como não utilizável o banco vai criar um novo plano de execução e um novo cursor para este valor de BIND VARIABLE.
Quando consultamos a visão dinâmica V$SQL podemos constatar que agora existem três cursores para a nossa consulta e o campo BIND-AWARE do segundo e terceiro cursor estão ativos.
CONCLUSÃO
O recurso ADAPTIVE CURSOR SHARING permite que o Oracle utilize um grupo de planos de execução diferentes para garantir o desempenho da instrução SQL dependendo do valor da BIND VARIABLE e suas estatísticas de HISTOGRAMS.
REFERÊNCIAS
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/
Uma dúvida : O curso do cursor_sharing=force, beneficia uma aplicação que faz pouco uso de BIND ?
Olá Jorge Luiz,
se entendi direito a sua pergunta, você que saber se o cursor_sharing=force traz algum beneficio para aplicações que passam as variáveis literalmente nas instruções SQL e não utilizam BIND VARIABLE, neste caso se o parâmetro cursor_sharing estiver definido como EXACT que é o padrão de instalação você vai ter um consumo de recurso do banco desnecessário toda vez que estas instruções forem executadas pois para cada execução será realizado um HARD PARSE, e se o volume de instruções nessa situação for alto você pode ter contenção de CPU no servidor do banco, pois o HARD PARSE consome CPU. Neste caso a utilização do cursor_sharing=force vai transformar as variáveis literais em BIND VARIABLE e com isso você eliminará os HARD PARSE desnecessários e você terá um ganho desempenho nas instruções SQL e diminuirá o load de CPU do servidor de banco.
Obrigado por compartilhar a sua dúvida, espero te-la esclarecido, se não fique a vontade para me questionar.
Um forte abraço,
Valter Aquino
Olá Jorge,
O curso do cursor_sharing=force pode beneficiar uma aplicação que não usa BIND e passa as variáveis nas instruções SQL como literais, pois esta opções do cursor_sharing transforma as variáveis literais em BIND antes do processo de parse, evitando assim que sejam realizados Hard parses desnecessários.
Um forte abraço,
Valter Aquino