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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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,
Re: How can i calculate the UGA in Dedicated server [message #248952 is a reply to message #248895] Mon, 02 July 2007 18:21 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
check this

http://www.jlcomp.demon.co.uk/faq/pat_presentation.html
Previous Topic: OEM Login problem...please help!!!
Next Topic: Oracle 10g Agent Configuration
Goto Forum:
  


Current Time: Wed Dec 07 18:41:03 CST 2016

Total time taken to generate the page: 0.53484 seconds