Home » RDBMS Server » Enterprise Manager » How can i calculate the UGA in Dedicated server
| How can i calculate the UGA in Dedicated server [message #248559] |
Fri, 29 June 2007 09:06  |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
Hi All,
i have oracle9i r.2 running on Unix hp-ux and i want to know how can i determine the size of the uga because I've got an error from the server tell me that:
' Maximum percentage of shared pool allocated to UGA is too high'
so i did some investigation regarding the uga but i could not resolve the problem of resizing it:
i used the following :
----------------------
SQL> SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
AND stat.STATISTIC# = name.STATISTIC#
AND name.NAME = 'session uga memory';
USERNAME Current UGA memory
------------------------------ ---------------------------------------------
78472bytes
78472bytes
78472bytes
78472bytes
143880bytes
143880bytes
78472bytes
78472bytes
OPC_OP 78472bytes
OPC_OP 143880bytes
OPC_OP 209288bytes
OPC_OP 143880bytes
OPC_OP 78472bytes
OPC_OP 340104bytes
OPC_OP 340104bytes
OPC_OP 143880bytes
OPC_OP 209288bytes
OPC_OP 143880bytes
OPC_OP 209288bytes
OPC_OP 143880bytes
OPC_OP 143880bytes
OPC_OP 143880bytes
SYSTEM 78472bytes
OPC_OP 143880bytes
OPC_OP 340104bytes
OPC_OP 143880bytes
OPC_OP 143880bytes
SYS 667144bytes
OPC_OP 143880bytes
OPC_OP 78472bytes
OPC_OP 340104bytes
OPC_OP 143880bytes
OPC_OP 732552bytes
OPC_OP 143880bytes
and then :
SQL> SELECT user_name, sid, count(*) FROM v$open_cursor GROUP BY
user_name, sid;
USER_NAME SID COUNT(*)
------------------------------ ---------- ----------
SYS 41 6
OPC_OP 14 38
OPC_OP 15 31
OPC_OP 18 49
OPC_OP 19 73
OPC_OP 20 25
OPC_OP 21 24
OPC_OP 23 51
OPC_OP 25 10
OPC_OP 27 25
OPC_OP 28 9
OPC_OP 33 5
OPC_OP 35 16
OPC_OP 36 13
OPC_OP 42 11
OPC_OP 44 5
OPC_OP 45 74
OPC_OP 47 86
OPC_OP 53 16
SYSTEM 30 1
and the last one:
SQL>
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 999 HEADING 'SID'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC
/
SID Oracle User O/S User Session Program Machine PGA Memory PGA Memory Max UGA Memory UGA Memory MAX
---- ------------ --------- ------------------ -------- -------------- -------------- -------------- --------------
<LF: output removed because of user's request>
30 rows selected.
SQL>
if there is a way that i can resize the uga and know what the good size for it and why, would be very nice of you..
thanks in advance,
[Updated on: Sun, 16 March 2008 14:00] by Moderator Report message to a moderator |
|
|
|
| Re: How can i calculate the UGA in Dedicated server [message #248572 is a reply to message #248559] |
Fri, 29 June 2007 10:33  |
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
|
Senior Member |
|
|
column name format a25
column total format 999 heading 'Cnt'
column bytes format 9999,999,999 heading 'Total Bytes'
column avg format 99,999,999 heading 'Avg Bytes'
column min format 99,999,999 heading 'Min Bytes'
column max format 9999,999,999 heading 'Max Bytes'
ttitle 'PGA = dedicated server processes - UGA = Client machine process'
compute sum of minmem on report
compute sum of maxmem on report
break on report
select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3 |
|
|
|
| Re: How can i calculate the UGA in Dedicated server [message #248585 is a reply to message #248572] |
Fri, 29 June 2007 11:14 |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
Thank you for your replay,
the result of your query =122442560 --> (116.7 MB)??
the shared pool of the server =128 MB
the PGA =32 MB
the error was generated as an alert in case the uga is become more than the threshold of 10% :
'Maximum percentage of shared pool allocated to UGA (12.32%) too hi'...
is the solution for this is by decreasing the total of SHARED POOL to say =96MB..or els?
Thanks in advance,
|
|
|
|
| Re: How can i calculate the UGA in Dedicated server [message #248590 is a reply to message #248585] |
Fri, 29 June 2007 11:50 |
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
|
Senior Member |
|
|
| Quote: | the result of your query =122442560 --> (116.7 MB)??
|
Yes
Whats the value of LARGE_POOL_SIZE???
| Quote: | is the solution for this is by decreasing the total of SHARED POOL to say =96MB..or els?
|
I guess by LARGE_POOL_SIZE
Also, remember that when we use shared server, the UGA is located in the SGA.
This means that when switching over to shared server,
you must be able to accurately determine your expected UGA memory needs, and allocate appropriately
in the SGA, via the LARGE_POOL.
So, the SGA requirements for the
shared server configuration are typically very large. This memory must be pre-allocated and thus,
can only be used by the database. Contrast
this with dedicated server, where anyone can use any memory not allocated to the SGA. So, if the SGA
is much larger due to the UGA being located in it, where does the memory savings come from? It comes
from having that many less PGAs allocated.
Each dedicated/shared server has a PGA.
http://www.apress.com/betabooks/bb_content/31/5300_BETA_Ch05.html
[Updated on: Fri, 29 June 2007 11:51] Report message to a moderator |
|
|
|
| Re: How can i calculate the UGA in Dedicated server [message #248821 is a reply to message #248559] |
Mon, 02 July 2007 03:27 |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
Thank you for your replay,
There is 'No' large pool in the memory ..check this :
POOL NAME BYTES
----------- -------------------------- ----------
java pool free memory 33554432
*********** ----------
sum 33554432
shared pool character set object 323744
dictionary cache 4274432
enqueue 121040
errors 369744
event statistics per sess 680640
FileOpenBlock 113376
fixed allocation callback 552
free memory 20516008
joxs heap init 4240
KGK heap 2200
KGLS heap 3924376
kglsim sga 137544
KQR L PO 2382104
KQR M PO 2336496
KQR S SO 4864
krvxrr 253056
ksm_file2sga region 370496
KSXR large reply queue 167624
KSXR pending messages que 853952
KSXR receive buffers 1034000
library cache 35264040
message pool freequeue 771984
miscellaneous 15492264
parameters 53560
partitioning d 280224
PLS non-lib hp 3280
PL/SQL DIANA 647384
PL/SQL MPCODE 1460784
sessions 162240
sim memory hea 149216
sql area 56555864
SYSTEM PARAMETERS 171648
table definiti 12072
trigger defini 1064
trigger inform 472
1M buffer 2099200
*********** ----------
sum 150995784
buffer_cache 83886080
fixed_sga 736536
log_buffer 266240
*********** ----------
sum 84888856
40 rows selected.
and also :
SQL> select name,bytes
from v$sgastat
where pool='larg pool';
no rows selected
I know that the 'UGA' is only present in the Shared Pool if the Shared Server option is being used..and in case of dedicated server the UGA will maintained in the PGA.and this can easily seen by this query:
SELECT SID,SERVER FROM V$SESSION WHERE AUDSID =
(SELECT USERENV('SESSIONID') FROM DUAL);
SID SERVER
---------- ---------
1 DEDICATED
2 DEDICATED
3 DEDICATED
4 DEDICATED
5 DEDICATED
6 DEDICATED
7 DEDICATED
8 DEDICATED
47 DEDICATED
9 rows selected.
When i issue the following queries, i got:
the UGA for 1 session:
SELECT SUM(VALUE)/1024/1024 || ' MB' "TOTAL MEMORY FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
TOTAL MEMORY FOR ALL SESSIONS
-------------------------------------------
4,66538238525390625 MB
the UGA for all sessions :
SELECT SUM(VALUE)/1024/1024 || ' MB' "TOTAL MAX MEM FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory max'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
TOTAL MAX MEM FOR ALL SESSIONS
-------------------------------------------
15,89340972900390625 MB
SQL>
So according to the result of this queries the UGA exist in the PGA and not in the Shared Pool ..so why i got that error from the Shared Pool,and who can i use the values I've already got from querying the UGA to increase the PGA?
Thank in Advance,
|
|
|
|
| Re: How can i calculate the UGA in Dedicated server [message #248875 is a reply to message #248821] |
Mon, 02 July 2007 09:30 |
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
|
Senior Member |
|
|
SQL> select name,bytes
from v$sgastat
where pool='larg pool';
2 3
no rows selected
spelling mistake..
SQL> select name,bytes
2 from v$sgastat
3 where pool='large pool';
NAME BYTES
-------------------------- ----------
free memory 3677800
PX msg pool 516504
|
|
|
|
| Re: How can i calculate the UGA in Dedicated server [message #248895 is a reply to message #248875] |
Mon, 02 July 2007 10:44 |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
thanks for your correction,
but still the query shows no large pool:
SQL> select name,bytes
from v$sgastat
where pool='large pool';
no rows selected
and the error generate the following result:
SQL>
Report For Database openview
Fri Jun 22 07:45:01 2007
Metric SessionUgamemoryMaxPct (0095)
SID TYPE SERIAL# USERNAME SERVER OSUSER PROCESS UGA MAX
----- ---- ------- --------------- --------- --------------- --------- ----------
9 USER 3869 OPC_OP DEDICATED root 3581 143880
10 USER 2719 OPC_OP DEDICATED root 3574 143880
14 USER 2352 OPC_OP DEDICATED root 3571 274696
15 USER 1808 OPC_OP DEDICATED root 3572 209288
17 USER 126 OPC_OP DEDICATED root 3581 143880
18 USER 2239 OPC_OP DEDICATED root 3572 863368
19 USER 27573 OPC_OP DEDICATED root 24973 78472
20 USER 440 OPC_OP DEDICATED root 3579 143880
21 USER 140 OPC_OP DEDICATED root 3578 1517448
22 USER 17 OPC_OP DEDICATED root 3577 143880
23 USER 281 OPC_OP DEDICATED root 3575 863368
25 USER 3453 OPC_OP DEDICATED root 10269 143880
28 USER 1804 OPC_OP DEDICATED root 3932 143880
33 USER 2663 OPC_OP DEDICATED root 10371 143880
34 USER 2716 OPC_OP DEDICATED root 3932 3283464
37 USER 53913 OPC_OP DEDICATED root 24973 3152648
42 USER 16332 SYSTEM DEDICATED daemon 26970 601736
43 USER 22623 OPC_OP DEDICATED root 4895 143880
44 USER 27428 OPC_OP DEDICATED root 3531 143880
46 USER 41067 OPC_OP DEDICATED root 3573 143880
47 USER 253 OPC_OP DEDICATED root 4895 3283464
thanks in advance,
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 23:40:09 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
