can we please have as a starter vmstat -Iwt 2 10 and the usual vmstat -s and vmstat -v outputs ?
In this case I would like to see iostat -Dl too, vmo -a, and I would like to know what is set within oracle - SETALL or async or nothing at all - and output of mount command would be interesting also
The full program ... with no data we cannot recommend anything.
Hdisk1 is so busy because your box is paging like hell - usually that means that either your vmo settings are still wrong, SETALL is not set or you really still have not enough memory
ORACLE SETALL PARAMETER
this parameter could be found in the option and the init.ora parameter filesystemio_options: see the file [COLOR=black] init.ora in your system Oracle init.ora: filesystemio_options = SETALL
or
JFS2 mount option: mount -o dio /oradata/ts1.dbf
your box is slow because you still do not have enough memory - it scans to frees itself to a virtual halt. You have occasionally a scan to free rate of 30:1 - anything above 4 would be considered a problem. Your minperm is too high, set it to 3% and set min/maxfree to 960/1088. If this would be my box, I would add at least 20 GB just as a starter and take it from there.
Furthermore your disk response times are a disaster and hdisk2 is completely saturated, hdisk1 doesnt look much better. Assuming that hdisk2 is your pagingdisk, this will get better with more memory too - still you should spread your load across more disks.
As you have already a whole lot of memory in your box, my next question would be 'how big is your SGA', did the DBAs pin it into memory - and does it have to be THAT big. I can see that you have some decent IO in both reads and writes so you are moving data - but still not enough to explain almost 100 GB memory usage. And a too big SGA both slows down the server + oracle itself because it ironically takes longer for oracle to use that memory.
I would still like to see the output of mount without any options.
What version of Oracle are you running?
Are you using Enterprise Manager?
If not are you using statspack?
Have the database objects been analyzed lately?
Is it one query that you're having problems with or are all queries slow?
If it's one query, can you run this:
set lines 130 trimout on trimspool on
set timing on
set autotrace on
spool query.log
"put your query here"
spool off
post the contents of query.log
If all queries are slow, then generate an AWR report (Enterprise Manager) or a statspack report (statspack). Run these reports for the time period you see the slowdown, but have the report cover no more than a 1 hour time period, preferably 15 to 30 minutes. You can run multiple reports, if necessary, to cover subsequent time periods.
Depending on the version, there may be a DBA view called V$SESSION_LONGOPS. You can query this view to see what operations are taking a long time to complete as well.
select start_time
,sql_hash_value
,elapsed_seconds
,message
from v$session_longops
order by start_time;
We are frequently analyzed but not all DB objects . most of objects heavily used are being analyzed. Our issues is related not to specific query .
SQL> select * from v$sgastat ;
POOL NAME BYTES
------------ -------------------------- ----------
fixed_sga 2234488
buffer_cache 7.9306E+10
log_buffer 64872448
shared pool ENQUEUE STATS 11760
shared pool VIRTUAL CIRCUITS 3736
shared pool transaction 5846232
shared pool table definiti 92952
shared pool KGSKI scheduler heap 2 de 232
shared pool KTCN: Obj Invalidation Se 2336
shared pool kgl lock hash table state 130200
shared pool kwqmncal: allocate buffer 8096
POOL NAME BYTES
------------ -------------------------- ----------
shared pool ASM file 19200
shared pool qmn tasks 4128
shared pool kglsim main lru size 151040
shared pool FileOpenBlock 13195576
shared pool log_checkpoint_timeout 12360
shared pool PX subheap 73200
shared pool post agent 960
shared pool partitioning d 973536
shared pool message pool freequeue 757568
shared pool qesblGF:bfm 680
shared pool PARAMETER TABLE 2048
POOL NAME BYTES
------------ -------------------------- ----------
shared pool state objects 5504080
shared pool pso tbs: ksunfy 624000
shared pool Cursor Stats 41326632
shared pool object stat dummy stat 288
shared pool pso child tracebuf ptrs 9600
shared pool KGLS SP 4704
shared pool kzull 28024
shared pool kfgbsg 40
shared pool namhsh_kfdsg 4104
shared pool object queue 2291632
shared pool set_descriptor_array 65664
POOL NAME BYTES
------------ -------------------------- ----------
shared pool qesmmaInitialize: ta_qesm 264
shared pool kgllk hash table 1904640
shared pool dbwriter coalesce bitmap 128
shared pool KEWS External IDs 768
shared pool kzsrs filename 280
shared pool dbwr suspend/resume ptr a 16
shared pool KCK type array 936
shared pool jsksncb: 9 28992
shared pool joxlod pcod hp 152072
shared pool file # translation table 86096
shared pool KTCCC OBJECT 928
POOL NAME BYTES
------------ -------------------------- ----------
shared pool kksss-heap 236232
shared pool kglsim heap 29578752
shared pool ksunfy: nodes of hierarch 320
shared pool kzekm heap descriptor 304
shared pool Core dump directory 264
shared pool sched job queue 3616
shared pool LGWR-network Server info 27648
shared pool Parameter Handle 1600
shared pool recov_kgqbtctx 4392
shared pool plwda:PLW_STR_NEW_RVAL 24
shared pool plwspv:PLW_STR_NEW_VAL 80
POOL NAME BYTES
------------ -------------------------- ----------
shared pool MS alert log 72704
shared pool latch classes 352
shared pool name-service table 15856
shared pool namhsh_kfgsg 288
shared pool Active Session History - 1528
shared pool temporary foreign ref 2848
shared pool ksmd unit test 1 5184
shared pool Detached dump directory 264
shared pool temp lob duration state o 3296
shared pool jsksncb: 3 4048
shared pool kspd run-time context 16
POOL NAME BYTES
------------ -------------------------- ----------
shared pool incr ckpt write count arr 168
shared pool enqueue 4154008
shared pool kks stbkt 1572864
shared pool simulator latch/bucket st 11264
shared pool knlsg 120
shared pool KGLS SD 2592
shared pool policy hash table descrpt 280
shared pool sim trace buf context 200
shared pool qesmmaInitialize: 112
shared pool xsoqophift 2848
shared pool list 4096
POOL NAME BYTES
------------ -------------------------- ----------
shared pool ksfm state object 32
shared pool kpssnfy: kpsssgct 32
shared pool dbwr message active flag 8
shared pool jsksncb: 8 1024
shared pool free memory 765096160
shared pool sessions 12729608
shared pool row cache 7506880
shared pool ksb ci process list (each 696
shared pool ksuloi: garbage collectio 48
shared pool ksfd shared pool recovery 24
shared pool kpummst global in the SGA 1136
POOL NAME BYTES
------------ -------------------------- ----------
shared pool PCursor 350580920
shared pool grplut_kfgsg 512
shared pool kcbl seq io throughput 16000
shared pool parameters 9304
shared pool agent name 64
shared pool sim cache sizes 640
shared pool LGWR post requested array 8
shared pool sql area:KOKA 125488
shared pool savepoints 12184
shared pool mvobj part des 223504
shared pool ktcmvcb 429359008
POOL NAME BYTES
------------ -------------------------- ----------
shared pool kks sga 48
shared pool kfgsga 104
shared pool SGA - SWRF Metric Eidbuf 681920
shared pool ptr to sessions under idl 96
shared pool character set memory 188408
shared pool qesmmaInitialize: pa_qesm 11088
shared pool db_block_hash_buckets 754974720
shared pool file # to first dba, exte 6160
shared pool KEWS statistic maps 2728
shared pool kebm slave message 152
shared pool X$KSFQP ANCHOR 80
POOL NAME BYTES
------------ -------------------------- ----------
shared pool ksir State Object 3360
shared pool KEWS fixed SGA 4600
shared pool messages 249600
shared pool joxlod exec hp 1110400
shared pool koh dur heap 352
shared pool ksv reaper 4128
shared pool Wait event pointers 336
shared pool KSXR pending consumption 20192
shared pool AW SGA 40
shared pool SHARED SERVERS INFO 240
shared pool primem_kfmdsg 1032
POOL NAME BYTES
------------ -------------------------- ----------
shared pool MTTR advisory context 1648
shared pool param hash values 5760
shared pool PX QC deq stats 1480
shared pool log file size history arr 168
shared pool dummy 194736
shared pool SHRINK STAT 3000
shared pool constraints 597072
shared pool cinfo_kfnsg 4104
shared pool kks stats 40
shared pool KSIR SGA 128
shared pool replication session stats 710400
POOL NAME BYTES
------------ -------------------------- ----------
shared pool osp pool handles 8
shared pool KSK SGA 76080
shared pool cross-platform compliance 2120
shared pool kfasga 1048
shared pool plwppwp:PLW_STR_NEW_VAL_V 40
shared pool obj stat memo 1763016
shared pool KTC cch latches 2720
shared pool generic process shared st 96
shared pool KRBMROS ANCHOR 96
shared pool KGX rowlist 1504
shared pool kzulsg SGA 2080
POOL NAME BYTES
------------ -------------------------- ----------
shared pool miscellaneous 2408
shared pool UNDO INFO HASH 84480
shared pool UNDO STAT INFO 59904
shared pool enqueue_hash 232080
shared pool groups_kfgbsg 4096
shared pool fdhsh_kffsg 16392
shared pool processes 9600
shared pool PX subheap desc 256
shared pool latch recovery structures 1096
shared pool sim trace buf 5144
shared pool Banner Storage 2048
POOL NAME BYTES
------------ -------------------------- ----------
shared pool Managed Standby Proc Arra 24576
shared pool max allowable # log files 337600
shared pool KQR L SO 454016
shared pool ASM rollback operations 2592
shared pool SGA struct - SWRF Metrics 2096
shared pool kponfy 672
shared pool kfdsga 120
shared pool namrec_kfdsg 24
shared pool msg Q child latches 1344
shared pool returns from metrics req 526336
shared pool java static objs 32128
POOL NAME BYTES
------------ -------------------------- ----------
shared pool KSI resource types 2704
shared pool ktlbk state objects 1837440
shared pool kqlpWrntoStr:string 224
shared pool KTI pool states 264
shared pool KSFQ buffer pool 3872
shared pool dbwr outstanding ios per 128
shared pool KGKP randnum 40000
shared pool trigger defini 228144
shared pool CCursor 443998304
shared pool ksunfy: is parent statist 3080
shared pool ksuloi: long op free list 192
POOL NAME BYTES
------------ -------------------------- ----------
shared pool ksr message pool free que 34480
shared pool KSXR pending reply queue 100280
shared pool PLS chunk 352
shared pool sim kghx free lists 8
shared pool sim state object 40
shared pool latch recovery alignment 56
shared pool object queue hash table d 22656
shared pool PARAMETER ENTRY 64
shared pool analytic workspace 3616
shared pool sga node map 16
shared pool OS proc request holder 55072
POOL NAME BYTES
------------ -------------------------- ----------
shared pool BRANCH TABLE SEGMENTED AR 276576
shared pool parallel_max_servers 1536
shared pool KGSKI sga 80
shared pool redo allocation latch(es) 42560
shared pool SGA - SWRF Time Model Bas 64
shared pool SGA - SWRF DrvMet Runtime 4960
shared pool kksss 99152
shared pool name-service entry 2592
shared pool KKJ SGA 848
shared pool row cache child latch 6400
shared pool Processor group descripto 192
POOL NAME BYTES
------------ -------------------------- ----------
shared pool slave class sga anchor 88
shared pool reservation state object 3168
shared pool xslongops 2592
shared pool returns from remote ops 49152
shared pool Heap0: KGL 34029392
shared pool done Q child latches 1344
shared pool xsoqmehift 2848
shared pool kodosgi kopfdo 416
shared pool TXN TABLE SEGMENTED ARRAY 229408
shared pool database creation languag 568
shared pool trace buffer header array 18704
POOL NAME BYTES
------------ -------------------------- ----------
shared pool where to latch num map 7184
shared pool KKJ WRK LAT 480
shared pool kfkrec_kfdsg 24
shared pool dpslut_kfdsg 512
shared pool hot latch diagnostics 160
shared pool resumable 2720
shared pool KTCN: Hash Table Segmente 5184
shared pool kolfsgi: KOLF's SGA initi 8
shared pool PLS non-lib hp 16456
shared pool Auto tune undo info 96
shared pool ArchLog Dest Array 7656
POOL NAME BYTES
------------ -------------------------- ----------
shared pool PX msg pool struct 1088
shared pool prmtzdini tz region 404888
shared pool KGLS SGA 24
shared pool krvxlctx 248
shared pool reserved entries for all 4096
shared pool SGA param ownership table 184
shared pool KEWS sysstat values 256
shared pool xsoqojhift 4128
shared pool kebm test replies 28672
shared pool kfkhsh_kfdsg 4104
shared pool KFG SO child 5056
POOL NAME BYTES
------------ -------------------------- ----------
shared pool KSXR pending messages que 856352
shared pool grptab_kfgsg 2464
shared pool Wait History 1152000
shared pool kso req alloc heapds 280
shared pool pspool_kfsg 80
shared pool latch nowait fails or sle 114848
shared pool PRESENTATION TABLE 2072
shared pool Monitor Modification Hash 512
shared pool sim_knlasg 1280
shared pool PX server deq stats 1480
shared pool RTA Boundary Info thread 12672
POOL NAME BYTES
------------ -------------------------- ----------
shared pool PARALLEL T RECO LATCH 1920
shared pool plwppwp:PLW_STR_NEW_LEN_V 40
shared pool qesmmaInitialize: ia_qesm 264
shared pool SGA Param Source Table 184
shared pool kglsim pin list arr 1224
shared pool KCB undo bitvec 4096
shared pool kglsim latch area 2720
shared pool X$KSVIT table 512
shared pool Transportable DB Converte 2552
shared pool dgtab_kfmdsg 13184
shared pool invalid low rba queue 3072
POOL NAME BYTES
------------ -------------------------- ----------
shared pool trigger inform 3240
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 LISTEN ADDRESS ENTRY 64
shared pool SERVICE NAME ENTRY 104
shared pool memory transfer history 19208
shared pool PL/SQL DIANA 18760352
shared pool PL/SQL PPCODE 541168
shared pool parameter handle 366504
POOL NAME BYTES
------------ -------------------------- ----------
shared pool repository 7036528
shared pool joxs heap 6312
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 rules engine context 200
shared pool AWR Table Info (KEW layer 872
shared pool character set object 449784
POOL NAME BYTES
------------ -------------------------- ----------
shared pool simulator hash latch 20480
shared pool kglsim object batch 56379624
shared pool ksws service object 2016
shared pool temporary table lock 3744
shared pool KEWS sesstat values 1228800
shared pool DBWR event stats array 192
shared pool KUPP subheap desc 232
shared pool kscdnfyinitnext 16
shared pool writes stopped lock state 48
shared pool kqlpaac:value-1 400
shared pool dbwr suspend/resume array 16
POOL NAME BYTES
------------ -------------------------- ----------
shared pool KCB Table Scan 40
shared pool KTU lat struct 2720
shared pool KSI Indexes 288
shared pool branch so 296
shared pool PQ/BizCard 1536
shared pool active checkp 1536
shared pool plwshs:temphdl 48
shared pool post/wait queues 6144
shared pool KQR S SO 109888
shared pool resize request state obje 366400
shared pool obj htab chun 655872
POOL NAME BYTES
------------ -------------------------- ----------
shared pool KCB private handles 14400
shared pool KTU latch cleanup 1872
shared pool writes stopped lock conte 48
shared pool kglsim latches 136
shared pool KFM state obj 3360
shared pool KKKI consumer 1104
shared pool kfkid hash 4104
shared pool File Space Usage 28808
shared pool latchnum to latch map 3104
shared pool sim lru segments 7680
shared pool PG latch table 640
POOL NAME BYTES
------------ -------------------------- ----------
shared pool PX QC msg stats 2288
shared pool sched job slv 5952
shared pool KQR M SO 1270440
shared pool channel context areas 30272
shared pool resize operation history 28808
shared pool ksb process so list 576
shared pool kgqbt_alloc_block 1024
shared pool kks stats latch 640
shared pool jsksncb: 2 7648
shared pool kfkid hrec 24
shared pool type object de 719688
POOL NAME BYTES
------------ -------------------------- ----------
shared pool trigger source 13880
shared pool kglsim count of pinned he 8160
shared pool kelr system metrics table 280
shared pool ksunfy: system-global sta 3080
shared pool knlu_txn_init_btree:init 48
shared pool work area tab 270144
shared pool State object pools 3072
shared pool policy hash ta 4136
shared pool Log Archive Config Contex 360
shared pool rule set evalu 107032
shared pool sga dev dict 56
POOL NAME BYTES
------------ -------------------------- ----------
shared pool block media rcv state obj 2592
shared pool SGA - SWRF Metrics WCTime 1216
shared pool KQR ENQ 165120
shared pool KFK SGA 1144
shared pool knstsg 48
shared pool LRMPD SGA Table 161280
shared pool ksws service events 27648
shared pool fixed allocation callback 1808
shared pool kebm slave reply 88
shared pool Global Context Heap descr 400
shared pool kspload:comment 320
POOL NAME BYTES
------------ -------------------------- ----------
shared pool x$ksmfs table 24
shared pool idtab_kfksg 44296
shared pool kks stats mem 64
shared pool KTU latches 2040
shared pool KTF MAPPINGS 12288
shared pool dev2node map 4096
shared pool KCB Table Scan Bitmap 256
shared pool service names array 40
shared pool qtree_kwqbspse 48
shared pool broker globals 160
shared pool media recovery state obje 6336
POOL NAME BYTES
------------ -------------------------- ----------
shared pool KRSF SGA 56
shared pool KQR X PO 5600
shared pool SGA - AWR Metric RBSM str 9600
shared pool change tracking recovery 262144
shared pool client/application info l 1920
shared pool os statistics 9192
shared pool process count for each CI 288
shared pool sim segment num bufs 3840
shared pool ksv slave class 14840
shared pool kodosgi kodos 32
shared pool kkj jobq slav 1280
POOL NAME BYTES
------------ -------------------------- ----------
shared pool dsktab_kfgsg 50440
shared pool ksuloi: child latches for 1920
shared pool kglsim alloc latches 136
shared pool stat hash values 1488
shared pool prirec_kfmdsg 24
shared pool ASM map operations 3360
shared pool name-service request 2592
shared pool Nettimeout Histogram 48800
shared pool krvxdka 592
shared pool kfmsg 3608
shared pool sys event stats for Other 196672
POOL NAME BYTES
------------ -------------------------- ----------
shared pool system default language h 568
shared pool Client ID trace settings 3872
shared pool Policy Cache Heap descrpt 160
shared pool session idle latches 1920
shared pool SQL Workarea Histogram 1056
shared pool downed inst bit vector 40
shared pool kscdnfyinithead 16
shared pool KKJ SHRD WRQS 288
shared pool ksleid alloc 112
shared pool kglsim sga 30456
shared pool trace buffer 5865472
POOL NAME BYTES
------------ -------------------------- ----------
shared pool temporary tabl 101464
shared pool KCB where statistics arra 25824
shared pool kzctxgjsi ksuseclid memor 10944
shared pool KSXR global channels 1176
shared pool sort segment handle 289504
shared pool LISTEN ADDRESS TABLE 2048
shared pool qtree_kwqbsgn 80
shared pool Checkpoint queue 3148800
shared pool ASH buffers 23592960
shared pool SYSTEM PARAMETERS 279712
shared pool log_simultaneous_copies 4800
POOL NAME BYTES
------------ -------------------------- ----------
shared pool UNDO INFO 16
shared pool private strands 34873344
shared pool message pool context area 10640
shared pool PX server msg stats 2288
shared pool KQR M PO 19184592
shared pool parameter table block 2638328
shared pool parameter text value 4880
shared pool change notification regis 16416
shared pool Prefetch history buffer 11328
shared pool change notification obj m 16416
shared pool Service-level trace setti 536
POOL NAME BYTES
------------ -------------------------- ----------
shared pool kcrrny 33760
shared pool Closed Thread SCN Bitvec 8448
shared pool SCHEDULING POLICY VECTOR 104
shared pool XCT XGA 784
shared pool State object subpools 4608
shared pool OS file lock 8576
shared pool x$rule_set 17608
shared pool kscdnfyglobalflags 8
shared pool KEWS statistic name 1760
shared pool KTI latch structure 42240
shared pool event descriptor table 49360
POOL NAME BYTES
------------ -------------------------- ----------
shared pool X$KSVII table 512
shared pool kxfpdp pointers 28800
shared pool KSFD SGA I/O b 4190248
shared pool HTTP fixed headers 72
shared pool event statistics per sess 22118400
shared pool event statistics ptr arra 19200
shared pool ksbtnfy: infrequent actio 1776
shared pool KEWS statistic metadata 12032
shared pool latch hashvalue table 1552
shared pool sga listelement 2048
shared pool bloom filter 3104
POOL NAME BYTES
------------ -------------------------- ----------
shared pool Service-level ID trace se 3744
shared pool kffsga 120
shared pool xscalc 4000
shared pool Prefetch history buffer a 320
shared pool kscdnfyinitflags 8
shared pool dbwr working sets kcbdbws 32
shared pool dbwriter coalesce struct 96
shared pool Temporary storage for RTA 16
shared pool jsksncb: 6 3480
shared pool kkj jobq wor 4128
shared pool jsksncb: 4 4080
POOL NAME BYTES
------------ -------------------------- ----------
shared pool dlo fib struct 32800
shared pool kmgsb circular statistics 108800
shared pool DISPATCHERS INFO 240
shared pool kebm run-once actions 16
shared pool KCB Table Scan Buffer 4198400
shared pool parameter blocks 11520
shared pool qmps connections 65280
shared pool lckhsr_kffsg 24
shared pool modification 287104
shared pool quiesce system context 384
shared pool kga sga 8
POOL NAME BYTES
------------ -------------------------- ----------
shared pool spfile cleanup structure 16760
shared pool xssinfo 5952
shared pool Prefetch client count per 32
shared pool kcrfa structures 93632
shared pool kglsim hash table 8208
shared pool Undo Meta Data 2112
shared pool branch 855984
shared pool call 745816
shared pool 1:kngisga 32088
shared pool User dump directory 264
shared pool kglsim alloc latch area 2720
POOL NAME BYTES
------------ -------------------------- ----------
shared pool fdrec_kffsg 24
shared pool kglsim hash table bkts 4194304
shared pool instance cnxn information 115200
shared pool segmented arrays 12768
shared pool sim segment hits 7680
shared pool PL/SQL MPCODE 123659624
shared pool procs: ksunfy 2419200
shared pool object queue hash buckets 787968
shared pool sim cache nbufs 640
shared pool Oracle Text Commit new id 1440
shared pool archive_lag_target 9624
POOL NAME BYTES
------------ -------------------------- ----------
shared pool buffer handles 1680008
shared pool db_files 475400
shared pool plugin datafile array 9232
shared pool parameter value memory 440
shared pool krvxmctx 40
shared pool KGLS heap 36440088
shared pool monitoring co 45760
shared pool buffers waiting for write 48
shared pool network connections 66280
shared pool change tracking state cha 3744
shared pool kokcd 3672
POOL NAME BYTES
------------ -------------------------- ----------
shared pool osp allocation 175480
shared pool plwda:PLW_STR_NEW_LEN_VEC 40
shared pool SGA - SWRF Metric CHBs 1339392
shared pool time manager context 40
shared pool xsoqsehift 3104
shared pool kwqmncini-tbl 272
shared pool KGSK scheduler 639816
shared pool joxs struct 144
shared pool jsksncb: 7 489472
shared pool jsksncb-latch 3840
shared pool trace_knlasg 504
POOL NAME BYTES
------------ -------------------------- ----------
shared pool KSXR receive buffers 1036000
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 LGWR debug module memory 8192
shared pool sys event stats 196672
shared pool Saved job ids 8000
shared pool object level stat table 1728
shared pool qm_init_sga:qmdpsg 32
POOL NAME BYTES
------------ -------------------------- ----------
shared pool KQR L PO 4522968
shared pool KSFV SGA 824
shared pool KGSK sga 448
shared pool RVWR post requested array 8
shared pool KGX 122976
shared pool Cleanup state objects 384
shared pool AQ Propagation Scheduling 16000
shared pool sess Q child latches 1344
shared pool dbwriter coalesce buffer 2105344
shared pool ksim client list 168
shared pool ksws RLB SGA ctx 40
POOL NAME BYTES
------------ -------------------------- ----------
shared pool kcbl statistics 6144
shared pool dbwr actual working sets 96
shared pool database NCHAR language h 568
shared pool UNDO INFO SEGMENTED ARRAY 470352
shared pool kwqicaqe2kc1 8208
shared pool SEQ S.O. 384000
shared pool evaluation con 4544
shared pool event classes 128
shared pool KESTB existence bitvec se 128
shared pool kolbsgi: KOLB's SGA initi 8
shared pool Alert log 264
POOL NAME BYTES
------------ -------------------------- ----------
shared pool ksfv subheap descriptor 184
shared pool kglsim free heap list 408
shared pool KEWS sesstat seg tbl 8
shared pool kebm slave descriptors 1352
shared pool logout storm management 9600
shared pool PLS cca hp desc 352
shared pool KSXR SGA 27848
shared pool multiblock re 123120
shared pool kkzias 144
shared pool SCHEDULING POLICY TABLE 160
shared pool plwda:PLW_STR_NEW_VAL_VEC 40
POOL NAME BYTES
------------ -------------------------- ----------
shared pool kcbl state objects 12800
shared pool FileIdentificatonBlock 1150008
shared pool distributed_transactions- 23048
shared pool joxlod rsrc hp 7392
shared pool sql area 3125436312
shared pool txncallback 401608
shared pool KCB buffer wait statistic 8344
shared pool kglsim size of pinned mem 16048
shared pool kea advisor definition ca 480
shared pool threshold ale 4032
shared pool execution ID memory 256
POOL NAME BYTES
------------ -------------------------- ----------
shared pool enqueue resources 2146680
shared pool kelt translation table 312
shared pool trace buf hdr xtend 205744
shared pool kglsim recovery area 2904
shared pool KSQ event description 5776
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 QSMQUTL summar 400
shared pool object level stats hash t 512
POOL NAME BYTES
------------ -------------------------- ----------
shared pool channel handle 331616
shared pool sql area:PLSQL 44591928
shared pool Parameter Table 34560
shared pool Sort Segment 573968
shared pool KTI-UNDO 20484288
shared pool lcktab_kffsg 2592
shared pool SGA - SWRF Metrics ksuTim 1216
shared pool kzulu 392
shared pool library cache 323103448
shared pool simulator hash buckets 33619968
shared pool kpscad: kpscscon 1288
POOL NAME BYTES
------------ -------------------------- ----------
shared pool kso req alloc 4136
shared pool kwrsnfy: kwrs 9744
shared pool Temporary Tables State Ob 562016
shared pool KTUR HIST INFO 4840
shared pool KTC latches 2720
shared pool KTPR HIST TB 2808
shared pool eventlist to post commits 3216
shared pool KQF optimizer stats table 4816
shared pool kewr MMON Remote Flush Re 28672
shared pool ksuloi: long op used list 192
shared pool Global Context 47016
POOL NAME BYTES
------------ -------------------------- ----------
shared pool kelr other metrics table 40
shared pool trace events array 68000
shared pool SERVICE NAMES TABLE 2048
shared pool channel sga anchor 280
shared pool Label Cache Heap descrptr 160
shared pool quiescing session 3872
shared pool process group array 33680
shared pool property service SO 3104
shared pool KGKP sga 32
shared pool lckhsh_kffsg 4104
shared pool JSX SGA 112
POOL NAME BYTES
------------ -------------------------- ----------
shared pool Background process state 48
shared pool SQL Memory Manager Base W 13400
shared pool Client ID trace setting h 536
shared pool object level 284832
shared pool Pre-Warm Initialized Seti 768
shared pool heap_kfsg 136
shared pool alter system errs: kspnfy 114688
shared pool qesmmaInitialize: oa_qesm 112
shared pool SGA structure for kelr 2336
shared pool kghx free lists 44576
shared pool kscdnfyinitprev 16
POOL NAME BYTES
------------ -------------------------- ----------
shared pool X$KSVIS table 128
shared pool KGSKI schedule 8296
shared pool kks stats hds 1024
shared pool KTI latches 8448
shared pool enqueue_hash_chain_latche 1920
shared pool KTPR SUBHEAP 168
shared pool KCB incremental ckpt entr 512
shared pool KTCN: Row Change Segmente 34496
shared pool rule set 24312
shared pool ksuloi: long op statistic 172000
shared pool kgsk subheap descriptor 136
POOL NAME BYTES
------------ -------------------------- ----------
shared pool KEWS aggregation objs 4032
shared pool KSXR large reply queue 168104
shared pool DML lock 2601672
shared pool object stat dummy elem 40
shared pool plwppwp:garbage handle 40
shared pool rules engine aggregate st 1416
shared pool SGA structure for SWRF (K 240
shared pool kohsg 8
shared pool buffer_pool_desc_array 3384
shared pool listener addresses 40
shared pool trigger condition node 72
POOL NAME BYTES
------------ -------------------------- ----------
shared pool error message file name 56
shared pool KTC latch cleanup 432
shared pool KFG state obj 4128
shared pool kwqmncini-slv 240
shared pool KSN WaitID 520
large pool free memory 16467112
large pool PX msg pool 310104
java pool free memory 30756288
java pool joxs heap 1032192
java pool joxlod exec hp 18543168
streams pool free memory 16781720
POOL NAME BYTES
------------ -------------------------- ----------
streams pool kwqbcqini:spilledovermsgs 1136
streams pool recov_kgqmctx 520
streams pool recov_kgqbtctx 5952
streams pool spilled:kwqbl 128
streams pool deqtree_kgqmctx 72
streams pool Sender info 2856
streams pool kwqbsinfy:bqg 536
streams pool substree_kgqmctx 72
streams pool msgtree_kgqmctx 72
streams pool kwqbsinfy:sta 432
streams pool time manager index 72
POOL NAME BYTES
------------ -------------------------- ----------
streams pool KGH: NO ACCESS 16760864
�� ������ 650 ��
SQL> spool off;
Could you post what result you have when running the following :
show parameter target
show parameter policy
show parameter cache
show parameter buffer
show parameter ga
set pages 999
set lines 200
select * from gv$resource_limit ;
select * from v$db_cache_advice ;
select * from v$pgastats ;
select * from v$pga_target_advice ;
Please do not make the mistake most DBAs do ... when you say you have 88 GB basically assigned to oracle that means that you have only 10 GB left - for the OS and all oracle processes, monitoring, batches, backups, ... - if you have a thousand userconnections, you might have a couple of hundred to a couple of thousand processes in parallel (we have about 2600 ...) - And each of these processes needs memory ... that adds up very fast to 50, 60 GB or more - depending on what these processes are doing - and your box clearly does the same explaining the 35+% pagingspace utilization
[quote=ctsgnb;302470664]
Could you post what result you have when running the following :
show parameter target
show parameter policy
show parameter cache
show parameter buffer
show parameter ga
set pages 999
set lines 200
select * from gv$resource_limit ;
select * from v$db_cache_advice ;
select * from v$pgastats ;
select * from v$pga_target_advice ;
?
output
SQL> show parameter target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
pga_aggregate_target big integer 6G
sga_target big integer 80G
SQL> show parameter policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
SQL> show parameter cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 32G
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 500
SQL> show parameter buffer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
buffer_pool_recycle string
db_block_buffers integer 0
log_buffer integer 62906368
use_indirect_data_buffers boolean FALSE
SQL> show parameter ga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
max_commit_propagation_delay integer 0
pga_aggregate_target big integer 6G
pre_page_sga boolean FALSE
sga_max_size big integer 80G
sga_target big integer 80G
SQL> set pages 999
SQL> set lines 200
SQL> select * from gv$resource_limit ;
INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
---------- ------------------------------ ------------------- --------------- ---------- ----------
1 processes 231 359 1200 1200
1 sessions 296 489 2400 2400
1 enqueue_locks 228 6584 26956 26956
1 enqueue_resources 205 6289 10532 UNLIMITED
1 ges_procs 0 0 0 0
1 ges_ress 0 0 0 UNLIMITED
1 ges_locks 0 0 0 UNLIMITED
1 ges_cache_ress 0 0 0 UNLIMITED
1 ges_reg_msgs 0 0 0 UNLIMITED
1 ges_big_msgs 0 0 0 UNLIMITED
1 ges_rsv_msgs 0 0 0 0
1 gcs_resources 0 0 0 0
1 gcs_shadows 0 0 0 0
1 dml_locks 20 585 10000 UNLIMITED
1 temporary_table_locks 0 7 UNLIMITED UNLIMITED
1 transactions 3 214 2640 UNLIMITED
1 branches 0 1 2640 UNLIMITED
1 cmtcallbk 5 36 2640 UNLIMITED
1 sort_segment_locks 115 627 UNLIMITED UNLIMITED
1 max_rollback_segments 75 75 2640 65535
1 max_shared_servers 0 0 UNLIMITED UNLIMITED
1 parallel_max_servers 0 12 12 3600
�� ������ 22 ��
SQL> select * from v$db_cache_advice ;
ID NAME BLOCK_SIZE ADV SIZE_FOR_ESTIMATE SIZE_FACTOR BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS ESTD_PHYSICAL_READ_TIME ESTD_PCT_OF_DB_TIME_FOR_READS
---------- -------------------- ---------- --- ----------------- ----------- -------------------- ------------------------- ------------------- ----------------------- -----------------------------
ESTD_CLUSTER_READS ESTD_CLUSTER_READ_TIME
------------------ ----------------------
3 DEFAULT 8192 ON 7520 .0998 930130 2.008 74635297 560396 39.7
0 0
3 DEFAULT 8192 ON 15040 .1997 1860260 1.5594 57959899 417918 29.6
0 0
3 DEFAULT 8192 ON 22560 .2995 2790390 1.3485 50121978 350949 24.8
0 0
3 DEFAULT 8192 ON 30080 .3993 3720520 1.2448 46269211 318030 22.5
0 0
3 DEFAULT 8192 ON 37600 .4992 4650650 1.1782 43790523 296851 21
0 0
3 DEFAULT 8192 ON 45120 .599 5580780 1.1389 42330501 284376 20.1
0 0
3 DEFAULT 8192 ON 52640 .6988 6510910 1.0945 40682522 270296 19.1
0 0
3 DEFAULT 8192 ON 60160 .7986 7441040 1.0575 39304625 258523 18.3
0 0
3 DEFAULT 8192 ON 67680 .8985 8371170 1.0173 37811414 245764 17.4
0 0
3 DEFAULT 8192 ON 75200 .9983 9301300 1.0003 37178833 240359 17
0 0
3 DEFAULT 8192 ON 75328 1 9317132 1 37168845 240274 17
0 0
3 DEFAULT 8192 ON 82720 1.0981 10231430 .9835 36556783 235044 16.6
0 0
3 DEFAULT 8192 ON 90240 1.198 11161560 .974 36204222 232032 16.4
0 0
3 DEFAULT 8192 ON 97760 1.2978 12091690 .9597 35671233 227478 16.1
0 0
3 DEFAULT 8192 ON 105280 1.3976 13021820 .9456 35148379 223011 15.8
0 0
3 DEFAULT 8192 ON 112800 1.4975 13951950 .9355 34771862 219794 15.6
0 0
3 DEFAULT 8192 ON 120320 1.5973 14882080 .9203 34207971 214976 15.2
0 0
3 DEFAULT 8192 ON 127840 1.6971 15812210 .9094 33800706 211496 15
0 0
3 DEFAULT 8192 ON 135360 1.7969 16742340 .8982 33385214 207946 14.7
0 0
3 DEFAULT 8192 ON 142880 1.8968 17672470 .8894 33059309 205161 14.5
0 0
3 DEFAULT 8192 ON 150400 1.9966 18602600 .8662 32193806 197766 14
0 0
�� ������ 21 ��
SQL> select * from v$pgastats ;
select * from v$pgastats
*
���� ��� ��� ��� 1:
ORA-00942: ������ �� ������ ��������� ��� �����
SQL> select * from sys.v$pgastats ;
select * from sys.v$pgastats
*
���� ��� ��� ��� 1:
ORA-00942: ������ �� ������ ��������� ��� �����
SQL> select * from v$pga_target_advice ;
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ------------------- ----------------------------- --------------------
805306368 .125 ON 6.1539E+12 3.3709E+11 95 18145
1610612736 .25 ON 6.1539E+12 2.5813E+11 96 11166
3221225472 .5 ON 6.1539E+12 6.8188E+10 99 1663
4831838208 .75 ON 6.1539E+12 3.9611E+10 99 0
6442450944 1 ON 6.1539E+12 1.9264E+10 100 0
7730940928 1.2 ON 6.1539E+12 1.8177E+10 100 0
9019430912 1.4 ON 6.1539E+12 1.8177E+10 100 0
1.0308E+10 1.6 ON 6.1539E+12 1.8177E+10 100 0
1.1596E+10 1.8 ON 6.1539E+12 1.8177E+10 100 0
1.2885E+10 2 ON 6.1539E+12 1.8177E+10 100 0
1.9327E+10 3 ON 6.1539E+12 1.8177E+10 100 0
2.5770E+10 4 ON 6.1539E+12 1.8177E+10 100 0
3.8655E+10 6 ON 6.1539E+12 1.8177E+10 100 0
5.1540E+10 8 ON 6.1539E+12 1.8177E+10 100 0
�� ������ 14 ��
SQL> spool off;
First of all, you should lower your SGA i would set it to 20Gb for a beginning and put it in memory with lock_sga = true
You should lower the db_cache_size as well (must be contained in sga), 32G is too big regarding to the current activity which show around 620M to be currently sufficient ....
so setting a db_cache_size to 2Gb would already be quite generous
it was lock_sga, not log_sga (that was a typo error that i have corrected)
You should consider setting lock_sga to true instead of the current value false, but make sur you fit the oracle recommendation (see the previous pdf and metalink reference previously posted)
I did a typo error : in the view name of the PGA : it is v$pgastat instead of v$pgastats (no trailing "s")
select * from v$pgastat;
By the way , you might consider setting your lock_sga to true instead of false since it seems you have enough physical memory to hold your SGA into it (this will force the SGA in RAM and not in swap). But do it carefully (if all the RAM is used by the SGA, there will be less RAM for PGA ...) and make sure you follow the oracle recommendations in the pdf link mentionned in my previous post.
Maybe you should generate an html report on your server using the standard report, @$ORACLE_HOME/rdbms/admin/awrrpt.sql
and selecting 2 snaps containing a timeframe in which you encountered performance problem.(The timeframe should not contain downtime, otherwise the reported stat are not relevant) so you could then have a look at it (have a look at heavy sql statements for example).
Does your server run a single oracle instance or more ?
Check how much RAM is used purely by your operating system.
Do you have some other application or ERP running on that server (SAP ? OAP ? other ?) or is it a pure oracle server?
The only reason your box is still alive is that the SGA is not pinned into memory if it is so huge. If you even try to do that the box will no doubt crash after some uptime as the kernel is pinning memory too and you cannot exceed the overall possible value of pinned memory that is about 85%. With sufficient tuning and sufficient memory, AIX is not going to page computational memory as long as the avm value doesnt exceed 97% of total physical memory. Btw I mentioned it before - your minperm is 10% - even by default it is on AIX 6.1 3% and with your memory consumption on the box you should do the same even for AIX 5.3.
The SGA is OVERsized ...
as well as the db_cache_size which is told not to decrease below 32G whereas it seems 75328 x 8192 ~ 620M are currently enough ...
Any chance of seeing the initSID.ora file ?
I agree that say 24 Gb total SGA (25% of memory) would be generous unless this database has a history of ORA errors with "normal" values.
An excessively large db_cache_size can itself cause performance issues on databases with a high rate of data change.
A reason that would justify such a big SGA would be if we would have plenty of users and the server would run in shared server mode (the UGA is then located in SGA instead of the PGA).
But it doesn't seem to be our case.
Please give us the output of