0

Can anybody help me in optimizing my SGA, PGA and other paramters of the init.ora file. We usually have average of 159 sessions on the database server. I think there's some thing wrong with my SGA, PGA memory allocation, here is my initXZ10.ora file.

    XZ10.__db_cache_size=512876288 
XZ10.__java_pool_size=33554432 
XZ10.__large_pool_size=16777216 
XZ10.__shared_pool_size=436207616 
XZ10.__streams_pool_size=0
    *.db_block_size=8192
    *.job_queue_processes=10
    *.open_cursors=5000
    *.open_links=20
    *.open_links_per_instance=20
    *.pga_aggregate_target=4865416704
    *.processes=500
    *.sessions=2000
    *.sga_max_size=11516192768
    *.sga_target=11516192768
    *.transactions=500
    *.db_cache_size=512876288
    *.java_pool_size=33554432
    *.large_pool_size=16777216
    *.shared_pool_size=436207616
    *.streams_pool_size=0

I have 16GB RAM on the server and 64 bit oracle 10.2.0.4.0

Can somebody please help me here to optimize the init.ora file and any parameters to speed up the database.

Thanks everyone.

UPDATE (stats i got from toad):

STATNUMBER  NAME    VALUE
286 CCursor + sql area evicted  0
12  CPU used by this session    136,387,539
11  CPU used when call started  131,552,808
108 CR blocks created   2,563,353
199 Cached Commit SCN referenced    1,216,999
198 Commit SCN cached   10,429
13  DB time 1,813,089,194
69  DBWR checkpoint buffers written 14,067,715
79  DBWR checkpoints    7,152
80  DBWR fusion writes  0
78  DBWR lru scans  0
77  DBWR make free requests 0
73  DBWR object drop buffers written    539
72  DBWR parallel query checkpoint buffers written  0
76  DBWR revisited being-written buffer 2
71  DBWR tablespace checkpoint buffers written  679
70  DBWR thread checkpoint buffers written  350,837
74  DBWR transaction table writes   436,305
75  DBWR undo block writes  4,106,537
316 DDL statements parallelized 26
317 DFO trees parallelized  26
315 DML statements parallelized 0
241 IMU CR rollbacks    84,759
233 IMU Flushes 5,998,035
243 IMU Redo allocation size    25,698,009,388
238 IMU bind flushes    0
232 IMU commits 925,782
234 IMU contention  6,922
237 IMU ktichg flush    13,324
239 IMU mbu flush   0
240 IMU pool not allocated  5,669,778
235 IMU recursive-transaction flush 1,313
242 IMU undo allocation size    23,110,167,184
236 IMU undo retention flush    0
244 IMU- failed to get a private strand 5,669,778
43  IPC CPU used by this session    0
245 Misses for writing mapping  45
373 OS Block input operations   0
374 OS Block output operations  0
367 OS Integral shared text size    0
368 OS Integral unshared data size  0
369 OS Integral unshared stack size 0
379 OS Involuntary context switches 0
366 OS Maximum resident set size    0
371 OS Page faults  0
370 OS Page reclaims    0
377 OS Signals received 0
376 OS Socket messages received 0
375 OS Socket messages sent 0
372 OS Swaps    0
365 OS System time used 0
364 OS User time used   0
378 OS Voluntary context switches   0
352 OTC commit optimization attempts    0
354 OTC commit optimization failure - setup 0
353 OTC commit optimization hits    0
325 PX local messages recv'd    17,484
324 PX local messages sent  17,484
327 PX remote messages recv'd   0
326 PX remote messages sent 0
323 Parallel operations downgraded 1 to 25 pct  0
322 Parallel operations downgraded 25 to 50 pct 0
321 Parallel operations downgraded 50 to 75 pct 0
320 Parallel operations downgraded 75 to 99 pct 0
319 Parallel operations downgraded to serial    0
318 Parallel operations not downgraded  26
187 RowCR - resume  0
186 RowCR - row contention  0
184 RowCR attempts  0
185 RowCR hits  0
230 SMON posted for dropping temp segment   0
228 SMON posted for instance recovery   0
231 SMON posted for supplemental logging cleanup    0
227 SMON posted for txn recovery for other instances    0
226 SMON posted for undo segment recovery   1,661
229 SMON posted for undo segment shrink 2,812
345 SQL*Net roundtrips to/from client   9,615,738,412
348 SQL*Net roundtrips to/from dblink   11,349,814
356 Workload Capture: dbtime    0
363 Workload Capture: errors    0
355 Workload Capture: size (in bytes) of recording  0
359 Workload Capture: unreplayable user calls   0
362 Workload Capture: unsupported user calls    0
357 Workload Capture: user calls    0
358 Workload Capture: user calls flushed    0
361 Workload Capture: user logins   0
360 Workload Capture: user txns 0
194 active txn count during cleanout    2,394,012
16  application wait time   4,088,410
200 auto extends on undo tablespace 0
167 background checkpoints completed    1,822
166 background checkpoints started  1,822
24  background timeouts 41,236,264
264 branch node splits  604
329 buffer is not pinned count  48,347,316,406
328 buffer is pinned count  58,909,520,331
344 bytes received via SQL*Net from client  262,539,429,785
347 bytes received via SQL*Net from dblink  1,528,844,510
343 bytes sent via SQL*Net to client    6,371,624,466,243
346 bytes sent via SQL*Net to dblink    1,429,707,142
131 calls to get snapshot scn: kcmgss   2,337,208,001
130 calls to kcmgas 47,361,851
128 calls to kcmgcs 1,210,241
129 calls to kcmgrs 0
89  change write time   298,330
195 cleanout - number of ktugct calls   8,049,194
183 cleanouts and rollbacks - consistent read gets  950,265
181 cleanouts only - consistent read gets   125,109
256 cluster key scan block gets 539,996,045
255 cluster key scans   282,870,617
14  cluster wait time   0
124 cold recycle reads  0
212 commit batch performed  329
209 commit batch requested  329
211 commit batch/immediate performed    74,830
208 commit batch/immediate requested    74,830
99  commit cleanout failures: block lost    8,070,469
102 commit cleanout failures: buffer being written  1,336
103 commit cleanout failures: callback failure  56,094
100 commit cleanout failures: cannot pin    418
101 commit cleanout failures: hot backup in progress    0
98  commit cleanout failures: write disabled    0
104 commit cleanouts    48,181,994
105 commit cleanouts successfully completed 40,053,677
213 commit immediate performed  74,501
210 commit immediate requested  74,501
218 commit nowait performed 0
215 commit nowait requested 0
193 commit txn count during cleanout    9,033,139
219 commit wait performed   0
216 commit wait requested   0
217 commit wait/nowait performed    0
214 commit wait/nowait requested    0
15  concurrency wait time   3,600,785
60  consistent changes  34,402,541
50  consistent gets 141,292,688,365
52  consistent gets - examination   14,193,653,926
53  consistent gets direct  571,739
51  consistent gets from cache  141,292,116,626
109 current blocks converted for CR 2,981
313 cursor authentications  7,537,029
179 data blocks consistent reads - undo records applied 23,718,094
59  db block changes    473,442,202
47  db block gets   415,291,683
49  db block gets direct    171,634
48  db block gets from cache    415,120,049
192 deferred (CURRENT) block cleanout applications  28,347,656
197 deferred CUR cleanouts (index blocks)   0
94  dirty buffers inspected 97,724
203 doubling up with imu segment    0
201 drop segment calls in space pressure    0
31  enqueue conversions 29,620,537
29  enqueue deadlocks   16
32  enqueue releases    227,213,383
30  enqueue requests    227,218,623
27  enqueue timeouts    5,130
28  enqueue waits   3,657
92  exchange deadlocks  0
342 execute count   2,163,608,493
265 failed probes on index block reclamation    556
170 flashback log writes    0
341 frame signature mismatch    7
97  free buffer inspected   11,876,445,818
93  free buffer requested   11,830,274,019
163 gc CPU used by this session 0
162 gc blocks corrupt   0
160 gc blocks lost  0
161 gc claim blocks lost    0
147 gc cr block build time  0
148 gc cr block flush time  0
155 gc cr block receive time    0
149 gc cr block send time   0
154 gc cr blocks received   0
146 gc cr blocks served 0
152 gc current block flush time 0
151 gc current block pin time   0
157 gc current block receive time   0
153 gc current block send time  0
156 gc current blocks received  0
150 gc current blocks served    0
158 gc local grants 0
159 gc remote grants    0
44  gcs messages sent   0
45  ges messages sent   0
46  global enqueue CPU used by this session 0
35  global enqueue get time 0
34  global enqueue gets async   0
33  global enqueue gets sync    0
36  global enqueue releases 0
220 global undo segment hints helped    0
221 global undo segment hints were stale    0
283 heap block compress 997,073
96  hot buffers moved to head of LRU    513,678,113
191 immediate (CR) block cleanout applications  1,075,374
190 immediate (CURRENT) block cleanout applications 2,409,651
196 immediate CR cleanouts (index blocks)   0
260 index crx upgrade (found)   3,982
261 index crx upgrade (positioned)  41,986,412
259 index crx upgrade (prefetch)    0
274 index fast full scans (direct read) 0
272 index fast full scans (full)    1,171,536
273 index fast full scans (rowid ranges)    0
275 index fetch by key  8,579,512,934
276 index scans kdiixs1 1,391,204,529
165 instance recovery database freeze count 0
301 java call heap collected bytes  0
300 java call heap collected count  0
299 java call heap gc count 0
297 java call heap live object count    0
298 java call heap live object count max    0
293 java call heap live size    0
294 java call heap live size max    0
295 java call heap object count 0
296 java call heap object count max 0
289 java call heap total size   0
290 java call heap total size max   0
291 java call heap used size    0
292 java call heap used size max    0
312 java session heap collected bytes   0
311 java session heap collected count   0
310 java session heap gc count  0
308 java session heap live object count 0
309 java session heap live object count max 0
304 java session heap live size 0
305 java session heap live size max 0
306 java session heap object count  0
307 java session heap object count max  0
302 java session heap used size 0
303 java session heap used size max 0
263 leaf node 90-10 splits  71,763
262 leaf node splits    201,580
269 lob reads   1,386,696
270 lob writes  403,509
271 lob writes unaligned    403,509
222 local undo segment hints helped 0
223 local undo segment hints were stale 0
0   logons cumulative   465,589
1   logons current  161
23  messages received   7,249,356
22  messages sent   7,249,352
267 native hash arithmetic execute  0
268 native hash arithmetic fail 0
330 no buffer to keep pinned count  9
180 no work - consistent read gets  126,776,111,391
169 number of map misses    0
168 number of map operations    0
2   opened cursors cumulative   529,558,048
3   opened cursors current  11,082
340 parse count (failures)  252,999
339 parse count (hard)  20,024,250
338 parse count (total) 298,314,754
336 parse time cpu  3,717,491
337 parse time elapsed  6,058,951
57  physical read IO requests   1,824,550,984
58  physical read bytes 96,983,136,133,120
37  physical read total IO requests 1,836,622,845
39  physical read total bytes   97,258,769,898,496
38  physical read total multi block requests    1,568,767,031
54  physical reads  11,838,761,735
55  physical reads cache    11,814,724,768
114 physical reads cache prefetch   9,997,689,125
56  physical reads direct   24,036,967
120 physical reads direct (lob) 469,336
121 physical reads direct temporary tablespace  23,454,567
127 physical reads for flashback new    0
115 physical reads prefetch warmup  45,204
119 physical reads retry corrupt    0
65  physical write IO requests  9,592,784
66  physical write bytes    329,012,215,808
40  physical write total IO requests    27,659,796
42  physical write total bytes  702,568,278,528
41  physical write total multi block requests   7,321,669
62  physical writes 40,162,624
63  physical writes direct  25,823,845
122 physical writes direct (lob)    13,939
123 physical writes direct temporary tablespace 25,788,346
64  physical writes from cache  14,338,779
67  physical writes non checkpoint  32,324,547
95  pinned buffers inspected    1,755,155
87  prefetch clients - 16k  0
84  prefetch clients - 2k   0
88  prefetch clients - 32k  0
85  prefetch clients - 4k   0
86  prefetch clients - 8k   0
83  prefetch clients - default  13,492
81  prefetch clients - keep 0
82  prefetch clients - recycle  0
117 prefetch warmup blocks aged out before use  8,973
118 prefetch warmup blocks flushed out before use   0
116 prefetched blocks aged out before use   1,049,270
19  process last non-idle time  1,422,452,776
314 queries parallelized    0
278 queue flush 0
281 queue ocp pages 0
279 queue position update   0
282 queue qno pages 0
280 queue single row    0
277 queue splits    0
258 queue update without cp update  0
107 recovery array read time    0
106 recovery array reads    0
61  recovery blocks read    0
266 recursive aborts on index block reclamation 1
7   recursive calls 10,500,659,011
8   recursive cpu usage 48,069,287
132 redo blocks read for recovery   0
139 redo blocks written 155,314,120
135 redo buffer allocation retries  6,674
133 redo entries    218,307,764
141 redo log space requests 9,790
142 redo log space wait time    362,850
143 redo log switch interrupts  0
144 redo ordering marks 3,666,834
134 redo size   75,476,262,524
145 redo subscn max counts  5,856,483
91  redo synch time 9,254,304
90  redo synch writes   1,743,577
136 redo wastage    1,404,035,416
140 redo write time 18,619,316
137 redo writer latching time   292
138 redo writes 5,342,119
188 rollback changes - undo records applied 3,824,467
182 rollbacks only - consistent read gets   1,515,895
257 rows fetched via callback   2,400,714,358
171 serializable aborts 0
18  session connect time    1,420,351,837
288 session cursor cache count  8,541,457
287 session cursor cache hits   379,984,258
9   session logical reads   141,707,980,048
25  session pga memory  420,888,738,808
26  session pga memory max  2,000,350,879,928
10  session stored procedure space  0
20  session uga memory  964,018,923,456,136
21  session uga memory max  680,443,769,952
125 shared hash latch upgrades - no wait    49,750,687
126 shared hash latch upgrades - wait   11,534
350 sorts (disk)    71
349 sorts (memory)  496,351,902
351 sorts (rows)    11,834,196,185
206 space was found by tune down    0
207 space was not found by tune down    0
285 sql area evicted    16,167,405
284 sql area purged 265,797
205 steps of tune down ret. in space pressure   0
68  summed dirty queue length   137,227
110 switch current to new buffer    8,800,503
253 table fetch by rowid    33,177,345,476
254 table fetch continued row   3,653,211,818
331 table lookup prefetch client count  0
252 table scan blocks gotten    82,020,216,469
251 table scan rows gotten  3,296,015,465,842
249 table scans (cache partitions)  0
250 table scans (direct read)   35
247 table scans (long tables)   380,793
248 table scans (rowid ranges)  41
246 table scans (short tables)  86,379,893
164 total number of slots   0
225 total number of times SMON posted   116,719
202 total number of undo segments dropped   0
175 transaction lock background get time    0
174 transaction lock background gets    0
172 transaction lock foreground requests    0
173 transaction lock foreground wait time   0
189 transaction rollbacks   74,388
178 transaction tables consistent read rollbacks    1,323
177 transaction tables consistent reads - undo records applied  248,693
204 tune down retentions in space pressure  0
176 undo change vector size 26,157,061,436
224 undo segment header was pinned  0
17  user I/O wait time  175,984,176
6   user calls  9,209,640,359
4   user commits    11,693,394
5   user rollbacks  205,089
335 workarea executions - multipass 0
334 workarea executions - onepass   3,803
333 workarea executions - optimal   291,884,218
332 workarea memory allocated   100,694
113 write clones created for recovery   0
112 write clones created in background  23,816
111 write clones created in foreground  9,301
| improve this question | | | | |
  • please add more info to the question like database usage, size, ..etc – Ahmad Abuhasna Jan 28 '15 at 13:32
  • increase your db_cache_size as well as shared_pool_size. – Md Haidar Ali Khan Jan 28 '15 at 13:36
  • database size is 52 GB, database usage is 159-200 sessions average at any given time, concurrent users will be 120 at peak times. – user3625561 Jan 28 '15 at 13:42
  • 2
    optimize for what? – Raj Jan 28 '15 at 13:44
  • 1
    It's unclear what you are asking. What is the problem? – kevinsky Jan 28 '15 at 13:47
-1

I am giving some sga dependent pool size according to your sga_max_size=11516192768 parameter . you can tune

db_cache_size=3221225472

java_pool_size=16777216

large_pool_size=16777216

shared_pool_size=2147483648

Before tune the init.ora keep backup of master DB and also keep separate backup of control files.

Give it try

| improve this answer | | | | |
  • my sga_max_size is 11gb, i have set it due to my ram which is 16gb, is the sga_max_size correct because nothing else is running on the server. – user3625561 Jan 28 '15 at 14:10
  • Kindly you provide "system health report" ,"run.conf" & "sysctl.conf" file also Your OS environment. – Md Haidar Ali Khan Jan 29 '15 at 5:02
  • Can you tell me in you environment in "run.conf" file how much RAM size declare like example That JAVA_OPTS="-server -Xms512m -Xmx512m -XX:PermSize=512m -XX:MaxPermSize=512m" – Md Haidar Ali Khan Jan 29 '15 at 5:04
  • for example JAVA_OPTS="-server -Xms512m -Xmx512m -XX:PermSize=512m -XX:MaxPermSize=512m" this is a parameter declare in "run.conf" is 512M (that is 512 MB) so once you have increase the RAM parameter size like 1024M or 1536M. In your case your Server RAM size is 16 GB but how much declare in "run.cnf" to utilization of RAM for your DB for proper intervention. – Md Haidar Ali Khan Jan 29 '15 at 5:31
  • Before tune the "init.ora" first you must check your "run.conf" file. – Md Haidar Ali Khan Jan 29 '15 at 5:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.