This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

How to see the real SHARED POOL being used ?

Yockee
Yockee Member Posts: 24 Green Ribbon

Hi,

I am having problem in determining the size of Shared Pool. I want to know the utilization of the shared pool as mention here : (https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_sga_pool_wastage_shared_free_pct.html)

When I use query : "SELECT pool, sum(bytes)/1024/1024/1024 AS "Size (GB)" FROM v$sgastat WHERE pool = 'shared pool' GROUP BY pool;"

select sum(bytes)/1024/1024/1024 AS "Size (GB)" from v$sgastat where pool = 'shared pool'

It gives me : 2.7 GB.

But, when I use : "select component, current_size, max_size, granule_size, last_oper_type

from v$memory_dynamic_components;", it gives me 6,106,906,624 (I assume this is in Bytes, so it is equal to 5.6875 GB).

Questions are :

  • What are the differences between the 2 queries ? Why differ almost twice between v$sgastat and v$memory_dynamic_components
  • For each one, Which query should I use for what ?
  • For my case in determining the utilization of memory, which one should i use ?

This is confusing to me

Please help me.

Thanks