3 decode(a.type,'PACKAGE','PKG','PACKAGE BODY','PBDY','FUNCTION','FNC','PROCEDURE','PRC') typex,

Figure 7-10. Shown is output the OSM script dboc.sql followed by the actual SQL. This report was run immediately after the instance was cycled. Production Oracle systems will produce thousands of rows, and therefore the threshold parameters will need to be adjusted.

When forcing objects to be kept in the shared pool, keep in mind that we are, in effect, gaming Oracle's least recently used (LRU)-based shared pool memory management algorithm. We are saying that we know better than Oracle does. This could actually be the case, since most DBAs know their applications very well. But until you do, stuffing your shared pool full of packages like a Christmas stocking can actually increase the likelihood of "out of memory" errors, because little room is left for all the hundreds-if not thousands-of other shared pool objects. So, think carefully before using this procedure.