1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
398.
399.
400.
401.
402.
403.
404.
405.
406.
407.
408.
409.
410.
411.
412.
413.
414.
415.
416.
417.
418.
419.
420.
421.
422.
423.
424.
425.
426.
427.
428.
429.
430.
431.
432.
433.
434.
435.
436.
437.
438.
439.
440.
441.
442.
443.
444.
445.
446.
447.
448.
449.
450.
451.
452.
453.
454.
455.
456.
457.
458.
459.
460.
461.
462.
463.
464.
465.
466.
467.
468.
469.
470.
471.
472.
473.
474.
475.
476.
477.
478.
479.
480.
481.
482.
483.
484.
485.
486.
487.
488.
489.
490.
491.
492.
493.
494.
495.
496.
497.
498.
499.
500.
501.
502.
503.
504.
505.
506.
507.
508.
509.
510.
511.
512.
513.
514.
515.
516.
517.
518.
519.
520.
521.
522.
523.
524.
525.
526.
527.
528.
529.
530.
531.
532.
533.
534.
535.
536.
537.
538.
539.
540.
541.
542.
543.
544.
545.
546.
547.
548.
549.
550.
551.
552.
553.
554.
555.
556.
557.
558.
559.
560.
561.
562.
563.
564.
565.
566.
567.
568.
569.
570.
571.
572.
573.
574.
575.
576.
577.
578.
579.
580.
581.
582.
583.
584.
585.
586.
587.
588.
589.
590.
591.
592.
593.
594.
595.
596.
597.
598.
599.
600.
601.
602.
603.
604.
605.
606.
607.
608.
609.
610.
611.
612.
613.
614.
615.
616.
617.
618.
619.
620.
621.
622.
623.
624.
625.
626.
627.
628.
629.
630.
631.
632.
633.
634.
635.
636.
637.
638.
639.
640.
641.
642.
643.
644.
645.
646.
647.
648.
649.
650.
651.
652.
653.
654.
655.
656.
657.
658.
659.
660.
661.
662.
663.
664.
665.
666.
667.
668.
669.
670.
671.
672.
673.
674.
675.
676.
677.
678.
679.
680.
681.
682.
683.
684.
685.
686.
687.
688.
689.
690.
691.
692.
693.
694.
695.
696.
697.
698.
699.
700.
701.
702.
703.
704.
705.
706.
707.
708.
709.
710.
711.
712.
713.
714.
715.
716.
717.
718.
719.
720.
721.
722.
723.
724.
725.
726.
727.
728.
729.
730.
731.
732.
733.
734.
735.
736.
737.
738.
739.
740.
741.
742.
743.
744.
745.
746.
747.
748.
749.
750.
751.
752.
753.
754.
755.
756.
757.
758.
759.
760.
761.
762.
763.
764.
765.
766.
767.
768.
769.
770.
771.
772.
773.
774.
775.
776.
777.
778.
779.
780.
781.
782.
783.
784.
785.
786.
787.
788.
789.
790.
791.
792.
793.
794.
795.
796.
797.
798.
799.
800.
801.
802.
803.
804.
805.
806.
807.
808.
809.
810.
811.
812.
813.
814.
815.
816.
817.
818.
819.
820.
821.
822.
823.
824.
825.
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
XXXTLS 2929404262 kztl 1 8 . 1 . 7 . 2 . 0 NO frigate
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 1 28 -May- 03 16 : 10 : 03 97
End Snap: 2 28 -May- 03 16 : 27 : 19 97
Elapsed: 17 . 27 (mins)
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 120000 log_buffer: 524288
db_block_size: 4096 shared_pool_size: 120000000
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 71 , 528 . 05 9 , 879 . 09
Logical reads: 36 , 170 . 77 4 , 995 . 72
Block changes: 481 . 15 66 . 45
Physical reads: 88 . 68 12 . 25
Physical writes: 66 . 85 9 . 23
User calls: 264 . 66 36 . 55
Parses: 469 . 57 64 . 85
Hard parses: 78 . 69 10 . 87
Sorts: 116 . 68 16 . 12
Logons: 0 . 09 0 . 01
Executes: 2 , 591 . 23 357 . 89
Transactions: 7 . 24
% Blocks changed per Read: 1 . 33 Recursive Call %: 96 . 20
Rollback per transaction %: 0 . 47 Rows per Sort: 21 . 90
Instance Efficiency Percentages (Target 100 %)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100 . 00 Redo NoWait %: 100 . 00
Buffer Hit %: 99 . 75 In-memory Sort %: 99 . 99
Library Hit %: 97 . 40 Soft Parse %: 83 . 24
Execute to Parse %: 81 . 88 Latch Hit %: 99 . 54
Parse CPU to Parse Elapsd %: 57 . 02 % Non-Parse CPU: 99 . 90
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 85 . 39 85 . 80
% SQL with executions> 1 : 69 . 38 73 . 43
% Memory for SQL w/exec> 1 : 71 . 28 71 . 60
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
PX Deq: Execution Msg 1 , 496 306 , 283 42 . 62
PX Deq Credit: send blkd 1 , 496 306 , 090 42 . 60
latch free 259 , 168 57 , 284 7 . 97
log file parallel write 12 , 694 20 , 093 2 . 80
db file sequential read 32 , 955 19 , 989 2 . 78
-------------------------------------------------------------
Wait Events for DB: XXXTLS Instance: kztl Snaps: 1 - 2
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
PX Deq: Execution Msg 1 , 496 1 , 491 306 , 283 2047 0 . 2
PX Deq Credit: send blkd 1 , 496 1 , 491 306 , 090 2046 0 . 2
latch free 259 , 168 138 , 152 57 , 284 2 34 . 6
log file parallel write 12 , 694 0 20 , 093 16 1 . 7
db file sequential read 32 , 955 0 19 , 989 6 4 . 4
log file sync 3 , 036 0 6 , 839 23 0 . 4
control file parallel write 342 0 1 , 049 31 0 . 0
db file scattered read 4 , 917 0 792 2 0 . 7
buffer busy waits 29 0 42 14 0 . 0
log file switch completion 1 0 32 320 0 . 0
enqueue 4 0 20 50 0 . 0
LGWR wait for redo copy 53 1 6 1 0 . 0
SQL*Net break/reset to clien 42 0 6 1 0 . 0
db file parallel write 6 , 947 0 5 0 0 . 9
SQL*Net more data to client 312 0 5 0 0 . 0
library cache pin 4 0 5 13 0 . 0
control file sequential read 61 0 4 1 0 . 0
direct path read 889 0 2 0 0 . 1
file open 202 0 2 0 0 . 0
file identify 4 0 2 5 0 . 0
log file single write 4 0 2 5 0 . 0
log file sequential read 2 0 2 10 0 . 0
PX Deq: Signal ACK 2 0 1 5 0 . 0
direct path write 35 0 0 0 0 . 0
refresh controlfile command 6 0 0 0 0 . 0
SQL*Net message from client 192 , 726 0 8 , 504 , 579 441 25 . 7
PX Idle Wait 1 , 615 1 , 614 330 , 870 2049 0 . 2
SQL*Net message to client 192 , 721 0 185 0 25 . 7
SQL*Net more data from clien 52 0 116 22 0 . 0
-------------------------------------------------------------
Background Wait Events for DB: XXXTLS Instance: kztl Snaps: 1 - 2
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
log file parallel write 12 , 695 0 20 , 094 16 1 . 7
control file parallel write 342 0 1 , 049 31 0 . 0
latch free 48 47 78 16 0 . 0
LGWR wait for redo copy 53 1 6 1 0 . 0
db file parallel write 6 , 950 0 5 0 0 . 9
db file sequential read 2 0 3 15 0 . 0
file open 88 0 2 0 0 . 0
control file sequential read 38 0 2 1 0 . 0
file identify 4 0 2 5 0 . 0
log file single write 4 0 2 5 0 . 0
log file sequential read 2 0 2 10 0 . 0
direct path read 21 0 0 0 0 . 0
direct path write 21 0 0 0 0 . 0
rdbms ipc message 13 , 872 1 , 875 582 , 033 420 1 . 8
pmon timer 380 308 103 , 495 2724 0 . 1
smon timer 3 3 92 , 161 ###### 0 . 0
-------------------------------------------------------------
SQL ordered by Gets for DB: XXXTLS Instance: kztl Snaps: 1 - 2
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
...
cut
...
-------------------------------------------------------------
Instance Activity Stats for DB: XXXTLS Instance: kztl Snaps: 1 - 2
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 167 , 688 161 . 9 22 . 4
CPU used when call started 182 , 347 176 . 0 24 . 3
CR blocks created 1 , 898 1 . 8 0 . 3
Cached Commit SCN referenced 0 0 . 0 0 . 0
DBWR buffers scanned 20 , 949 20 . 2 2 . 8
DBWR checkpoint buffers written 48 , 131 46 . 5 6 . 4
DBWR checkpoints 1 0 . 0 0 . 0
DBWR free buffers found 20 , 676 20 . 0 2 . 8
DBWR lru scans 313 0 . 3 0 . 0
DBWR make free requests 313 0 . 3 0 . 0
DBWR revisited being-written buff 0 0 . 0 0 . 0
DBWR summed scan depth 20 , 949 20 . 2 2 . 8
DBWR transaction table writes 1 , 989 1 . 9 0 . 3
DBWR undo block writes 11 , 700 11 . 3 1 . 6
DFO trees parallelized 0 0 . 0 0 . 0
PX local messages recv'd 0 0 . 0 0 . 0
PX local messages sent 0 0 . 0 0 . 0
Parallel operations downgraded 25 0 0 . 0 0 . 0
Parallel operations downgraded 50 0 0 . 0 0 . 0
Parallel operations downgraded to 0 0 . 0 0 . 0
Parallel operations not downgrade 0 0 . 0 0 . 0
SQL*Net roundtrips to/from client 192 , 583 185 . 9 25 . 7
background checkpoints completed 1 0 . 0 0 . 0
background checkpoints started 1 0 . 0 0 . 0
background timeouts 2 , 044 2 . 0 0 . 3
branch node splits 10 0 . 0 0 . 0
buffer is not pinned count 28 , 697 , 875 27 , 700 . 7 3 , 825 . 9
buffer is pinned count 14 , 494 , 552 13 , 990 . 9 1 , 932 . 4
bytes received via SQL*Net from c 71 , 125 , 005 68 , 653 . 5 9 , 482 . 1
bytes sent via SQL*Net to client 44 , 474 , 379 42 , 928 . 9 5 , 929 . 1
calls to get snapshot scn: kcmgss 2 , 488 , 808 2 , 402 . 3 331 . 8
calls to kcmgas 12 , 109 11 . 7 1 . 6
calls to kcmgcs 2 , 686 2 . 6 0 . 4
change write time 929 0 . 9 0 . 1
cleanouts and rollbacks - consist 1 , 168 1 . 1 0 . 2
cleanouts only - consistent read 1 , 782 1 . 7 0 . 2
cluster key scan block gets 58 , 335 56 . 3 7 . 8
cluster key scans 20 , 919 20 . 2 2 . 8
commit cleanout failures: block l 3 , 561 3 . 4 0 . 5
commit cleanout failures: buffer 417 0 . 4 0 . 1
commit cleanout failures: callbac 4 0 . 0 0 . 0
commit cleanout failures: cannot 32 0 . 0 0 . 0
commit cleanouts 36 , 872 35 . 6 4 . 9
commit cleanouts successfully com 32 , 858 31 . 7 4 . 4
consistent changes 40 , 591 39 . 2 5 . 4
consistent gets 33 , 297 , 148 32 , 140 . 1 4 , 439 . 0
current blocks converted for CR
cursor authentications 4 , 330 4 . 2 0 . 6
data blocks consistent reads - un 3 , 494 3 . 4 0 . 5
db block changes 498 , 473 481 . 2 66 . 5
db block gets 4 , 175 , 776 4 , 030 . 7 556 . 7
deferred (CURRENT) block cleanout 16 , 735 16 . 2 2 . 2
dirty buffers inspected 241 0 . 2 0 . 0
enqueue conversions 28 0 . 0 0 . 0
enqueue deadlocks 0 0 . 0 0 . 0
enqueue releases 174 , 018 168 . 0 23 . 2
Instance Activity Stats for DB: XXXTLS Instance: kztl Snaps: 1 - 2
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
enqueue requests 173 , 986 167 . 9 23 . 2
enqueue timeouts 0 0 . 0 0 . 0
enqueue waits 0 0 . 0 0 . 0
exchange deadlocks 0 0 . 0 0 . 0
execute count 2 , 684 , 511 2 , 591 . 2 357 . 9
free buffer inspected 259 0 . 3 0 . 0
free buffer requested 99 , 275 95 . 8 13 . 2
hot buffers moved to head of LRU 47 , 701 46 . 0 6 . 4
immediate (CR) block cleanout app 2 , 950 2 . 9 0 . 4
immediate (CURRENT) block cleanou 6 , 860 6 . 6 0 . 9
index fast full scans (full) 2 , 209 2 . 1 0 . 3
leaf node splits 1 , 063 1 . 0 0 . 1
logons cumulative 93 0 . 1 0 . 0
messages received 23 , 381 22 . 6 3 . 1
messages sent 23 , 381 22 . 6 3 . 1
no buffer to keep pinned count 1 , 154 , 903 1 , 114 . 8 154 . 0
no work - consistent read gets 23 , 064 , 176 22 , 262 . 7 3 , 074 . 8
opened cursors cumulative 482 , 205 465 . 5 64 . 3
opened cursors current
parse count (hard) 81 , 522 78 . 7 10 . 9
parse count (total) 486 , 474 469 . 6 64 . 9
parse time cpu 16 , 276 15 . 7 2 . 2
parse time elapsed 28 , 546 27 . 6 3 . 8
physical reads 91 , 876 88 . 7 12 . 3
physical reads direct 11 , 517 11 . 1 1 . 5
physical writes 69 , 253 66 . 9 9 . 2
physical writes direct 20 , 608 19 . 9 2 . 8
physical writes non checkpoint 33 , 222 32 . 1 4 . 4
pinned buffers inspected 0 0 . 0 0 . 0
prefetched blocks 42 , 485 41 . 0 5 . 7
prefetched blocks aged out before 0 0 . 0 0 . 0
process last non-idle time 68 , 282 , 667 , 985 ############ 9 , 103 , 142 . 0
queries parallelized 0 0 . 0 0 . 0
recursive calls 6 , 935 , 229 6 , 694 . 2 924 . 6
recursive cpu usage 239 , 922 231 . 6 32 . 0
redo blocks written 80 , 048 77 . 3 10 . 7
redo buffer allocation retries 1 0 . 0 0 . 0
redo entries 249 , 570 240 . 9 33 . 3
redo log space requests 1 0 . 0 0 . 0
redo log space wait time 32 0 . 0 0 . 0
redo ordering marks 0 0 . 0 0 . 0
redo size 74 , 103 , 064 71 , 528 . 1 9 , 879 . 1
redo synch time 6 , 839 6 . 6 0 . 9
redo synch writes 2 , 790 2 . 7 0 . 4
redo wastage 6 , 584 , 648 6 , 355 . 8 877 . 8
redo write time 20 , 100 19 . 4 2 . 7
redo writer latching time 6 0 . 0 0 . 0
redo writes 12 , 694 12 . 3 1 . 7
rollback changes - undo records a 583 0 . 6 0 . 1
rollbacks only - consistent read 729 0 . 7 0 . 1
rows fetched via callback 2 , 204 , 306 2 , 127 . 7 293 . 9
session connect time 81 , 167 , 569 , 873 ############ ############
session logical reads 37 , 472 , 914 36 , 170 . 8 4 , 995 . 7
session pga memory 84 , 762 , 912 81 , 817 . 5 11 , 300 . 2
session pga memory max 84 , 688 , 584 81 , 745 . 7 11 , 290 . 3
session uga memory max 39 , 747 , 160 38 , 366 . 0 5 , 298 . 9
Instance Activity Stats for DB: XXXTLS Instance: kztl Snaps: 1 - 2
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
sorts (disk) 7 0 . 0 0 . 0
sorts (memory) 120 , 872 116 . 7 16 . 1
sorts (rows) 2 , 647 , 723 2 , 555 . 7 353 . 0
summed dirty queue length 26 0 . 0 0 . 0
switch current to new buffer
table fetch by rowid 11 , 514 , 254 11 , 114 . 1 1 , 535 . 0
table fetch continued row 19 , 399 18 . 7 2 . 6
table scan blocks gotten 19 , 022 , 385 18 , 361 . 4 2 , 536 . 0
table scan rows gotten 650 , 873 , 063 628 , 255 . 9 86 , 771 . 5
table scans (direct read) 0 0 . 0 0 . 0
table scans (long tables) 5 0 . 0 0 . 0
table scans (rowid ranges) 0 0 . 0 0 . 0
table scans (short tables) 895 , 032 863 . 9 119 . 3
total file opens 202 0 . 2 0 . 0
transaction rollbacks 101 0 . 1 0 . 0
transaction tables consistent rea 8 0 . 0 0 . 0
transaction tables consistent rea 6 , 442 6 . 2 0 . 9
user calls 274 , 186 264 . 7 36 . 6
user commits 7 , 466 7 . 2 1 . 0
user rollbacks 35 0 . 0 0 . 0
write clones created in backgroun 17 0 . 0 0 . 0
write clones created in foregroun 7 , 262 7 . 0 1 . 0
-------------------------------------------------------------
Tablespace IO Stats for DB: XXXTLS Instance: kztl Snaps: 1 - 2
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
FASTCOM
32 , 431 31 5 . 6 2 . 3 17 , 085 16 7 1 . 4
FASTCOM_CDR_IDX
1 , 527 1 8 . 3 1 . 0 12 , 256 12 0 0 . 0
RBS
4 0 7 . 5 1 . 0 13 , 679 13 22 18 . 6
FASTCOM_CDR_DATA
3 , 096 3 2 . 7 1 . 0 4 , 010 4 0 0 . 0
TEMP
1 , 921 2 0 . 1 6 . 0 1 , 826 2 0 0 . 0
SYSTEM
706 1 7 . 4 1 . 9 848 1 0 0 . 0
USERS
84 0 3 . 3 1 . 0 659 1 0 0 . 0
DES_DATE
1 0 0 . 0 1 . 0 1 0 0 0 . 0
DES_INDX
1 0 0 . 0 1 . 0 1 0 0 0 . 0
INDX
1 0 0 . 0 1 . 0 1 0 0 0 . 0
TOOLS
1 0 0 . 0 1 . 0 1 0 0 0 . 0
-------------------------------------------------------------
File IO Stats for DB: XXXTLS Instance: kztl Snaps: 1 - 2
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
DES_DATE /home/oracle8i/ora8i/oradata/XXXantls/des_data.dbf
1 0 0 . 0 1 . 0 1 0 0
DES_INDX /home/oracle8i/ora8i/oradata/XXXantls/des_indx.dbf
1 0 0 . 0 1 . 0 1 0 0
FASTCOM /home/oracle/oradata/ASR/dbs6/fastcom03.dbf
6 , 467 6 4 . 0 3 . 5 2 , 632 3 0
/home/oracle8i/ora8i/oradata/XXXantls/fastcom.dbf
19 , 396 19 6 . 1 2 . 0 11 , 857 11 7 1 . 4
/home/oracle8i/ora8i/oradata/XXXantls/fastcom02.dbf
6 , 568 6 5 . 9 1 . 8 2 , 596 3 0
FASTCOM_CDR_DATA /home/oracle/oradata/ASR/dbs2/fastcom_cdr_data_4.dbf
100 0 5 . 4 1 . 0 1 0 0
/home/oracle/oradata/ASR/dbs7/fastcom_cdr_data_3.dbf
102 0 5 . 5 1 . 0 1 0 0
/home/oracle8i/ora8i/oradata/XXXantls/fastcom_cdr_da
ta_1.dbf
800 1 7 . 1 1 . 0 2 , 887 3 0
/home/oracle8i/ora8i/oradata/XXXantls/fastcom_cdr_da
ta_2.dbf
137 0 7 . 5 1 . 0 138 0 0
/home/oradata2/XXXantls/fastcom_cdr_data_5.dbf
1 , 957 2 0 . 2 1 . 0 983 1 0
FASTCOM_CDR_IDX /home/oracle/oradata/ASR/dbs8/fast_cdr_idx03.dbf
725 1 8 . 8 1 . 0 6 , 595 6 0
/home/oracle8i/ora8i/oradata/XXXantls/fast_cdr_idx01
.dbf
346 0 9 . 6 1 . 0 1 , 889 2 0
/home/oracle8i/ora8i/oradata/XXXantls/fast_cdr_idx02
.dbf
44 0 10 . 9 1 . 0 63 0 0
/home/oradata2/XXXantls/fast_cdr_idx04.dbf
412 0 6 . 1 1 . 0 3 , 709 4 0
INDX /home/oracle8i/ora8i/oradata/XXXantls/indx01.dbf
1 0 0 . 0 1 . 0 1 0 0
RBS /home/oracle8i/ora8i/oradata/XXXantls/fastcom_817_rb
s.dbf
2 0 5 . 0 1 . 0 999 1 1 20 . 0
/home/oracle8i/ora8i/oradata/XXXantls/rbs01.dbf
2 0 10 . 0 1 . 0 12 , 680 12 21 18 . 6
SYSTEM /home/oracle8i/ora8i/oradata/XXXantls/system01.dbf
706 1 7 . 4 1 . 9 848 1 0
TEMP /home/oracle8i/ora8i/oradata/XXXantls/temp01.dbf
1 , 921 2 0 . 1 6 . 0 1 , 826 2 0
File IO Stats for DB: XXXTLS Instance: kztl Snaps: 1 - 2
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
TOOLS /home/oracle8i/ora8i/oradata/XXXantls/tools01.dbf
1 0 0 . 0 1 . 0 1 0 0
USERS /home/oracle8i/ora8i/oradata/XXXantls/users01.dbf
84 0 3 . 3 1 . 0 659 1 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: XXXTLS Instance: kztl Snaps: 1 - 2
-> Pools D: default pool, K: keep pool, R: recycle pool
Free Write Buffer
Buffer Consistent Physical Physical Buffer Complete Busy
P Gets Gets Reads Writes Waits Waits Waits
- ----------- ------------- ----------- ---------- ------- -------- ----------
D 99 , 270 24 , 668 , 651 80 , 358 48 , 612 0 0 29
-------------------------------------------------------------
Buffer wait Statistics for DB: XXXTLS Instance: kztl Snaps: 1 - 2
-> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (cs) Time (cs)
------------------ ----------- ---------- ---------
undo header 17 39 2
undo block 5 2 0
data block 7 1 0
-------------------------------------------------------------
Rollback Segment Stats for DB: XXXTLS Instance: kztl Snaps: 1 - 2
->A high value for "Pct Waits" suggests more rollback segments may be required
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ ------------ ------- --------------- -------- -------- --------
0 4 . 0 0 . 00 0 0 0 0
1 404 . 0 0 . 00 394 , 356 0 0 0
2 9 , 612 . 0 0 . 00 3 , 617 , 350 1 0 0
3 15 , 823 . 0 0 . 05 21 , 958 , 784 0 0 0
4 3 , 985 . 0 0 . 00 1 , 491 , 924 0 0 0
5 252 . 0 0 . 00 98 , 284 0 0 0
6 2 , 408 . 0 0 . 00 920 , 124 0 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: XXXTLS Instance: kztl Snaps: 1 - 2
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 122 , 880 0 122 , 880
1 377 , 483 , 264 46 , 431 , 126 377 , 483 , 264
2 314 , 568 , 704 97 , 103 , 430 314 , 568 , 704
3 629 , 141 , 504 111 , 736 , 620 629 , 141 , 504
4 62 , 910 , 464 6 , 350 , 438 62 , 910 , 464
5 131 , 067 , 904 22 , 791 , 183 131 , 067 , 904
6 183 , 496 , 704 11 , 239 , 499 183 , 496 , 704
-------------------------------------------------------------
Latch Activity for DB: XXXTLS Instance: kztl Snaps: 1 - 2
-> "Get Requests" , "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
-> "NoWait Requests" , "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0 . 0
Pct Avg Pct
Get Get Slps NoWait NoWait
Latch Name Requests Miss /Miss Requests Miss
----------------------------- -------------- ------ ------ ------------ ------
Token Manager 156 0 . 0 13 , 785 0 . 0
active checkpoint queue latch 11 , 998 0 . 0 0 . 3 0
cache buffer handles 238 , 138 0 . 0 0 . 2 0
cache buffers chains 67 , 722 , 384 0 . 1 0 . 0 144 , 108 0 . 0
cache buffers lru chain 124 , 199 0 . 0 0 . 3 91 , 267 0 . 1
channel handle pool latch 168 0 . 0 0
channel operations parent lat 245 0 . 0 0
checkpoint queue latch 209 , 068 0 . 0 0 . 2 0
dml lock allocation 163 , 149 0 . 0 0 . 2 0
enqueue hash chains 348 , 025 0 . 1 0 . 2 0
enqueues 520 , 932 0 . 1 0 . 1 0
error message lists 21 0 . 0 0
event group latch 77 0 . 0 0
job_queue_processes parameter 57 0 . 0 0
ktm global data 3 0 . 0 0
latch wait list 150 , 604 0 . 7 0 . 1 154 , 090 0 . 2
library cache 18 , 949 , 054 1 . 8 0 . 6 229 , 015 8 . 1
library cache load lock 19 , 072 0 . 0 0 . 0 0
list of block allocation 24 , 333 0 . 0 0
loader state object freelist 99 0 . 0 0
longop free list 33 0 . 0 0
messages 73 , 935 0 . 0 0 . 3 0
mostly latch-free SCN 24 , 802 0 . 0 0 . 0 0
multiblock read objects 13 , 186 0 . 0 0 . 0 0
ncodef allocation latch 57 0 . 0 0
parallel query alloc buffer 125 7 . 2 0 . 0 0
parallel query stats 12 16 . 7 0 . 5 0
process allocation 77 0 . 0 77 0 . 0
process group creation 168 0 . 0 0
process queue 9 0 . 0 0
process queue reference 41 , 917 0 . 0 21 0 . 0
query server freelists 80 1 . 3 0 . 0 0
redo allocation 274 , 960 0 . 0 0 . 1 0
redo writing 60 , 587 0 . 0 0 . 1 0
row cache objects 1 , 255 , 134 0 . 1 0 . 2 27 , 438 0 . 4
sequence cache 76 , 051 0 . 0 0 . 0 0
session allocation 368 , 415 0 . 0 0 . 0 0
session idle bit 622 , 715 0 . 0 0 . 0 0
session switching 57 0 . 0 0
shared pool 5 , 714 , 781 0 . 9 0 . 8 0
sort extent pool 439 0 . 0 0
temporary table state object 8 0 . 0 0
transaction allocation 157 , 168 0 . 0 0 . 1 0
transaction branch allocation 57 0 . 0 0
undo global data 48 , 940 0 . 0 0 . 0 0
user lock 3 , 498 0 . 0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: XXXTLS Instance: kztl Snaps: 1 - 2
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1 -> 4
-------------------------- -------------- ----------- ----------- ------------
library cache 18 , 949 , 054 333 , 634 213 , 670 208682 / 48183
/ 66859 / 9910 /
0
cache buffers chains 67 , 722 , 384 60 , 393 2 , 236 58274 / 2014 / 9
5 / 10 / 0
shared pool 5 , 714 , 781 53 , 080 42 , 771 32415 / 3458 / 1
3228 / 3979 / 0
row cache objects 1 , 255 , 134 1 , 265 276 992 / 270 / 3 / 0 /
0
latch wait list 150 , 604 1 , 036 93 958 / 64 / 13 / 1 /
0
enqueues 520 , 932 321 41 284 / 34 / 2 / 1 / 0
enqueue hash chains 348 , 025 253 60 196 / 54 / 3 / 0 / 0
session allocation 368 , 415 87 4 83 / 4 / 0 / 0 / 0
redo allocation 274 , 960 57 6 51 / 6 / 0 / 0 / 0
checkpoint queue latch 209 , 068 55 12 43 / 12 / 0 / 0 / 0
dml lock allocation 163 , 149 38 6 32 / 6 / 0 / 0 / 0
transaction allocation 157 , 168 32 3 29 / 3 / 0 / 0 / 0
cache buffers lru chain 124 , 199 29 10 19 / 10 / 0 / 0 / 0
messages 73 , 935 26 7 20 / 5 / 1 / 0 / 0
redo writing 60 , 587 23 3 20 / 3 / 0 / 0 / 0
cache buffer handles 238 , 138 5 1 4 / 1 / 0 / 0 / 0
active checkpoint queue la 11 , 998 4 1 3 / 1 / 0 / 0 / 0
parallel query stats 12 2 1 1 / 1 / 0 / 0 / 0
-------------------------------------------------------------
Latch Miss Sources for DB: XXXTLS Instance: kztl Snaps: 1 - 2
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- -------
active checkpoint queue kcbbacq: scan active check 0 1 1
cache buffer handles kcbzfs 0 1 0
cache buffers chains kcbgtcr: kslbegin 0 1 , 887 1 , 685
cache buffers chains kcbrls: kslbegin 0 180 394
cache buffers chains kcbgcur: kslbegin 0 116 103
cache buffers chains kcbget: pin buffer 0 19 9
cache buffers chains kcbchg: kslbegin: bufs not 0 10 9
cache buffers chains kcbbxsv 0 6 6
cache buffers chains kcbbwb1 0 3 10
cache buffers chains kcbzib: finish free bufs 0 3 1
cache buffers chains kcbchg: kslbegin: call CR 0 2 4
cache buffers chains kcbibr 0 2 3
cache buffers chains kcbzib: multi-block read: 0 2 0
cache buffers chains kcbzgb: scan from tail. no 0 2 0
cache buffers chains kcbget: exchange 0 2 0
cache buffers chains kcbget: exchange rls 0 2 2
cache buffers lru chain kcbzgb: multiple sets nowa 0 5 0
cache buffers lru chain kcbzgb: posted for free bu 0 4 0
cache buffers lru chain kcbbiop: lru scan 0 1 0
checkpoint queue latch kcbbwthc: thread checkpoin 0 8 0
checkpoint queue latch kcbk0rrd: update recovery 0 2 0
checkpoint queue latch kcbbxsv: move to being wri 0 1 0
checkpoint queue latch kcbklbc: Link buffer into 0 1 2
dml lock allocation ktaiam 0 3 4
dml lock allocation ktaidm 0 3 2
enqueue hash chains ksqgtl3 0 49 39
enqueue hash chains ksqrcl 0 11 21
enqueues ksqgtl2 0 19 8
enqueues ksqgel: create enqueue 0 14 6
enqueues ksqrcl 0 4 3
enqueues ksqdel 0 3 0
enqueues ksqies 0 1 24
latch wait list kslfre 109 75 90
latch wait list kslges 151 18 3
library cache kglhdgn: child: 0 50 , 157 13 , 249
library cache kgllkdl: child: cleanup 0 37 , 906 3 , 073
library cache kglpnal: child: alloc spac 0 11 , 040 9 , 277
library cache kglupc: child 0 8 , 411 35 , 357
library cache kglpnal: child: before pro 0 7 , 420 25 , 034
library cache kglpnc: child 0 6 , 093 44 , 570
library cache kglhdgc: child: 0 5 , 532 2 , 373
library cache kglpnp: child 0 4 , 782 33 , 210
library cache kgldti: 2child 0 4 , 401 1 , 040
library cache kglget: child: KGLDSBRD 0 3 , 057 3 , 468
library cache kglpin 0 1 , 386 3 , 167
library cache kglget: child: KGLDSBYD 0 691 4 , 757
library cache kgllkdl: child: free pin 0 582 11 , 456
library cache kglic 0 523 233
library cache kglnti 0 364 52
library cache kglati 0 240 29
library cache kglobpn: child: 0 194 229
library cache kglobld: child: 0 41 1 , 172
library cache kgldte: child 0 0 19 1 , 021
Latch Miss Sources for DB: XXXTLS Instance: kztl Snaps: 1 - 2
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- -------
library cache kgldtld: 2child 0 18 16
library cache kgldnp: child 0 15 691
library cache kglrtl 0 14 4
library cache kglsca: parent 0 8 0
library cache kglini: child 0 7 78
messages ksarcv: after wait 0 4 0
messages ksarcv 0 2 0
messages ksaamb: after wakeup 0 1 7
parallel query stats kxfprst: KSLBEGIN 0 1 1
redo allocation kcrfwi: before write 0 3 0
redo allocation kcrfwr: redo allocation 0 2 6
redo allocation kcrfwi: more space 0 1 0
redo writing kcrfsr 0 2 1
redo writing kcrfss 0 1 1
row cache objects kqrpre: find obj 0 201 109
row cache objects kqreqd 0 4 115
row cache objects kqreqd: rel enqueue 0 2 15
row cache objects kqrpup 0 2 4
session allocation ksucri 0 3 3
session allocation ksuxds: KSUSFCLC not set 0 1 0
shared pool kghfrunp: clatch: nowait 0 65 , 170 0
shared pool kghfrunp: alloc: clatch no 0 36 , 165 0
shared pool kghfrunp: clatch: wait 0 5 , 621 20 , 093
shared pool kghalo 0 2 , 596 7 , 001
shared pool kghfrunp: alloc: wait 0 1 , 659 215
shared pool kghfnd: min scan 0 775 0
shared pool kghfnd: req scan 0 374 0
shared pool kghfre 0 307 846
shared pool kghupr1 0 288 32 , 061
shared pool kghfnd: get next extent 0 275 0
shared pool kghalp 0 222 1 , 947
shared pool kghfen: not perm alloc cla 0 78 479
shared pool kghfru 0 7 203
shared pool kghfrunp: no latch 0 3 0
shared pool kghfrunp: batch min scan 0 2 0
transaction allocation ktcdso 0 3 1
-------------------------------------------------------------
Dictionary Cache Stats for DB: XXXTLS Instance: kztl Snaps: 1 - 2
-> "Pct Misses" should be very low (< 2 % in most cases)
-> "Cache Usage" is the number of cache entries being used
-> "Pct SGA" is the ratio of usage to allocated size for that cache
Get Pct Scan Pct Mod Final Pct
Cache Requests Miss Requests Miss Req Usage SGA
---------------------- ------------ ------ -------- ----- -------- ------ ----
dc_constraints 0 0 0 182 97
dc_database_links 0 0 0 0 0
dc_files 0 0 0 10 91
dc_free_extents 1 , 509 0 . 1 1 0 . 0 3 504 99
dc_global_oids 0 0 0 3 21
dc_histogram_data 0 0 0 0 0
dc_histogram_data_valu 0 0 0 0 0
dc_histogram_defs 21 , 431 2 . 2 0 0 1 , 450 100
dc_object_ids 271 , 243 0 . 1 0 0 955 99
dc_objects 12 , 487 3 . 9 0 0 1 , 388 100
dc_outlines 0 0 0 0 0
dc_profiles 77 0 . 0 0 0 2 40
dc_rollback_segments 50 0 . 0 0 0 8 62
dc_segments 30 , 539 0 . 6 0 1 893 99
dc_sequence_grants 21 9 . 5 0 0 15 22
dc_sequences 3 , 218 0 . 1 0 3 , 054 42 88
dc_synonyms 1 , 888 14 . 1 0 0 513 99
dc_tablespace_quotas 4 0 . 0 0 1 3 23
dc_tablespaces 700 0 . 0 0 0 15 75
dc_used_extents 1 100 . 0 0 1 57 84
dc_user_grants 1 , 734 0 . 1 0 0 27 44
dc_usernames 4 , 463 0 . 0 0 0 44 80
dc_users 66 , 542 0 . 0 0 0 49 89
ifs_acl_cache_entries 0 0 0 0 0
-------------------------------------------------------------
Library Cache Activity for DB: XXXTLS Instance: kztl Snaps: 1 - 2
-> "Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 63 , 610 0 . 0 63 , 376 0 . 0 1 0
CLUSTER 1 , 057 0 . 0 1 , 477 0 . 0 0 0
INDEX 0 0 0 0
OBJECT 0 0 0 0
PIPE 1 , 054 0 . 1 1 , 054 0 . 1 0 0
SQL AREA 363 , 975 19 . 1 3 , 909 , 337 3 . 9 11 , 919 7
TABLE/PROCEDURE 735 , 722 0 . 1 2 , 187 , 595 0 . 4 6 , 296 0
TRIGGER 679 2 . 9 679 35 . 3 220 0
-------------------------------------------------------------
SGA Memory Summary for DB: XXXTLS Instance: kztl Snaps: 1 - 2
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 491 , 520 , 000
Fixed Size 104 , 936
Redo Buffers 532 , 480
Variable Size 156 , 180 , 480
----------------
sum 648 , 337 , 896
-------------------------------------------------------------
SGA breakdown difference for DB: XXXTLS Instance: kztl Snaps: 1 - 2
Pool Name Begin value End value Difference
----------- ------------------------ -------------- -------------- -----------
java pool free memory 32 , 768 32 , 768 0
large pool PX msg pool 1 , 966 , 104 1 , 966 , 104 0
large pool free memory 1 , 179 , 624 1 , 179 , 624 0
shared pool DML locks 165 , 984 165 , 984 0
shared pool KGFF heap 17 , 136 17 , 136 0
shared pool KGK heap 8 , 152 8 , 152 0
shared pool KQLS heap 1 , 367 , 152 1 , 241 , 680 - 125 , 472
shared pool PL/SQL DIANA 27 , 369 , 912 27 , 143 , 552 - 226 , 360
shared pool PL/SQL MPCODE 20 , 035 , 432 19 , 928 , 592 - 106 , 840
shared pool PLS non-lib hp 2 , 136 2 , 136 0
shared pool PX subheap 95 , 232 95 , 232 0
shared pool State objects 447 , 680 447 , 680 0
shared pool db_block_buffers 24 , 960 , 000 24 , 960 , 000 0
shared pool db_block_hash_buckets 4 , 044 , 912 4 , 044 , 912 0
shared pool db_handles 176 , 000 176 , 000 0
shared pool dictionary cache 2 , 537 , 552 3 , 476 , 776 939 , 224
shared pool event statistics per ses 781 , 200 781 , 200 0
shared pool fixed allocation callbac 1 , 904 1 , 904 0
shared pool free memory 22 , 348 , 432 21 , 715 , 968 - 632 , 464
shared pool ktlbk state objects 132 , 392 132 , 392 0
shared pool library cache 35 , 560 , 584 36 , 283 , 224 722 , 640
shared pool message pool freequeue 191 , 192 191 , 192 0
shared pool miscellaneous 2 , 920 , 512 2 , 801 , 760 - 118 , 752
shared pool processes 214 , 400 214 , 400 0
shared pool sessions 547 , 200 547 , 200 0
shared pool sql area 8 , 449 , 112 8 , 049 , 032 - 400 , 080
shared pool table columns 25 , 384 25 , 440 56
shared pool table definiti 5 , 944 1 , 520 - 4 , 424
shared pool temporary tabl 170 , 472 153 , 496 - 16 , 976
shared pool transactions 375 , 440 375 , 440 0
db_block_buffers 491 , 520 , 000 491 , 520 , 000 0
fixed_sga 104 , 936 104 , 936 0
log_buffer 524 , 288 524 , 288 0
-------------------------------------------------------------
init.ora Parameters for DB: XXXTLS Instance: kztl Snaps: 1 - 2
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
background_dump_dest /home/oracle8i/ora8i/admin/XXXant
compatible 8 . 1 . 7
control_files /home/oracle/oradata/ASR/dbs1/con
core_dump_dest /home/oracle8i/ora8i/admin/XXXant
db_block_buffers 120000
db_block_lru_latches 4
db_block_size 4096
db_domain telecet.ru
db_file_multiblock_read_count 16
db_name XXXTLS
db_writer_processes 4
instance_name kztl
java_pool_size 32768
job_queue_interval 300
job_queue_processes 3
large_pool_size 3145728
log_buffer 524288
log_checkpoint_interval 68300
log_checkpoint_timeout 1800
max_enabled_roles 50
nls_date_format dd.mm.yyyy
open_cursors 600
parallel_automatic_tuning TRUE
parallel_min_servers 2
processes 200
rollback_segments BIG, SUPERBIG, RBS_1, RBS_2, RBS_
shared_pool_size 120000000
sort_area_retained_size 65536
sort_area_size 1048576
timed_statistics TRUE
user_dump_dest /home/oracle8i/ora8i/admin/XXXant
utl_file_dir /home/oracle8i/ora8i/admin/XXXant
-------------------------------------------------------------
End of Report
