Shared POOL

Shared pool is very important part of the production system.It contains all the neccessary element for execution of the SQL statement and PL/SQL programs.

If you use shared pool effectively you can reduce resource consumption in at least four ways

• Parse overhead is avoided if the SQL statement is already in the shared pool. This saves CPU resources on the host and elapsed time for the end user.
• Latching resource usage is significantly reduced, which results in greater scalability.
• Shared pool memory requirements are reduced, because all applications use the same pool of SQL statements and dictionary resources.
• I/O resources are saved, because dictionary elements that are in the shared pool do not require disk access.

Main components of shared pool are library cache (executable forms of SQL cursors, PL/SQL programs, and Java classes.) and the dictionary cache (usernames, segment information, profile data, tablespace information, and sequence numbers. )

The Library Cache

The library cache stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. The dictionary cache stores data referenced from the data dictionary. This caches are managed by LRU algorithm to “age out” memory structures that have not been reused over time. Allocation of memory from the shared pool is performed in chunks. This allows large objects (over 5k) to be loaded into the cache without requiring a single contiguous area, hence reducing the possibility of running out of enough contiguous memory due to fragmentation. Starting with 9i The Shared Pool divide its shared memory areas into subpools. Each subpool will have Free List Buckets (containing pointers to memory chunks within the subpool ) and , memory structure entries, and LRU list. This architecture is designed to to increase the throughput of shared pool in that now each subpool is protected by a Pool child latch. This means there is no longer contention in the Shared Pool for a single latch as in earlier versions.

Infrequently, Java, PL/SQL, or SQL cursors may make allocations out of the shared pool that are larger than 5k then Oracle must search for and free enough memory to satisfy this request. This operation could conceivably hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation. To allow these allocations to occur most efficiently, Oracle segregates a small amount of the shared pool. This memory is used if the shared pool does not have enough space. The segregated area of the shared pool is called the reserved pool which is also divided into subpools. Smaller objects will not fragment the reserved list, helping to ensure the reserved list will have large contiguous chunks of memory. Once the memory allocated from the reserved list is freed, it returns to the reserved list.

The library cache holds the parsed and executable versions of SQL and PL/SQL code

                       

• Parsing, which includes syntactic and semantic verification of SQL statements and checking of object privileges to perform the actions.

• Optimization, where the Oracle optimizer evaluates how to process the statement with the
• least cost, after it evaluates several alternatives.

• Execution, where Oracle uses the optimized physical execution plan to perform the action
• stated in the SQL statement.

• Fetching, which only applies to SELECT statements where Oracle has to return rows to you.

This step isn’t necessary in any nonquery-type statements. Parsing is a resource-intensive operation, and if your application needs to execute the same

SQL statement repeatedly, having a parsed version in memory will reduce contention for latches, CPU, I/O, and memory usage. The first time Oracle parses a statement, it creates a parse tree. The optimization step is necessary only for the first execution of a SQL statement. Once the statement is optimized, the best access path is encapsulated in the access plan. Both the parse tree and the access plan are stored in the library cache before the statement is executed for the first time. Future invocation of the same statement will need to go through only the last stage, execution, which avoids the overhead of parsing and optimizing as long as Oracle can find the parse tree and access plan in the library cache. Of course, if the statement is a SQL query, the last step will be the fetch

operation.

The library cache, being limited in size, discards old SQL statements when there’s no more room for new SQL statements. The only way you can use a parsed statement repeatedly for multiple executions is if a SQL statement is identical to the parsed statement. Two SQL statements are identical if they use exactly the same code, including case and spaces. The reason for this is that when Oracle compares a new statement to existing statements in the library cache, it uses simple string comparisons. In addition, any bind variables used must be similar in data type and size. Here are a couple of examples that show you how picky Oracle is when it comes to considering whether two SQL statements are identical.

Data Dictionary Cache

This part of the Shared Pool memory structure is used for storing the most recently used data definitions in the Oracle DB. These data definitions may include information about: database files, tables, indexes, privileges, users, etc.

Caching these inforamtion in memory improves the performance especially for queries and updates using DML. During the parsing phase, the server process scans this memory structure to resolve the database object names and validate access.

SQL area.

 This area contains the binary form, executable by Oracle, of the SQL and PL/SQL cursors.

Find the indexes used or not used in query

Find the List of index which have been used in last 10 days and the way they used i.e range scan, unique sacn, fast full scan

SELECT

DISTINCT sql_id,object_owner owner, OPTIONs ,

object_name,TIMESTAMP NAME

FROM

dba_hist_sql_plan

WHERE

object_owner NOT LIKE '%SYS%'

AND operation = 'INDEX'

--AND object_NAME='&INDEX' –for singe index

AND TRUNC(TIMESTAMP) BETWEEN  TRUNC(SYSDATE)- &startday AND  TRUNC(SYSDATE)- &endday

ORDER BY 1

&owner1,&owner2 --- owner name

&startday day from which you start i.e 10

&endday i.e 0

Find the index which have not been used in last 10 days

SELECT owner,table_name,index_name FROM dba_indexes WHERE owner  IN('&owner1','&owner2') AND

       index_name NOT IN

      (SELECT 

     object_name 

   FROM

 dba_hist_sql_plan

   WHERE

   object_owner NOT LIKE '%SYS%' AND

      operation = 'INDEX'   AND TRUNC(TIMESTAMP) BETWEEN  TRUNC(SYSDATE)- &startdays AND  TRUNC(SYSDATE)- &enddays

       ) ORDER BY 1,2

Find the sql and tables which are performing full table scan (FTS)

1) Below query give number of full table scan of tables in last one day along with owner.

  SELECT 

     object_owner,

     object_name ,COUNT(*) FTS_NO

   FROM

 dba_hist_sql_plan

   WHERE

   object_owner NOT LIKE '%SYS%' AND

      operation = 'TABLE ACCESS'

      AND TRUNC(TIMESTAMP)=TRUNC(SYSDATE)-1 AND

      options = 'FULL' GROUP BY object_name,object_owner ORDER BY 3 DESC

OBJECT_OWNER	OBJECT_NAME	FTS_NO
User1	T1	931
User1	T2	288

2)  Below query helps to find sql id and objects name along owner which has accessed using FTS in last one day

SELECT DISTINCT

    sql_id stid,

     object_owner owner,

     object_name NAME

   FROM

 dba_hist_sql_plan

   WHERE

   object_owner NOT LIKE '%SYS%' AND

      operation = 'TABLE ACCESS'

      AND TRUNC(TIMESTAMP)=TRUNC(SYSDATE)-1 AND

      options = 'FULL' ORDER BY 1

STID	OWNER	NAME
00hk65r3g82u4	User1	T1
00wh32sp1z8j8	User1	T2

3) Using above SQL_ID you can find the sql text.

   SELECT sql_text FROM v$sqltext WHERE sql_id='0601k3shzwrdj' ORDER BY piece

4) By combining the above queries you can find the tables accesed by FTS and related sql statements

SELECT sql_id,sql_text FROM v$sqltext WHERE sql_id IN (      SELECT DISTINCT

    sql_id

   FROM

 dba_hist_sql_plan

   WHERE

   object_owner NOT LIKE '%SYS%' AND

      operation = 'TABLE ACCESS'

      AND TRUNC(TIMESTAMP)=TRUNC(SYSDATE)-1 AND

      options = 'FULL'  ) ORDER BY 1,piece

Find the candidate for keep pool & how to keep objects in buffer cache keep pool

Oracle documentation recommend about the candidate for keep pool that is smaller then 10% of the size of

Default buffer pool and has incurred at least 1% I/O in system. In other word highly accessed and small tables are candidates for keep pool. The goal is to locate those objects that are small and experience a disproportional amount of I/O activity

• Cache tables & indexes where the table is small (<100 blocks) and the table experiences frequent full-table scans.
• Cache any objects that consume more than 10% of the size of their data buffer.

1a) To find the tables which are highly executed (access is high) and Performing FTS and small tables are good candidate of keep pool

SELECT

   p.owner,

   p.NAME,

t.CACHE,

 t.BUFFER_POOL,

s.blocks blocks,

   SUM(sa.executions) FTS_NO

FROM

   dba_tables t,

   dba_segments s,

  v$sqlarea sa,

  (SELECT DISTINCT

    sql_id stid,

     object_owner owner,

     object_name NAME

   FROM

   dba_hist_sql_plan

   WHERE

   object_owner NOT LIKE '%SYS%' AND

      operation = 'TABLE ACCESS'

      AND

      options = 'FULL') p

WHERE

 sa.sql_id = p.stid

 AND

   t.owner = s.owner

   AND

   t.table_name = s.segment_name

   AND

   t.table_name = p.NAME

   AND

   t.owner = p.owner

GROUP BY

   p.owner, p.NAME, t.CACHE, t.BUFFER_POOL, s.blocks

   HAVING

   SUM(sa.executions) > 10

   AND s.blocks < 100

ORDER BY

  SUM(sa.executions) DESC;

1b) You can also find the frequency for FTS using below query

SELECT 

   b.owner,object_type  mytype,

   object_name    myname,

   blocks,

   COUNT(1) buffers,

   AVG(tch) avg_touches

FROM

   sys. x$bh    a,

   dba_objects b,

   dba_segments s

WHERE

   a.obj = b.object_id

AND

   b.object_name = s.segment_name

AND

   b.owner NOT IN ('SYS','SYSTEM','SYSMAN')

GROUP BY

   object_name,

   object_type,

   blocks,

   obj,b.owner

HAVING

   AVG(tch) > 5

AND

   COUNT(1) > 20 ORDER BY 6 DESC

2a) Find the objects that consume more than 10% of the size of their data buffer

SELECT

   s.segment_type,t1.owner,s.segment_name, (SUM(num_blocks)/GREATEST(SUM(blocks), .001))*100 AS "segment_%_in_sga"

FROM

     (

SELECT

   o.owner          owner,

   o.object_name    object_name,

   o.subobject_name subobject_name,

   o.object_type    object_type,

   COUNT(DISTINCT FILE# || BLOCK#)         num_blocks

FROM

   dba_objects  o,

   v$bh         bh

WHERE

   o.data_object_id  = bh.objd

AND

   o.owner NOT IN ('SYS','SYSTEM')

AND

   bh.status != 'free'

GROUP BY

   o.owner,

   o.object_name,

   o.subobject_name,

   o.object_type

ORDER BY

   COUNT(DISTINCT FILE# || BLOCK#) DESC

)t1,

   dba_segments s

WHERE

   s.segment_name = t1.object_name

AND

   s.owner = t1.owner

AND

   s.segment_type = t1.object_type

AND

   NVL(s.partition_name,'-') = NVL(t1.subobject_name,'-')

AND

   BUFFER_POOL <> 'KEEP'

AND

   object_type IN ('TABLE','INDEX')

GROUP BY

   s.segment_type,

   t1.owner,

   s.segment_name

HAVING

   (SUM(num_blocks)/GREATEST(SUM(blocks), .001))*100 > 10

   ORDER BY 4 DESC

;

2b) Below query helps you to find the size of objects in SGA and compare it with actual size of segments .And % of segment present in sga.

   SELECT

s.segment_type,t1.owner,s.segment_name,S.BYTES/1024/1024/1024 total_size ,num_blocks*32/1024/1024 size_occup_In_SGA,

(num_blocks*32/1024/1024)*100/(S.BYTES/1024/1024/1024) SIZE_%_IN_SGA

FROM

  ( SELECT

   o.owner          owner,

   o.object_name    object_name,

   o.subobject_name subobject_name,

   o.object_type    object_type,

   COUNT(*)         num_blocks

FROM

   dba_objects  o,

   v$bh         bh

WHERE

   o.data_object_id  = bh.objd

AND

   o.owner NOT IN ('SYS','SYSTEM','SYSMAN')

AND

   bh.status != 'free'

GROUP BY

   o.owner,

   o.object_name,

   o.subobject_name,

   o.object_type

) t1,

   dba_segments s

WHERE

   s.segment_name = t1.object_name

AND

   s.owner = t1.owner

AND

   s.segment_type = t1.object_type

AND

   NVL(s.partition_name,'-') = NVL(t1.subobject_name,'-')

AND

   BUFFER_POOL != 'KEEP'

AND

   object_type IN ('TABLE','INDEX')

   ORDER BY 6 DESC

On the basis of above results you can decide which tables are the

Good candidate for the keep pool cache.

First adjust the size of parameter db_keep_cache_size (dynamic parameter ) to ensure that   cache has enough blocks to fully cache all of the segments that are assigned to the pool.

After choosing tables and indexes you can keep in pool using below commands

ALTER TABLE xyz STORAGE (BUFFER_POOL KEEP);

ALTER INDEX ind_xyz STORAGE (BUFFER_POOL KEEP);

Find the sql and tables which are performing full table scan (FTS)