Home » RDBMS Server » Server Administration » Shared Pool Size More Than Expected (Oracle 10.1.0.2.0,HP-UX B.11.23)
Shared Pool Size More Than Expected [message #415966] Thu, 30 July 2009 00:08 Go to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
Hi All,i fired a query
rem :
rem Filename: sgamap.sql
rem Purpose: Script to Report SGA Memory Map
rem Author: Ahmed
rem Date Submitted:7-Jul-2007
rem Date Published:19-Jun-2007
rem -----------------------------------------------------------------------

set echo off
set feedback off
set linesize 512

prompt
prompt SGA Memory Map (overall)
prompt

column dummy noprint
column area format a20 heading 'Main SGA Areas'
column name format a20
column pool format a20
column bytes format 999,999,999,999
column sum(bytes) format 999,999,999,999

break on report
compute sum of sum(bytes) on report

SELECT 1 dummy, 'DB Buffer Cache' area, name, sum(bytes)
FROM v$sgastat
WHERE pool is null and
name = 'db_block_buffers'
group by name
union all
SELECT 2, 'Shared Pool', pool, sum(bytes)
FROM v$sgastat
WHERE pool = 'shared pool'
group by pool
union all
SELECT 3, 'Large Pool', pool, sum(bytes)
FROM v$sgastat
WHERE pool = 'large pool'
group by pool
union all
SELECT 4, 'Java Pool', pool, sum(bytes)
FROM v$sgastat
WHERE pool = 'java pool'
group by pool
union all
SELECT 5, 'Redo Log Buffer', name, sum(bytes)
FROM v$sgastat
WHERE pool is null and
name = 'log_buffer'
group by name
union all
SELECT 6, 'Fixed SGA', name, sum(bytes)
FROM v$sgastat
WHERE pool is null and
name = 'fixed_sga'
group by name
ORDER BY 4 desc;

column area format a20 heading 'Shared Pool Areas'

prompt
prompt SGA Memory Map (shared pool)
prompt

SELECT 'Shared Pool' area, name, sum(bytes)
FROM v$sgastat
WHERE pool = 'shared pool' and
name in ('library cache','dictionary cache','free memory','sql area')
group by name
union all
SELECT 'Shared Pool' area, 'miscellaneous', sum(bytes)
FROM v$sgastat
WHERE pool = 'shared pool' and
name not in ('library cache','dictionary cache','free memory','sql area')
group by pool
order by 3 desc;

I am getting the result as
Main SGA Areas NAME SUM(BYTES)
-------------------- -------------------- ----------------
Shared Pool shared pool 2,142,617,936
Large Pool large pool 100,663,296
Java Pool java pool 8,388,608
Redo Log Buffer log_buffer 2,097,152
Fixed SGA fixed_sga 1,301,608
----------------
2,255,068,600
Shared Pool Areas NAME SUM(BYTES)
-------------------- -------------------- ----------------
Shared Pool miscellaneous 1,907,951,456
Shared Pool sql area 130,203,408
Shared Pool library cache 84,111,528
Shared Pool free memory 20,351,544
----------------
sum 2,142,617,936

from here i refer that my shared pool size is 2 gb but my sga_max_size is 1gb how is this possible and what is miscellaneous part in shared pool it is occupying a lot of memmory.
i have also fired a TOP command on o/s prompt
2041800K (1209972K) real, 2488124K (1536608K) virtual, 3821840K free
there is no increase in the memory than why shared pool size is more than expected.Thanx in advance




Re: Shared Pool Size More Than Expected [message #415982 is a reply to message #415966] Thu, 30 July 2009 00:52 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From one of your previous topic:
Michel Cadot wrote on Thu, 16 April 2009 10:48
...
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel


Previous Topic: Oracle XML DB
Next Topic: Tablespace usages
Goto Forum:
  


Current Time: Mon Dec 05 06:30:32 CST 2016

Total time taken to generate the page: 0.15538 seconds