Shared pool stores and caches the SQL and PL/SQL queries hitting the database , which avoids hard parsing the repetitive SQLs and It leads to increasing performance and memory usage.

shared_pool_size is the parameter which controls the size of shared_pool.But Most of the time DBAs wonder how much value should be the shared_pool_size. In that case shared pool advisory will be helpful.

What is shared pool advisory:

Shared pool advisory keeps track of usage of the shared pool and stores them in v$shared_pool_advice view.This view (v$shared_pool_advice) will give information on such items as an estimate on how much memory is being used by the library cache, the sizes of objects in the library cache, the estimated parse time and the time savings one might experience when parsing if one was to change the shared pool size.

Check whether shared pool advisory is enable?

SQL> show parameter statistics_level

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
statistics_level                     string                           TYPICAL

If the value of statistic_level is TYPICAL/ALL, then shared pool advisory is enable. If it is set TO BASIC, then change the same using alter system command.

shared pool advisory Report:

SQL> SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB", shared_pool_size_factor "Size Factor",
    estd_lc_time_saved "Time Saved in sec" FROM v$shared_pool_advice;

Size of Shared Pool in MB Size Factor Time Saved in sec
------------------------- ----------- -----------------
                    16384          .8         893075908
                    17920        .875         991632104
                    18176       .8875        1006089525
                    18432          .9        1020537992
                    18688       .9125        1034931997
                    18944        .925        1049236196
                    19200       .9375        1062352060
                    19456         .95        1073809821
                    19712       .9625        1085169153
                    19968        .975        1096499353
                    20224       .9875        1106038404
                    20480           1        1115516679 ----- >>>> current setting 
                    20736      1.0125        1115519068
                    20992       1.025        1115519253
                    21248      1.0375        1115519489
                    21504        1.05        1115519791
                    21760      1.0625        1115520025
                    22016       1.075        1115520156
                    22272      1.0875        1115520339
                    22528         1.1        1115520500
                    22784      1.1125        1115520667
                    24576         1.2        1115521551
                    26624         1.3        1115522701
                    28672         1.4        1115523861
                    30720         1.5        1115525189
                    32768         1.6        1115526870
                    34816         1.7        1115528414
                    36864         1.8        1115529452
                    38912         1.9        1115530394
                    40960           2        1115531281

30 rows selected.

SIZE FACTOR 1 is the current setting of shared_pool(20480 MB). This report shows that, even if we increase the value of shared_pool size from 20480 to 40960MB, there hardly any increase in TIME_SAVED. So there is no need of changing the shared_pool size.

But if the report shows that by with increased size_factor and shared_pool size, the time_saved is more, then we can increase the shared_pool size accordingly.

performance Tuningsga PERFORMANCE TUNING