Session cursor cache count first query in that loop didn't get a hit (we hadn't cached it yet), the subsequent 99 did. Session cursor cache hits 104 99 more hits! Now, lets cache upto 10 alter session set session_cached_cursors=10 Ħ execute immediate 'select count(*) from dual d2' into l_cnt PL/SQL procedure successfully select a.name, b.value Session cursor cache hits 5 thats from logging inĦ execute immediate 'select count(*) from dual d1' into l_cnt
![apex sql caching too aggressively apex sql caching too aggressively](https://about.gitlab.com/images/13_12/dast_on_demand_auth.png)
The parse count will still go up alter session set session_cached_cursors=0 It'll do a faster soft parse - hows that. Accordng t this note, Oracle only caches cursors only when session_cached_cursor is set to a value?Īnd asked him what happens if SESSION_CACHED_CURSOR is set to ZERO and he just said that Oracle caches the cursor if this is set to zero which contradicts the paperġ) you were measuring this one the wrong way. Not very clear to me.Īlso related to this parameter I asked him if setting 0 Oracle stops caching the cursors, he said that as some parameters when they are set to zero it basically tells Oracle dont use that parameter. So I asked him if Oracle manage a cached cursor list, he told me that MAYBE. I talked to an Oracle Support guy in Spain and the guy told me that Oracle would use what it needs, for example if there are 1000 cursors opened once then it would try to cache all 1000. If session_cached_cursor is set to zero (default) how many cursors would each session cache.
![apex sql caching too aggressively apex sql caching too aggressively](https://mikesmithers.files.wordpress.com/2016/06/lru_algorithm.gif)
![apex sql caching too aggressively apex sql caching too aggressively](https://bs-uploads.toptal.io/blackfish-uploads/uploaded_file/file/195009/image-1582846933716-7a1322da6d3ea5e1f02088b297e0a108.png)
Then checked parse_calls in v$sqlarea it says 1, then I executed again it says 2 then again it increased to 3 does this means that this select is not cached? According to the paper on Metalink if session_cached_cursor is set then no soft and hard parse would happen if the cursor is cached. I did a small test with session_cached_cursor, I set it to 4 and bounced the database. I have a couple of questions regarding session_cached_cursor parameter