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

Related Topics

Useful TFACTL commands
How to generate AWR report in RAC
How to install trace file analyzer( TFACTL)
What is colored sql_id in dba_hist_colored_sql
How to drop SQL baselines in oracle
Shared Pool Advisory in oracle
How to generate ADDM report
How to get the execution plan for a SQL between two AWR snapshots
Useful ADRCI commands in oracle

You May Also Like

how to send mail using utl_mail in oracle 11g
Shell script to report failed login attempt in oracle
How to create encrypted tablespace in PDB( oracle 12c)
ORA-32774: more than one file was specified for bigfile tablespace
How to install oracle client in silent mode using response file
Shell script to monitor asm diskgroup usage
Apply database patch from OEM Cloud control
How to get the execution plan for a SQL between two AWR snapshots
ORA-01536: space quota exceeded for tablespace

From This Website

ORA-02304: invalid object identifier literal while import with CREATE TYPE OID
oraversion utility in oracle 18c - New feature
How to generate AWR report in RAC
orapwd tool for password file in oracle
Steps for changing public hostname for a standalone grid infrastructure
ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
ERROR: No checkpoint table specified for ADD REPLICAT
TNS-12542: TNS:address already in use
How to change the case (Lower to Upper and Vice Versa) in R