You can directly enter SQL data manipulation language (DML) statements inside PL/SQL blocks, and you can use procedures supplied by Oracle to perform data definition language (DDL) statements.PL/SQL code runs on the server, so using PL/SQL lets you centralize significant parts of your database applications for increased maintainability and security.This parameter also constrains the size of the PL/SQL cursor cache used by PL/SQL to avoid reparsing the statements re-submittted by a user.In this first installment of this two-article series, we will explore this feature in more detail by means of following practical scenarios: Note that the CURSOR_TYPE (in this instance) is displaying the status of the cursor incorrectly as "DICTIONARY LOOKUP CURSOR CACHED".Thus, session cursor caching: Cursors cached in session cursor cache are managed using an LRU algorithm, which removes older entries from the session cursor cache to make room for newer ones whenever needed.To enable caching of session cursors, a positive integer value must be assigned to the initialization parameter SESSION_CACHED_CURSORS, which specifies the maximum number of session cursors to cache.The result set is fetched using this cursor variable and then it is closed.PL/SQL is a modern, block-structured programming language.
See Section 4.8.6 for details on cursor attributes.It also enables you to achieve a significant reduction of network overhead in client/server applications. The declarative part declares PL/SQL variables, exceptions, and cursors.The executable part contains PL/SQL code and SQL statements, and can contain nested blocks.Whereas hard parsing is a resource intensive operation, soft parse, although less expensive, also incurs some cost, as the server process has to search the library cache for previously parsed SQL, which requires the use of the library cache and shared pool latches.Latches can often become points of contention for busy OLTP systems, thereby affecting response time and scalability.
Hence, we have practically verifiedthat a SQL statement which has not been previously executed in an instance, when repeatedly executed in a session, gets cached at the end of the third execution. When the same SQL statement is issued in another session, it gets cached in that session’s cursor cache after the very first execution.