The one basic rule for tuning the shared pool is to
avoid or minimize parses of any kind, whether they be
soft parses or hard parses.


Parses can only be avoided by your developers by efficiently coding
the application.

As a DBA you can only minimize parses.

So, if you have lot have hard parses, Then you have to convert
them to soft one's.

And, if you have lot of soft parses then you have to minimize them.

This article focuses on tuning your shared pool using AWR or statspack reports
along with some init parameters.



Load profile

Code:
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
   DB Time(s):         0.8                   2.5
      DB CPU(s):                   0.8                   2.5
                  Redo size:              26,727.5              85,874.4
              Logical reads:              14,003.8              44,993.8
              Block changes:                  53.2                 171.0
             Physical reads:                 111.3                 357.7
            Physical writes:                  11.2                  36.0
                 User calls:                   7.1                  22.7
                     Parses:                 820.8               2,637.1
                Hard parses:                 743.5               2,388.9
    W/A MB processed:   828,525.2      2,662,020.7
                     Logons:                   0.1                   0.3
                   Executes:               1,025.7               3,295.4
    Rollbacks:                   0.0                   0.0
               Transactions:                   0.3

The important values to check here are "Parses & Hard Parses".
So, out of total 90% of the parses are hard parses (90.582% to be precise).

Possible reason for that is maybe cursors are not being shared.

The general rule is that

In an OLTP system number of hard parses should be few.

and

In and DWH and DSS environments the percentage of hard parses is normally higher.



Instance Efficiency

Code:
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.21    In-memory Sort %:  100.00
            Library Hit   %:   65.63        Soft Parse %:    9.41
         Execute to Parse %:   19.98         Latch Hit %:   99.98
Parse CPU to Parse Elapsd %:    0.01     % Non-Parse CPU:   22.67

Indicators which mean that there is problem with shared pool.

Library Hit% - Shows you in percentage the number of times a requested object
was found in the shared pool(ideal range (95 to 100)%).

Soft Parse % - Shows you in percentage the number of times a cursor was found
and reused (ideal range (95 to 100)%).

A low percentage means that cursors are not being reused.

Execute to Parse% - The execute to parse ratio should be very high in a ideal database (ideal range (95 to 100)%).

The execute to parse ratio is basically a measure between the number
Of times a sql is executed versus the number of times it is parsed.

The ratio will move higher as the number of executes go up, while
The number of parses either go down or remain the same.

The ratio will be close to zero if the number of executes and parses
Are almost equal.

The ratio will be negative executes are lower but the parses are higher.


Parse CPU to Parse Elasped% - Parse CPU means amount of CPU time used for parsing.
Parse Elapsed means amount of clock time used for parsing.

So, if for example if this percentage is high
say 87.88% then (1/.8788 = 1.13791534) that means for every 1 cpu second 1.13 seconds
of wall clock time has elapsed in order to do parses.

Low percentage in this ratio may be an indicator of latch problems.

Ideal Range is between 95% and 100%.


Top 5 Timed Events

Code:
Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                               67         98.21
db file sequential read               8,371           0      0   0.52 User I/O
latch: row cache objects                 16           0      8   0.19 Concurrency 
latch: shared pool                      956           0      0   0.15 Concurrency
log file sync                            25           0      2   0.06 Commit

          -------------------------------------------------------------
Both latch: row cach objects & latch: shared pool indicate that there is
some issue with shared pool (latch contention).

So, according to the given slide (0.19 + 0.15) 0.34% of the CPU time
is being eaten by latch contention.

"Row cache objects" is a latch which is used to protect the access to
data dictionary cache in the SGA.

A high value for this may indicate the following things.

1) There is excessive use of data dictionary information.
2) There is lot of hard parsing.

General solution to this problem is to increase the size of shared pool.


Latch: shared pool

Shared pool latch is used to protect memory allocation.

shared pool latch contention may indicate the following:

1) There is excessive hard parsing because the application is using literals
instead of bind variables.
2) Cursors are not being shared.


Time Model Statistics

Time related statistics presents the various operations which are consuming most of the database time.



Code:
Statistic Name                                       Time (s) % of DB Time
------------------------------------------ ------------------ ------------
DB CPU                                                    67.39      98.21
sql execute elasped time                                  61.13      89.09
parse time elasped                                        56.36      82.14
hard parse elasped time                                   49.99      72.86
PL/SQL execution elasped time                              4.30       6.26
PL/SQL compilation elasped time                            0.76       1.10
connection management call elasped time                    0.26       0.38
hard parse (sharing criteria) elasped time                 0.15       0.22
hard parse (bind mismatch) elasped time                    0.09       0.13
repeated bind elasped time                                 0.06       0.08
sequence load elasped time                                 0.03       0.04
DB time                                                   68.62    
background elasped time                                    1.50                  
background cpu time                                        0.12
-------------------------------------------------------------


According to oracle docs

Parse time elapsed - Amount of elapsed time spent parsing SQL statements. It includes both soft and

hard parse time.

Hard parse elapsed time - Amount of elapsed time spent hard parsing SQL statements.


So, according to the slide ((72.86*100)/82.14) 88.70% of DB time is spent on hard parses
which is not good.



Library Cache Activity.

Code:
Namespace     Get Requests   Pct Miss  Pin Requests Pct Miss  Reloads  Invalidations
----------    ------------   --------  ------------ --------  -------  -------------
BODY                     26      30.77           354     6.50       15              0  
CLUSTER                  576       1.04           322     1.86        0              0                  
INDEX                 35,320       0.07        53,320     0.07        0              0                  
SQL AREA             130,580      97.43       225,838    85.78      705            479                  

TABLE/PROCEDURE      405,501       0.35       313,834     1.31      908              0                 
TRIGGER                   90      15.56           100    14.00        0              0
All the compiled cursors are stored in SQL Area.

High Pct Miss can indicate the following things.

1) Cursors ar not being used.
2) Cursors are being aged out or reloaded frequently because of low memory.

The number of reloads should not be more than 1% of the number of pins.

In our case it is ((705*100)/225838) 0.31%.

The reloads to pin ration can be in excess of 1% in the following scenarios.

1) Shared parsed areas have been aged-out because of lack of space. Main solution
to this problem is to increase the shared pool size.

2) Shared parsed areas are invalidated. Possible solution for this is to do
house keeping service like index creation & gathering statistics when there is
low database activity.


So, in order to tune shared pool we have to avoid hard parses.

There are three main methods to do it.

1) Make sure your shared pool is sufficiently sized.
2) Tell your developers to use bind variables instead of literals.
3) The third method is to use cursor_sharing only if the application
has already been developed and does not use bind variables.
This method should be used as a last resort.



Sharing your Cursors.

Cursors can be shared by using the init parameter "cursor_sharing".

It can be set to three values.

1) EXACT(default) - cursors can be shared only when the sql statements text are exactly similar.

2) Similar(Deprecated in 11g) - cursors are shared when sql statements are identical.
The execution plan can be different depending on the literal value.

3) FORCE - SQL statements that are similar will share cursors and their is going
to be only one execution plan for all the sql statements.

Recommendations

1) cursor_sharing should be set to "EXACT" for DSS environments if complex
queries are being used.
2) cursor_sharing should be set to "FORCE" for OLTP environments if bind
variables are not being used.



Adaptive Cursor sharing (11g)

It is always recommended to use bind variables while developing applications
in order to gain performance benefits.

But sometimes instead of improving, The performance is degraded because of a combination of
bind peeking and skewness of data in certain columns.

In order to solve this problem oracle 11g has come up with a new feature
called "adaptive cursor sharing".

For this to work properly histograms should be created on columns where the
skewness of data is high. They are collected automatically in 11g.

Adaptive cursor sharing works by observing the execution plans of statements across
various executions. If it finds any suboptimal plans, then it allows the bind variables
to use different execution plans for the same statement in order to improve performance.

Minimizing overhead of soft parses.

SESSION_CACHED_CURSORS

The overhead on soft parses and aging out of cursors can be avoided by using the parameter "session_cached_cursors".

This works by keeping a cache of the closed cursors in the session memory.
So when a query is executed, The session searches the session cache first and if
the cursor is found, the soft parse is avoided.

NOTE: Even if the cursor is found in the session cache, It will still require
validation. It does that by validating that the opened cursor points to the right sql statement
in the shared pool. So, in essence its more like a "softer" soft parse.



SHARED_POOL_RESERVED_SIZE

The SHARED_POOL_RESERVED_SIZE parameter specifies the shared pool space that is reserved for large contiguous
requests for shared pool memory. This static parameter should be set high enough to avoid performance
degradation in the shared pool from situations where pool fragmentation forces Oracle to search for
free chunks of unused pool to satisfy the current request.

Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list
without flushing objects from the shared pool.

The default value is 5% of the shared pool size, while the maximum value is 50% of the shared pool size.
For interMedia applications, a value at or close to the maximum can provide performance benefits.

Recommended for databases with large chunks of data or LOB objects(audio, video, images).



SQL query result cache (11g).


SQL query result cache enables the database to store the query result sets in the shared pool.

In brief it stores the result of a query for future reuse.

This cache can be used by multiple statements from the same session as well as
by multiple sessions.

For example if the first session executes, after fething the data from the database it caches the result
in the SQL query result.

So, if any other instance executes the exact query, the query result will be fetched
from the SQL query result caches instead of the datafiles.


SQL query result cache in RAC.


Each RAC node has it's own private SQL query result cache.
One node cannot share the SQL query result cache of another node.


Initialization parameters.

RESULT_CACHE_MODE

MANUAL: You have to specify the RESULT_CACHE hint in the SQL statement in
order to store result in cache i.e "select /*+ RESULT_CACHE */ dept_id, emp_id, sal from employees;".


FORCE: All the result sets are stored in the cache by default.
if with setting you do not want a particular query to use this feature then
mention the NO_RESULT_CACHE hint i.e "select /*+ NO_RESULT_CACHE */ dept_id, emp_id, sal from employees".


RESULT_CACHE_MAX_SIZE


This parameter defines the memory allocation of the result cache.

Value of "0" means it is disabled.
Maxlimit is 75% of shared pool.

Default value is derived as following.

0.25% of memory_target or
0.5% of sga_target or
1% of shared_pool_size.


RESULT_CACHE_MAX_RESULT

Defines the max memory allocation for a single result.
Default is 5% of RESULT_CACHE_MAX_SIZE


DBMS_RESULT_CACHE package.


To check the status of the cache.

SQL> select dbms_result_cache.status from dual;

Code:
STATUS
--------------------------------------------------------------------------------
ENABLED

To check the cache memory usage.

SQL> set serveroutput on

SQL> exec dbms_result_cache.memory_report(detailed => true);

Code:
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 672K bytes (672 blocks)
Maximum Result Size = 33K bytes (33 blocks)
[Memory]
Total Memory = 107836 bytes [0.135% of the Shared Pool]
... Fixed Memory = 9440 bytes [0.012% of the Shared Pool]
....... Memory Mgr = 124 bytes
....... Bloom Fltr = 2K bytes
....... Cache Mgr  = 4416 bytes
....... State Objs = 2852 bytes
... Dynamic Memory = 98396 bytes [0.123% of the Shared Pool]
....... Overhead = 65628 bytes
........... Hash Table    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 8284 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 23 blocks
........... Used Memory = 9 blocks
............... Dependencies = 3 blocks (3 count)
............... Results = 6 blocks
................... SQL     = 6 blocks (6 count)

PL/SQL procedure successfully completed.

To remove the contents from result cache.

SQL> execute dbms_result_cache.flush;

PL/SQL procedure successfully completed.

NOTE: The contents from the query result cache will only be removed
if the cache is not in use. To purge successfully set "RESULT_CACHE_MAX_SIZE=0".

In order to reactivate the previous parameter you will have to restart
the instance after giving it a non zero value.

Things to consider for Query Result Cache.

Result cache is disabled for queries containing:

1) Temporary or dictionary tables
2) Nondeterministic PL/SQL functions.
3) CURRVAL and NEXTVAL(sequences).
4) SQL functions i.e current_date,sysdate etc.


Flashback queries can also be cached.


Result cache grows until it reaches its maximum size.


Objects or results in result cache are aged out based on the LRU algorithm.


References:

Oracle documentation.