Índice do fórum Treinamentos Avançados Treinamento Oracle Performance Diagnostics & Tuning Parse - CURSOR_SHARING

Parse - CURSOR_SHARING

Dúvidas, dicas e atualizações sobre o Treinamento Oracle Performance Diagnostics & Tuning.

Mensagem Ter Dez 17, 2013 11:07 pm

Mensagens: 1
Olá!

Analisando um cenário administrado por outro DBA, recebi a seguinte informação:

Para explicar, nesse ambiente Oracle RAC, há services fixando aplicações em um nó somente, então uma aplicação só roda em um nó a menos que esse nó seja perdido. (se o failover funcionar(se não estiver correto, me corrija)).

Então, o DBA analisou o que há de hard parses no banco. Como há muitos literais nos SQLs com falta de variáveis bind, o DBA resolveu alterar o parâmetro CURSOR_SHARING para FORCE até que o desenvolvimento melhore esses SQLs. Como já vimos no outro post sobre alterar CURSOR_SHARING para FORCE, pode ser perigoso, podendo mudar o valor das colunas, nesse cenário, poderíamos pensar em alterar CURSOR_SHARING para SIMILAR?

Desde já, agradeço!

Mensagem Qui Dez 26, 2013 10:16 am
portilho Site Admin

Mensagens: 482
Quanto ao Oracle RAC, você disse certo, e eu até prefiro configurar desta forma quando uma aplicação não pode ser dividida em Services.
Certamente tiveram que configurar desta forma pois um ou outro nó devia até reiniciar devido so problema dos Hard Parses.

Quanto ao CURSOR_SHARING, ambos valores diferentes do padrão (SIMILAR E FORCE) do CURSOR_SHARING possuem efeitos colaterais.

Como diz a documentação:
Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications, or applications that use stored outlines.
http://docs.oracle.com/cd/B28359_01/ser ... REFRN10025

Mas na versão 11.2, o SIMILAR foi descontinuado. Só existe o EXACT (padrão) e o FORCE.
http://docs.oracle.com/cd/E11882_01/ser ... REFRN10025

Os efeitos colaterais são:
Setting CURSOR_SHARING to FORCE has the following drawbacks:
- The database must perform extra work during the soft parse to find a similar statement in the shared pool.
- There is an increase in the maximum lengths (as returned by DESCRIBE) of any selected expressions that contain literals in a SELECT statement. However, the actual length of the data returned does not change.
- Star transformation is not supported.


Ou seja, é preciso homologar novamente a aplicação. Daria menos trabalho (sei que não é possível quando o código não está em nossas mãos) utilizar BINDs em SQLs repetitivos.

E vimos no Treinamento (Lab 6.2) que o CURSOR_SHARING=FORCE ajuda, mas não resolve.

E finalmente, a melhor parte da documentação:
he best practice is to write sharable SQL and use the default of EXACT for CURSOR_SHARING.
http://docs.oracle.com/cd/E11882_01/ser ... PFGRF94345

Mensagem Sex Jun 12, 2015 11:08 am

Mensagens: 0
Aproveitando o post, me corrijam se eu estiver errado:

O select abaixo mostra o cursores filhos a quantitdade de execuções, leituras e parses para um sql_id.
select sql_id,child_number,EXECUTIONS,LOADS,PARSE_CALLS from gv$sql
SQL_ID CHILD_NUMBER EXECUTIONS LOADS PARSE_CALLS
------------- ------------ ---------- ---------- -----------
4hxkxzpas5hyq 0 1 1 1
4hxkxzpas5hyq 1 1 1 1
4hxkxzpas5hyq 2 1 1 1

select version_count from v$sqlarea where sql_id='4hxkxzpas5hyq';

VERSION_COUNT
-------------
3

Isso motra que foram criadas três versões para o mesmo sql_id devido a coluna HASH_MATCH_FAILED (coluna H) estar como Y.

select hash_value,address from v$sqlarea where sql_text like 'select /*TEST_SIMILAR*/%';

HASH_VALUE ADDRESS
---------- ----------------
1560817556 0000000095B58FD8

select * from v$sql_shared_cursor where address='0000000095B58FD8';

SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER H P B
------------- ---------------- ---------------- ------------ - - -
2m80tr9fhhbwn 0000000095B58FD8 000000009160F6B8 0 N N N
2m80tr9fhhbwn 0000000095B58FD8 0000000091633E68 1 Y N N
2m80tr9fhhbwn 0000000095B58FD8 0000000091741E10 2 Y N N
2m80tr9fhhbwn 0000000095B58FD8 000000008DA39108 3 Y N N
2m80tr9fhhbwn 0000000095B58FD8 00000000947D5B30 4 Y N N

Já no segundo teste ele reutilizou:

select sql_id,child_number,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*TEST_SIMILAR*/%';

SQL_ID CHILD_NUMBER EXECUTIONS LOADS PARSE_CALLS
------------- ------------ ---------- ---------- -----------
68rgrrrpfas96 0 2 1 2
68rgrrrpfas96 1 1 1 1

SQL> select version_count from v$sqlarea where sql_id='68rgrrrpfas96';

VERSION_COUNT
-------------
2

SQL> select hash_value,address from v$sqlarea where sql_text like 'select /*TEST_SIMILAR*/%';

HASH_VALUE ADDRESS
---------- ----------------
3940901158 0000000096498EA0

SQL> select * from v$sql_shared_cursor where address='0000000096498EA0';

SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER H P B
------------- ---------------- ---------------- ------------ - - -
68rgrrrpfas96 0000000096498EA0 0000000099A374D0 0 N N N
68rgrrrpfas96 0000000096498EA0 0000000094546818 1 N N N

Me baseando na coluna HASH_MATCH_FAILED é o indicador para eu saber se o cursor_sharing está surtindo efeito num determinado SQL?

Mensagem Seg Jun 22, 2015 10:48 am
portilho Site Admin

Mensagens: 482
Não, o HASH_MATCH_FAILED ocorre quando é considerado que não é seguro (a reutilização pode alterar o sentido do SQL, ou o plano não será considerado otimizado) reutilizar o mesmo plano (ou seja, será criado outro CHILD) para um novo conjunto de literais que foi substituída por Bind pelo CURSOR_SHARING.

Mensagem Qui Jul 02, 2015 1:27 am

Mensagens: 0
Mas então como consigo verificar se o cursor_sharing está surtindo efeito ou não? Fiquei confuso agora.

Mensagem Qui Jul 02, 2015 10:02 am
portilho Site Admin

Mensagens: 482
Você pode verificar o EXECUTIONs e PARSE_CALLS da V$SQL, com um SELECT similar ao que está na página 70 da apostila de OTSQL.


Voltar para Treinamento Oracle Performance Diagnostics & Tuning

cron