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?

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:

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.