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  |
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  |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
Goto Forum:
Current Time: Fri Oct 31 08:31:58 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
