Document Type | Troubleshooting
Category | Administration
Applicable Product Versions | 6FS02, 6FS03, 6FS04, 6FS05, 6FS06, 6FS07, 6FS07PS, 7FS01, 7FS02, 7FS02PS
Document Number | TADTS058
Issue
An error code ERROR_TOO_MANY_OPEN_CURSORS_USING_SAME_OBJECT(-12098) occurred due to a phenomenon where queries executed internally by Tibero were invalidated but not cache out and remained.
NoteWhat is cache out? It means that a data block is removed from the Buffer Cache.
Cause
- The error occurred due to repeated generation of Physical Plans (pp) for the same SQL statement.
- The error occurred because the maximum limit that can be loaded into the shared pool was reached.
According to Tibero policy, there is a maximum number of pps that can be generated for the same SQL statement. (Default value 32767, _SC_OBJ_PIN_COUNT_MAX: a parameter setting that limits the Pin count of Shared Cursor (SC) objects and can be changed.) Generally, once a pp is generated, it is loaded into the pp cache for reuse, and even if a previously generated pp becomes invalid and another pp is generated, when the shared pool capacity is judged insufficient, the invalid pp is cache out, so reaching the maximum limit rarely occurs. However, if DML and truncate are repeatedly executed causing repeated generation of pps for a single SQL statement, this error may occur by reaching the maximum number of pps before the shared pool capacity runs out.
Solutions
SQL> select count(*) from v$sql where sql_text like 'SELECT /*+ default_stat%';
Depending on the query result value, the resolution method is divided into two as follows.
1. When the value does not exceed 32,768
_USE_LAZY_SC_OBJ_FREE=N
Apply the parameter as above: _USE_LAZY_SC_OBJ_FREE=N.
Note_USE_LAZY_SC_OBJ_FREE is a parameter that controls whether to use the Lazy Free (delayed release) feature of Shared Cursor Objects. It adjusts to delay (free delay) releasing Shared Cursor objects (SC Objects) under certain conditions instead of immediate removal, thereby improving performance and reducing contention.
2. When the value exceeds 32,768
Resolve by applying a patch. (Applied patch: 134147)
CautionApply the patch through technical support provided by Tmax Tibero.