RSS Feed

Method R Discussion Group

We recently had a interesting experience. We upgraded to OEM agent 10.3 and started experiencing ORA-4031 on many of our databases. These databases had cursor_sharing = SIMILAR. We made it to FORCE to see if the error would go away but it didnt. Surprisingly when we set it to EXACT, it went away.

I would normally expect the opposite behavior - if we set it to FORCE, then Oracle would force sharing of similar cursors and hence conserve shared_pool. But what we saw was the opposite.

Any ideas why this happens ?

Thanks

Babu

Babu
I would open a TAR with Oracle to understand this issue.

Since we have no idea about the version of the database and how your code was performing from a shared pool perspective, it is hard to speculate.

Thanks
Mahesh

We have this escalated to Oracle beyond the regular lines of support. We still havent gotten an answer back..

We have seen this happen only with 9.2.0.6. The SQL is very bad with many many literals..

More than the specifics of version, SQL etc, my question was more generic. Why would cursor_sharing=Exact free up more shared pool that cursor_sharing=force or similar..

There is no explanation I can think of as to how/why EXACT would free up more space in the shared pool.  So, in my estimation, what you are experiencing must be a bug of some sort.  The whole point of using cursor_sharing of either SIMILAR or FORCE is to reduce the number of cursors created in the shared pool. 

Check out Note 403616.1 on Metalink that discusses a problem that sounds similar to yours.  The description says: "Many child cursors may be created for SQL statements using bind variables which have different maximum bind lengths when the same SQL was shared in previous releases.  This can lead to concurrency problems such as latch contention, memory problems such as ORA-4031 or internal errors such as ORA-600."  In that note there is a test case you can run to see if you are being affected by that particular bug.

In the meantime, you may want to check and see if you do have an issue with many (more than appear normal) child cursors getting created.  Try this query:
(Version 10g)
select sa.sql_text,sa.version_count,ss.*
from v$sqlarea sa,v$sql_shared_cursor ss
where sa.address=ss.address
and sa.version_count > 50
order by sa.version_count ;

(Version 8,9)
select sa.sql_text,sa.version_count ,ss.*
from v$sqlarea sa,v$sql_shared_cursor ss
where sa.address=ss.kghldpar
and sa.version_count > 50
order by sa.version_count ;

This query will show cursors with more than 50 children and the column showing 'Y' will indicate the reason for the generation of the child cursors. It's likely if the problem discussed in note 403616.1 is what you're experiencing, the BIND_MISMATCH column will have a 'Y'.  The columns with a value of 'N' can be ignored in the output.  Of course, if you run the test case, you'd have to set your cursor_sharing parameter back to SIMILAR when you do the test.

Please let us know how this one gets resolved.

Thanks for the info but I am not hitting that bug. If I run the test described in 403616.1, I just see one cursor. Also on the databases where I get this error the max(version_count) from v$sqlarea is just 4..

Anjo Kolk has temporarily some difficulty accessing this site, so I am posting on his behalf

This looks to me like a case of SQL statements that stay open too long. Check session_cached_cursors, it this is set >0, set it to 0. 

Also check in application code if this is caused by SELECT statement that are not closed. A typical situation would be that they fetch, but don't hit EOF and the statement stays open. That is why the heaps stay pinned for each cursor.

What is the size of the shared pool?

Michael Möller, Miracle (likewise Anjo Kolk)

Hi Babu, it sounds as if there are at least two questions to be answered:
1) Why are the 4031's happening?
2) Why did cursor_sharing = EXACT help relieve the problem, when that seems somewhat counter to the documnted description and use of EXACT versus FORCE versus SIMILAR?

This does sound like a problem best resolved with Oracle Support and Development through an SR.  To help Oracle best understand your question, it would help a lot if you could let them know if you've done anything to "override" normal shared pool manangement in your instance.  For example
a) have you set any values to override the defaults for the init.ora parameters _kgl_latch_count, _kghdsidx_count, _shared_pool_reserved_min_alloc, cursor_space_for_time, or session_cached_cursors? 
b) how many cpu's do you have, the number of which becomes important in the calculation of how many shared_pool heaps will be allocated for you in lieu of an overriding value for _kghdsidx_count
c) the outputs from select * from v$sga, v$sgastat, and v$shared_pool_reserved

This sounds like a question that will require some conversation and a few heap dumps to resolve.

Good luck and regards,

>> Check session_cached_cursors, it this is set >0, set it to 0. 
It is set to 0

>> Also check in application code if this is caused by SELECT statement that are not closed.
The code in this case is the OEM agent. We looked through the PERL code and found that they are using very poor coding techniques in terms of using exteremely long literals but nothing we can do. I have escalated this case of poor SQL to the OEM product support

>> What is the size of the shared pool?
This error happens when shared_pool is around 40Mb to about 120M. If its higher than 120 then this doesnt happen even if cursor_sharing is set to "SIMILAR" or "FORCE"

>> a) have you set any values to override the defaults for the init.ora parameters _kgl_latch_count, _kghdsidx_count, _shared_pool_reserved_min_alloc, cursor_space_for_time, or session_cached_cursors? 
No
>> b) how many cpu's do you have, the number of which becomes important in the calculation of how many shared_pool heaps will be allocated for you in lieu of an overriding value for _kghdsidx_count
We have seen this happening for servers with 4 and 8 CPUs.
>> c) the outputs from select * from v$sga, v$sgastat, and v$shared_pool_reserved
This is from one of the databases that have the error..
SQL> select * from v$sga;

NAME                      VALUE
-------------------- ----------
Fixed Size              456124
Variable Size          62914560
Database Buffers      33554432
Redo Buffers            1204224

SQL> select * from v$sgastat;

POOL        NAME                            BYTES
----------- -------------------------- ----------
            fixed_sga                      456124
            buffer_cache                33554432
            log_buffer                    1180672
shared pool krvxrr                        126528
shared pool KGK heap                        1516
shared pool KQR M PO                      736832
shared pool KQR S PO                        54540
shared pool KQR S SO                        4892
shared pool KQR X PO                        12160
shared pool sessions                      224688
shared pool sql area                    31091388
shared pool 1M buffer                    2098176
shared pool KGLS heap                      662800
shared pool kglsim sga                    136104
shared pool parameters                      38900
shared pool free memory                  2909380
shared pool PL/SQL DIANA                  678248
shared pool FileOpenBlock                  118384
shared pool PL/SQL MPCODE                  866152
shared pool library cache                5937700
shared pool miscellaneous                6686916
shared pool PLS non-lib hp                  2068
shared pool joxs heap init                  4220
shared pool sim memory hea                  25400
shared pool table definiti                  3036
shared pool trigger defini                    180
shared pool trigger inform                    992
shared pool trigger source                    176
shared pool dictionary cache              1614976
shared pool sim trace entries              196608
shared pool ksm_file2sga region            404860
shared pool KSXR receive buffers          1033000
shared pool character set object          323740
shared pool KSXR large reply queue        166584
shared pool message pool freequeue        665792
shared pool KSXR pending messages que      841036
shared pool event statistics per sess    1052016
shared pool fixed allocation callback        268
java pool  free memory                  4194304

39 rows selected.

SQL> select * from v$shared_pool_reserved;

FREE_SPACE AVG_FREE_SIZE FREE_COUNT MAX_FREE_SIZE USED_SPACE AVG_USED_SIZE
---------- ------------- ---------- ------------- ---------- -------------
USED_COUNT MAX_USED_SIZE  REQUESTS REQUEST_MISSES LAST_MISS_SIZE MAX_MISS_SIZE
---------- ------------- ---------- -------------- -------------- -------------
REQUEST_FAILURES LAST_FAILURE_SIZE ABORTED_REQUEST_THRESHOLD ABORTED_REQUESTS
---------------- ----------------- ------------------------- ----------------
LAST_ABORTED_SIZE
-----------------
  2407384        171956        14        171956          0            0
        0            0        448              0              0            0
              0                0                3.0065E+10                0
                0


As said earlier it looks like we arent going anywhere with Ora"kill" support :-)

When cursor sharing is set to exact, only the cursors parsed and deemed "exact" are pinned in the shared pool. This means the fewest cursors are in the shared pool and this causes the least amount of fragmentation which generates the fewest 4031 erros. 

Cursor sharing - similar relaxes the constraint and lets Oracle pin more cursors in the shared pool and this is why 4031 erros are more frequent.

Lee Collins
Oracle DBA

Lee, I'd have to disagree with your statement "When cursor sharing is set to exact, only the cursors parsed and deemed "exact" are pinned in the shared pool. This means the fewest cursors are in the shared pool and this causes the least amount of fragmentation which generates the fewest 4031 errors."

EXACT will be truly "exact" such that if you are using literals in your SQL, then each different literal value will cause a new/different entry in the shared pool.  This can/will allow for the possibility of numerous "almost duplicates" (i.e. the queries are the same except for their literal values but Oracle thinks each one is entirely different/unique). 

This is the whole reason SIMILAR and FORCE parameters exist....to cause Oracle to change the literal values into bind variables and thus reduce the number of entries in the shared pool.  SIMILAR allows more "similar but different" entries than FORCE does but still both settings are intended to cause fewer entries in the shared pool than EXACT.