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. 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.

1

SQL> show parameter statistics_level