PGA Top Consumer

set pagesize 1000

set lines 100

col sid format 9999

col username format a12

col module format a30

column pga_memory_mb format 9,999.99 heading "PGA MB"

column max_pga_memory_mb format 9,999.99 heading "PGA MAX|MB"

col service name format a20

col sql_text format a70 heading "Currently executing SQL"

set echo on

WITH pga AS

(SELECT sid,

ROUND(SUM(CASE name WHEN 'session pga memory'

THEN VALUE / 1048576 END),2) pga_memory_mb,

ROUND(SUM(CASE name WHEN 'session pga memory max'

THEN VALUE / 1048576 END),2) max_pga_memory_mb

FROM v$sesstat

JOIN v$statname USING (statistic#)

WHERE name IN ('session pga memory','session pga memory max' )

GROUP BY sid)

SELECT sid, username,s.module,

pga_memory_mb,

max_pga_memory_mb, substr(sql_text,1,70) sql_text

FROM v$session s

JOIN (SELECT sid, pga_memory_mb, max_pga_memory_mb,

RANK() OVER (ORDER BY pga_memory_mb DESC) pga_ranking

FROM pga)

USING (sid)

LEFT OUTER JOIN v$sql sql

ON (s.sql_id=sql.sql_id and s.sql_child_number=sql.child_number)

WHERE pga_ranking <=5

ORDER BY pga_ranking

/

PGA incease When???

Whenever the value of the v$sysstat statistic estimated PGA memory for one-pass exceeds pga_aggregate_target, then you’ll want to increase pga_aggregate_target.

select name,value/1024/1024 from v$sysstat where name like '%pga%';

select value/1024/1024 from v$parameter where name like '%pga%';

Whenever the value of the v$sysstat statistic workarea executions—multipass is greater than 1 percent, the database may benefit from additional RAM memory.

select name,value from v$sysstat where name = 'workarea executions - multipass';

You can overallocate PGA memory and you may consider reducing the value of pga_aggregate_target whenever the value of the v$sysstat row workarea executions—optimal consistently measures 100 percent.

select name,value from v$sysstat where name = 'workarea executions - optimal';

---------------------------PGA top consumer-----------------

set pagesize 1000

set lines 100

col sid format 9999

col username format a12

col module format a30

column pga_memory_mb format 9,999.99 heading "PGA MB"

column max_pga_memory_mb format 9,999.99 heading "PGA MAX|MB"

col service name format a20

col sql_text format a70 heading "Currently executing SQL"

set echo on

WITH pga AS

(SELECT sid,

ROUND(SUM(CASE name WHEN 'session pga memory'

THEN VALUE / 1048576 END),2) pga_memory_mb,

ROUND(SUM(CASE name WHEN 'session pga memory max'

THEN VALUE / 1048576 END),2) max_pga_memory_mb

FROM v$sesstat

JOIN v$statname USING (statistic#)

WHERE name IN ('session pga memory','session pga memory max' )

GROUP BY sid)

SELECT sid, username,s.module,

pga_memory_mb,

max_pga_memory_mb, substr(sql_text,1,70) sql_text

FROM v$session s

JOIN (SELECT sid, pga_memory_mb, max_pga_memory_mb,

RANK() OVER (ORDER BY pga_memory_mb DESC) pga_ranking

FROM pga)

USING (sid)

LEFT OUTER JOIN v$sql sql

ON (s.sql_id=sql.sql_id and s.sql_child_number=sql.child_number)

WHERE pga_ranking <=5

ORDER BY pga_ranking

/

--------------------------------------------

SGA PGA measuring

select * from v$sgastat order by 1;

select * from v$pgastat order by 1;

I noticed that you have some Pools in your SGA which are not used:

large pool free memory 209715200

But your PGA could reach about 340 Mo.

So, you may decrease about 160 Mo the large_pool_size parameter (you have 200 Mo free).

It will decrease the SGA (about 160 Mo).

Then you may increase the PGA_AGGREGATE_TARGET to 512 Mo.

The most important is that SGA + PGA remains below 2GB (except if you use /3GB parameter

which may help you to get 1 GB more).

------------------------Used SGA-----------------

select name, round(sum(mb),1) mb, round(sum(inuse),1) inuse

from (select case when name = 'buffer_cache'

then 'db_cache_size'

when name = 'log_buffer'

then 'log_buffer'

else pool

end name,

bytes/1024/1024 mb,

case when name <> 'free memory'

then bytes/1024/1024

end inuse

from v$sgastat

)group by name;

------------------------Free SGA-----------------

select name, round(sum(mb),1) mb, round(sum(inuse),1) free

from (select case when name = 'buffer_cache'

then 'db_cache_size'

when name = 'log_buffer'

then 'log_buffer'

else pool

end name,

bytes/1024/1024 mb,

case when name = 'free memory'

then bytes/1024/1024

end inuse

from v$sgastat

)group by name;

--------------------

select name,value from v$parameter where name ='sort_area_size';

---------------------------------- maximum PGA usage per process:--

select

max(pga_used_mem) max_pga_used_mem

, max(pga_alloc_mem) max_pga_alloc_mem

, max(pga_max_mem) max_pga_max_mem

from v$process

/

-----------sum of all current PGA usage per process---------

select

sum(pga_used_mem) sum_pga_used_mem

, sum(pga_alloc_mem) sum_pga_alloc_mem

, sum(pga_max_mem) sum_pga_max_mem

from v$process

/

-----------pga requirement as per high water mark

select

(select highwater from dba_high_water_mark_statistics where name = ('SESSIONS'))*(2048576+a.value+b.value)/1024/1024 pga_size_MB

from

v$parameter a,

v$parameter b

where

a.name = 'sort_area_size'

and

b.name = 'hash_area_size'

;

pga requirement as per high watermark

select

(select highwater from dba_high_water_mark_statistics where name = ('SESSIONS'))*(2048576+a.value+b.value)/1024/1024 pga_size_MB

from

v$parameter a,

v$parameter b

where

a.name = 'sort_area_size'

and

b.name = 'hash_area_size'

;

Buffer Cache Size

High CPU_COUNT and increased granule size can cause ORA-0431 error.

------CPU_COUNT specifies the number of CPUs available to Oracle. On single-CPU computers, the value of CPU_COUNT is 1.

Memory sizing depends on CPU_COUNT (No of processor groups).

Please use below formulas to calculate min buffer cache size

--Minimum Buffer Cache Size

10g : max(CPU_COUNT) * max(Granule size)

11g : max(4MB * CPU_COUNT)

Please note that If SGA_MAX_SIZE < 1GB then use Granule size = 4mb, SGA_MAX_SIZE > 1G then use Granule size = 8MB.

-- _PARALLEL_MIN_MESSAGE_POOL Size

If PARALLEL_AUTOMATIC_TUNING =TRUE then large pool is used for this area otherwise shared pool is used.

CPU_COUNT*PARALLEL_MAX_SERVERS*1.5*(OS msg bufferr size) OR CPU_COUNT*5*1.5*(OS message size)

-- Add extra 2MB per CPU_COUNT for shared pool.

Here is the example:-

Sun Solaris server has threaded CPUs. 2 physical CPUs has 8 cores, and each core has 8 threads, then Oracle evaluates CPU_COUNT = 2*8*8=128.

When SGA_MAX_SIZE=900MB,

Minimum Buffer Cache = CPU_COUNT *Granule size = 128*4M = 512MB

Shared Pool can use 338MB

When SGA_MAX_SIZE=1200MB,

Minimum Buffer Cache = CPU_COUNT *Granule size = 128*8M = 1024MB

Shared Pool can use 176 MB, so ORA-4031 occurs despite larger SGA_MAX_SIZE.

You need to manually tune CPU_COUNT parameter to resolve this error.