ORA-10639: Dump library cache during reparse loops
Cause: During this error a library cache dump is necessary , so enable librarycache dump if event is set.
Action: Dump a library cache if this event is set when reparse error.
ORA-10639: Dump library cache during reparse loops is an Oracle error message.
It occurs when the compilation process is unable to generate fully optimized SQL query execution plans because the cursor being inspected has already been parsed. This often happens when a previously generated SQL query execution plan is used for a query that has many variations in its parameter or data value ranges or columns.
This can cause excessive compilation of the query by the underlying database engine, leading to memory leaks in the library cache and eventually an ORA-10639 error. The library cache contains cursors that are parsed and optimized by Oracle’s Cost Based Optimizer (CBO). If a query is parsed and optimized excessively due to frequent changes in parameters or data values, the library cache can become bloated, leading to an insufficient amount of available memory in the Oracle SGA.
As a result, Oracle issues an ORA-10639 error, which suggests that the library cache needs to be dumpled. To resolve this issue, users should try to reduce the number of executed queries by reusing plans and reducing the number of data variations used in parameterised SQL queries.
In addition, implementing the following strategies can reduce the likelihood of this error:
• Use bind variables instead of literals
• Avoid multiple arguments for stored procedures
• Limit the use of literals when possible
• Use the query optimizer hint /*+ALL_ROWS*/
• Refrain from using the NO_merge hint
• Make use of query caching
• Tune the query optimizer parameter and adjust the library cache option to ensure sufficient memory available
• Establish a procedure to monitor the library cache and dump if needed through the ALTER SYSTEM command
• Avoid the use of parallel query hints where possible