Doubt about Shared Pool Size - Variable Size!

[amitstora]

Hi!

Working on Oracle v8i with OS as Win2000/NT with 512 MB RAM with Dedicated Connection for sql*plus from clients to the server and DB_BLOCK_SIZE=8K.



SQL> select * from v$sga;

NAME VALUE
-------------------- ---------
Fixed Size 70924
Variable Size 49999872
Database Buffers 251658240
Redo Buffers 77824

SQL>

What is meant by Variable Size 49999872 and what could be the impact on the Database
if we increase or reduce it!By doing so will it enhance or decrease the performance of the
database!

We are running Oracle D2k Forms & reports v6i but sometimes while the developers work suddenly
Reports connection to the server gets hanged and we have to terminate the conection through
end task through task Manager!This is really frustating!

In addition to that,Forms and specially Reports are running slow.Sql*plus takes some time say
a minute to retrieve select statements from server!


What and where should we be looking into for correcting the errors!


Thanks & Regards,

Amit.

[soniaarora]

An excerpt from Tom's site:

The variable component of the SGA the sum of the "named" pools -- large pool,
java pool and shared pool.

The large pool is configured by the LARGE_POOL_SIZE init.ora parameter. It is
used for allocation of "big" chunks of memory such as used by MTS, Parallel
Query, and RMAN.

The java pool is configured by the JAVA_POOL_SIZE init.ora parameter. The Java
pool is a fixed amount of memory allocated for the JVM running in the database.

the shared pool is *mostly* configured by the SHARED_POOL_SIZE init.ora
parameter but many other things contribute to it. The shared pool is where
Oracle caches many bits of “program” data. When we parse a query – the results
of that are cached here. Before we go through the job of parsing an entire
query – Oracle searches here to see if the work has already been done. PLSQL
code that you run is cached here, so the next time you run it Oracle doesn’t
have to read it in from disk again. PLSQL code is not only cached here, it is
shared here as well. If you have 1,000 sessions all executing the same code,
only one copy of the code is loaded and shared amongst all sessions. Oracle
stores the system parameters in the shared pool. The data dictionary cache,
cached information about database objects, is stored here. In short, everything
but the kitchen sink is stored in the shared pool.

The shared pool is characterized by lots of small (4k or thereabouts) chunks of
memory. The memory in the shared pool is managed on a LRU basis. It is similar
the buffer cache in that respect – if you don’t use it, you’ll lose it. So, if
over time a cached parsed query plan is not reused, it will become subject to
aging out of the shared pool. Even PLSQL code, which can be rather large, is
managed in a paging mechanism so that when you execute code in a very large
package, only the code that is needed is loaded into the shared pool in small
chunks. If you don’t use it for an extended period of time, it will be aged out
if the shared pool fills up and space is needed for other objects.



So, in general -- the fixed size is computed for you when Oracle is compiled.
You can do nothing to affect it. The variable size is MOST affected by
java_pool_size + large_pool_size + shared_pool_size but other parameters will
contribute to it (eg: every control_file will consume 256 bytes of variable size
memory. If you have 4 control files, the SGA will have 1024 bytes set aside for
them) in a small way.

Note that the large_pool_size was added in 8.0 and the java_pool_size in 8.1.
Prior to that, shared_pool_size was the largest (but not only) contributor to
that size)

I think this helps