› Fóruns › Banco de dados Oracle › Aumentar memoria Oracle – › Aumentar memoria Oracle –
leandrolbsOlá,
1º Select = 0 rows.
e o resto:
Connected to Oracle Database 10g Release 10.2.0.1.0
Connected as jr
SQL>
SQL> SET LINESIZE 200
SQL> COLUMN sid FORMAT 9999
SQL> COLUMN serial# FORMAT 9999999
SQL> COLUMN machine FORMAT A30
SQL> COLUMN progress_pct FORMAT 99999999.00
SQL> COLUMN elapsed FORMAT A10
SQL> COLUMN remaining FORMAT A10
SQL> SELECT s.inst_id,
2 s.sid,
3 s.serial#,
4 s.username,s.osuser,
5 s.module,
6 ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
7 ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
8 ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
9 FROM gv$session s,
10 gv$session_longops sl
11 WHERE s.sid = sl.sid
12 AND s.inst_id = sl.inst_id
13 AND s.serial# = sl.serial#;
INST_ID SID SERIAL# USERNAME OSUSER MODULE ELAPSED REMAINING PROGRESS_PCT
1 232 778 ELZA elza Pos_Carteira_venc.exe 0:9 0:0 100
1 198 57 MARISA marisa RelResumoFolha.exe 0:18 0:0 100
SQL> SET LINESIZE 200
SQL> COLUMN username FORMAT A20
SQL> COLUMN module FORMAT A20
SQL> COLUMN osuser FORMAT A10
SQL> SELECT a.osuser,a.status,a.inst_id,
2 NVL(a.username,'(oracle)') AS username,
3 a.module,
4 a.program,
5 trunc(b.value/1024) AS memory_kb
6 FROM gv$session a,
7 gv$sesstat b,
8 gv$statname c
9 WHERE a.sid = b.sid
10 AND a.inst_id = b.inst_id
11 AND b.statistic# = c.statistic#
12 AND b.inst_id = c.inst_id
13 AND c.name = 'session pga memory'
14 AND a.program IS NOT NULL
15 ORDER BY b.value DESC;
OSUSER STATUS INST_ID USERNAME MODULE PROGRAM MEMORY_KB
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (LGWR) 4791
caetaninho INACTIVE 1 CAETANINHOPETRELLA PedidoVendaTS.exe PedidoVendaTS.exe 3255
petrella
Loja2 INACTIVE 1 MARCELA NotaFiscalSaidaComPe NotaFiscalSaidaComPedido.exe 3063
dido.exe
Loja2 INACTIVE 1 MARCELA PedidoVendaLoja.exe PedidoVendaLoja.exe 2231
Loja2 INACTIVE 1 MARCELA PedidoVendaLoja.exe PedidoVendaLoja.exe 2039
INACTIVE 1 SYSMAN OEM.SystemPool OMS 2022
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (DBW0) 1935
vagner INACTIVE 1 VAGNER FPG_CalculoFolha.exe FPG_CalculoFolha.exe 1911
NT AUTHORI INACTIVE 1 DBSNMP emagent.exe emagent.exe 1911
TYSYSTEM
alessandro INACTIVE 1 SAN Estoque_Disponivel.e Estoque_Disponivel.exe 1783
san xe
ALANBUENO INACTIVE 1 ALANBUENO PedidoVendaTS.exe PedidoVendaTS.exe 1719
INACTIVE 1 SYSMAN OEM.SystemPool OMS 1719
Loja2 INACTIVE 1 MARCELA NotaFiscalEletronica NotaFiscalEletronica.exe 1719
.exe
elza INACTIVE 1 ELZA Miner_Geral_Financei Miner_Geral_Financeiro.exe 1655
ro.exe
edilene INACTIVE 1 EDILENE AnaliseCliente.exe AnaliseCliente.exe 1591
producao1 INACTIVE 1 EXPEDICAO ConsistenciaRotulo.e ConsistenciaRotulo.exe 1591
xe
SERVER3le ACTIVE 1 JR PL/SQL Developer PlSqlDev.exe 1527
andro_jrti
LOJA INACTIVE 1 MONIQUE MovEstoquePA.exe MovEstoquePA.exe 1527
SERVER3le INACTIVE 1 JR PL/SQL Developer PlSqlDev.exe 1527
andro_jrti
ACTIVE 1 SYSMAN OEM.SystemPool OMS 1463
OSUSER STATUS INST_ID USERNAME MODULE PROGRAM MEMORY_KB
edilene INACTIVE 1 EDILENE AnaliseCliente.exe AnaliseCliente.exe 1463
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (MMON) 1455
franciscop INACTIVE 1 FRANCISCOPOMPEU PedidoVendaTS.exe PedidoVendaTS.exe 1335
ompeu
marisa INACTIVE 1 MARISA RelResumoFolha.exe RelResumoFolha.exe 1318
edilene INACTIVE 1 EDILENE AprovCredito.exe AprovCredito.exe 1271
ALEXANDRES INACTIVE 1 SILMARAANDREATTI PedidoVendaTS.exe PedidoVendaTS.exe 1271
ILMARA
Loja2 INACTIVE 1 MARCELA Empresa.exe Empresa.exe 1271
LOJA INACTIVE 1 MONIQUE MovEstoquePA.exe MovEstoquePA.exe 1207
laila INACTIVE 1 LAILA RecPagto.exe RecPagto.exe 1207
leandro INACTIVE 1 ALESSANDROPCP plano.exe plano.exe 1207
EDOARDOADR INACTIVE 1 EDOARDOADRIANA PedidoVendaTS.exe PedidoVendaTS.exe 1143
IANA
laila INACTIVE 1 LAILA ReciboRecisao.exe ReciboRecisao.exe 1143
NT AUTHORI ACTIVE 1 DBSNMP emagent.exe emagent.exe 1143
TYSYSTEM
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (CJQ0) 1087
laila INACTIVE 1 LAILA Funcionarios.exe Funcionarios.exe 1079
alessandra INACTIVE 1 ALESSANDRACARBONEZI Funcionarios.exe Funcionarios.exe 1015
carbonezi
cleversonh INACTIVE 1 CLEVERSONHUDSON PedidoVendaTS.exe PedidoVendaTS.exe 1015
udson
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (J008) 1015
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (q000) 1015
alessandro INACTIVE 1 SAN ModPCP.exe ModPCP.exe 951
san
vagner INACTIVE 1 VAGNER Movimentos.exe Movimentos.exe 951
OSUSER STATUS INST_ID USERNAME MODULE PROGRAM MEMORY_KB
laila INACTIVE 1 LAILA ModPCP.exe ModPCP.exe 951
vagner INACTIVE 1 VAGNER ModPCP.exe ModPCP.exe 951
alessandro INACTIVE 1 SAN Pedido_Compra.exe Pedido_Compra.exe 951
san
edilene ACTIVE 1 EDILENE AprovCredito.exe AprovCredito.exe 951
ELAINE INACTIVE 1 ELAINE ModPCP.exe ModPCP.exe 951
clementina INACTIVE 1 CLEMENTINA ModPCP.exe ModPCP.exe 951
marisa INACTIVE 1 MARISA ModPCP.exe ModPCP.exe 887
EDOARDOADR INACTIVE 1 EDOARDOADRIANA MenuTS.exe MenuTS.exe 887
IANA
clementina INACTIVE 1 CLEMENTINA Funcionarios.exe Funcionarios.exe 887
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (J005) 887
franciscop INACTIVE 1 FRANCISCOPOMPEU MenuTS.exe MenuTS.exe 887
ompeu
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (J002) 887
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (J004) 887
edilene INACTIVE 1 EDILENE ModPCP.exe ModPCP.exe 887
cleversonh INACTIVE 1 CLEVERSONHUDSON MenuTS.exe MenuTS.exe 887
udson
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (J003) 887
reginaldon INACTIVE 1 REGINALDONETO MenuTS.exe MenuTS.exe 887
eto
SERVER3le INACTIVE 1 JR PL/SQL Developer PlSqlDev.exe 823
andro_jrti
leandro INACTIVE 1 ALESSANDROPCP ModPCP.exe ModPCP.exe 823
edilene INACTIVE 1 EDILENE AnaliseCliente.exe AnaliseCliente.exe 823
LOJA INACTIVE 1 MONIQUE Empresa.exe Empresa.exe 823
OSUSER STATUS INST_ID USERNAME MODULE PROGRAM MEMORY_KB
Loja2 INACTIVE 1 MARCELA CabTabPreco.exe CabTabPreco.exe 823
elza INACTIVE 1 ELZA Pos_Carteira_venc.ex Pos_Carteira_venc.exe 823
e
producao1 INACTIVE 1 EXPEDICAO ModPCP.exe ModPCP.exe 823
ALANBUENO INACTIVE 1 ALANBUENO MenuTS.exe MenuTS.exe 823
alessandra INACTIVE 1 ALESSANDRACARBONEZI ModPCP.exe ModPCP.exe 823
carbonezi
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (J001) 823
cleversonh INACTIVE 1 CLEVERSONHUDSON PedidoVendaTS.exe PedidoVendaTS.exe 759
udson
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (SMON) 759
LOJA INACTIVE 1 MONIQUE NotaFiscalSaidaComPe NotaFiscalSaidaComPedido.exe 695
dido.exe
luizcarlos INACTIVE 1 LUIZCARLOS ModPCP.exe ModPCP.exe 695
SILMARAAND INACTIVE 1 SILMARAANDREATTI MenuTS.exe MenuTS.exe 631
REATTI
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (J000) 631
Loja2 INACTIVE 1 MARCELA ModPCP.exe ModPCP.exe 631
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (RECO) 631
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (J006) 631
LOJA INACTIVE 1 MONIQUE Entrada_ProdAcab_Doc Entrada_ProdAcab_Docto.exe 631
to.exe
LOJA INACTIVE 1 MONIQUE PedidoVendaLoja.exe PedidoVendaLoja.exe 567
caetaninho INACTIVE 1 CAETANINHOPETRELLA MenuTS.exe MenuTS.exe 567
petrella
EDOARDOADR INACTIVE 1 EDOARDOADRIANA PedidoVendaTS.exe PedidoVendaTS.exe 567
IANA
LOJA INACTIVE 1 MONIQUE Empresa.exe Empresa.exe 567
caetaninho INACTIVE 1 CAETANINHOPETRELLA PedidoVendaTS.exe PedidoVendaTS.exe 567
petrella
OSUSER STATUS INST_ID USERNAME MODULE PROGRAM MEMORY_KB
ALANBUENO INACTIVE 1 ALANBUENO PedidoVendaTS.exe PedidoVendaTS.exe 567
Loja2 INACTIVE 1 MARCELA Empresa.exe Empresa.exe 567
reginaldon INACTIVE 1 REGINALDONETO Miner_Pedido.exe Miner_Pedido.exe 567
eto
franciscop INACTIVE 1 FRANCISCOPOMPEU PedidoVendaTS.exe PedidoVendaTS.exe 567
ompeu
reginaldon INACTIVE 1 REGINALDONETO Miner_Pedido.exe Miner_Pedido.exe 567
eto
AMANDA INACTIVE 1 AMANDA ModPCP.exe ModPCP.exe 567
elza INACTIVE 1 ELZA ModPCP.exe ModPCP.exe 567
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (J007) 567
ALEXANDRES INACTIVE 1 SILMARAANDREATTI MenuTS.exe MenuTS.exe 567
ILMARA
LOJA INACTIVE 1 MONIQUE ModPCP.exe ModPCP.exe 567
edilene INACTIVE 1 EDILENE AnaliseCliente.exe AnaliseCliente.exe 503
INACTIVE 1 SYSMAN OMS OMS 503
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (J009) 503
INACTIVE 1 SYSMAN OMS OMS 503
ALEXANDRES INACTIVE 1 SILMARAANDREATTI PedidoVendaTS.exe PedidoVendaTS.exe 503
ILMARA
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (MMNL) 439
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (CKPT) 382
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (PMON) 311
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (MMAN) 311
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (PSP0) 311
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (QMNC) 311
OSUSER STATUS INST_ID USERNAME MODULE PROGRAM MEMORY_KB
SYSTEM ACTIVE 1 (oracle) ORACLE.EXE (q001) 311
105 rows selected
SQL> SET LINESIZE 200
SQL> SET PAGESIZE 1000
SQL> COLUMN username FORMAT A20
SQL> COLUMN event FORMAT A30
SQL> COLUMN wait_class FORMAT A15
SQL> SELECT s.inst_id,
2 NVL(s.username, '(oracle)') AS username,
3 s.sid,
4 s.serial#,
5 sw.event,
6 sw.wait_class,
7 sw.wait_time,
8 sw.seconds_in_wait,
9 sw.state
10 FROM gv$session_wait sw,
11 gv$session s
12 WHERE s.sid = sw.sid
13 AND s.inst_id = sw.inst_id and sw.event not in ('SQL*Net message from client')
14 ORDER BY sw.seconds_in_wait asc;
INST_ID USERNAME SID SERIAL# EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
1 EDILENE 71 2185 db file sequential read User I/O 0 0 WAITING
1 (oracle) 277 1 rdbms ipc message Idle 0 0 WAITING
1 (oracle) 276 1 rdbms ipc message Idle 0 0 WAITING
1 (oracle) 275 1 rdbms ipc message Idle 0 3 WAITING
1 DBSNMP 262 51 Streams AQ: waiting for messag Idle 0 5 WAITING
es in the queue
1 (oracle) 114 549 jobq slave wait Idle 0 7 WAITING
1 SYSMAN 254 6 wait for unread message on bro Idle 0 15 WAITING
adcast channel
1 (oracle) 271 1 rdbms ipc message Idle 0 30 WAITING
1 (oracle) 278 1 rdbms ipc message Idle 0 30 WAITING
1 (oracle) 62 711 jobq slave wait Idle 0 31 WAITING
1 (oracle) 82 984 jobq slave wait Idle 0 32 WAITING
1 (oracle) 129 327 jobq slave wait Idle 0 32 WAITING
1 (oracle) 111 716 jobq slave wait Idle 0 32 WAITING
1 (oracle) 100 793 jobq slave wait Idle 0 32 WAITING
1 (oracle) 223 633 jobq slave wait Idle 0 37 WAITING
1 (oracle) 208 276 jobq slave wait Idle 0 65 WAITING
1 (oracle) 138 75 jobq slave wait Idle 0 67 WAITING
1 (oracle) 149 176 jobq slave wait Idle 0 134 WAITING
1 (oracle) 272 1 rdbms ipc message Idle 0 188 WAITING
1 (oracle) 274 1 smon timer Idle 0 315 WAITING
1 (oracle) 280 1 pmon timer Idle 0 469 WAITING
1 (oracle) 279 1 rdbms ipc message Idle 0 1213 WAITING
1 (oracle) 273 1 rdbms ipc message Idle 0 1409 WAITING
1 (oracle) 270 1 rdbms ipc message Idle 0 2273 WAITING
1 (oracle) 256 4 Streams AQ: waiting for time m Idle 0 3611 WAITING
anagement or cleanup tasks
1 (oracle) 250 38 Streams AQ: qmn slave idle wai Idle 0 5044 WAITING
t
1 (oracle) 265 1 Streams AQ: qmn coordinator id Idle 0 5044 WAITING
le wait
27 rows selected
SQL> SELECT h.event
2 ,h.wait_class
3 ,count(*) total
4 ,sum(h.wait_time + h.time_waited)/1000000 total_wait_time
5 FROM
6 gv$active_session_history h,
7 v$event_name e
8 WHERE h.sample_time BETWEEN sysdate - (1/24) AND sysdate
9 AND h.event_id = e.event_id
10 GROUP BY h.event, h.wait_class
11 ORDER BY total_wait_time DESC;
EVENT WAIT_CLASS TOTAL TOTAL_WAIT_TIME
db file sequential read User I/O 2099 87.188512
enq: TM - contention Application 43 42.796171
log file switch (checkpoint in Configuration 50 38.859854
complete)
log file parallel write System I/O 241 34.37354
db file scattered read User I/O 632 29.551245
log file sync Commit 262 25.384334
control file parallel write System I/O 65 17.543877
log file switch completion Configuration 19 12.021062
buffer busy waits Concurrency 9 5.646401
db file parallel write System I/O 135 4.547604
latch: enqueue hash chains Other 3 3.334483
log buffer space Configuration 4 2.522565
log file sequential read System I/O 2 1.901553
kksfbc child completion Other 80 1.819761
db file parallel read User I/O 31 1.414713
enq: CF - contention Other 2 1.257112
direct path read User I/O 8 0.655086
control file sequential read System I/O 6 0.207457
direct path write User I/O 2 0.161346
os thread startup Concurrency 2 0.156018
read by other session User I/O 7 0.114254
library cache pin Concurrency 9 0.03606
direct path write temp User I/O 1 0.034761
direct path read temp User I/O 1 0.012068
log file single write System I/O 1 0.007393
SQL*Net more data to client Network 1 0.000706
latch: shared pool Concurrency 1 0.000113
library cache load lock Concurrency 2 0
null event Other 11 0
29 rows selected
SQL>