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 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 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 | 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') 9 order by endpoint_value; COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ROW_COUNT ------------ --------------- -------------- ---------- COD 99800 0 99800 COD 99801 1 1 COD 99802 2 1 COD 99803 3 1 COD 99804 4 1 COD 99805 5 1 COD 99806 6 1 COD 99807 7 1 COD 99808 8 1 COD 99809 9 1 COD 99810 10 1 COD 99811 11 1 COD 99812 12 1 COD 99813 13 1 COD 99814 14 1 COD 99815 15 1 COD 99816 16 1 COD 99817 17 1 COD 99818 18 1 COD 99819 19 1 COD 99820 20 1 COD 99821 21 1 COD 99822 22 1 COD 99823 23 1 COD 99824 24 1 COD 99825 25 1 COD 99826 26 1 COD 99827 27 1 COD 99828 28 1 COD 99829 29 1 COD 99830 30 1 COD 99831 31 1 COD 99832 32 1 COD 99833 33 1 COD 99834 34 1 COD 99835 35 1 COD 99836 36 1 COD 99837 37 1 COD 99838 38 1 COD 99839 39 1 COD 99840 40 1 COD 99841 41 1 COD 99842 42 1 COD 99843 43 1 COD 99844 44 1 COD 99845 45 1 COD 99846 46 1 COD 99847 47 1 COD 99848 48 1 COD 99849 49 1 COD 99850 50 1 COD 99851 51 1 COD 99852 52 1 COD 99853 53 1 COD 99854 54 1 COD 99855 55 1 COD 99856 56 1 COD 99857 57 1 COD 99858 58 1 COD 99859 59 1 COD 99860 60 1 COD 99861 61 1 COD 99862 62 1 COD 99863 63 1 COD 99864 64 1 COD 99865 65 1 COD 99866 66 1 COD 99867 67 1 COD 99868 68 1 COD 99869 69 1 COD 99870 70 1 COD 99871 71 1 COD 99872 72 1 COD 99873 73 1 COD 99874 74 1 COD 99875 75 1 COD 99876 76 1 COD 99877 77 1 COD 99878 78 1 COD 99879 79 1 COD 99880 80 1 COD 99881 81 1 COD 99882 82 1 COD 99883 83 1 COD 99884 84 1 COD 99885 85 1 COD 99886 86 1 COD 99887 87 1 COD 99888 88 1 COD 99889 89 1 COD 99890 90 1 COD 99891 91 1 COD 99892 92 1 COD 99893 93 1 COD 99894 94 1 COD 99895 95 1 COD 99896 96 1 COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ROW_COUNT ------------ --------------- -------------- ---------- COD 99897 97 1 COD 99898 98 1 COD 99899 99 1 COD 99900 100 1 COD 99901 101 1 COD 99902 102 1 COD 99903 103 1 COD 99904 104 1 COD 99905 105 1 COD 99906 106 1 COD 99907 107 1 COD 99908 108 1 COD 99909 109 1 COD 99910 110 1 COD 99911 111 1 COD 99912 112 1 COD 99913 113 1 COD 99914 114 1 COD 99915 115 1 COD 99916 116 1 COD 99917 117 1 COD 99918 118 1 COD 99919 119 1 COD 99920 120 1 COD 99921 121 1 COD 99922 122 1 COD 99923 123 1 COD 99924 124 1 COD 99925 125 1 COD 99926 126 1 COD 99927 127 1 COD 99928 128 1 COD 99929 129 1 COD 99930 130 1 COD 99931 131 1 COD 99932 132 1 COD 99933 133 1 COD 99934 134 1 COD 99935 135 1 COD 99936 136 1 COD 99937 137 1 COD 99938 138 1 COD 99939 139 1 COD 99940 140 1 COD 99941 141 1 COD 99942 142 1 COD 99943 143 1 COD 99944 144 1 COD 99945 145 1 COD 99946 146 1 COD 99947 147 1 COD 99948 148 1 COD 99949 149 1 COD 99950 150 1 COD 99951 151 1 COD 99952 152 1 COD 99953 153 1 COD 99954 154 1 COD 99955 155 1 COD 99956 156 1 COD 99957 157 1 COD 99958 158 1 COD 99959 159 1 COD 99960 160 1 COD 99961 161 1 COD 99962 162 1 COD 99963 163 1 COD 99964 164 1 COD 99965 165 1 COD 99966 166 1 COD 99967 167 1 COD 99968 168 1 COD 99969 169 1 COD 99970 170 1 COD 99971 171 1 COD 99972 172 1 COD 99973 173 1 COD 99974 174 1 COD 99975 175 1 COD 99976 176 1 COD 99977 177 1 COD 99978 178 1 COD 99979 179 1 COD 99980 180 1 COD 99981 181 1 COD 99982 182 1 COD 99983 183 1 COD 99984 184 1 COD 99985 185 1 COD 99986 186 1 COD 99987 187 1 COD 99988 188 1 COD 99989 189 1 COD 99990 190 1 COD 99991 191 1 COD 99992 192 1 COD 99993 193 1 COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ROW_COUNT ------------ --------------- -------------- ---------- COD 99994 194 1 COD 99995 195 1 COD 99996 196 1 COD 99997 197 1 COD 99998 198 1 COD 99999 199 1 COD 100000 200 1 201 linhas selecionadas. SQL > |
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 > |
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/
