SHARED_POOL_SIZE
---------------
The Shared_Pool_Size is specific to all versions of Oracle and is measured in bytes. This is the memory that is allocated to Oracle for areas like the data dictionary, stored procedures, and statements. Comprising a large part of the SGA, the Shared_Pool_Size is comprised of the dictionary cache and library cache, and just like DB_Block_Buffers above, should not be set too low or too high.
The Shared_Pool_Size can be monitored through the data dictionary cache and the library cache. Both should be continuously monitored for an appropriate hit ratio.
When a user processes a SQL statement, Oracle references the data dictionary several times. Reducing physical disk IO is very important, so the more information that is stored in memory, the less that needs to be read from disk.
The data dictionary cache is very important in this respect because this is where the data dictionary components are buffered (think of it as a print
buffer). The data dictionary component can be monitored via v$rowcache using the following select statement:
select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
from v$rowcache;
You should see output similar to the following:
Hit Ratio
95.40%
You should aim to have this value above 90%. The exception is when the database is first started, the value will be somewhere around 85%.
The library cache consists of shared SQL and PL/SQL areas. When SQL is executed, the statement has to be parsed (or taken apart for further clarification). The library cache reduces the overhead of this by maintaining parsed SQL and PL/SQL in the library cache. When there is a subsequent execution of this statement, there will not be the need to re-parse the statement. Essentially, you are reducing the work of the database. If, for example, the customer is on an OLTP system which consistently issues the same SQL, performance is enhanced. Of course, using bind variables to have sharable versions of the statements.
We have discussed having the Shared_Pool_Size set too low, but adverse effects may comes from setting the Shared_Pool_Size too high as well. Having the shared pool set too large increase the probability of latch contention when the database needs to find a free peace of memory to load a new statement. You can query v$sgastat to show the available free memory. This will tell you memory is being wasted. As an example, let's look at the following problem:
select name, bytes/1024/1024 "Size in MB"
from v$sgastat
where name='free memory';
You should see output similar to the following:
NAME Size in MB
Free memory 39.6002884
What this return would tell you is that there is 39M of free memory in the shared pool, which would mean that the shared pool is being under utilized. If the shared pool was 70M, over half of it would be under utilized. This memory could be allocated elsewhere.
See <Note.62143.1> for further reference on tuning the shared pool.
|