Pinning PLSQL Code in the SGA

Pinning PL/SQL Code in the SGA

One database tuning technique that isn't used often enough even though it can yield significant improvements is Oracle's ability to pin PL/SQL code within the SGA memory. After the PL/SQL code has been pinned, it cannot be aged out of the SGA memory for any reason. For PL/SQL code that is heavily referenced, this can yield substantial results. The problem is that the method to accomplish this is called to PL/SQL packages provided by Oracle, and not SQL commands. So many DBAs have yet to discover this simple yet powerful tuning technique. TOAD provides a screen that makes this process both simple and quick.

TOAD's Pinned Code screen is shown in Figure 6.37 and is accessible from the main menu at DBAPinned Code. Prior to using this screen, you must connect to Oracle as SYS and run the DBMSPOOL.SQL script found in Oracle's RDBMS admin directory. The screen itself is split into two halves . The top half shows any PL/SQL code currently in the SGA and whether or not it's been marked as pinned. The bottom half is a mini-schema browser for navigating schema's PL/SQL code. To pin code that's in the SGA, you merely select it on the grid and click the Pin Selected Code icon (the lock). To pin code not in the SGA, you navigate to it on the mini-schema browser and click the Pin Selected Code button. That's it. It is recommended that you do turn the autorefresh off because this tends to make the screen difficult to use, especially with low refresh intervals.

Figure 6.37. TOAD Pinned Code screen.