Menu de navegação

[Luigui27Participante]

Olá, estou com um database 10.2.0.3 em AIX 5.3, e estou tendo cada vez mais “hangs” na base, onde fica impossível até conectar, sendo necessário matar o smon e matar todas as conexões locais para reiniciar de novo o banco.

A 2 semanas atrás, consegui mapear que o servidor de aplicação, começa a abrir sessões com DLLHOST.exe (provavelmente oriundo de páginas ASP) infinitamente, e ficam presas na base. Ao mesmo tempo o DLLHOST.exe do servidor chega a 1GB e para também o servidor de aplicação.

Já possuo um script que mata as sessões SNIPPED de tempos em tempos, porém nas horas críticas não é suficiente.

Em paralelo, tenho o problema que as vezes aparece no alert a seguinte mensagem: “PMON failed to acquire latch”. Do mesmo modo, é necessário matar todas as sessões e matar o smon para resolver o problema.

Lendo a respeito, notei que existe um bug no 10.2.0.3, podendo ser visto no note 468740.1, onde a solução temporária é modificar o SGA_TARGET para 0 e assim acabando com o ASMM.

A minha dúvida é se alguém já passou por problema similar e se resolveu desse modo, além de que gostaria de saber se setar a SGA_TARGET para 0, como irá ficar os parâmetros internos da SGA logo após a mudança.

Abs a todos,
Luiz

[vieriParticipante]

Não fou muito fã de estudar BUG’s eles emburrecem…
faça oque a Oracle diz !!

Se vc setar o SGA_TARGET para 0 estará desligando o modo de gerênciamento automático.

Irá fica como em versões antigas.

Distribua o valor do parâmetro da SGA target entre os pools.

Básicos:
large_pool , java_pool , shared_pool, db_cache_size

[Luigui27Participante]

Oi Vieri, obrigado.

Mas a minha dúvida é como ficariam os parâmetros logo após a mudança. Ficariam como estavam antes de zerar?

E não me conformo em perder essa ótima funcionalidade do 10g por causa de um bug ainda sem solução (quase 1 ano).

Abs,
Luiz

[vieriParticipante]

os parâmetros irão ficar do jeito que estão agora.

confira com o comando show <nomeparametro); no sqlplus.

ajuste eles antes de setar para 0.

[vieriParticipante]

ops..

o comando é show parameters

😉

[Luigui27Participante]

De acordo com a Oracle, a solução seria a seguinte:

alter system set db_cache_size = 256M scope=both;
alter system set “_bloom_filter_enabled”=false scope=both;
alter system set “_shared_pool_reserved_pct”=10 scope=spfile;

Existe um ação continua quando você usa ASSM entre a Buffer Cache e a Shared Pool de tranfêrencia de dados, e com a reserva mínima acima, além de desligar o bloom_filter, você evita o hang da sua base, de acordo com a resposta do chamado.

Ainda estou questionando alguns dados, além de arranjar uma janela para essas modificações, mas assim que tiver uma resposta entro em contato.

Abs,
Luiz

[Ricardo Portilho ProniParticipante]

Oi Luiz.

Pode colocar aqui o resultado do SQL abaixo?

SET PAGESIZE 10000
SET LINESIZE 170
SELECT EVENT, AVERAGE_WAIT, TOTAL_TIMEOUTS FROM V$SYSTEM_EVENT WHERE TOTAL_TIMEOUTS > 0 ORDER BY TOTAL_TIMEOUTS;
SELECT EVENT, AVERAGE_WAIT, TOTAL_TIMEOUTS FROM V$SYSTEM_EVENT WHERE AVERAGE_WAIT > 0 ORDER BY AVERAGE_WAIT;

[Luigui27Participante]

Oi Ricardo, segue a informação:

SQL> SELECT EVENT, AVERAGE_WAIT, TOTAL_TIMEOUTS FROM V$SYSTEM_EVENT WHERE TOTAL_TIMEOUTS > 0 ORDER BY TOTAL_TIMEOUTS;

EVENT AVERAGE_WAIT TOTAL_TIMEOUTS

---

control file heartbeat 390.63 1
PX Deq: Signal ACK 2.83 3
LGWR wait for redo copy .02 3
Streams AQ: waiting for messages in the queue 358.2 3
latch: library cache 2.28 9
latch: shared pool 1.39 16
kksfbc child completion 4.89 24
smon timer 18297.17 71
log file sync .57 127
SGA: allocation forcing component growth 1.03 322
dispatcher timer 5815.74 384
virtual circuit status 2919.09 768
PX Idle Wait 193.93 1653
SGA: MMAN sleep for component shrink .95 2000
cursor: pin S wait on X 1 2150
jobq slave wait 284.73 7329
PX qref latch 0 7698
pmon timer 233.74 9159
wait for unread message on broadcast channel 97.04 22982
rdbms ipc message 94.01 73795

20 rows selected.

SQL> SELECT EVENT, AVERAGE_WAIT, TOTAL_TIMEOUTS FROM V$SYSTEM_EVENT WHERE AVERAGE_WAIT > 0 ORDER BY AVERAGE_WAIT;

EVENT AVERAGE_WAIT TOTAL_TIMEOUTS

---

enq: PS – contention .01 0
SQLNet more data from dblink .01 0
instance state change .02 0
SQLNet more data from client .02 0
LGWR wait for redo copy .02 3
latch: object queue header operation .02 0
db file single write .03 0
latch: redo allocation .04 0
Log archive I/O .05 0
latch: cache buffers chains .06 0
control file sequential read .06 0
class slave wait .06 0
buffer busy waits .07 0
latch: library cache lock .08 0
reliable message .08 0
row cache lock .08 0
db file scattered read .1 0
direct path write .1 0
latch: library cache pin .11 0
latch: session allocation .14 0
PX Deq: Join ACK .15 0
db file sequential read .19 0
PX Deque wait .22 0
SQLNet break/reset to client .25 0
control file parallel write .29 0
log file single write .29 0
latch: cache buffers lru chain .3 0
read by other session .31 0
log file parallel write .32 0
latch: row cache objects .36 0
enq: SQ – contention .37 0
enq: KO – fast object checkpoint .38 0
latch: enqueue hash chains .4 0
latch free .42 0
PX Deq: Parse Reply .51 0
log file sync .57 127
db file parallel write .59 0
direct path read .78 0
direct path write temp .82 0
SGA: MMAN sleep for component shrink .95 2000
PX Deq: Par Recov Reply .96 0
cursor: pin S wait on X 1 2150
SGA: allocation forcing component growth 1.03 322
direct path read temp 1.11 0
db file parallel read 1.18 0
PX Deq: Execution Msg 1.21 0
PX Deq: Execute Reply 1.29 0
latch: shared pool 1.39 16
PX Deq: Msg Fragment 1.4 0
latch: library cache 2.28 9
enq: TX – index contention 2.29 0
SQLNet message from dblink 2.4 0
os thread startup 2.6 0
PX Deq: Signal ACK 2.83 3
PX Deq: Par Recov Execute 4.31 0
kksfbc child completion 4.89 24
rdbms ipc reply 5.92 0
enq: JS – queue lock 8.28 0
log file switch completion 10.33 0
log file sequential read 11.65 0
library cache load lock 11.66 0
single-task message 16.32 0
enq: CF – contention 24.95 0
SQL*Net message from client 33.09 0
rdbms ipc message 94 73833
wait for unread message on broadcast channel 97.04 22993
PX Idle Wait 193.93 1653
pmon timer 231.31 9163
jobq slave wait 284.73 7333
Streams AQ: waiting for messages in the queue 358.2 3
control file heartbeat 390.63 1
virtual circuit status 2919.09 768
dispatcher timer 5815.74 384
smon timer 18297.17 71

[Ricardo Portilho ProniParticipante]

Você usa paralelismo?
Eles estão sendo uma boa causa de lentidão de seu sistema, mais do que o gerenciamento do SGA.

-> PX Idle Wait 193.93 1653
–> PX qref latch 0 7698
–> SGA: MMAN sleep for component shrink .95 2000

E você usa Shared Server?
–> virtual circuit status 2919.09 768

Mande o resultado de select * from v$sgastat.

[Luigui27Participante]

Oi Ricardo.

Como sou novo no ambiente fui verificar a informação.
Estranhamente o parametro shared_server esta com 1

SQL> show parameter shared_servers

NAME TYPE VALUE

---

max_shared_servers integer
shared_servers integer 1

Porem as conexões estão todas como dedicated.

SQL> select count(*), server from v$session group by server;

COUNT(*) SERVER

---

   246 DEDICATED
    61 PSEUDO

Será que estou perdendo algo?
Quer que eu passe o resultado de ‘select * from v$sgastat.’? O resultado é bem grande.

Abs,
Luiz

[Ricardo Portilho ProniParticipante]

Se não precisa do Shared Server, não use, você está perdendo algo, mas é pouco.

Sim, coloque o resultado do SELECT aqui, vamos ver se sua SGA está ruim.

[Luigui27Participante]

Beleza, irei tentar contactar o antigo dba daqui para saber se existia essa necessidade de shared servers, já que é um banco para aplicação web, apesar de haver alguma coisa errada, já que as conexões estão todas como DEDICTED.

Segue abaixo a informação:

POOL NAME BYTES

---

         fixed_sga                     2072520
         buffer_cache                620756992
         log_buffer                   14704640

shared pool ENQUEUE STATS 11760
shared pool VIRTUAL CIRCUITS 6694040
shared pool transaction 6977784
shared pool table definiti 3912
shared pool KGSKI scheduler heap 2 de 232
shared pool KTCN: Obj Invalidation Se 2336
shared pool kgl lock hash table state 81200
shared pool ksunfy: nodes of hierarch 320
shared pool ASM file 19200
shared pool qmn tasks 4128
shared pool kwqmncal: allocate buffer 4048
shared pool kspd run-time context 16
shared pool kzekm heap descriptor 304
shared pool incr ckpt write count arr 168
shared pool kglsim main lru size 151040
shared pool FileOpenBlock 8786280
shared pool Core dump directory 264
shared pool log_checkpoint_timeout 12360
shared pool PX subheap 261176
shared pool message pool freequeue 757568
shared pool sched job queue 3616
shared pool LGWR-network Server info 27648
shared pool Parameter Handle 1600
shared pool PARAMETER TABLE 2048
shared pool state objects 5680
shared pool pso tbs: ksunfy 1352000
shared pool qm_init_sga:oidctx 8
shared pool recov_kgqbtctx 4392
shared pool Cursor Stats 6828648
shared pool enqueue 5275648
shared pool object stat dummy stat 288
shared pool kks stbkt 1572864
shared pool plwda:PLW_STR_NEW_RVAL 24
shared pool plwspv:PLW_STR_NEW_VAL 32
shared pool simulator latch/bucket st 5632
shared pool pso child tracebuf ptrs 20800
shared pool MS alert log 72704
shared pool knlsg 120
shared pool KGLS SD 2592
shared pool KGLS SP 4704
shared pool latch classes 352
shared pool name-service table 15856
shared pool kzull 29280
shared pool kfgbsg 40
shared pool namhsh_kfdsg 4104
shared pool namhsh_kfgsg 288
shared pool policy hash table descrpt 280
shared pool object queue 745920
shared pool sim trace buf context 200
shared pool Active Session History – 1528
shared pool set_descriptor_array 21888
shared pool qesmmaInitialize: ta_qesm 264
shared pool qesmmaInitialize: 112
shared pool temporary foreign ref 2848
shared pool xsoqophift 2848
shared pool list 4096
shared pool kgllk hash table 1187840
shared pool ksfm state object 32
shared pool kpssnfy: kpsssgct 32
shared pool ksmd unit test 1 5184
shared pool dbwriter coalesce bitmap 64
shared pool Detached dump directory 264
shared pool KEWS External IDs 768
shared pool kzsrs filename 280
shared pool dbwr message active flag 8
shared pool dbwr suspend/resume ptr a 16
shared pool KGH: NO ACCESS 604260544
shared pool KCK type array 936
shared pool temp lob duration state o 3296
shared pool jsksncb: 9 28992
shared pool jsksncb: 8 1024
shared pool jsksncb: 3 4048
shared pool file # translation table 33656
shared pool kksss-heap 280248
shared pool kglsim heap 11737152
shared pool free memory 225388184
shared pool miscellaneous 5208
shared pool Wait event pointers 336
shared pool UNDO INFO HASH 100832
shared pool UNDO STAT INFO 59904
shared pool sessions 15195968
shared pool row cache 7506880
shared pool enqueue_hash 276720
shared pool KSXR pending consumption 20192
shared pool ksb ci process list (each 696
shared pool ksuloi: garbage collectio 16
shared pool ksfd shared pool recovery 24
shared pool kpummst global in the SGA 1136
shared pool groups_kfgbsg 4096
shared pool PCursor 5983008
shared pool fdhsh_kffsg 16392
shared pool grplut_kfgsg 512
shared pool AW SGA 40
shared pool processes 20800
shared pool kcbl seq io throughput 16000
shared pool SHARED SERVERS INFO 4000
shared pool primem_kfmdsg 1032
shared pool sim cache sizes 640
shared pool MTTR advisory context 1648
shared pool SWRF Alerted 4160
shared pool LGWR post requested array 8
shared pool param hash values 5760
shared pool PX subheap desc 256
shared pool PX QC deq stats 1480
shared pool sql area:KOKA 80960
shared pool log file size history arr 168
shared pool latch recovery structures 824
shared pool dummy 233632
shared pool sim trace buf 5144
shared pool Banner Storage 2048
shared pool SHRINK STAT 3000
shared pool Managed Standby Proc Arra 24576
shared pool max allowable # log files 337600
shared pool constraints 712824
shared pool cinfo_kfnsg 4104
shared pool kks stats 40
shared pool KQR L SO 117336
shared pool KSIR SGA 128
shared pool ASM rollback operations 2592
shared pool replication session stats 848040
shared pool MTTR advisory 170696
shared pool ktcmvcb 94392
shared pool SGA struct – SWRF Metrics 2096
shared pool osp pool handles 8
shared pool kks sga 48
shared pool KSK SGA 76080
shared pool kponfy 672
shared pool cross-platform compliance 2120
shared pool kfasga 1048
shared pool kfdsga 120
shared pool kfgsga 104
shared pool qm_init_sga:rootname 8
shared pool namrec_kfdsg 24
shared pool msg Q child latches 448
shared pool plwppwp:PLW_STR_NEW_VAL_V 32
shared pool obj stat memo 867232
shared pool SGA – SWRF Metric Eidbuf 792840
shared pool ptr to sessions under idl 32
shared pool returns from metrics req 526336
shared pool character set memory 43656
shared pool qesmmaInitialize: pa_qesm 11088
shared pool db_block_hash_buckets 5898240
shared pool java static objs 32128
shared pool KSI resource types 2704
shared pool ktlbk state objects 2193096
shared pool kqlpWrntoStr:string 208
shared pool file # to first dba, exte 2416
shared pool KEWS statistic maps 2728
shared pool kebm slave message 152
shared pool X$KSFQP ANCHOR 80
shared pool ksir State Object 3360
shared pool KTC cch latches 1760
shared pool KTI pool states 320
shared pool generic process shared st 96
shared pool KSFQ buffer pool 3872
shared pool dbwr outstanding ios per 64
shared pool messages 540800
shared pool KRBMROS ANCHOR 96
shared pool joxlod exec hp 275808
shared pool KEWS fixed SGA 4600
shared pool koh dur heap 352
shared pool KGX rowlist 1504
shared pool ksv reaper 4128
shared pool kzulsg SGA 2080
shared pool KGKP randnum 40000
shared pool dpslut_kfdsg 512
shared pool hot latch diagnostics 160
shared pool resumable 2720
shared pool KSXR pending messages que 856352
shared pool trigger defini 1272
shared pool KTCN: Hash Table Segmente 5184
shared pool kolfsgi: KOLF’s SGA initi 8
shared pool CCursor 9414648
shared pool ksunfy: is parent statist 3080
shared pool ksuloi: long op free list 64
shared pool KSXR pending reply queue 100280
shared pool grptab_kfgsg 2464
shared pool Wait History 1375200
shared pool PLS chunk 352
shared pool kso req alloc heapds 280
shared pool alert threshol 4136
shared pool sim kghx free lists 8
shared pool pspool_kfsg 80
shared pool sim state object 40
shared pool latch recovery alignment 56
shared pool PLS non-lib hp 16928
shared pool object queue hash table d 7552
shared pool PARAMETER ENTRY 152
shared pool Auto tune undo info 96
shared pool ArchLog Dest Array 7656
shared pool latch nowait fails or sle 114848
shared pool analytic workspace 3616
shared pool PRESENTATION TABLE 2048
shared pool PX msg pool struct 1088
shared pool sga node map 16
shared pool prmtzdini tz region 404888
shared pool Monitor Modification Hash 512
shared pool sim_knlasg 1280
shared pool OS proc request holder 46272
shared pool PX server deq stats 1480
shared pool KGLS SGA 24
shared pool BRANCH TABLE SEGMENTED AR 276576
shared pool RTA Boundary Info thread 12672
shared pool krvxlctx 248
shared pool PARALLEL T RECO LATCH 12800
shared pool parallel_max_servers 10240
shared pool KGSKI sga 80
shared pool reserved entries for all 4096
shared pool redo allocation latch(es) 50720
shared pool SGA param ownership table 184
shared pool SGA – SWRF DrvMet Runtime 4960
shared pool SGA – SWRF Time Model Bas 64
shared pool name-service entry 2592
shared pool kksss 116392
shared pool KKJ SGA 848
shared pool row cache child latch 6400
shared pool plwppwp:PLW_STR_NEW_LEN_V 16
shared pool Processor group descripto 192
shared pool slave class sga anchor 88
shared pool reservation state object 3168
shared pool xslongops 2592
shared pool qesmmaInitialize: ia_qesm 264
shared pool returns from remote ops 49152
shared pool SGA Param Source Table 184
shared pool kglsim pin list arr 1224
shared pool Heap0: KGL 1360800
shared pool done Q child latches 448
shared pool KEWS sysstat values 256
shared pool xsoqojhift 4128
shared pool xsoqmehift 2848
shared pool KCB undo bitvec 4096
shared pool kglsim latch area 2720
shared pool kebm test replies 28672
shared pool X$KSVIT table 512
shared pool kodosgi kopfdo 416
shared pool KSPD key heap 4136
shared pool TXN TABLE SEGMENTED ARRAY 229408
shared pool database creation languag 568
shared pool trace buffer header array 18704
shared pool Transportable DB Converte 2552
shared pool where to latch num map 7184
shared pool KKJ WRK LAT 480
shared pool kfkhsh_kfdsg 4104
shared pool kfkrec_kfdsg 24
shared pool KFG SO child 5056
shared pool File Space Usage 11336
shared pool dgtab_kfmdsg 13184
shared pool invalid low rba queue 1024
shared pool branch so 296
shared pool trigger inform 4328
shared pool kglsim main lru count 76800
shared pool plwpil:wa 4264
shared pool event-class map 3512
shared pool qmtb_init_data 1712
shared pool latchnum to latch map 3104
shared pool LISTEN ADDRESS ENTRY 152
shared pool SERVICE NAME ENTRY 264
shared pool PQ/BizCard 1536
shared pool active checkp 2560
shared pool memory transfer history 19208
shared pool PL/SQL DIANA 805944
shared pool plwshs:temphdl 48
shared pool sim lru segments 2560
shared pool PG latch table 640
shared pool parameter handle 430536
shared pool post/wait queues 6144
shared pool PX QC msg stats 2288
shared pool repository 7832
shared pool sched job slv 5952
shared pool KQR M SO 125192
shared pool KQR S SO 30792
shared pool resize request state obje 366400
shared pool joxs heap 8392
shared pool SGA Obsolete Param Source 16
shared pool SGA – SWRF RawMet Runtime 3280
shared pool Array of cached attr 400
shared pool spfile callback table: ks 448
shared pool kfmdsg 144
shared pool CGS system incarn array 24864
shared pool qm_init_sga:origroot 88
shared pool xdbconf 8
shared pool obj htab chun 645624
shared pool rules engine context 200
shared pool channel context areas 30272
shared pool resize operation history 28808
shared pool AWR Table Info (KEW layer 872
shared pool simulator hash latch 10240
shared pool character set object 561232
shared pool kglsim object batch 21263592
shared pool ksb process so list 576
shared pool ksws service object 2688
shared pool KCB private handles 31200
shared pool temporary table lock 3744
shared pool KEWS sesstat values 1466880
shared pool DBWR event stats array 192
shared pool DISPATCHER CONFIG TABLE 2048
shared pool kks stats latch 640
shared pool KTU latch cleanup 624
shared pool KUPP subheap desc 232
shared pool kscdnfyinitnext 16
shared pool writes stopped lock state 24
shared pool writes stopped lock conte 24
shared pool kqlpaac:value-1 320
shared pool kglsim latches 136
shared pool KFM state obj 3360
shared pool KKKI consumer 4136
shared pool dbwr suspend/resume array 16
shared pool KCB Table Scan 40
shared pool KTU lat struct 800
shared pool jsksncb: 2 7648
shared pool KSI Indexes 288
shared pool kfkid hash 4104
shared pool kfkid hrec 24
shared pool dsktab_kfgsg 50440
shared pool dev2node map 4096
shared pool type object de 7112
shared pool trigger source 416
shared pool kglsim count of pinned he 8160
shared pool kelr system metrics table 280
shared pool ksunfy: system-global sta 3080
shared pool ksuloi: child latches for 640
shared pool knlu_txn_init_btree:init 48
shared pool work area tab 270144
shared pool KCB Table Scan Bitmap 256
shared pool kglsim alloc latches 136
shared pool service names array 24
shared pool qtree_kwqbspse 48
shared pool State object pools 3072
shared pool stat hash values 1488
shared pool prirec_kfmdsg 24
shared pool ASM map operations 3360
shared pool Log Archive Config Contex 360
shared pool name-service request 2592
shared pool Nettimeout Histogram 48800
shared pool broker globals 160
shared pool sga dev dict 56
shared pool media recovery state obje 6336
shared pool KRSF SGA 56
shared pool KQR X PO 969624
shared pool block media rcv state obj 2592
shared pool krvxdka 592
shared pool SGA – SWRF Metrics WCTime 1216
shared pool SGA – AWR Metric RBSM str 11464
shared pool client/application info l 640
shared pool change tracking recovery 262144
shared pool kfmsg 3608
shared pool KFK SGA 1144
shared pool sys event stats for Other 196672
shared pool system default language h 568
shared pool knstsg 48
shared pool KQR ENQ 90816
shared pool LRMPD SGA Table 161280
shared pool Client ID trace settings 3872
shared pool os statistics 9192
shared pool Policy Cache Heap descrpt 160
shared pool process count for each CI 288
shared pool sim segment num bufs 1280
shared pool qmcInitSGA:qmsga_acl_prop 32
shared pool NETWORK BUFFER 32808
shared pool qm_init_uga_helper: qmkm 16
shared pool session idle latches 640
shared pool SQL Workarea Histogram 1056
shared pool downed inst bit vector 40
shared pool ksws service events 36864
shared pool fixed allocation callback 464
shared pool kebm slave reply 88
shared pool Global Context Heap descr 400
shared pool kspload:comment 24
shared pool x$ksmfs table 24
shared pool ksv slave class 14840
shared pool kscdnfyinithead 16
shared pool idtab_kfksg 44296
shared pool KKJ SHRD WRQS 288
shared pool dispatcher service names 16
shared pool kks stats mem 64
shared pool kodosgi kodos 32
shared pool ksleid alloc 112
shared pool KTU latches 600
shared pool kkj jobq slav 1280
shared pool kglsim sga 30456
shared pool KTF MAPPINGS 12288
shared pool trace buffer 4849664
shared pool dlo fib struct 12832
shared pool KCB where statistics arra 25824
shared pool KTI SGA freeable small po 81920
shared pool kmgsb circular statistics 108800
shared pool kzctxgjsi ksuseclid memor 34888
shared pool ksbtnfy: infrequent actio 1776
shared pool KEWS statistic metadata 12032
shared pool DISPATCHERS INFO 2400
shared pool dispatcher rate 1312
shared pool kebm run-once actions 16
shared pool KSXR global channels 1176
shared pool KCB Table Scan Buffer 4198400
shared pool sort segment handle 3872
shared pool latch hashvalue table 1552
shared pool LISTEN ADDRESS TABLE 2048
shared pool qtree_kwqbsgn 48
shared pool Checkpoint queue 410624
shared pool ASH buffers 8388608
shared pool sga listelement 2048
shared pool SYSTEM PARAMETERS 279256
shared pool log_simultaneous_copies 1600
shared pool parameter blocks 11520
shared pool bloom filter 3104
shared pool UNDO INFO 16
shared pool private strands 41610240
shared pool qmps connections 65280
shared pool message pool context area 10640
shared pool PX server msg stats 2288
shared pool parameter table block 3089912
shared pool KQR M PO 955640
shared pool lckhsr_kffsg 24
shared pool parameter text value 4520
shared pool modification 209168
shared pool quiesce system context 384
shared pool kga sga 8
shared pool Prefetch history buffer 11328
shared pool change notification regis 16416
shared pool change notification obj m 16416
shared pool kcrrny 33760
shared pool spfile cleanup structure 16760
shared pool xssinfo 5952
shared pool kffsga 120
shared pool Service-level trace setti 536
shared pool Service-level ID trace se 3744
shared pool Closed Thread SCN Bitvec 8448
shared pool xscalc 4000
shared pool SCHEDULING POLICY VECTOR 104
shared pool Prefetch history buffer a 320
shared pool Prefetch client count per 32
shared pool State object subpools 3840
shared pool OS file lock 4416
shared pool x$rule_set 17608
shared pool kscdnfyglobalflags 8
shared pool KEWS statistic name 1760
shared pool KTI latch structure 50400
shared pool kcrfa structures 111584
shared pool event descriptor table 49360
shared pool kscdnfyinitflags 8
shared pool kglsim hash table 8208
shared pool X$KSVII table 512
shared pool kxfpdp pointers 28800
shared pool dbwr working sets kcbdbws 16
shared pool dbwriter coalesce struct 48
shared pool Temporary storage for RTA 16
shared pool HTTP fixed headers 72
shared pool KSFD SGA I/O b 4190248
shared pool kkj jobq wor 4128
shared pool jsksncb: 6 4176
shared pool jsksncb: 4 4080
shared pool event statistics per sess 26403840
shared pool event statistics ptr arra 22920
shared pool Undo Meta Data 2520
shared pool trace_knlasg 504
shared pool SEQ S.O. 458400
shared pool event classes 128
shared pool branch 1021944
shared pool KESTB existence bitvec se 128
shared pool kolbsgi: KOLB’s SGA initi 8
shared pool Alert log 264
shared pool call 618016
shared pool 1:kngisga 32088
shared pool User dump directory 264
shared pool ksfv subheap descriptor 184
shared pool kglsim alloc latch area 2720
shared pool fdrec_kffsg 24
shared pool kglsim free heap list 408
shared pool KEWS sesstat seg tbl 8
shared pool kglsim hash table bkts 4194304
shared pool KSXR receive buffers 1036000
shared pool kebm slave descriptors 1352
shared pool instance cnxn information 137520
shared pool kglsim free obj list 408
shared pool procs_kfgbsg 616
shared pool block_sizes_array 48
shared pool post stats 1088
shared pool plis struct 136
shared pool segmented arrays 12768
shared pool sim segment hits 2560
shared pool PL/SQL MPCODE 861984
shared pool procs: ksunfy 5241600
shared pool object queue hash buckets 262656
shared pool sim cache nbufs 640
shared pool LGWR debug module memory 8192
shared pool logout storm management 20800
shared pool sys event stats 196672
shared pool PLS cca hp desc 352
shared pool archive_lag_target 9624
shared pool Saved job ids 8000
shared pool object level stat table 1728
shared pool qm_init_sga:qmdpsg 32
shared pool qm_init_uga:qmsg 20632
shared pool buffer handles 3640008
shared pool db_files 213432
shared pool KQR L PO 390784
shared pool KSFV SGA 824
shared pool KSXR SGA 27848
shared pool plugin datafile array 3616
shared pool KGSK sga 448
shared pool RVWR post requested array 8
shared pool parameter value memory 440
shared pool krvxmctx 40
shared pool multiblock re 73872
shared pool KGLS heap 2463304
shared pool monitoring co 24960
shared pool buffers waiting for write 24
shared pool network connections 66280
shared pool change tracking state cha 3744
shared pool kkzias 144
shared pool KGX 122976
shared pool SCHEDULING POLICY TABLE 160
shared pool osp allocation 145080
shared pool plwda:PLW_STR_NEW_VAL_VEC 8
shared pool plwda:PLW_STR_NEW_LEN_VEC 8
shared pool Cleanup state objects 384
shared pool AQ Propagation Scheduling 16000
shared pool qmuCreatePermSubHeap:subh 136
shared pool sess Q child latches 448
shared pool SGA – SWRF Metric CHBs 1436224
shared pool time manager context 40
shared pool kcbl state objects 12800
shared pool xsoqsehift 3104
shared pool dbwriter coalesce buffer 1052672
shared pool ksim client list 168
shared pool ksws RLB SGA ctx 40
shared pool DISPATCHER CONFIG ENTRY 88
shared pool kcbl statistics 6144
shared pool FileIdentificatonBlock 449664
shared pool distributed_transactions- 23048
shared pool kwqmncini-tbl 272
shared pool KGSK scheduler 1019872
shared pool database NCHAR language h 568
shared pool dbwr actual working sets 32
shared pool joxs struct 144
shared pool jsksncb: 7 489472
shared pool UNDO INFO SEGMENTED ARRAY 561528
shared pool kwqicaqe2kc1 8208
shared pool jsksncb-latch 1280
shared pool sql area 12580312
shared pool txncallback 465520
shared pool subheap 103880
shared pool KCB buffer wait statistic 3352
shared pool KCB incremental ckpt entr 512
shared pool KQF optimizer stats table 4816
shared pool KTCN: Row Change Segmente 34496
shared pool kglsim size of pinned mem 16048
shared pool kewr MMON Remote Flush Re 28672
shared pool kea advisor definition ca 480
shared pool ksuloi: long op statistic 172000
shared pool ksuloi: long op used list 64
shared pool dispatcher queue 280
shared pool threshold ale 12192
shared pool kelr other metrics table 40
shared pool kgsk subheap descriptor 136
shared pool enqueue resources 1771984
shared pool kelt translation table 312
shared pool trace buf hdr xtend 168336
shared pool trace events array 68000
shared pool KEWS aggregation objs 7952
shared pool KSXR large reply queue 168104
shared pool KSQ event description 5776
shared pool kglsim recovery area 2904
shared pool KESTB existence bitvec 16384
shared pool ksb cic process list 576
shared pool PQ/ResSched 1040
shared pool latch descriptor table 1552
shared pool SERVICE NAMES TABLE 2048
shared pool channel sga anchor 280
shared pool object level stats hash t 512
shared pool channel handle 718216
shared pool sql area:PLSQL 119944
shared pool Parameter Table 34560
shared pool Label Cache Heap descrptr 160
shared pool Sort Segment 207888
shared pool quiescing session 3872
shared pool process group array 33680
shared pool property service SO 3104
shared pool object stat dummy elem 40
shared pool DML lock 3280120
shared pool KTI-UNDO 24441480
shared pool plwppwp:garbage handle 32
shared pool KGKP sga 32
shared pool lckhsh_kffsg 4104
shared pool lcktab_kffsg 2592
shared pool JSX SGA 112
shared pool Background process state 48
shared pool rules engine aggregate st 1416
shared pool SGA structure for SWRF (K 240
shared pool SGA – SWRF Metrics ksuTim 1216
shared pool SQL Memory Manager Base W 13400
shared pool kohsg 8
shared pool library cache 12724128
shared pool Client ID trace setting h 536
shared pool buffer_pool_desc_array 3384
shared pool kzulu 3808
shared pool object level 140352
shared pool Pre-Warm Initialized Seti 256
shared pool heap_kfsg 136
shared pool simulator hash buckets 262656
shared pool alter system errs: kspnfy 114688
shared pool qesmmaInitialize: oa_qesm 112
shared pool SGA structure for kelr 2336
shared pool listener addresses 8
shared pool trigger condition node 72
shared pool error message file name 56
shared pool kpscad: kpscscon 1312
shared pool kghx free lists 33824
shared pool XDB Schema Cac 9537904
shared pool KTC latch cleanup 288
shared pool kscdnfyinitprev 16
shared pool X$KSVIS table 128
shared pool KFG state obj 4128
shared pool kso req alloc 4136
shared pool kwqmncini-slv 240
shared pool Temporary Tables State Ob 674376
shared pool KTUR HIST INFO 4840
shared pool kks stats hds 1024
shared pool KGSKI schedule 8296
shared pool KTC latches 1760
shared pool KTI latches 10080
shared pool KSN WaitID 1560
shared pool kwrsnfy: kwrs 1624
shared pool enqueue_hash_chain_latche 640
shared pool KTPR HIST TB 2808
shared pool KTPR SUBHEAP 168
shared pool eventlist to post commits 3816
large pool free memory 15701216
large pool PX msg pool 1076000
java pool free memory 11459008
java pool joxs heap 72576
java pool joxlod exec hp 5245632
streams pool free memory 50331648

[Ricardo Portilho ProniParticipante]

Vamos alterar apenas os

buffer_cache 620756992 — Deixe usando 50% do SGA
shared pool library cache 12724128 — Deixe usando 30% do SGA

Quanto vc tem de RAM?
Qual o tamanho do sga_max_size e sga_target atualmente?

[Autor]

Posts