loading
hello dummmy text

One of the major player in the SGA is SHARED_POOL, without which we can say that there are no query executions. During some performance tuning trials, you would have used ALTER SYSTEM command to flush out the contents in SHARED_POOL. Do you really know what exactly this command cleans out? As we know that internally SHARED_POOL has LIBRARY_CACHE, RESULT_CACHE, DATA_DICTIONARY_CACHE as most important parts. Are these components also gets cleaned when you FLUSH SHARED_POOL? Let us deep dive in to this instance functionality through this blog and get answers by a step by step analysis.

LIBRARY_CACHE plays a vital role in the articles which we discussed “EXPLAIN PLAN for multiple SQL statements”, “Ways of generating EXECUTION PLAN in Oracle Database”, “New SQL_ID ? Does not mean hard parsing or new EXECUTION plan” and many more.

Concepts:

Before we run the Case Study to find answers to our questions raised, let us look at some basic concepts on SHARED_POOL in Oracle Database. Being an Oracle DBA, the more you know about Architecture, the best you can work on Oracle Performance Tuning.

Untitled

This image will help you to quickly refresh about all that SHARED_POOL is capable being the part of SGA.

  • Library Cache: is used for storing the recently executed SQL and PL/SQL statements and hence these statements if encountered again will be shared which will subsequently boost the performance of the database.
  • Data Dictionary Cache: 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.
  • Result Cache: The server result cache is a memory pool within the shared pool. This memory pool consists of the SQL query result cache—which stores results of SQL queries—and the PL/SQL function result cache, which stores values returned by PL/SQL functions.
  • Private SQL Area: This behaves as UGA (User Global Area) for Shared Server architecture.

Environment details:

  • OS: OEL 64 bit
  • Database: Oracle 11gR2

Are you really flushing out SHARED_POOL with ALTER SYSTEM command in #Oracle #Database Click To Tweet

Case Study:

Before we actually flush SHARED POOL, let us check the contents of all the internal components by querying multiple dynamic performance views or packages as below.

1.Checking the contents of Library cache: A simple count from v$SQL tells us how many SQL statement details are currently available in LIBRARY CACHE.

SQL> select count(*) from v$sql;/

 COUNT(*)

———-

356

2. Checking the contents of Data Dictionary Cache: Running a query on v$rowcache dynamic performance view.

SET PAGESIZE 59

SET LINESIZE 100 echo off

COLUMN parameter FORMAT A20

COLUMN type FORMAT a10

COLUMN percent FORMAT 999.99 HEADING “%”;

SELECT   parameter, TYPE, gets, getmisses,

   (getmisses / gets * 100) PERCENT, COUNT, usage

  FROM v$rowcache

 WHERE gets > 100 AND getmisses > 0

ORDER BY parameter;SQL> SQL> SQL> SQL> SQL>   2    3    4    5

PARAMETER            TYPE             GETS  GETMISSES       %      COUNT      USAGE

———————————— ———————————— ———————– ———————- ——————- ———————-

dc_awr_control       PARENT            328          5    1.52          1          1

dc_global_oids       PARENT           2523        166    6.58         31         31

dc_histogram_data    SUBORDINAT       9604       1273   13.25        220        220

…

3. Checking the contents of Result cache: By running DBMS_RESULT_CACHE.MEMORY_REPORT procedure.

SQL> set serveroutput on

SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;

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  = 1248K bytes (1248 blocks)

Maximum Result Size = 62K bytes (62 blocks)

[Memory]

Total Memory = 5180 bytes [0.004% of the Shared Pool]

… Fixed Memory = 5180 bytes [0.004% of the Shared Pool]

… Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

Seems that result cache is empty. Let us load some results from a query using Oracle HINT.

SELECT /*+ RESULT_CACHE */ department_id, AVG(salary)

FROM hr.employees

GROUP BY department_id;

SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;

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  = 1248K bytes (1248 blocks)

Maximum Result Size = 62K bytes (62 blocks)

[Memory]

Total Memory = 103532 bytes [0.082% of the Shared Pool]

… Fixed Memory = 5180 bytes [0.004% of the Shared Pool]

… Dynamic Memory = 98352 bytes [0.078% of the Shared Pool]

……. Overhead = 65584 bytes

……. Cache Memory = 32K bytes (32 blocks)

……….. Unused Memory = 30 blocks

……….. Used Memory = 2 blocks

…………… Dependencies = 1 blocks (1 count)

…………… Results = 1 blocks

………………. SQL     = 1 blocks (1 count)

Now RESULT_CACHE is also loaded with some data.

4. Now that all the components of SHARED_POOL are filled with some content in it. Let us FLUSH it now.

SQL> alter system flush shared_pool;

System altered.

5. Now let us recheck contents in all the above components.

6. Contents of LIBRARY_CACHE after flush:

SQL> select count(*) from v$SQL;

 COUNT(*)

———-

62

Yeah – there was an impact.

Flushing SHARED_POOL means flushing LIBRARY_CAHCE #Oracle #Database Click To Tweet

7. Contents of DATA_DICTIONARY_CACHE after flush:

SET PAGESIZE 59

SET LINESIZE 100 echo off

COLUMN parameter FORMAT A20

COLUMN type FORMAT a10

COLUMN percent FORMAT 999.99 HEADING “%”;

SELECT   parameter, TYPE, gets, getmisses,

   (getmisses / gets * 100) PERCENT, COUNT, usage

  FROM v$rowcache

 WHERE gets > 100 AND getmisses > 0

ORDER BY parameter;SQL> SQL> SQL> SQL> SQL>   2    3    4    5

PARAMETER            TYPE             GETS  GETMISSES       %      COUNT      USAGE

———————————— ———————————— ———————– ———————- ——————- ———————-

dc_awr_control       PARENT            334          6    1.80          1          1

dc_global_oids       PARENT           2616        177    6.77         10         10

dc_histogram_data    SUBORDINAT       9828       1315   13.38         41         41

E

NOTE: There was no impact of SHARED_POOL flush.

8. Contents of RESULT_CACHE after flush:

SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;

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  = 1248K bytes (1248 blocks)

Maximum Result Size = 62K bytes (62 blocks)

[Memory]

Total Memory = 103532 bytes [0.082% of the Shared Pool]

… Fixed Memory = 5180 bytes [0.004% of the Shared Pool]

… Dynamic Memory = 98352 bytes [0.078% of the Shared Pool]

……. Overhead = 65584 bytes

……. Cache Memory = 32K bytes (32 blocks)

……….. Unused Memory = 30 blocks

……….. Used Memory = 2 blocks

…………… Dependencies = 1 blocks (1 count)

…………… Results = 1 blocks

………………. SQL     = 1 blocks (1 count)

NOTE: Even RESULT_CACHE. has no impact of SHARED_POOL flush.

Conclusion:

I love to see your comments!

Feel free to give additional information in the comments, that I can validate and include in blog posts or update this as well.

32 Comment

  1. Vinay Malla

    Thanks for educating us with interesting topics 🙂 Looking forward for some more posts 🙂

    Best Regards
    Vinay Malla

    1. Pawan Kumar Yaddanapudi

      My pleasure 🙂

  2. Petar Spasov

    Great post Pawan, looking forward to see more !

    1. Pawan Kumar Yaddanapudi

      Thanks Petar …

  3. Pinto

    Nice explanation…

    Regards,
    Pinto

    1. Pawan Kumar Yaddanapudi

      Thanks Pinto 🙂

  4. Ravikumar

    Very impressive Mr.Pawan…what a architecture..thanks a lot..

    1. Pawan Kumar Yaddanapudi

      Thank you Ravi 🙂

  5. Rakesh kumar

    Nice explanation of shared pool flush command.

    I have a Question raised in my mind …
    In which situation or performance issue … i will used it command.

    1. Pawan Kumar Yaddanapudi

      Thanks.

      You might be using it, when you wish to perform HARD parse of a query to identify bottlenecks.

  6. Yash

    Grt explanation pawan…thanks a lot for sharing knowledge and very much exited to see more post

    1. Pawan Kumar Yaddanapudi

      Thank you 🙂

  7. Gopal

    Nice explanation Pawan and all your posts in this blog are very informative. Thank you.

    1. Pawan Kumar Yaddanapudi

      Thank you Gopal 🙂

  8. Ramakrishna

    Actually not only this topic…all the topics in this blog pretty much different from other blogs and very useful as well…. Thanks a lot for the information

    1. Pawan Kumar Yaddanapudi

      Thanks Krishna.

  9. Rama Krishna

    Thanks Pavan for beautiful article. Your videos helped me in getting job quickly. Every day i visit at least once i keep an on this blog.

    1. Pawan Kumar Yaddanapudi

      Happy to hear that Rama Krishna. 🙂

  10. Raju

    Good explanation pawan..

    I have one question last week we faced one issue on prod servet geeting very slow..users complaining to db is very slow.they are unable perform tasks..CPU is running neraly 95% ..one of onsite DBA login to server flush memory..after that server is running fine and CPU also fine..

    My doubt is here what he is did I was not understood excatly…he did samething above you posted..?

    Alter system flush shared_pool..?
    I’m new to oracle DBA..I’m planing to learn oracle pls give me your number..

    1. Pawan Kumar Yaddanapudi

      The blog doesn’t mean to flush SHARED_POOL as soon as you see some issue. It explains the actuals when you flush SHARED_POOL. Deciding to flush shared pool or not is only after doing good research on performance of the database at memory level. You can find the number in “Contact US” page…

  11. Raja

    Hello Pawan,

    Your videos, articles, your voice, the way of teaching and everything is amazing. Heads up to you sir.

    1. Pawan Kumar Yaddanapudi

      Cheers Raja !!! Good that it helps you

  12. Younus

    Awesome.. Really liked your way of explanation.

    1. Pawan Kumar Yaddanapudi

      Thank you !!!

  13. shihab abdulrahiman

    Great Explanation Mr. Pawan

  14. Vijay

    Hi Pawan,

    when we tried to query Result_Cache memory report getting below error.Please advise.

    SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;
    BEGIN DBMS_RESULT_CACHE.MEMORY_REPORT; END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00201: identifier ‘DBMS_RESULT_CACHE’ must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    1. Pawan Kumar Yaddanapudi

      Can you please share following details.
      Which version of Oracle database?
      With which user did you login?

  15. Vijay

    database version :11.2.0.4
    logging with our individual ID we won’t having some of the sysdba privileges.Actually getting below error.

    ORA-04031: unable to allocate 65560 bytes of shared memory (“large pool”,”unknown object”,”large pool”,”PX msg pool”)

  16. Satyanarayana k v

    Nice blog, this is useful info.

    1. Pawan Kumar Yaddanapudi

      Thank you !!!

  17. Jijokm

    Nice Explanation..

  18. prathap reddy

    Useful article, thank you so much for uploading

Write a Reply or Comment

Your email address will not be published. Required fields are marked *

Knowledge That Can Always Keep Your Inbox Informed