{"id":328,"date":"2016-02-28T22:35:43","date_gmt":"2016-02-29T01:35:43","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=328"},"modified":"2019-10-10T23:01:49","modified_gmt":"2019-10-11T02:01:49","slug":"session_cached_cursors-sera-que-voce-realmente-conhece-a-importancia-desse-parametro","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/session_cached_cursors-sera-que-voce-realmente-conhece-a-importancia-desse-parametro\/","title":{"rendered":"SESSION_CACHED_CURSORS: Ser\u00e1 que voc\u00ea realmente conhece a import\u00e2ncia desse par\u00e2metro?"},"content":{"rendered":"

Existe muita confus\u00e3o com rela\u00e7\u00e3o a defini\u00e7\u00e3o dos par\u00e2metros OPEN_CURSORS e SESSION_CACHED_CURSORS, o entendimento da fun\u00e7\u00e3o de cada um deles \u00e9 fundamental para o ajuste de desempenho da inst\u00e2ncia Oracle. Nesse post vamos abordar o papel de cada um desses par\u00e2metros e como eles influenciam o desempenho das instru\u00e7\u00f5es SQL, vamos ver num arquivo trace 10046 o par\u00e2metro SESSION_CACHED_CURSORS em a\u00e7\u00e3o.<\/span><\/p>\n

<\/h2>\n

 <\/p>\n

 <\/p>\n

Par\u00e2metro OPEN_CURSORS<\/span><\/h2>\n

Cursores abertos ocupam espa\u00e7o na LIBRARY CACHE da SHARED POOL, o objetivo principal do par\u00e2metro OPEN_CURSORS \u00e9 definir um limite de cursores que podem ser abertos ao mesmo tempo para uma sess\u00e3o, dessa forma ele impede que uma sess\u00e3o encha a LIBRARY CACHE, ou consuma toda a CPU com milh\u00f5es de requisi\u00e7\u00f5es de PARSE.<\/span><\/p>\n

Por exemplo, se o par\u00e2metro OPEN_CURSORS for definido com o valor 200, para cada sess\u00e3o podemos ter at\u00e9 200 cursores abertos ao mesmo tempo. Se uma sess\u00e3o atingir esse numero de cursores abertos e tentar abrir mais um, ela receber\u00e1 o erro ORA-1000.<\/span><\/p>\n

Por padr\u00e3o o par\u00e2metro OPEN_CURSORS vem definido igual 50, mas a Oracle recomenda que ele seja definido pelo menos como 500 para a maioria das aplica\u00e7\u00f5es. Algumas aplica\u00e7\u00f5es podem necessitar mais, por exemplo uma aplica\u00e7\u00e3o WEB que tem centenas de usu\u00e1rios compartilhando um grupo de sess\u00f5es.<\/span><\/p>\n

Par\u00e2metro SESSION_CACHED_CURSORS<\/span><\/h2>\n

O par\u00e2metro SESSION_CACHED_CURSORS define o numero de cursores que cada sess\u00e3o pode reter na \u00e1rea SESSION CURSOR CACHE.Voc\u00ea pode definir o par\u00e2metro SESSION_CACHED_CURSORS com um valor maior ou menor que o par\u00e2metro OPEN_CURSORS. Esse par\u00e2metro n\u00e3o tem efeito sobre o erro ORA-1000 ou no numero de cursores que ser\u00e3o abertos por sess\u00e3o. Da mesma maneira o par\u00e2metro OPEN_CURSORS n\u00e3o tem nenhuma influ\u00eancia no numero de cursores retidos na \u00e1rea SESSION CURSOR CACHE.<\/span><\/p>\n

Caso o par\u00e2metro SESSION_CACHED_CURSORS seja definido como 0, nenhum cursor ser\u00e1 retido na \u00e1rea SESSION CURSOR CACHE. (Os cursores continuar\u00e3o sendo retidos na SHARED POOL, mas as sess\u00f5es ter\u00e3o que procur\u00e1-los l\u00e1) Quando o par\u00e2metro SESSION_CACHED_CURSORS \u00e9 definido com algum valor ou o seu valor padr\u00e3o que \u00e9 50, durante uma requisi\u00e7\u00e3o de PARSE o Oracle verifica na LIBRARY CACHE se j\u00e1 houve mais de 3 PARSES para a mesma instru\u00e7\u00e3o SQL, e se houve o Oracle move o cursor associado a instru\u00e7\u00e3o SQL da LIBRARY CACHE para a SESSION CURSOR CACHE. Os pr\u00f3ximos PARSES dessa instru\u00e7\u00e3o SQL na mesma sess\u00e3o s\u00e3o atendidos pela \u00e1rea SESSION CURSOR CACHE, evitando um SOFT PARSE.<\/span><\/p>\n

Algumas pessoas acreditam que fazer um SOFT PARSE \u00e9 o melhor dos mundos, pois o consumo de CPU \u00e9 relativamente pequeno, porem quando uma inst\u00e2ncia \u00e9 submetida a um n\u00famero excessivo de SOFT PARSE (Ex: 2.000 soft parse por segundo) ela pode apresentar conten\u00e7\u00e3o de LIBRARY CACHE (library cache pin e library cache lock). Nessa situa\u00e7\u00e3o podemos ajustar o par\u00e2metro SESSION_CACHED_CURSORS e eliminar essa conten\u00e7\u00e3o de LIBRARY CACHE.<\/span><\/p>\n

Monitora\u00e7\u00e3o do uso de cursores<\/span><\/h2>\n

A vis\u00e3o V$OPEN_CURSOR permite a monitora\u00e7\u00e3o dos cursores abertos por sess\u00e3o na SHARED POOL, bem como a monitora\u00e7\u00e3o dos cursores retidos na SESSION CURSOR CACHE.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
<\/div><\/td>
SELECT sid,<\/span> count<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde
\n  FROM v$open_cursor<\/span>
\n WHERE cursor_type =<\/span> 'SESSION CURSOR CACHED'<\/span>
\n GROUP by sid
\n ORDER by 2<\/span>;<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Demonstra\u00e7\u00e3o do par\u00e2metro SESSION_CACHED_CURSORS<\/span><\/h2>\n

Para ilustrar na pr\u00e1tica como funciona o par\u00e2metro SESSION_CACHED_CURSORS, vamos fazer uma simula\u00e7\u00e3o simples com uma consulta, vamos executa-la algumas vezes com o TRACE 10046 habilitado na nossa sess\u00e3o e em seguida vamos verificar no TRACE como o Oracle tratou os cursores de cada uma das execu\u00e7\u00f5es.<\/span><\/p>\n

Execu\u00e7\u00f5es repetidas da consulta<\/span><\/h3>\n

Na log de execu\u00e7\u00e3o abaixo vamos realizar as seguintes opera\u00e7\u00f5es:<\/span><\/p>\n

1. Limpar a SHARED_POOL para evitar que cursores de execu\u00e7\u00f5es anteriores prejudiquem o resultado do nosso teste;<\/span>
\n 2. Executar a consulta pela primeira vez para que o Oracle realize um HARD PARSE da consulta;<\/span>
\n 3. Habilitar o TRACE 10046 na nossa sess\u00e3o;<\/span>
\n 4. Repetir a execu\u00e7\u00e3o da nossa consulta seis vezes;<\/span>
\n 5. Encerrar o TRACE;<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
20<\/span>:<\/span>44<\/span>:<\/span>38<\/span> dbauser@<\/span>LAB11 ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\n
\n20<\/span>:<\/span>44<\/span>:<\/span>39<\/span> dbauser@<\/span>LAB11 ><\/span> alter system<\/span><\/a> flush<\/span><\/a> shared_pool;<\/span>
\n
\nSystem<\/span><\/a> altered.<\/span>
\n
\n20<\/span>:<\/span>44<\/span>:<\/span>40<\/span> dbauser@<\/span>LAB11 ><\/span> select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> where SID =<\/span> userenv(<\/span>'SID'<\/span>)<\/span>;<\/span>
\n
\nSQL_ID        CURSOR_TYPE                       SQL_TEXT
\n-------------<\/span> ---------------------------------<\/span> ------------------------------------------------------------<\/span>
\n4kp0kn4jr1jv4 OPEN                              table_1_ff_21f_0_0_0
\nauczr4f525nuy OPEN                              select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> whe
\n4vs91dcv7u1p6 OPEN-<\/span>RECURSIVE                    insert into sys.<\/span>aud$(<\/span> sessionid,<\/span>entryid,<\/span>statement,<\/span>ntimestamp
\n96g93hntrzjtr BUNDLE DICTIONARY LOOKUP CACHED   select \/*+ rule *\/<\/span> bucket_cnt,<\/span> row_cnt,<\/span> cache_cnt,<\/span> null_cnt,<\/span>
\n
\n20<\/span>:<\/span>44<\/span>:<\/span>49<\/span> dbauser@<\/span>LAB11 ><\/span> alter session set tracefile_identifier=<\/span>'TEST_TRACE010'<\/span>;<\/span>
\n
\nSession altered.<\/span>
\n
\n20<\/span>:<\/span>44<\/span>:<\/span>57<\/span> dbauser@<\/span>LAB11 ><\/span>
\n20<\/span>:<\/span>45<\/span>:<\/span>18<\/span> dbauser@<\/span>LAB11 ><\/span> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'<\/span>;<\/span>
\n
\nSession altered.<\/span>
\n
\n20<\/span>:<\/span>45<\/span>:<\/span>28<\/span> dbauser@<\/span>LAB11 ><\/span> select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> where SID =<\/span> userenv(<\/span>'SID'<\/span>)<\/span>;<\/span>
\n
\nSQL_ID        CURSOR_TYPE                       SQL_TEXT
\n-------------<\/span> ---------------------------------<\/span> ------------------------------------------------------------<\/span>
\n4kp0kn4jr1jv4 OPEN                              table_1_ff_21f_0_0_0
\nauczr4f525nuy OPEN                              select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> whe
\n4vs91dcv7u1p6 OPEN-<\/span>RECURSIVE                    insert into sys.<\/span>aud$(<\/span> sessionid,<\/span>entryid,<\/span>statement,<\/span>ntimestamp
\n
\n20<\/span>:<\/span>45<\/span>:<\/span>34<\/span> dbauser@<\/span>LAB11 ><\/span>
\n20<\/span>:<\/span>45<\/span>:<\/span>54<\/span> dbauser@<\/span>LAB11 ><\/span>
\n20<\/span>:<\/span>46<\/span>:<\/span>02<\/span> dbauser@<\/span>LAB11 ><\/span> select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> where SID =<\/span> userenv(<\/span>'SID'<\/span>)<\/span>;<\/span>
\n
\nSQL_ID        CURSOR_TYPE                       SQL_TEXT
\n-------------<\/span> ---------------------------------<\/span> ------------------------------------------------------------<\/span>
\n4kp0kn4jr1jv4 OPEN                              table_1_ff_21f_0_0_0
\nauczr4f525nuy OPEN                              select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> whe
\n4vs91dcv7u1p6 OPEN-<\/span>RECURSIVE                    insert into sys.<\/span>aud$(<\/span> sessionid,<\/span>entryid,<\/span>statement,<\/span>ntimestamp
\n
\n20<\/span>:<\/span>46<\/span>:<\/span>04<\/span> dbauser@<\/span>LAB11 ><\/span>
\n20<\/span>:<\/span>46<\/span>:<\/span>26<\/span> dbauser@<\/span>LAB11 ><\/span> select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> where SID =<\/span> userenv(<\/span>'SID'<\/span>)<\/span>;<\/span>
\n
\nSQL_ID        CURSOR_TYPE                       SQL_TEXT
\n-------------<\/span> ---------------------------------<\/span> ------------------------------------------------------------<\/span>
\n4kp0kn4jr1jv4 OPEN                              table_1_ff_21f_0_0_0
\nauczr4f525nuy OPEN                              select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> whe
\n4vs91dcv7u1p6 OPEN-<\/span>RECURSIVE                    insert into sys.<\/span>aud$(<\/span> sessionid,<\/span>entryid,<\/span>statement,<\/span>ntimestamp
\n
\n20<\/span>:<\/span>46<\/span>:<\/span>28<\/span> dbauser@<\/span>LAB11 ><\/span>
\n20<\/span>:<\/span>46<\/span>:<\/span>49<\/span> dbauser@<\/span>LAB11 ><\/span> select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> where SID =<\/span> userenv(<\/span>'SID'<\/span>)<\/span>;<\/span>
\n
\nSQL_ID        CURSOR_TYPE                       SQL_TEXT
\n-------------<\/span> ---------------------------------<\/span> ------------------------------------------------------------<\/span>
\n4kp0kn4jr1jv4 OPEN                              table_1_ff_21f_0_0_0
\nauczr4f525nuy DICTIONARY LOOKUP CURSOR CACHED   select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> whe
\n4vs91dcv7u1p6 OPEN-<\/span>RECURSIVE                    insert into sys.<\/span>aud$(<\/span> sessionid,<\/span>entryid,<\/span>statement,<\/span>ntimestamp
\n
\n20<\/span>:<\/span>46<\/span>:<\/span>52<\/span> dbauser@<\/span>LAB11 ><\/span>
\n20<\/span>:<\/span>47<\/span>:<\/span>21<\/span> dbauser@<\/span>LAB11 ><\/span> select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> where SID =<\/span> userenv(<\/span>'SID'<\/span>)<\/span>;<\/span>
\n
\nSQL_ID        CURSOR_TYPE                       SQL_TEXT
\n-------------<\/span> ---------------------------------<\/span> ------------------------------------------------------------<\/span>
\n4kp0kn4jr1jv4 OPEN                              table_1_ff_21f_0_0_0
\nauczr4f525nuy SESSION CURSOR CACHED             select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> whe
\n4vs91dcv7u1p6 OPEN-<\/span>RECURSIVE                    insert into sys.<\/span>aud$(<\/span> sessionid,<\/span>entryid,<\/span>statement,<\/span>ntimestamp
\n
\n20<\/span>:<\/span>47<\/span>:<\/span>23<\/span> dbauser@<\/span>LAB11 ><\/span>
\n20<\/span>:<\/span>47<\/span>:<\/span>42<\/span> dbauser@<\/span>LAB11 ><\/span> select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> where SID =<\/span> userenv(<\/span>'SID'<\/span>)<\/span>;<\/span>
\n
\nSQL_ID        CURSOR_TYPE                       SQL_TEXT
\n-------------<\/span> ---------------------------------<\/span> ------------------------------------------------------------<\/span>
\n4kp0kn4jr1jv4 OPEN                              table_1_ff_21f_0_0_0
\nauczr4f525nuy SESSION CURSOR CACHED             select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> whe
\n4vs91dcv7u1p6 OPEN-<\/span>RECURSIVE                    insert into sys.<\/span>aud$(<\/span> sessionid,<\/span>entryid,<\/span>statement,<\/span>ntimestamp
\n
\n20<\/span>:<\/span>47<\/span>:<\/span>46<\/span> dbauser@<\/span>LAB11 ><\/span> ALTER SESSION SET EVENTS '10046 trace name context off'<\/span>;<\/span>
\n
\nSession altered.<\/span>
\n
\n20<\/span>:<\/span>47<\/span>:<\/span>52<\/span> dbauser@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analise do TRACE 10046<\/span><\/h3>\n

 <\/p>\n

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
<\/div><\/td>
Trace file<\/span><\/a> \/<\/span>oracle\/<\/span>diag\/<\/span>rdbms\/<\/span>lab11\/<\/span>lab11\/<\/span>trace\/<\/span>lab11_ora_2512_TEST_TRACE010.<\/span>trc
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> Production
\nWith the Partitioning,<\/span> OLAP,<\/span> Data Mining and Real Application Testing options
\nORACLE_HOME =<\/span> \/<\/span>oracle\/<\/span>app\/<\/span>oracle\/<\/span>product\/<\/span>11.<\/span>2.<\/span>0.<\/span>4\/<\/span>db
\nSystem<\/span><\/a> name:<\/span>  Linux
\nNode name:<\/span>  odbsrv11.<\/span>localhost
\nRelease:<\/span>  2.<\/span>6.<\/span>18-<\/span>164<\/span>.<\/span>el5
\nVersion:<\/span>  #1 SMP Thu Sep 3 02:16:47 EDT 2009
\n<\/span>Machine:<\/span>  i686
\nInstance name:<\/span> lab11
\nRedo thread mounted by this instance:<\/span> 1<\/span>
\nOracle process number:<\/span> 23<\/span>
\nUnix process pid:<\/span> 2512<\/span>,<\/span> image:<\/span> oracle@<\/span>odbsrv11.<\/span>localhost
\n
\n
\n***<\/span> 2016<\/span>-<\/span>02<\/span>-<\/span>24<\/span> 20<\/span>:<\/span>45<\/span>:<\/span>33.159<\/span>
\n***<\/span> SESSION ID:<\/span>(<\/span>28.5<\/span>)<\/span> 2016<\/span>-<\/span>02<\/span>-<\/span>24<\/span> 20<\/span>:<\/span>45<\/span>:<\/span>33.159<\/span>
\n***<\/span> CLIENT ID:<\/span>(<\/span>)<\/span> 2016<\/span>-<\/span>02<\/span>-<\/span>24<\/span> 20<\/span>:<\/span>45<\/span>:<\/span>33.159<\/span>
\n***<\/span> SERVICE NAME:<\/span>(<\/span>lab11)<\/span> 2016<\/span>-<\/span>02<\/span>-<\/span>24<\/span> 20<\/span>:<\/span>45<\/span>:<\/span>33.159<\/span>
\n***<\/span> MODULE NAME:<\/span>(<\/span>SQL*<\/span>Plus)<\/span> 2016<\/span>-<\/span>02<\/span>-<\/span>24<\/span> 20<\/span>:<\/span>45<\/span>:<\/span>33.159<\/span>
\n***<\/span> ACTION NAME:<\/span>(<\/span>)<\/span> 2016<\/span>-<\/span>02<\/span>-<\/span>24<\/span> 20<\/span>:<\/span>45<\/span>:<\/span>33.159<\/span>
\n 
\nCLOSE #3084060556:c=0,e=214,dep=0,type=1,tim=1456357533158475
\n<\/span>=====================<\/span>
\nPARSING IN CURSOR #3084113424 len=37 dep=1 uid=0 oct=3 lid=0 tim=1456357533161204 hv=1398610540 ad='42375d84' sqlid='grwydz59pu6mc'
\n<\/span>select text from view$ where rowid=:<\/span>1<\/span>
\nEND<\/span><\/a> OF STMT
\nPARSE #3084113424:c=0,e=525,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1456357533161200
\n<\/span>EXEC<\/span><\/a> #3084113424:c=2000,e=89740,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=3684871272,tim=1456357533251192
\n<\/span>FETCH #3084113424:c=0,e=93,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1456357533251362
\n<\/span>STAT<\/span><\/a> #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)'
\n<\/span>CLOSE #3084113424:c=0,e=5340,dep=1,type=0,tim=1456357533256747
\n<\/span>=====================<\/span>
\nPARSING IN CURSOR #3084114056 len=83 dep=0 uid=93 oct=3 lid=93 tim=1456357533257308 hv=2317538142 ad='470f29fc' sqlid='auczr4f525nuy'
\n<\/span>select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> where SID =<\/span> userenv(<\/span>'SID'<\/span>)<\/span>
\nEND<\/span><\/a> OF STMT
\nPARSE #3084114056:c=3999,e=97519,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=2659856713,tim=1456357533257308
\n<\/span>EXEC<\/span><\/a> #3084114056:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357533257332
\n<\/span>FETCH #3084114056:c=1000,e=1086,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2659856713,tim=1456357533258519
\n<\/span>FETCH #3084114056:c=1000,e=1048,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=2659856713,tim=1456357533260224
\n<\/span>STAT<\/span><\/a> #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)'
\n<\/span>
\n***<\/span> 2016<\/span>-<\/span>02<\/span>-<\/span>24<\/span> 20<\/span>:<\/span>46<\/span>:<\/span>03.244<\/span>
\nCLOSE #3084114056:c=0,e=58,dep=0,type=0,tim=1456357563244517
\n<\/span>=====================<\/span>
\nPARSING IN CURSOR #3084114056 len=83 dep=0 uid=93 oct=3 lid=93 tim=1456357563245555 hv=2317538142 ad='470f29fc' sqlid='auczr4f525nuy'
\n<\/span>select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> where SID =<\/span> userenv(<\/span>'SID'<\/span>)<\/span>
\nEND<\/span><\/a> OF STMT
\nPARSE #3084114056:c=0,e=222,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357563245551
\n<\/span>EXEC<\/span><\/a> #3084114056:c=0,e=122,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357563245792
\n<\/span>FETCH #3084114056:c=1000,e=1160,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2659856713,tim=1456357563247106
\n<\/span>FETCH #3084114056:c=999,e=1129,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=2659856713,tim=1456357563248725
\n<\/span>STAT<\/span><\/a> #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)'
\n<\/span>
\n***<\/span> 2016<\/span>-<\/span>02<\/span>-<\/span>24<\/span> 20<\/span>:<\/span>46<\/span>:<\/span>27.516<\/span>
\nCLOSE #3084114056:c=0,e=32,dep=0,type=0,tim=1456357587516361
\n<\/span>=====================<\/span>
\nPARSING IN CURSOR #3084114056 len=83 dep=0 uid=93 oct=3 lid=93 tim=1456357587517286 hv=2317538142 ad='470f29fc' sqlid='auczr4f525nuy'
\n<\/span>select SQL_ID,<\/span> CURSOR_TYPE,<\/span> SQL_TEXT  from V$OPEN_CURSOR<\/span> where SID =<\/span> userenv(<\/span>'SID'<\/span>)<\/span>
\nEND<\/span><\/a> OF STMT
\nPARSE #3084114056:c=0,e=171,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357587517282
\n<\/span>EXEC<\/span><\/a> #3084114056:c=0,e=124,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357587517593
\n<\/span>FETCH #3084114056:c=1000,e=1275,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2659856713,tim=1456357587519512
\n<\/span>FETCH #3084114056:c=1000,e=897,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=2659856713,tim=1456357587521310
\n<\/span>STAT<\/span><\/a> #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)'
\n<\/span>
\n***<\/span> 2016<\/span>-<\/span>02<\/span>-<\/span>24<\/span> 20<\/span>:<\/span>46<\/span>:<\/span>51.290<\/span>
\nCLOSE #3084114056:c=0,e=190,dep=0,type=1,tim=1456357611290765
\n<\/span>PARSE #3084114056:c=0,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357611291139
\n<\/span>EXEC<\/span><\/a> #3084114056:c=0,e=129,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357611291334
\n<\/span>FETCH #3084114056:c=1000,e=1319,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2659856713,tim=1456357611292864
\n<\/span>FETCH #3084114056:c=1000,e=1036,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=2659856713,tim=1456357611294785
\n<\/span>STAT<\/span><\/a> #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)'
\n<\/span>
\n***<\/span> 2016<\/span>-<\/span>02<\/span>-<\/span>24<\/span> 20<\/span>:<\/span>47<\/span>:<\/span>22.161<\/span>
\nCLOSE #3084114056:c=0,e=70,dep=0,type=3,tim=1456357642161143
\n<\/span>PARSE #3084114056:c=1000,e=147,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357642161807
\n<\/span>EXEC<\/span><\/a> #3084114056:c=0,e=173,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357642162094
\n<\/span>FETCH #3084114056:c=2000,e=1435,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2659856713,tim=1456357642163832
\n<\/span>FETCH #3084114056:c=1000,e=581,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=2659856713,tim=1456357642165015
\n<\/span>STAT<\/span><\/a> #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)'
\n<\/span>
\n***<\/span> 2016<\/span>-<\/span>02<\/span>-<\/span>24<\/span> 20<\/span>:<\/span>47<\/span>:<\/span>45.688<\/span>
\nCLOSE #3084114056:c=0,e=40,dep=0,type=3,tim=1456357665688904
\n<\/span>PARSE #3084114056:c=0,e=111,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357665689501
\n<\/span>EXEC<\/span><\/a> #3084114056:c=0,e=138,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2659856713,tim=1456357665689712
\n<\/span>FETCH #3084114056:c=1000,e=926,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2659856713,tim=1456357665691046
\n<\/span>FETCH #3084114056:c=1000,e=1090,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,plh=2659856713,tim=1456357665692622
\n<\/span>STAT<\/span><\/a> #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)'
\n<\/span>
\n***<\/span> 2016<\/span>-<\/span>02<\/span>-<\/span>24<\/span> 20<\/span>:<\/span>47<\/span>:<\/span>51.485<\/span>
\nCLOSE #3084114056:c=0,e=45,dep=0,type=3,tim=1456357671485385
\n<\/span>=====================<\/span>
\nPARSING IN CURSOR #3084060556 len=55 dep=0 uid=93 oct=42 lid=93 tim=1456357671486619 hv=2655499671 ad='0' sqlid='0kjg1c2g4gdcr'
\n<\/span>ALTER SESSION SET EVENTS '10046 trace name context off'<\/span>
\nEND<\/span><\/a> OF STMT
\nPARSE #3084060556:c=1000,e=621,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1456357671486614
\n<\/span>EXEC<\/span><\/a> #3084060556:c=1000,e=1071,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=1456357671487814<\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Na linha 34 “PARSING IN CURSOR #3084114056” temos o registro do primeiro SOFT PARSE da primeira execu\u00e7\u00e3o da consulta ap\u00f3s a abertura do TRACE 10046;<\/span>
\nNa linha 46 “PARSING IN CURSOR #3084114056” temos o registro do segundo SOFT PARSE da segunda execu\u00e7\u00e3o da consulta;<\/span>
\nNa linha 58 “PARSING IN CURSOR #3084114056” temos o registro do terceiro SOFT PARSE da terceira execu\u00e7\u00e3o da consulta;<\/span>
\nNa linha 68 “CLOSE #3084114056” temos o registro fechamento do terceiro SOFT PARSE, nessa linha temos um par\u00e2metro “type=1” que indica que o cursor foi movido para \u00e1rea SESSION CURSOR CACHE e as pr\u00f3ximas execu\u00e7\u00f5es da consulta n\u00e3o apresentam mais a linha “PARSING IN CURSOR #3084114056” pois o Oracle n\u00e3o precisa mais realizar SOFT PARSE, ele obtem o curso direto da \u00e1rea SESSION CURSOR CACHE;<\/span><\/p>\n

 <\/p>\n

Conclus\u00e3o<\/span><\/h2>\n

O par\u00e2metro SESSION_CACHED_CURSORS \u00e9 um poderoso recurso do banco Oracle para melhorar o desempenho das consultas evitando a execu\u00e7\u00e3o de SOFT PARSE e em algumas situa\u00e7\u00f5es evitando conten\u00e7\u00e3o de LIBRARY CACHE, procure monitorar a inst\u00e2ncia e verificar se a defini\u00e7\u00e3o padr\u00e3o desse par\u00e2metro esta atendendo as necessidades das aplica\u00e7\u00f5es, se as sess\u00f5es estiverem atingindo o limite padr\u00e3o de 50 cursores por sess\u00e3o aumente o valor do par\u00e2metro para melhorar o desempenho da inst\u00e2ncia.<\/span><\/p>\n

 <\/p>\n

Refer\u00eancias<\/span><\/h2>\n

https:\/\/docs.oracle.com\/cd\/B28359_01\/server.111\/b28320\/initparams218.htm<\/a><\/strong><\/p>\n

https:\/\/docs.oracle.com\/cd\/B28359_01\/server.111\/b28274\/memory.htm#i38400<\/a><\/strong><\/p>\n

http:\/\/www.orafaq.com\/node\/758<\/a><\/strong><\/p>\n

 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

Existe muita confus\u00e3o com rela\u00e7\u00e3o a defini\u00e7\u00e3o dos par\u00e2metros OPEN_CURSORS e SESSION_CACHED_CURSORS, o entendimento da fun\u00e7\u00e3o de cada um deles \u00e9 fundamental para o ajuste de desempenho da inst\u00e2ncia Oracle. Nesse post vamos abordar o papel de cada um desses par\u00e2metros e como eles influenciam o desempenho das instru\u00e7\u00f5es SQL, vamos ver num arquivo trace 10046 o par\u00e2metro SESSION_CACHED_CURSORS em<\/p>\n","protected":false},"author":2,"featured_media":329,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[6,42,97,10,98,96,95],"class_list":["post-328","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cost-base-optimizer","tag-cbo","tag-cost-base-optimizer","tag-open_cursors","tag-otimizador","tag-session_cached_cursors","tag-soft-parse","tag-trace-10046"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t