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.
