SESSION_CACHED_CURSORS: Será que você realmente conhece a importância desse parâmetro?

fevereiro 28, 2016 V. J.A. Cost Base Optimizer

Existe muita confusão com relação a definição dos parâmetros OPEN_CURSORS e SESSION_CACHED_CURSORS, o entendimento da função de cada um deles é fundamental para o ajuste de desempenho da instância Oracle. Nesse post vamos abordar o papel de cada um desses parâmetros e como eles influenciam o desempenho das instruções SQL, vamos ver num arquivo trace 10046 o parâmetro SESSION_CACHED_CURSORS em ação.

Parâmetro OPEN_CURSORS

Cursores abertos ocupam espaço na LIBRARY CACHE da SHARED POOL, o objetivo principal do parâmetro OPEN_CURSORS é definir um limite de cursores que podem ser abertos ao mesmo tempo para uma sessão, dessa forma ele impede que uma sessão encha a LIBRARY CACHE, ou consuma toda a CPU com milhões de requisições de PARSE.

Por exemplo, se o parâmetro OPEN_CURSORS for definido com o valor 200, para cada sessão podemos ter até 200 cursores abertos ao mesmo tempo. Se uma sessão atingir esse numero de cursores abertos e tentar abrir mais um, ela receberá o erro ORA-1000.

Por padrão o parâmetro OPEN_CURSORS vem definido igual 50, mas a Oracle recomenda que ele seja definido pelo menos como 500 para a maioria das aplicações. Algumas aplicações podem necessitar mais, por exemplo uma aplicação WEB que tem centenas de usuários compartilhando um grupo de sessões.

Parâmetro SESSION_CACHED_CURSORS

O parâmetro SESSION_CACHED_CURSORS define o numero de cursores que cada sessão pode reter na área SESSION CURSOR CACHE.Você pode definir o parâmetro SESSION_CACHED_CURSORS com um valor maior ou menor que o parâmetro OPEN_CURSORS. Esse parâmetro não tem efeito sobre o erro ORA-1000 ou no numero de cursores que serão abertos por sessão. Da mesma maneira o parâmetro OPEN_CURSORS não tem nenhuma influência no numero de cursores retidos na área SESSION CURSOR CACHE.

Caso o parâmetro SESSION_CACHED_CURSORS seja definido como 0, nenhum cursor será retido na área SESSION CURSOR CACHE. (Os cursores continuarão sendo retidos na SHARED POOL, mas as sessões terão que procurá-los lá) Quando o parâmetro SESSION_CACHED_CURSORS é definido com algum valor ou o seu valor padrão que é 50, durante uma requisição de PARSE o Oracle verifica na LIBRARY CACHE se já houve mais de 3 PARSES para a mesma instrução SQL, e se houve o Oracle move o cursor associado a instrução SQL da LIBRARY CACHE para a SESSION CURSOR CACHE. Os próximos PARSES dessa instrução SQL na mesma sessão são atendidos pela área SESSION CURSOR CACHE, evitando um SOFT PARSE.

Algumas pessoas acreditam que fazer um SOFT PARSE é o melhor dos mundos, pois o consumo de CPU é relativamente pequeno, porem quando uma instância é submetida a um número excessivo de SOFT PARSE (Ex: 2.000 soft parse por segundo) ela pode apresentar contenção de LIBRARY CACHE (library cache pin e library cache lock). Nessa situação podemos ajustar o parâmetro SESSION_CACHED_CURSORS e eliminar essa contenção de LIBRARY CACHE.

Monitoração do uso de cursores

Title of the document

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:

https://dbtimewizard.com.br/mentoria

A visão V$OPEN_CURSOR permite a monitoração dos cursores abertos por sessão na SHARED POOL, bem como a monitoração dos cursores retidos na SESSION CURSOR CACHE.

1
2
3
4
5

SELECT sid, count(1) qtde
FROM v$open_cursor
WHERE cursor_type = 'SESSION CURSOR CACHED'
GROUP by sid
ORDER by 2;

Demonstração do parâmetro SESSION_CACHED_CURSORS

Para ilustrar na prática como funciona o parâmetro SESSION_CACHED_CURSORS, vamos fazer uma simulação simples com uma consulta, vamos executa-la algumas vezes com o TRACE 10046 habilitado na nossa sessão e em seguida vamos verificar no TRACE como o Oracle tratou os cursores de cada uma das execuções.

Execuções repetidas da consulta

Na log de execução abaixo vamos realizar as seguintes operações:

1. Limpar a SHARED_POOL para evitar que cursores de execuções anteriores prejudiquem o resultado do nosso teste;
2. Executar a consulta pela primeira vez para que o Oracle realize um HARD PARSE da consulta;
3. Habilitar o TRACE 10046 na nossa sessão;
4. Repetir a execução da nossa consulta seis vezes;
5. Encerrar o TRACE;

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

20:44:38 dbauser@LAB11 > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

20:44:39 dbauser@LAB11 > alter system flush shared_pool;

System altered.

20:44:40 dbauser@LAB11 > select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR where SID = userenv('SID');

SQL_ID CURSOR_TYPE SQL_TEXT
------------- --------------------------------- ------------------------------------------------------------
4kp0kn4jr1jv4 OPEN table_1_ff_21f_0_0_0
auczr4f525nuy OPEN select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR whe
4vs91dcv7u1p6 OPEN-RECURSIVE insert into sys.aud$( sessionid,entryid,statement,ntimestamp
96g93hntrzjtr BUNDLE DICTIONARY LOOKUP CACHED select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,

20:44:49 dbauser@LAB11 > alter session set tracefile_identifier='TEST_TRACE010';

Session altered.

20:44:57 dbauser@LAB11 >
20:45:18 dbauser@LAB11 > ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Session altered.

20:45:28 dbauser@LAB11 > select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR where SID = userenv('SID');

SQL_ID CURSOR_TYPE SQL_TEXT
------------- --------------------------------- ------------------------------------------------------------
4kp0kn4jr1jv4 OPEN table_1_ff_21f_0_0_0
auczr4f525nuy OPEN select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR whe
4vs91dcv7u1p6 OPEN-RECURSIVE insert into sys.aud$( sessionid,entryid,statement,ntimestamp

20:45:34 dbauser@LAB11 >
20:45:54 dbauser@LAB11 >
20:46:02 dbauser@LAB11 > select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR where SID = userenv('SID');

SQL_ID CURSOR_TYPE SQL_TEXT
------------- --------------------------------- ------------------------------------------------------------
4kp0kn4jr1jv4 OPEN table_1_ff_21f_0_0_0
auczr4f525nuy OPEN select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR whe
4vs91dcv7u1p6 OPEN-RECURSIVE insert into sys.aud$( sessionid,entryid,statement,ntimestamp

20:46:04 dbauser@LAB11 >
20:46:26 dbauser@LAB11 > select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR where SID = userenv('SID');

SQL_ID CURSOR_TYPE SQL_TEXT
------------- --------------------------------- ------------------------------------------------------------
4kp0kn4jr1jv4 OPEN table_1_ff_21f_0_0_0
auczr4f525nuy OPEN select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR whe
4vs91dcv7u1p6 OPEN-RECURSIVE insert into sys.aud$( sessionid,entryid,statement,ntimestamp

20:46:28 dbauser@LAB11 >
20:46:49 dbauser@LAB11 > select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR where SID = userenv('SID');

SQL_ID CURSOR_TYPE SQL_TEXT
------------- --------------------------------- ------------------------------------------------------------
4kp0kn4jr1jv4 OPEN table_1_ff_21f_0_0_0
auczr4f525nuy DICTIONARY LOOKUP CURSOR CACHED select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR whe
4vs91dcv7u1p6 OPEN-RECURSIVE insert into sys.aud$( sessionid,entryid,statement,ntimestamp

20:46:52 dbauser@LAB11 >
20:47:21 dbauser@LAB11 > select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR where SID = userenv('SID');

SQL_ID CURSOR_TYPE SQL_TEXT
------------- --------------------------------- ------------------------------------------------------------
4kp0kn4jr1jv4 OPEN table_1_ff_21f_0_0_0
auczr4f525nuy SESSION CURSOR CACHED select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR whe
4vs91dcv7u1p6 OPEN-RECURSIVE insert into sys.aud$( sessionid,entryid,statement,ntimestamp

20:47:23 dbauser@LAB11 >
20:47:42 dbauser@LAB11 > select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR where SID = userenv('SID');

SQL_ID CURSOR_TYPE SQL_TEXT
------------- --------------------------------- ------------------------------------------------------------
4kp0kn4jr1jv4 OPEN table_1_ff_21f_0_0_0
auczr4f525nuy SESSION CURSOR CACHED select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR whe
4vs91dcv7u1p6 OPEN-RECURSIVE insert into sys.aud$( sessionid,entryid,statement,ntimestamp

20:47:46 dbauser@LAB11 > ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

20:47:52 dbauser@LAB11 >

Analise do TRACE 10046

Trace file /oracle/diag/rdbms/lab11/lab11/trace/lab11_ora_2512_TEST_TRACE010.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/11.2.0.4/db
System name: Linux
Node name: odbsrv11.localhost
Release: 2.6.18-164.el5
Version: #1 SMP Thu Sep 3 02:16:47 EDT 2009
Machine: i686
Instance name: lab11
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 2512, image: oracle@odbsrv11.localhost

*** 2016-02-24 20:45:33.159
*** SESSION ID:(28.5) 2016-02-24 20:45:33.159
*** CLIENT ID:() 2016-02-24 20:45:33.159
*** SERVICE NAME:(lab11) 2016-02-24 20:45:33.159
*** MODULE NAME:(SQL*Plus) 2016-02-24 20:45:33.159
*** ACTION NAME:() 2016-02-24 20:45:33.159

CLOSE #3084060556:c=0,e=214,dep=0,type=1,tim=1456357533158475
=====================
PARSING IN CURSOR #3084113424 len=37 dep=1 uid=0 oct=3 lid=0 tim=1456357533161204 hv=1398610540 ad='42375d84' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #3084113424:c=0,e=525,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1456357533161200
EXEC #3084113424:c=2000,e=89740,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=3684871272,tim=1456357533251192
FETCH #3084113424:c=0,e=93,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1456357533251362
STAT #3084113424 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=73 us cost=1 size=15 card=1)'
CLOSE #3084113424:c=0,e=5340,dep=1,type=0,tim=1456357533256747
=====================
PARSING IN CURSOR #3084114056 len=83 dep=0 uid=93 oct=3 lid=93 tim=1456357533257308 hv=2317538142 ad='470f29fc' sqlid='auczr4f525nuy'
select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR where SID = userenv('SID')
END OF STMT
PARSE #3084114056:c=3999,e=97519,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=2659856713,tim=1456357533257308
EXEC #3084114056:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357533257332
FETCH #3084114056:c=1000,e=1086,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2659856713,tim=1456357533258519
FETCH #3084114056:c=1000,e=1048,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=2659856713,tim=1456357533260224
STAT #3084114056 id=1 cnt=3 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$KGLLK (cr=0 pr=0 pw=0 time=1044 us cost=0 size=121 card=1)'

*** 2016-02-24 20:46:03.244
CLOSE #3084114056:c=0,e=58,dep=0,type=0,tim=1456357563244517
=====================
PARSING IN CURSOR #3084114056 len=83 dep=0 uid=93 oct=3 lid=93 tim=1456357563245555 hv=2317538142 ad='470f29fc' sqlid='auczr4f525nuy'
select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR where SID = userenv('SID')
END OF STMT
PARSE #3084114056:c=0,e=222,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357563245551
EXEC #3084114056:c=0,e=122,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357563245792
FETCH #3084114056:c=1000,e=1160,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2659856713,tim=1456357563247106
FETCH #3084114056:c=999,e=1129,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=2659856713,tim=1456357563248725
STAT #3084114056 id=1 cnt=3 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$KGLLK (cr=0 pr=0 pw=0 time=1133 us cost=0 size=121 card=1)'

*** 2016-02-24 20:46:27.516
CLOSE #3084114056:c=0,e=32,dep=0,type=0,tim=1456357587516361
=====================
PARSING IN CURSOR #3084114056 len=83 dep=0 uid=93 oct=3 lid=93 tim=1456357587517286 hv=2317538142 ad='470f29fc' sqlid='auczr4f525nuy'
select SQL_ID, CURSOR_TYPE, SQL_TEXT from V$OPEN_CURSOR where SID = userenv('SID')
END OF STMT
PARSE #3084114056:c=0,e=171,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357587517282
EXEC #3084114056:c=0,e=124,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357587517593
FETCH #3084114056:c=1000,e=1275,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2659856713,tim=1456357587519512
FETCH #3084114056:c=1000,e=897,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=2659856713,tim=1456357587521310
STAT #3084114056 id=1 cnt=3 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$KGLLK (cr=0 pr=0 pw=0 time=1279 us cost=0 size=121 card=1)'

*** 2016-02-24 20:46:51.290
CLOSE #3084114056:c=0,e=190,dep=0,type=1,tim=1456357611290765
PARSE #3084114056:c=0,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357611291139
EXEC #3084114056:c=0,e=129,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357611291334
FETCH #3084114056:c=1000,e=1319,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2659856713,tim=1456357611292864
FETCH #3084114056:c=1000,e=1036,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=2659856713,tim=1456357611294785
STAT #3084114056 id=1 cnt=3 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$KGLLK (cr=0 pr=0 pw=0 time=1277 us cost=0 size=121 card=1)'

*** 2016-02-24 20:47:22.161
CLOSE #3084114056:c=0,e=70,dep=0,type=3,tim=1456357642161143
PARSE #3084114056:c=1000,e=147,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357642161807
EXEC #3084114056:c=0,e=173,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357642162094
FETCH #3084114056:c=2000,e=1435,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2659856713,tim=1456357642163832
FETCH #3084114056:c=1000,e=581,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=2659856713,tim=1456357642165015
STAT #3084114056 id=1 cnt=3 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$KGLLK (cr=0 pr=0 pw=0 time=1410 us cost=0 size=121 card=1)'

*** 2016-02-24 20:47:45.688
CLOSE #3084114056:c=0,e=40,dep=0,type=3,tim=1456357665688904
PARSE #3084114056:c=0,e=111,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357665689501
EXEC #3084114056:c=0,e=138,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357665689712
FETCH #3084114056:c=1000,e=926,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2659856713,tim=1456357665691046
FETCH #3084114056:c=1000,e=1090,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=2659856713,tim=1456357665692622
STAT #3084114056 id=1 cnt=3 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$KGLLK (cr=0 pr=0 pw=0 time=923 us cost=0 size=121 card=1)'

*** 2016-02-24 20:47:51.485
CLOSE #3084114056:c=0,e=45,dep=0,type=3,tim=1456357671485385
=====================
PARSING IN CURSOR #3084060556 len=55 dep=0 uid=93 oct=42 lid=93 tim=1456357671486619 hv=2655499671 ad='0' sqlid='0kjg1c2g4gdcr'
ALTER SESSION SET EVENTS '10046 trace name context off'
END OF STMT
PARSE #3084060556:c=1000,e=621,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1456357671486614
EXEC #3084060556:c=1000,e=1071,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1456357671487814

Na linha 34 “PARSING IN CURSOR #3084114056” temos o registro do primeiro SOFT PARSE da primeira execução da consulta após a abertura do TRACE 10046;
Na linha 46 “PARSING IN CURSOR #3084114056” temos o registro do segundo SOFT PARSE da segunda execução da consulta;
Na linha 58 “PARSING IN CURSOR #3084114056” temos o registro do terceiro SOFT PARSE da terceira execução da consulta;
Na linha 68 “CLOSE #3084114056” temos o registro fechamento do terceiro SOFT PARSE, nessa linha temos um parâmetro “type=1” que indica que o cursor foi movido para área SESSION CURSOR CACHE e as próximas execuções da consulta não apresentam mais a linha “PARSING IN CURSOR #3084114056” pois o Oracle não precisa mais realizar SOFT PARSE, ele obtem o curso direto da área SESSION CURSOR CACHE;

Conclusão

O parâmetro SESSION_CACHED_CURSORS é um poderoso recurso do banco Oracle para melhorar o desempenho das consultas evitando a execução de SOFT PARSE e em algumas situações evitando contenção de LIBRARY CACHE, procure monitorar a instância e verificar se a definição padrão desse parâmetro esta atendendo as necessidades das aplicações, se as sessões estiverem atingindo o limite padrão de 50 cursores por sessão aumente o valor do parâmetro para melhorar o desempenho da instância.