WL#2929: Stored Procedures: shared global cache
Affects: Server-7.0 — Status: Un-Assigned — Priority: Low
Implement shared global cache of stored procedures. Per Robin Schumacher, required for SSA: > - Cache management. The database sets up a cache per user per > connection for the prepared statements, which results into > excessive memory usage. > Chicago Metallic saw its memory usage go up to 100% when running > the batch installer. Instead we require a shared cache. -- Trudy Pelzer, 2005-12-15
Merge all per-connection stored routine caches into one. An entry in such cache for every stored procedure shall contain as many compiled stored procedure bodies, as have been ever simultaneously used by all connections of the server. This task does not require an Item tree structure reusable among multiple threads, as each thread uses an own separately compiled stored procedure body. Therefore, this task has no dependencies. Proof of concept: a stored procedure definition, that is used in a trigger's body, is already stored in the table cache within TABLE structure. As TABLE structure can be used (sequentially) by multiple threads, this design approach has certain degree of vitality. Additionally, the implementation of recursive stored procedures already utilizes the idea of multiple compiled instances of a stored procedure used in one connection. Rationale: 1) In order to stabilize triggers implementation, the main execution workflow of a stored procedure should rely on the same set of prerequirements, as the workflow of trigger execution. 2) A shared stored procedure cache of the described type will still allow for tangible memory savings, as more opportunities for reuse of compiled code will exist. 3) Cache invalidation should become simplier as there will be only one global cache, no per-connection caches.
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.