Bom, pra não ficar só no blablablá vou te passar um exemplo que usei num
Treinamento particular que dei há um tempo atrás - no caso o Treinamento foi em
11gR2 SE mas o conceito Funciona em outras editions/versões :
BIND PEEKING : porém, as BIND VARIABLEs abrem um outro senão, um Outro Ponto
possível de divergência no que tange a performance : como vimos anteriormente,
o Otimizador precisa conhecer os VALORES informados como argumentos de
filtro/pesquisa de colunas no momento em que o PARSE inicial é feito e o plano
de execução é montado.. Para isso foi introduzido no banco 9i o conceito de
BIND PEEKING, ie : o Otimizador espera até que o SQL seja efetivamente
executado para só então capturar o valor informado nas BINDs e aí sim montar e
otimizar o Plano de execução - isso pode levar a uma mínima demora na primeira
vez que o SQL é executado mas o tempo demonstrou que esse overhead é mínimo..
As próximas execuções, OBVIAMENTE, vão simplesmente reaproveitar esse mesmo
plano, seja quais forem os valores informados para a BIND variable... VIA DE
REGRA isso funciona bem (principalmente com acesso a registros via chave
completa, já que uma chave completa ocorre só uma vez na tabela, assim qualquer
valor chave retorna exatamente 1 registro), MAS para acesso com chave parcial
(ou com colunas indexadas e com histogramas mas que não são chave) isso pode
causar problemas... E não, não é viável refazer o Plano de Execução / reparsear
o SQL a cada nova execução : o RDBMS Oracle é programado/arquitetado para poder
servir a dezenas (ou mesmo centenas) de usuários simultâneos, e potencialmente
todos eles podem estar executando SQLs com binds, o overhead impostos a cada
Execução seria inviável....
Exemplo de SQL sujeito a issues de BIND VARIABLE PEEKING :
=> Setup, criando uma tabela com dados mal-distribuídos :
SYSTEM:@O11GR2SE:SQL>DROP TABLE acs_test_tab;
DROP TABLE acs_test_tab
*
ERRO na linha 1:
ORA-00942: a tabela ou view não existe
SYSTEM:@O11GR2SE:SQL>
SYSTEM:@O11GR2SE:SQL>CREATE TABLE acs_test_tab (
2 id NUMBER,
3 record_type NUMBER,
4 description VARCHAR2(50),
5 CONSTRAINT acs_test_tab_pk PRIMARY KEY (id)
6 );
Tabela criada.
SYSTEM:@O11GR2SE:SQL>CREATE INDEX acs_test_tab_record_type_i ON
acs_test_tab(record_type);
Índice criado.
==> vamos fazer a tabela ter uma distribuição Irregular de dados na coluna
RECORD_TYPE, com qtdades diferentes de linhas para RECORD_TYPE=2 (50000) e para
outros valores possíveis da coluna haverá apenas um registro :
SYSTEM:@O11GR2SE:SQL>DECLARE
2 TYPE t_acs_test_tab IS TABLE OF acs_test_tab%ROWTYPE;
3 l_tab t_acs_test_tab := t_acs_test_tab();
4
5 BEGIN
6 FOR i IN 1 .. 100000 LOOP
7 l_tab.extend;
8 IF MOD(i,2)=0 THEN
9 l_tab(l_tab.last).record_type := 2;
10 ELSE
11 l_tab(l_tab.last).record_type := i;
12 END IF;
13
14 l_tab(l_tab.last).id := i;
15 l_tab(l_tab.last).description := 'Description for ' || i;
16 END LOOP;
17 --
18 FORALL i IN l_tab.first .. l_tab.last
19 INSERT INTO acs_test_tab VALUES l_tab(i);
20
21 COMMIT;
22 END;
23 /
Procedimento PL/SQL concluído com sucesso.
==> coelta de estatísticas :
SYSTEM:@O11GR2SE:SQL>EXEC DBMS_STATS.gather_table_stats(USER, 'acs_test_tab',
method_opt=>'for all indexed columns size skewonly', cascade=>TRUE);
Procedimento PL/SQL concluído com sucesso.
SYSTEM:@O11GR2SE:SQL>SELECT column_name, histogram FROM user_tab_cols WHERE
table_name = 'ACS_TEST_TAB';
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
ID NONE
RECORD_TYPE HEIGHT BALANCED
DESCRIPTION NONE
==> O método de coleta usado ao invés de histogramas de tamanho fixo vai criar
histogramas de tamanho variável cfrme a distribuição de dados em cada coluna
seja regular ou irregular ... Executando o SQL :
SYSTEM:@O11GR2SE:SQL>VARIABLE l_record_type NUMBER;
SYSTEM:@O11GR2SE:SQL>EXEC :l_record_type := 1;
Procedimento PL/SQL concluído com sucesso.
SYSTEM:@O11GR2SE:SQL>
SYSTEM:@O11GR2SE:SQL>SELECT MAX(id) FROM acs_test_tab WHERE record_type =
:l_record_type;
MAX(ID)
----------
1
SYSTEM:@O11GR2SE:SQL>SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 3987223107
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 2 (100)| |
| 1 | SORT AGGREGATE | | 1 |
9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 |
9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 |
| 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RECORD_TYPE"=:L_RECORD_TYPE)
20 linhas selecionadas.
==> Esse plano é Ótimo, pois para o RECORD_TYPE=1 realmente sei que só exite
uma linha mesmo, é Eficiente buscar poucas linhas via índice...Porém, como está
ativo o BIND PEEKING, este é o Plano que será usado daqui pra frente, mesmo que
outros valores sejam informados :
SYSTEM:@O11GR2SE:SQL>EXEC :l_record_type := 2;
Procedimento PL/SQL concluído com sucesso.
SYSTEM:@O11GR2SE:SQL>
SYSTEM:@O11GR2SE:SQL>SELECT MAX(id) FROM acs_test_tab WHERE record_type =
:l_record_type;
MAX(ID)
----------
100000
SYSTEM:@O11GR2SE:SQL>SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 3987223107
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 2 (100)| |
| 1 | SORT AGGREGATE | | 1 |
9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 |
9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 |
| 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RECORD_TYPE"=:L_RECORD_TYPE)
20 linhas selecionadas.
SYSTEM:@O11GR2SE:SQL>
===> Este Plano está completamente mal-direcionado, nós SABEMOS como um fato
que Existem mais de uma linha para RECORD_TYPE=2...
O que se pode fazer ? Até a versão 10g não havia solução direta, nós
poderíamos :
a) forçar um HARD PARSE (e portanto um PEEK nas BIND VARIABLES) a cada
execução, forçando o aplicativo a cada vez enviar o texto do SQL sujeito a bind
issues com um pequena alteração, um comentário talvez que seja
ou
b) zerar TODO o cache de SQLs (e portanto zerar também o cache de planos) via
FLUSH com a sintaxe ALTER SYSTEM FLUSH SHARED_POOL;
ou
c) remover esse dado SQL bind sensitive (e consequentemente seu Plano de
Execução) do cache, com o comando DBMS_SHARED_POOL.PURGE - vide no site de
Suporte (pago) Oracle metalink/My Oracle Support a nota "How To Flush an Object
Out The Library Cache [SGA] Using The DBMS_SHARED_POOL Package" (Doc ID
457309.1)
ou
d) INVALIDAR esse SQL (e portanto seus planos), forçando um novo PARSE na
próxima execução : isso pode ser feito coletando novas estatísticas via
DBMS_STATS com o parâmetro NO_INVALIDATE marcado como FALSE, OU fazendo uma
alteração simples de estrutura na tabela que não altere os dados (um simples
COMMENT ON TABLE nomedatabela IS ''; já é suficiente) : vide o artigo para
mais refs/exemplos
ou
e) desativar o BIND variable peeking : isso pode ser feito ativando o
parâmetro '_optim_peek_user_binds' (com Aprovação do Suporte Oracle, já que é
um parâmetro interno / "oculto")
ou
f) forçar o Plano a ser sempre o mesmo não importando o valor dos BINDs, seja
desativando Histogramas (não os coletando, ou os coletando-os com SIZE 1), seja
fazendo o plano ser sempre o mesmo com a introdução de hints
(Para detalhamento destas opções até a versão 10g, a ref é o artigo
https://blog.pythian.com/stabilize-oracle-10gs-bind-peeking-behaviour/)
No 11g em diante, porém, passamos a ter a figura do Adaptive Cursor Sharing :
com esse recurso, o RDBMS Oracle já 'marcou' o SQL em questão como
BIND-SENSITIVE :
SYSTEM:@O11GR2SE:SQL>SELECT sql_id, child_number, is_bind_sensitive,
is_bind_aware
2 FROM v$sql
3 WHERE sql_text = 'SELECT MAX(id) FROM acs_test_tab WHERE record_type =
:l_record_type';
SQL_ID CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf 0 Y N
SYSTEM:@O11GR2SE:SQL>
==> na próxima Execução um SQL marcado como BIND-SENSITIVE (ou BIND AWARE, na
terminologia Oracle) Automaticamente vai re-analisar Histogramas e
potencialmente re-escrever (para melhor, espera-se) o Plano de Execução :
SYSTEM:@O11GR2SE:SQL>SELECT MAX(id) FROM acs_test_tab WHERE record_type =
:l_record_type;
MAX(ID)
----------
100000
SYSTEM:@O11GR2SE:SQL>SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 1
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 509473618
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 138 (100)|
|
| 1 | SORT AGGREGATE | | 1 | 9 | |
|
|* 2 | TABLE ACCESS FULL| ACS_TEST_TAB | 48819 | 429K| 138 (1)|
00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RECORD_TYPE"=:L_RECORD_TYPE)
19 linhas selecionadas.
SYSTEM:@O11GR2SE:SQL>
==> Agora sim : o Plano reconheceu que há muito mais que uma linha, E um FULL
TABLE SCAN vai fazer muito menos chamadas ao sub-sistema de I/O, já solicitando
o máximo de blocos que o SO aceita a cada chamada, potencialmente completando a
tarefa de ler a tabela TODA muito mais rapidamente...
È importante notar que esse novo Plano NÂO SUBSTITUIU o Plano original, o
RDBMS reconheceu que é uma derivação do plano original - assim ele criou o que
se chama de CHILD CURSOR, uma nova entrada na cache para manter o novo plano
para este valor específico de BIND :
SYSTEM:@O11GR2SE:SQL>SELECT sql_id, child_number, is_bind_sensitive,
is_bind_aware
2 FROM V$SQL
3* WHERE sql_text = 'SELECT MAX(id) FROM acs_test_tab WHERE record_type =
:l_record_type'
SYSTEM:@O11GR2SE:SQL>/
SQL_ID CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf 0 Y N
9bmm6cmwa8saf 1 Y Y
SYSTEM:@O11GR2SE:SQL>
SYSTEM:@O11GR2SE:SQL>SELECT * FROM
table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'9bmm6cmwa8saf', CURSOR_CHILD_NO=>0,
FORMAT=>'ALLSTATS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 3987223107
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RECORD_TYPE"=:L_RECORD_TYPE)
Note
-----
- Warning: basic plan statistics not available. These are only collected
when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system
level
26 linhas selecionadas.
SYSTEM:@O11GR2SE:SQL>SELECT * FROM
table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'9bmm6cmwa8saf', CURSOR_CHILD_NO=>1,
FORMAT=>'ALLSTATS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 1
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 509473618
----------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| ACS_TEST_TAB | 48819 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RECORD_TYPE"=:L_RECORD_TYPE)
Note
-----
- Warning: basic plan statistics not available. These are only collected
when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system
level
25 linhas selecionadas.
SYSTEM:@O11GR2SE:SQL>
Adicionalmente, no 11g também foram introduzidas as hints BIND_AWARE e
NO_BIND_AWARE, para já 'marcar' logo de cara um dado SQL como bind-sensitive...
==> Olhando a captura de binds, que é a sua dúvida :
SYSTEM:@O11GR2SE:SQL>alter session set NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss';
Sessão alterada.
SYSTEM:@O11GR2SE:SQL>select child_number, name, value_string, last_captured
from V$sql_bind_capture
2 where sql_id='9bmm6cmwa8saf';
CHILD_NUMBER NAME
------------ ------------------------------
VALUE_STRING
------------------------------------------------------------------------------------------------------------------
LAST_CAPTURED
-------------------
1 :L_RECORD_TYPE
2
18/09/2018 14:48:10
0 :L_RECORD_TYPE
1
18/09/2018 14:46:41
SYSTEM:@O11GR2SE:SQL>
===> Captou a mensagem, friendão ??? SIM, podem haver para o MESMO EXATO SQL_ID
mais de uma data de captura, entre outras causas se houver CHILD CURSORS, yes
????
[]s
Chiappa
