Getting Contiguous Space Currently in the Shared Pool(Oracle10g Tuning Technology)

Why does the shared pool return errors when an object is loaded? The answer is that a large

enough piece of the shared pool is not available to fit the piece of code. We saw in the last

section how to find the size of the code that you have. We also saw in a previous section how to

pin pieces of code into the shared pool. Now, we must look at the query that will tell you which

code, that has made it into the shared pool, is either very large and should be pinned or should

be investigated and shortened if possible.

The following query accesses an x$ table (see Chapter 15), and you must be the SYS user to

access these tables:

select ksmchsiz, ksmchcom

from x$ksmsp

where ksmchsiz > 10000

and ksmchcom like '%PL/SQL%';

This query shows that the packages that have been accessed are very large and should be

pinned at the time that the database has started. If the last line of this query is eliminated, it will

also show the large pieces of free memory (KSMCHCOM = ‘free memory’ and KSMCHCOM =

‘permanent memory’) that are still available (unfragmented) for future large pieces of code to be

loaded.

TIP

Query x$ksmsp to find all large pieces of PL/SQL that have appeared

in the shared pool. These are candidates to be pinned when the

database has started.