Efeito Colateral de CURSOR_SHARING = FORCE

O certo é fazer certo.

O certo é a aplicação utilizar Bind Variables em SQLs que são muito executados (geralmente OLTP, ou em uma carga de dados de BI / OLAP / DSS), para evitar uma grande quantidade de Hard Parse.

O parâmetro CURSOR_SHARING em FORCE (o padrão é EXACT) faz com que as literais sejam trocadas para variáveis Bind. Isto ajuda, mas não resolve – é simples provar que a utilização de Binds reais é mais rápida.

Além de não ser tão eficiente, o CURSOR_SHARING tem pelo menos um problema – como ele troca TODAS as literais para Bind, ele troca também as que influenciam o tamanho do campo retornado, como no caso abaixo com SUSTR. Os dados retornam corretamente, mas o tamanho da coluna, o FORMAT, não. Esta situação é descrita na nota do MOS 462520.1, e pode acontecer com o uso de SUBSTR, LPAD, RPAD, or TO_CHAR.

Isto acontece porque agora o tamanho da coluna retornada agora é desconhecido para o Oracle durante a compilação (o número 1 foi trocado por algo como :SYS_B1).

Isto pode ser um problema para sua aplicação, que pode não ter sido preparada para lidar com isto. Siga a regra de ouro: teste.

Repare abaixo no tamanho do campo retornado em relação ao resultado.

SQL> ALTER SESSION SET CURSOR_SHARING = EXACT;

SessÒo alterada.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

Sistema alterado.

SQL> SELECT SUBSTR(C1, 1, 1) C1 FROM T1;

C1
----
1

SQL> SELECT SUBSTR(C1, 1, 2) C1 FROM T1;

C1
--------
12

SQL> SELECT SUBSTR(C1, 1, 3) C1 FROM T1;

C1
------------
123

SQL> SELECT SUBSTR(C1, 1, 4) C1 FROM T1;

C1
----------------
1234

SQL> SELECT SUBSTR(C1, 1, 5) C1 FROM T1;

C1
--------------------
12345

SQL> SELECT SUBSTR(C1, 1, 6) C1 FROM T1;

C1
------------------------
123456

SQL> SELECT SUBSTR(C1, 1, 7) C1 FROM T1;

C1
----------------------------
1234567

SQL> SELECT SUBSTR(C1, 1, 8) C1 FROM T1;

C1
--------------------------------
12345678

SQL> SELECT SUBSTR(C1, 1, 9) C1 FROM T1;

C1
------------------------------------
123456789

SQL> SELECT SUBSTR(C1, 1, 10) C1 FROM T1;

C1
----------------------------------------
1234567890

SQL> SELECT SUBSTR(C1, 1, 9) C1 FROM T1;

C1
------------------------------------
123456789

SQL> SELECT SUBSTR(C1, 1, 8) C1 FROM T1;

C1
--------------------------------
12345678

SQL> SELECT SUBSTR(C1, 1, 7) C1 FROM T1;

C1
----------------------------
1234567

SQL> SELECT SUBSTR(C1, 1, 6) C1 FROM T1;

C1
------------------------
123456

SQL> SELECT SUBSTR(C1, 1, 5) C1 FROM T1;

C1
--------------------
12345

SQL> SELECT SUBSTR(C1, 1, 4) C1 FROM T1;

C1
----------------
1234

SQL> SELECT SUBSTR(C1, 1, 3) C1 FROM T1;

C1
------------
123

SQL> SELECT SUBSTR(C1, 1, 2) C1 FROM T1;

C1
--------
12

SQL> SELECT SUBSTR(C1, 1, 1) C1 FROM T1;

C1
----
1

SQL> ALTER SESSION SET CURSOR_SHARING = FORCE;

SessÒo alterada.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

Sistema alterado.

SQL> SELECT SUBSTR(C1, 1, 1) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1

SQL> SELECT SUBSTR(C1, 1, 2) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
12

SQL> SELECT SUBSTR(C1, 1, 3) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
123

SQL> SELECT SUBSTR(C1, 1, 4) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1234

SQL> SELECT SUBSTR(C1, 1, 5) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
12345

SQL> SELECT SUBSTR(C1, 1, 6) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
123456

SQL> SELECT SUBSTR(C1, 1, 7) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1234567

SQL> SELECT SUBSTR(C1, 1, 8) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
12345678

SQL> SELECT SUBSTR(C1, 1, 9) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
123456789

SQL> SELECT SUBSTR(C1, 1, 10) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1234567890

SQL> SELECT SUBSTR(C1, 1, 9) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
123456789

SQL> SELECT SUBSTR(C1, 1, 8) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
12345678

SQL> SELECT SUBSTR(C1, 1, 7) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1234567

SQL> SELECT SUBSTR(C1, 1, 6) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
123456

SQL> SELECT SUBSTR(C1, 1, 5) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
12345

SQL> SELECT SUBSTR(C1, 1, 4) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1234

SQL> SELECT SUBSTR(C1, 1, 3) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
123

SQL> SELECT SUBSTR(C1, 1, 2) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
12

SQL> SELECT SUBSTR(C1, 1, 1) C1 FROM T1;

C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1

SQL>

6 comments

  1. Bom dia Portilho,

    Excelente post, como sempre.
    É aquela história se fosse a melhor opção já seria configuração padrão.

    Abs.

  2. Bom dia, Portilho.

    Tenho uma aplicação A que não faz uso de binds, portanto o Oracle foi configurado com CURSOR_SHARING=FORCE (via parâmetro). Agora tenho outra aplicação B que vai acessar o mesmo BD, mas esta app faz o uso de binds.
    Faz algum sentido eu mudar o parâmetro do banco para CURSOR_SHARING=EXACT e colocar uma trigger de logon para aplicação A mudando o parâmetro da sessão para CURSOR_SHARING=FORCE ?
    Qual seria a melhor opção para este cenário ?

    Obrigado,

    1. A melhor opção seria utilizar BINDs. 🙂
      Como imagino que não é possível, faz sentido sim mudar o padrão para EXACT, e ter a Trigger de Logon alterando para FORCE apenas para a aplicação A.

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.