Menu de navegação

[jspaulonciParticipante]

Bom dia pessoal, mais uma dúvida referente a outro parâmetro de memória.
Usando conexão dedicada , se eu tenho uma PGA_AGGREGATE_TARGET com 100mb quer dizer que cada conexão irá alocar 100mb de RAM da máquina para cada nova sessão, ou 100mb ficará compartilhado para todas as sessões ?
Eu leio, leio, leio documentação referente a esse parametro e só consigo entender dessa maneira, acho que estou entendo errado.

Abraços

João Paulo Spaulonci

[Rodrigo AlmeidaParticipante]

Vai alocar geral para todas as sessões, se estiver usando DEDICATED connection, com o uso do MTS, fica um pouco diferente.

Geralmente, uma sessão ocupa em torno de 1.3MB no banco de dados, e o PGA_AGGREGATED_TARGET, internamente possui o UGA que irá alocar essas sessões.

Abraços,

Rodrigo Almeida

[ramasineParticipante]

É isso aí..

O parâmetro PGA_AGGREGATE_TARGET tem o mesmo princípio da SGA_TARGET, ele estabelece um teto, UM LIMITE de alocação de memória aos processos/programas e o carinha que faz isso é exatamente a UGA (User Global Area)..

Veja abaixo:

UGA Memory

The UGA, or User Global Area, is allocated in the PGA for each session connected to Oracle in a dedicated server environment. The PGA is memory allocated at the client to hold a stack which contains all of the session’s variables, etc. In a Shared Server environment, Oracle allocates this memory in the Shapred Pool (the shared pool is contained in the SGA), for all sessions. This helps to reduce the PGA (client) memory footprint of Oracle, but will increase the SGA (shared pool) requirements.

E ainda rola o script abaixo para vc verificar o quanto de PGA e UGA seus processos estão usando!

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
/

Abs

[jspaulonciParticipante]

Ramasine..tem como vc dar uma olhada no script, está dando erro
ORA-00907 Missing Right Parenthesis

O erro está na linha
, lpad(s.machine,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

Não sei se você percebeu mas apareceu um caracter espcecial, na hora que você colou, uma carinha, é um emocion.

Estou tentando arrumar também

Obrigado

[ramasineParticipante]

Veja se agora vai!

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

Qq coisa fala!

Abs

[ramasineParticipante]

Veja se agora vai!

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

Qq coisa fala!

Abs

[ramasineParticipante]

Queri ver agora!! Desativei os Smileysssssssssssssssssss!!!

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

[jspaulonciParticipante]

Ramasine, funcionou , obrigado, só uma dúvida, a informações vem em bytes né ?

[ramasineParticipante]

É em bytes mesmo, mas fiz algumas alterações, veja se te atende!

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 round(ss.VALUE/1024/1024,2) 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 round(ss.VALUE/1024/1024,2) 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 round(ss.VALUE/1024,2)
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 round(ss.VALUE/1024,2) 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

Abs

[jspaulonciParticipante]

Passa por favor a query toda depois que você alterou…

Obrigado

[ramasineParticipante]

Ta aí, só está sem as definições de header..do sqlplus…

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 round(ss.VALUE/1024/1024,2) 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 round(ss.VALUE/1024/1024,2) 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 round(ss.VALUE/1024,2)
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 round(ss.VALUE/1024,2) 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

[David SiqueiraParticipante]

Sem querer se redundante João, segue abaixo as determinações da Oracle sobre este parametro :

[i]PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZE parameters.

Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the work areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.

When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET.[/i]

Espero que ajude.

Abraço!

[jspaulonciParticipante]

Moçada…mais uma dúvida, quando eu defino por exemplo um valor para a PGA_AGGREGATE_TARGET de 1000mb, quando a instância Oracle sobe ela já aloca o 1000m ou ela vai alocando conforme vai necessitando ?
Esta pergunta pra mim é importante pois caso ela alocar já no momento do startup, no meu caso, estou ocupando recursos de forma indevida .

Obrigado

João Paulo Spaulonci

[paleoParticipante]

Vai alocando conforme a necessidade. A medida que existam conecções , existe alocação.

[paleoParticipante]

So uma pergunta. Voce falaem gastar recursos de 1Gb em PGA. Quanto de memoria tem a sua configuração?

[Autor]

Posts