O'Reilly logo

Oracle PL/SQL Programming, Third Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

PL/SQL’s Use of Memory in the Oracle Server

By economizing on its use of machine resources such as memory and CPU, Oracle can support tens of thousands of simultaneous users on a single database. Oracle’s memory management techniques have become quite sophisticated over the years, and correspondingly difficult to understand. Although administrators of busy databases need a thorough knowledge of memory management, advanced PL/SQL programmers should also have a good understanding of this topic. Virtually all PL/SQL programmers will want to know how to avoid undermining Oracle’s memory sharing algorithms.

Server Memory 101

Let’s first look at some basics. An Oracle database instance consists of a shared memory area known as the System Global Area (SGA), plus a number of background processes.[25] Although a large part of the SGA typically consists of buffer pools that cache table data, another part that’s of particular importance to PL/SQL performance is the shared pool. The shared pool performs two maincaching functions:

  • It holds metadata from the data dictionary

  • It holds parsed representations of SQL statements and PL/SQL programs

The first time any user session runs a particular PL/SQL program, Oracle puts the executable portion of the program into the part of the shared pool called the library cache . When that session needs to execute the same PL/SQL program, Oracle will re-use the cached copy of it, saving a trip to the relatively slow disks. Oracle can even use this cached ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required