13-6. Improving Initial Execution Running Time

Problem

You have a procedure that you run frequently, and you want to improve its overall running time by minimizing its startup time.

Solution

Use the DBMS_SHAPRED_POOL.KEEP procedure to keep a permanent copy of your code in the shared memory pool. For example, the following statement pins the procedure my_large_procedure in the database’s shared memory pool:

DBMS_SHARED_POOL.KEEP (    Name => 'my_large_procedure',    flag => 'P');

How It Works

The DBMS_SHARED_POOL.KEEP procedure permanently keeps your code in the shared memory pool. By default, when PL/SQL code is executed, Oracle must first read the entire block of code into memory if it isn't already there from a previous execution. As additional ...

Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.