Divide the shared pool into fixed and variable structures 1

[pissallio]

As we know the shared pool enviroment contains both fixed and variable structures,what's the benefit of that ?
If the shared pool enviroment contains only fixed or variable structures, what will be the result ?

 

[SantaMufasa]

Pissallio,

Your enquiry is similar to saying, "We know that an automobile is composed of several systems, including the engine and the chassis. What is the benefit of that? If the auto contained just engine or just chassis, what will be the result?"

You really cannot have a working vehicle with just one system or the other. The same is true of Oracle: Oracle doesn't work if you have just the fixed or just the variable portions of the SGA.

The nice thing about using an automobile is that you can still use the automobile without being an automotive engineer. The nice thing about Oracle is that you can use it to great advantage without understanding the intricasies of the fixed versus variable portions of the shared pool, especially since we do not have any control over the fixed portion...it is compiled into your installation of Oracle.

As a DBA, I do have control over certain portions of the shared pool. I know that for a successfully running Oracle instance that does particular work for a certain number of concurrent users, I allocate from 200MB-400MB of shared pool and life is good.

But if you are the type that likes to know how your auto was built, then here are some additional concepts (compliments of Tom Kite):

"The fixed SGA is a component of the SGA that varies in size from platform to platform and release to release. It is “compiled” into the database. The fixed SGA contains a set of variables that point to the other components of the SGA and variables that contain the values of various parameters. The size of the fixed SGA is something over which we have no control and it is generally very small. Think of this area as a “bootstrap” section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.

You can see the components of the "variable size" with the following query:

compute sum of bytes on pool
break on pool skip 1
select pool, name, bytes
  from v$sgastat
  order by pool, name;

POOL        NAME                                BYTES
----------- ------------------------------ ----------
java pool   free memory                      18366464
            memory in use                     2605056
***********                                ----------
sum                                          20971520

large pool  free memory                       6079520
            session heap                        64480
***********                                ----------
sum                                           6144000

shared pool Checkpoint queue                    73764
            KGFF heap                            5900
            KGK heap                            17556
            KQLS heap                          554560
            PL/SQL DIANA                       364292
            PL/SQL MPCODE                      138396
            PLS non-lib hp                       2096
            SYSTEM PARAMETERS                   61856
            State objects                      125464
            VIRTUAL CIRCUITS                    97752
            character set object                58936
            db_block_buffers                   408000
            db_block_hash_buckets              179128
            db_files                           370988
            dictionary cache                   319604
            distributed_transactions-          180152
            dlo fib struct                      40980
            enqueue_resources                   94176
            event statistics per sess          201600
            file # translation table            65572
            fixed allocation callback             320
            free memory                       9973964
            joxlod: in ehe                      52556
            joxlod: in phe                       4144
            joxs heap init                        356
            library cache                     1403012
            message pool freequeue             231152
            miscellaneous                      562744
            processes                           40000
            sessions                           127920
            sql area                          2115092
            table columns                       19812
            transaction_branches               368000
            transactions                        58872
            trigger defini                       2792
            trigger inform                        520
***********                                ----------
sum                                          18322028

            db_block_buffers                 24576000
            fixed_sga                           70924
            log_buffer                          66560
***********                                ----------
sum                                          24713484


43 rows selected.

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)

There are some nice diagrams and additional explanations of these components at:

http://www.oracle.com/technology/products/oracle8i/htdocs/jserver_faq/815faq0009.html

.

I hope this helps,

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 16:57 (28Oct04) UTC (aka "GMT" and "Zulu"),
@ 09:57 (28Oct04) Mountain Time

 

[pissallio]

Hi,santa
Thanks for your reply.
We can use "show sga" to list fixed size and variable size.
I think that "fixed size+variable size" should be equal to "java_pool_size + large_pool_size+shared_pool_size" which is configured in the init.ora file.
Am i right ?