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
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
|
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
3831552662 inforh 1 21-Févr.-16 04: 11.2.0.3.0 NO
31
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
VSRVINFORH Microsoft Windows x86 2 2 1 4.0
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 32974 24-Févr.-16 12:59:5 32 2.3
2
End Snap: 32975 24-Févr.-16 13:05:1 30 2.4
1
Elapsed: 5.32 (mins) Av Act Sess: 0.8
DB time: 4.24 (mins) DB CPU: 3.45 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 1,408M Std Block Size: 8K
Shared Pool: 512M Log Buffer: 18,176K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.8 5.5 0.23 0.42
DB CPU(s): 0.7 4.5 0.19 0.34
Redo size: 7,363.1 51,061.7
Logical reads: 555.7 3,853.5
Block changes: 19.7 136.7
Physical reads: 487.8 3,382.8
Physical writes: 1.9 13.4
User calls: 1.9 13.2
Parses: 1.7 11.5
Hard parses: 0.0 0.0
W/A MB processed: 0.2 1.5
Logons: 0.1 0.8
Executes: 3.5 24.0
Rollbacks: 0.0 0.0
Transactions: 0.1
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 12.22 Optimal W/A Exec %: 100.00
Library Hit %: 99.59 Soft Parse %: 99.62
Execute to Parse %: 51.99 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 18.52 % Non-Parse CPU: 99.98
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 81.75 81.74
% SQL with executions>1: 94.18 94.13
% Memory for SQL w/exec>1: 97.62 97.61
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 208 81.3
db file scattered read 1,257 44 35 17.4
db file sequential read 178 1 7 .5
control file sequential read 1,452 1 1 .4
control file parallel write 119 0 2 .1
-------------------------------------------------------------
Host CPU (CPUs: 2 Cores: 2 Sockets: 1)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
6.36 28.14 65.51
Instance CPU
~~~~~~~~~~~~ % Time (seconds)
-------- --------------
Host: Total time (s): 638.9
Host: Busy CPU time (s): 220.4
% of time Host is Busy: 34.5
Instance: Total CPU time (s): 207.4
% of Busy CPU used for Instance: 94.1
Instance: Total Database time (s): 275.6
%DB time waiting for CPU (Resource Mgr): 0.0
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 4,095.6 4,095.6
SGA use (MB): 2,548.6 2,548.6
PGA use (MB): 241.8 238.7
% Host Mem used for SGA+PGA: 68.1 68.1
-------------------------------------------------------------
Time Model System Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 253.4 99.7
DB CPU 207.0 81.5
parse time elapsed 0.2 .1
PL/SQL execution elapsed time 0.1 .1
connection management call elapsed 0.1 .0
failed parse elapsed time 0.1 .0
hard parse elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 254.1
background elapsed time 21.5
background cpu time 0.3
-------------------------------------------------------------
Foreground Wait Events DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
db file scattered read 1,257 0 44 35 27.3 17.4
db file sequential read 145 0 1 7 3.2 .4
control file sequential read 1,163 0 1 1 25.3 .3
log file sync 30 0 0 2 0.7 .0
Disk file operations I/O 11 0 0 1 0.2 .0
SQL*Net message from client 386 0 371 960 8.4
jobq slave wait 714 99 367 514 15.5
Streams AQ: waiting for mess 64 100 320 5007 1.4
SQL*Net message to client 386 0 0 0 8.4
-------------------------------------------------------------
Background Wait Events DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
control file sequential read 289 0 0 1 6.3 .1
db file sequential read 33 0 0 7 0.7 .1
control file parallel write 119 0 0 2 2.6 .1
os thread startup 13 0 0 13 0.3 .1
log file parallel write 77 0 0 2 1.7 .1
db file parallel write 47 0 0 3 1.0 .0
Disk file operations I/O 12 0 0 4 0.3 .0
direct path read 4 0 0 5 0.1 .0
latch: messages 1 0 0 10 0.0 .0
db file async I/O submit 8 0 0 1 0.2 .0
log file sync 1 0 0 3 0.0 .0
direct path write 1 0 0 1 0.0 .0
rdbms ipc message 1,422 94 6,542 4600 30.9
DIAG idle wait 628 100 637 1014 13.7
shared server idle wait 11 100 330 30014 0.2
pmon timer 105 101 319 3041 2.3
Space Manager: slave idle wa 64 98 316 4942 1.4
Streams AQ: qmn coordinator 22 50 308 14008 0.5
Streams AQ: qmn slave idle w 11 0 308 28016 0.2
dispatcher timer 5 100 300 60019 0.1
smon timer 1 0 49 49209 0.0
SQL*Net message from client 60 0 1 14 1.3
-------------------------------------------------------------
Wait Events (fg and bg) DB/Inst: INFORH/inforh Snaps: 32974-32975
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
db file scattered read 1,257 0 44 35 27.3 17.4
db file sequential read 178 0 1 7 3.9 .5
control file sequential read 1,452 0 1 1 31.6 .4
control file parallel write 119 0 0 2 2.6 .1
os thread startup 13 0 0 13 0.3 .1
log file parallel write 77 0 0 2 1.7 .1
db file parallel write 47 0 0 3 1.0 .0
log file sync 31 0 0 2 0.7 .0
Disk file operations I/O 23 0 0 3 0.5 .0
direct path read 4 0 0 5 0.1 .0
latch: messages 1 0 0 10 0.0 .0
db file async I/O submit 8 0 0 1 0.2 .0
SQL*Net break/reset to clien 4 0 0 0 0.1 .0
direct path write 1 0 0 1 0.0 .0
rdbms ipc message 1,422 94 6,542 4600 30.9
DIAG idle wait 628 100 637 1014 13.7
SQL*Net message from client 446 0 371 833 9.7
jobq slave wait 714 99 367 514 15.5
shared server idle wait 11 100 330 30014 0.2
Streams AQ: waiting for mess 64 100 320 5007 1.4
pmon timer 105 101 319 3041 2.3
Space Manager: slave idle wa 64 98 316 4942 1.4
Streams AQ: qmn coordinator 22 50 308 14008 0.5
Streams AQ: qmn slave idle w 11 0 308 28016 0.2
dispatcher timer 5 100 300 60019 0.1
smon timer 1 0 49 49209 0.0
SQL*Net message to client 431 0 0 0 9.4
-------------------------------------------------------------
Wait Event Histogram DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
ADR block file read 2 100.0
asynch descriptor resize 28 100.0
control file parallel writ 119 12.6 77.3 6.7 .8 .8 1.7
control file sequential re 1452 93.2 3.2 1.3 1.4 .6 .2 .1
db file async I/O submit 8 75.0 12.5 12.5
db file parallel write 47 63.8 17.0 8.5 2.1 4.3 2.1 2.1
db file scattered read 1257 .7 .3 .2 1.4 2.9 59.4 35.0
db file sequential read 188 14.9 1.6 13.3 39.4 24.5 6.4
direct path read 4 50.0 50.0
direct path write 1 100.0
Disk file operations I/O 23 91.3 4.3 4.3
latch: messages 1 100.0
LGWR wait for redo copy 1 100.0
log file parallel write 77 68.8 15.6 3.9 9.1 1.3 1.3
log file sync 31 58.1 22.6 6.5 9.7 3.2
os thread startup 13 15.4 69.2 15.4
SQL*Net break/reset to cli 4 100.0
class slave wait 3 100.0
DIAG idle wait 630 100.0
dispatcher timer 5 100.0
jobq slave wait 714 100.0
pmon timer 106 100.0
rdbms ipc message 1422 .5 .1 .5 .4 1.1 .6 24.4 72.4
shared server idle wait 11 100.0
smon timer 1 100.0
Space Manager: slave idle 64 1.6 98.4
SQL*Net message from clien 446 55.4 23.5 11.0 2.7 .7 2.0 2.2 2.5
SQL*Net message to client 431 100.0
SQL*Net more data from cli 1 100.0
Streams AQ: qmn coordinato 22 50.0 50.0
Streams AQ: qmn slave idle 11 100.0
Streams AQ: waiting for me 64 100.0
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Total DB CPU (s): 207
-> Captured SQL accounts for 99.2% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
201.43 1 201.43 97.3 246.32 155,462 1755199020
Module: SQL*Plus
select count(*) from refhr.rh_zx8k
-------------------------------------------------------------
SQL ordered by Elapsed time for DB: INFORH Instance: inforh Snaps: 32974 -3297
-> Total DB Time (s): 254
-> Captured SQL accounts for 100.8% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
246.32 1 246.32 96.9 201.43 155,417 1755199020
Module: SQL*Plus
select count(*) from refhr.rh_zx8k
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: INFORH/inforh Snaps: 32974-32975
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 177,259
-> Captured SQL accounts for 100.2% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
155,462 1 155,462.0 87.7 201.43 246.32 1755199020
Module: SQL*Plus
select count(*) from refhr.rh_zx8k
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: INFORH/inforh Snaps: 32974-32975
-> End Disk Reads Threshold: 1000 Total Disk Reads: 155,607
-> Captured SQL accounts for 100.0% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd
Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
155,417 1 155,417.0 99.9 201.43 246.32 1755199020
Module: SQL*Plus
select count(*) from refhr.rh_zx8k
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: INFORH/inforh Snaps: 32974-32975
-> End Executions Threshold: 100 Total Executions: 1,106
-> Captured SQL accounts for 107.8% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: INFORH/inforh Snaps: 32974-32975
-> End Parse Calls Threshold: 1000 Total Parse Calls: 531
-> Captured SQL accounts for 93.6% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
-------------------------------------------------------------
truncate table STATS$TEMP_SQLSTATS
*
ERREUR à la ligne 1 :
ORA-00942: Table ou vue inexistante
Instance Activity Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
active txn count during cleanout 113 0.4 2.5
application wait time 0 0.0 0.0
background checkpoints completed 1 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 1,325 4.2 28.8
Block Cleanout Optim referenced 12 0.0 0.3
branch node splits 1 0.0 0.0
buffer is not pinned count 4,694 14.7 102.0
buffer is pinned count 1,999 6.3 43.5
bytes received via SQL*Net from c 163,683 513.1 3,558.3
bytes sent via SQL*Net to client 139,869 438.5 3,040.6
Cached Commit SCN referenced 0 0.0 0.0
calls to get snapshot scn: kcmgss 1,477 4.6 32.1
calls to kcmgas 424 1.3 9.2
calls to kcmgcs 851 2.7 18.5
CCursor + sql area evicted 0 0.0 0.0
cell physical IO interconnect byt 1,309,904,896 4,106,284.9 ############
change write time 1 0.0 0.0
cleanout - number of ktugct calls 115 0.4 2.5
cleanouts and rollbacks - consist 0 0.0 0.0
cleanouts only - consistent read 0 0.0 0.0
cluster key scan block gets 831 2.6 18.1
cluster key scans 675 2.1 14.7
commit batch performed 0 0.0 0.0
commit batch requested 0 0.0 0.0
commit batch/immediate performed 5 0.0 0.1
commit batch/immediate requested 5 0.0 0.1
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 9 0.0 0.2
commit cleanout failures: cannot 0 0.0 0.0
commit cleanouts 702 2.2 15.3
commit cleanouts successfully com 693 2.2 15.1
commit immediate performed 5 0.0 0.1
commit immediate requested 5 0.0 0.1
Commit SCN cached 0 0.0 0.0
commit txn count during cleanout 44 0.1 1.0
concurrency wait time 16 0.1 0.4
consistent changes 12 0.0 0.3
consistent gets 170,354 534.0 3,703.4
consistent gets - examination 1,486 4.7 32.3
consistent gets direct 0 0.0 0.0
consistent gets from cache 170,354 534.0 3,703.4
consistent gets from cache (fastp 167,068 523.7 3,631.9
CPU used by this session 20,757 65.1 451.2
CPU used when call started 20,724 65.0 450.5
CR blocks created 9 0.0 0.2
cursor authentications 1 0.0 0.0
data blocks consistent reads - un 12 0.0 0.3
db block changes 6,286 19.7 136.7
db block gets 6,905 21.7 150.1
db block gets direct 0 0.0 0.0
db block gets from cache 6,905 21.7 150.1
db block gets from cache (fastpat 1,992 6.2 43.3
DB time 97,893 306.9 2,128.1
DBWR checkpoint buffers written 7 0.0 0.2
Instance Activity Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
DBWR checkpoints 0 0.0 0.0
DBWR object drop buffers written 0 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR tablespace checkpoint buffer 0 0.0 0.0
DBWR thread checkpoint buffers wr 7 0.0 0.2
DBWR transaction table writes 1 0.0 0.0
DBWR undo block writes 241 0.8 5.2
deferred (CURRENT) block cleanout 239 0.8 5.2
dirty buffers inspected 376 1.2 8.2
Effective IO time 0 0.0 0.0
enqueue conversions 65 0.2 1.4
enqueue releases 3,655 11.5 79.5
enqueue requests 3,655 11.5 79.5
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 1,106 3.5 24.0
failed probes on index block recl 0 0.0 0.0
file io service time 0 0.0 0.0
file io wait time 51,887,905 162,658.0 1,127,997.9
free buffer inspected 156,436 490.4 3,400.8
free buffer requested 155,850 488.6 3,388.0
heap block compress 54 0.2 1.2
Heap Segment Array Inserts 148 0.5 3.2
Heap Segment Array Updates 2 0.0 0.0
hot buffers moved to head of LRU 44,220 138.6 961.3
HSC Heap Segment Block Changes 438 1.4 9.5
immediate (CR) block cleanout app 0 0.0 0.0
immediate (CURRENT) block cleanou 453 1.4 9.9
IMU commits 40 0.1 0.9
IMU contention 0 0.0 0.0
IMU CR rollbacks 0 0.0 0.0
IMU- failed to get a private stra 0 0.0 0.0
IMU Flushes 78 0.2 1.7
IMU ktichg flush 0 0.0 0.0
IMU pool not allocated 0 0.0 0.0
IMU recursive-transaction flush 0 0.0 0.0
IMU Redo allocation size 79,676 249.8 1,732.1
IMU undo allocation size 233,296 731.3 5,071.7
in call idle wait time 916,812 2,874.0 19,930.7
index crx upgrade (positioned) 189 0.6 4.1
index crx upgrade (prefetch) 1 0.0 0.0
index fast full scans (full) 15 0.1 0.3
index fetch by key 2,050 6.4 44.6
index scans kdiixs1 1,750 5.5 38.0
leaf node splits 45 0.1 1.0
leaf node 90-10 splits 9 0.0 0.2
lob reads 0 0.0 0.0
LOB table id lookup cache misses 0 0.0 0.0
lob writes 0 0.0 0.0
lob writes unaligned 0 0.0 0.0
logical read bytes from cache 1,452,105,728 4,552,055.6 ############
logons cumulative 35 0.1 0.8
max cf enq hold time 0 0.0 0.0
messages received 134 0.4 2.9
messages sent 134 0.4 2.9
min active SCN optimization appli 0 0.0 0.0
Instance Activity Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 167,866 526.2 3,649.3
non-idle wait count 3,658 11.5 79.5
non-idle wait time 4,781 15.0 103.9
Number of read IOs issued 0 0.0 0.0
opened cursors cumulative 1,049 3.3 22.8
parse count (describe) 0 0.0 0.0
parse count (failures) 1 0.0 0.0
parse count (hard) 2 0.0 0.0
parse count (total) 531 1.7 11.5
parse time cpu 5 0.0 0.1
parse time elapsed 27 0.1 0.6
physical read bytes 1,274,732,544 3,996,026.8 ############
physical read IO requests 1,452 4.6 31.6
physical read total bytes 1,298,522,112 4,070,602.2 ############
physical read total IO requests 2,904 9.1 63.1
physical read total multi block r 1,238 3.9 26.9
physical reads 155,607 487.8 3,382.8
physical reads cache 155,601 487.8 3,382.6
physical reads cache prefetch 154,155 483.2 3,351.2
physical reads direct 6 0.0 0.1
physical reads direct (lob) 0 0.0 0.0
physical reads direct temporary t 0 0.0 0.0
physical reads prefetch warmup 0 0.0 0.0
physical write bytes 5,029,888 15,767.7 109,345.4
physical write IO requests 282 0.9 6.1
physical write total bytes 11,382,784 35,682.7 247,451.8
physical write total IO requests 650 2.0 14.1
physical write total multi block 15 0.1 0.3
physical writes 614 1.9 13.4
physical writes direct 6 0.0 0.1
physical writes direct (lob) 0 0.0 0.0
physical writes direct temporary 0 0.0 0.0
physical writes from cache 608 1.9 13.2
physical writes non checkpoint 520 1.6 11.3
pinned buffers inspected 0 0.0 0.0
pinned cursors current 0 0.0 0.0
prefetch clients - default 0 0.0 0.0
prefetch warmup blocks aged out b 0 0.0 0.0
prefetched blocks aged out before 0 0.0 0.0
process last non-idle time 0 0.0 0.0
recursive calls 8,509 26.7 185.0
recursive cpu usage 244 0.8 5.3
redo blocks checksummed by FG (ex 2,990 9.4 65.0
redo blocks written 4,792 15.0 104.2
redo buffer allocation retries 0 0.0 0.0
redo entries 3,128 9.8 68.0
redo KB read 0 0.0 0.0
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo ordering marks 148 0.5 3.2
redo size 2,348,836 7,363.1 51,061.7
redo size for direct writes 0 0.0 0.0
redo subscn max counts 349 1.1 7.6
redo synch long waits 0 0.0 0.0
redo synch time 4 0.0 0.1
Instance Activity Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
redo synch time (usec) 69,091 216.6 1,502.0
redo synch writes 38 0.1 0.8
redo wastage 42,668 133.8 927.6
redo write time 14 0.0 0.3
redo writes 77 0.2 1.7
Requests to/from client 385 1.2 8.4
rollback changes - undo records a 5 0.0 0.1
rollbacks only - consistent read 9 0.0 0.2
RowCR attempts 2 0.0 0.0
RowCR hits 2 0.0 0.0
rows fetched via callback 459 1.4 10.0
scheduler wait time 0 0.0 0.0
session connect time 0 0.0 0.0
session cursor cache hits 515 1.6 11.2
session logical reads 177,259 555.7 3,853.5
session pga memory 11,953,152 37,470.7 259,851.1
session pga memory max 17,785,856 55,755.0 386,649.0
session uga memory 30,066,654,568 94,252,835.6 ############
session uga memory max 62,888,256 197,141.9 1,367,136.0
shared hash latch upgrades - no w 445 1.4 9.7
shared hash latch upgrades - wait 0 0.0 0.0
SMON posted for undo segment shri 0 0.0 0.0
sorts (memory) 1,041 3.3 22.6
sorts (rows) 18,472 57.9 401.6
sql area evicted 0 0.0 0.0
sql area purged 1 0.0 0.0
SQL*Net roundtrips to/from client 385 1.2 8.4
summed dirty queue length 1,825 5.7 39.7
switch current to new buffer 85 0.3 1.9
table fetch by rowid 1,896 5.9 41.2
table fetch continued row 3 0.0 0.1
table scan blocks gotten 285 0.9 6.2
table scan rows gotten 7,137 22.4 155.2
table scans (direct read) 0 0.0 0.0
table scans (long tables) 0 0.0 0.0
table scans (rowid ranges) 0 0.0 0.0
table scans (short tables) 196 0.6 4.3
TBS Extension: tasks created 0 0.0 0.0
TBS Extension: tasks executed 0 0.0 0.0
temp space allocated (bytes) 0 0.0 0.0
total cf enq hold time 109 0.3 2.4
total number of cf enq holders 5 0.0 0.1
total number of times SMON posted 1 0.0 0.0
transaction rollbacks 5 0.0 0.1
undo change vector size 835,948 2,620.5 18,172.8
user calls 608 1.9 13.2
user commits 46 0.1 1.0
user I/O wait time 4,597 14.4 99.9
user rollbacks 0 0.0 0.0
workarea executions - onepass 0 0.0 0.0
workarea executions - optimal 485 1.5 10.5
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 0 0.0 0.0
-------------------------------------------------------------
Instance Activity Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 32 30
opened cursors current 73 73
session cursor cache count 425,787 426,309
-------------------------------------------------------------
Instance Activity Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------
OS Statistics DB/Inst: INFORH/inforh Snaps: 32974-32975
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
BUSY_TIME 22,036
IDLE_TIME 41,850
SYS_TIME 17,977
USER_TIME 4,060
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 4,294,500,352
NUM_CPU_CORES 2
NUM_CPUS 2
NUM_CPU_SOCKETS 1
-------------------------------------------------------------
OS Statistics - detail DB/Inst: INFORH/inforh Snaps: 32974-32975
Snap Snapshot
Id Day Time Load %Busy %User %System %WIO %WCPU
------ --------------- ------ ------ ------ ------- ------ ------
32974 Mer. 24 12:59:5
2
32975 Mer. 24 13:05:1 34.5 6.4 28.1
1
-------------------------------------------------------------
IO Stat by Function - summary DB/Inst: INFORH/inforh Snaps: 32974-32975
->Data Volume values suffixed with M,G,T,P are in multiples of 1024,
other values suffixed with K,M,G,T,P are in multiples of 1000
->ordered by Data Volume (Read+Write) desc
---------- Read --------- --------- Write -------- --- Wait ----
Data Requests Data Data Requests Data Avg
Function Volume /sec Vol/sec Volume /sec Vol/sec Count Tm(ms)
--------------- ------ -------- -------- ------ -------- -------- ------ ------
Buffer Cache Re 1215M 4.5 3.8M 1445 0.0
Others 23M 4.6 .1M 4M .7 .0M 1571 0.0
Direct Reads .0 .0 0.0
-------------------------------------------------------------
IO Stat by Function - detail DB/Inst: INFORH/inforh Snaps: 32974-32975
->ordered by Data Volume (Read+Write) desc
----------- Read ---------- ----------- Write ---------
Small Large Small Large Small Large Small Large
Read Read Data Data Write Write Data Data
Function Reqs Reqs Read Read Reqs Reqs Writn Writn
------------------ ------ ------ ------ ------ ------ ------ ------ ------
Buffer Cache Reads 207 1238 2M 1213M
Others 1452 23M 238 4M
Direct Reads 6 5
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
USR
1,264 4 35.2 123.0 2 0 0 0.0
SYSAUX
175 1 7.0 1.0 254 1 0 0.0
UNDOTBS1
4 0 5.0 1.0 22 0 0 0.0
SYSTEM
7 0 12.9 1.0 3 0 0 0.0
USERS
1 0 0.0 1.0 1 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Mx Av
Av Rd Rd Av Av Buffer BufWt
Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
SYSAUX O:\BASE\INFORH\SYSAUX01.DBF
175 1 7.0 32 1.0 254 1 0
SYSTEM O:\BASE\INFORH\SYSTEM01.DBF
7 0 12.9 32 1.0 3 0 0
UNDOTBS1 O:\BASE\INFORH\UNDOTBS01.DBF
4 0 5.0 16 1.0 22 0 0
USERS O:\BASE\INFORH\USERS01.DBF
1 0 0.0 1.0 1 0 0
USR O:\BASE\INFORH\USR1INFO.ORA
200 1 49.1 16 122.4 1 0 0
O:\BASE\INFORH\USR2INFO.ORA
1,064 3 32.6 32 123.1 1 0 0
-------------------------------------------------------------
File Read Histogram Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
->Number of single block reads in each time range
->Tempfiles are not included
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
USR O:\BASE\INFORH\USR2INFO.ORA
0 0 0 0 2 0
UNDOTBS1 O:\BASE\INFORH\UNDOTBS01.DBF
0 0 2 1 0 0
SYSTEM O:\BASE\INFORH\SYSTEM01.DBF
0 0 1 2 3 0
SYSAUX O:\BASE\INFORH\SYSAUX01.DBF
29 22 64 51 8 0
USR O:\BASE\INFORH\USR1INFO.ORA
0 1 0 2 0 0
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
-> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- --------- --------- ---------- --------- ------------
B 0 0 634 5615 5638 165888 5638
E 0 0 500 12362 10195 165888 10195
-------------------------------------------------------------
Memory Target Advice DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Advice Reset: if this is null, the data shown has been diffed between
the Begin and End snapshots. If this is 'Y', the advisor has been
reset during this interval due to memory resize operations, and
the data shown is since the reset operation.
Memory Size Est. Advice
Memory Size (M) Factor DB time (s) Reset
--------------- ----------- ------------ ------
1,536 .5 254
2,304 .8 254
3,072 1.0 254
3,840 1.3 254
4,608 1.5 254
5,376 1.8 254
6,144 2.0 254
-------------------------------------------------------------
Memory Dynamic Components DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Op - memory resize Operation
-> Cache: D: Default, K: Keep, R: Recycle
-> Mode: DEF: DEFerred mode, IMM: IMMediate mode
Begin Snap End Snap Op Last Op
Cache Size (M) Size (M) Count Type/Mode Last Op Time
---------------------- ---------- -------- ------- ---------- ---------------
D:buffer cache 1,408 0 SHRINK/DEF 24-Févr. 12:03:
29
java pool 16 0 STATIC
large pool 16 0 STATIC
PGA Target 1,072 0 STATIC
SGA Target 2,000 0 STATIC
shared pool 512 0 GROW/DEF 24-Févr. 12:03:
29
streams pool 16 0 SHRINK/DEF 24-Févr. 09:33:
05
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: INFORH/inforh End Snap: 32975
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D 128 .1 16 1.1 13,659 20,469 63.7
D 256 .2 32 1.1 13,300 17,395 54.1
D 384 .3 47 1.0 13,134 15,974 49.7
D 512 .4 63 1.0 13,035 15,127 47.1
D 640 .5 79 1.0 12,937 14,293 44.5
D 768 .5 95 1.0 12,836 13,425 41.8
D 896 .6 110 1.0 12,744 12,639 39.3
D 1,024 .7 126 1.0 12,684 12,127 37.7
D 1,152 .8 142 1.0 12,636 11,723 36.5
D 1,280 .9 158 1.0 12,609 11,489 35.8
D 1,408 1.0 173 1.0 12,591 11,338 35.3
D 1,536 1.1 189 1.0 12,575 11,201 34.9
D 1,664 1.2 205 1.0 12,539 10,887 33.9
D 1,792 1.3 221 1.0 12,527 10,786 33.6
D 1,920 1.4 237 1.0 12,511 10,648 33.1
D 2,048 1.5 252 1.0 12,487 10,447 32.5
D 2,176 1.5 268 1.0 12,452 10,148 31.6
D 2,304 1.6 284 1.0 12,401 9,711 30.2
D 2,432 1.7 300 1.0 12,317 8,996 28.0
D 2,560 1.8 315 1.0 12,178 7,807 24.3
-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000
Free Writ Buffer
Pool Buffer Physical Physical Buffer Comp Busy
P Buffers Hit% Gets Reads Writes Waits Wait Waits
--- ------- ---- -------------- ------------ ----------- ------- ---- ----------
D 173K 12 177,332 155,597 608 0 0 0
-------------------------------------------------------------
PGA Aggr Target Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
-> B: Begin snap E: End snap (rows identified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all WorkAreas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to WorkAreas
-> %Auto W/A Mem - percentage of WorkArea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of WorkArea memory under Manual control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 68 0
PGA Aggr Target Histogram DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 422 422 0 0
64K 128K 10 10 0 0
128K 256K 6 6 0 0
512K 1024K 37 37 0 0
1M 2M 10 10 0 0
4M 8M 4 4 0 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: INFORH/inforh End Snap: 32975
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd
PGA Aggr W/A MB Estd Time PGA Estd PGA
Target Size W/A MB Read/Written to Process Cache Overalloc
Est (MB) Factr Processed to Disk Bytes (s) Hit % Count
---------- ------ -------------- -------------- ---------- ------ ----------
134 0.1 36,429 8,660 152.6 81.0 806
268 0.3 36,429 31 123.4 100.0 3
536 0.5 36,429 15 123.4 100.0 0
804 0.8 36,429 15 123.4 100.0 0
1,072 1.0 36,429 15 123.4 100.0 0
1,286 1.2 36,429 0 123.3 100.0 0
1,501 1.4 36,429 0 123.3 100.0 0
1,715 1.6 36,429 0 123.3 100.0 0
1,930 1.8 36,429 0 123.3 100.0 0
2,144 2.0 36,429 0 123.3 100.0 0
3,216 3.0 36,429 0 123.3 100.0 0
4,288 4.0 36,429 0 123.3 100.0 0
6,432 6.0 36,429 0 123.3 100.0 0
8,576 8.0 36,429 0 123.3 100.0 0
-------------------------------------------------------------
Process Memory Summary Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
-> B: Begin snap E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc
Hist Num
Avg Std Dev Max Max Procs
Alloc Used Freeabl Alloc Alloc Alloc Alloc or
Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B -------- 241.8 180.5 44.1 7.1 10.6 43 48 34
Other 193.2 5.7 10.0 42 42 34
Freeable 44.1 .0 2.6 3.8 14 17
PL/SQL 4.1 2.8 .1 .3 1 1 32
SQL .4 .1 .0 .0 0 21 10
E -------- 238.7 179.2 44.1 7.5 10.8 43 48 32
Other 190.1 5.9 10.3 42 42 32
Freeable 44.1 .0 2.6 3.8 14 17
PL/SQL 4.1 2.8 .1 .3 1 1 30
SQL .4 .1 .0 .0 0 21 10
-------------------------------------------------------------
Top Process Memory (by component) DB/Inst: INFORH/inforh Snaps: 32974-32975
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 22 ARC2 -------- 42.9 39.6 1.1 42.9 42.9
Other 41.9 41.9 41.9
Freeable 1.1 .0 1.1
PL/SQL .0 .0 .0 .0
20 ARC0 -------- 32.3 29.6 1.1 32.3 32.3
Other 31.3 31.3 31.3
Freeable 1.1 .0 1.1
PL/SQL .0 .0 .0 .0
21 ARC1 -------- 32.3 29.6 1.1 32.3 32.3
Other 31.3 31.3 31.3
Freeable 1.1 .0 1.1
PL/SQL .0 .0 .0 .0
10 DBW0 -------- 21.1 7.1 13.8 21.1 21.1
Freeable 13.8 .0 13.8
Other 7.3 7.3 7.3
PL/SQL .0 .0 .0 .0
23 ARC3 -------- 17.9 16.8 .0 17.9 17.9
Other 17.9 17.9 17.9
PL/SQL .0 .0 .0 .0
19 SHAD -------- 11.9 9.2 1.8 11.9 48.4
Other 9.0 9.0 24.8
Freeable 1.8 .0 1.8
PL/SQL 1.0 .5 1.0 1.1
SQL .0 .0 .0 20.6
25 CJQ0 -------- 11.4 1.3 9.9 11.4 11.4
Freeable 9.9 .0 9.9
Other 1.5 1.5 1.5
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 5.3
26 SHAD -------- 11.3 9.1 1.1 11.3 40.1
Other 9.1 9.1 17.1
Freeable 1.1 .0 1.1
PL/SQL 1.0 .4 1.0 1.2
SQL .0 .0 .0 20.7
29 SHAD -------- 10.2 8.2 1.2 10.2 16.9
Other 7.9 7.9 10.4
Freeable 1.2 .0 1.2
PL/SQL 1.0 .9 1.0 1.1
SQL .1 .0 .1 4.2
27 SHAD -------- 8.6 1.5 6.4 8.6 8.6
Freeable 6.4 .0 6.4
Other 1.9 1.9 1.9
PL/SQL .2 .2 .2 .2
SQL .1 .1 .1 6.3
11 LGWR -------- 5.7 4.8 .6 5.7 5.7
Other 5.1 5.1 5.1
Freeable .6 .0 .6
PL/SQL .0 .0 .0 .0
28 Q001 -------- 5.2 3.5 .6 5.2 5.2
Other 4.5 4.5 4.5
Freeable .6 .0 .6
PL/SQL .1 .1 .1 .1
SQL .0 .0 .0 .6
Top Process Memory (by component) DB/Inst: INFORH/inforh Snaps: 32974-32975
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 15 MMON -------- 4.5 2.0 2.0 4.5 4.5
Other 2.4 2.4 2.4
Freeable 2.0 .0 2.0
PL/SQL .1 .1 .1 .1
SQL .0 .0 .0 1.1
33 SHAD -------- 3.5 2.0 .9 3.5 7.3
Other 1.9 1.9 1.9
Freeable .9 .0 .9
PL/SQL .6 .6 .6 .6
SQL .0 .0 .0 4.1
13 SMON -------- 3.0 1.1 1.6 3.0 3.0
Freeable 1.6 .0 1.6
Other 1.4 1.4 1.4
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 1.4
31 J000 -------- 1.9 .7 .0 1.9 1.9
Other 1.9 1.9 1.9
PL/SQL .0 .0 .0 .8
SQL .0 .0 .0 .1
12 CKPT -------- 1.7 .8 .7 1.7 1.7
Other 1.0 1.0 1.0
Freeable .7 .0 .7
PL/SQL .0 .0 .0 .0
8 DIA0 -------- 1.5 1.3 .1 1.5 1.5
Other 1.4 1.4 1.4
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
14 RECO -------- 1.4 .8 .3 1.4 1.4
Other 1.1 1.1 1.1
Freeable .3 .0 .3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .5
17 D000 -------- 1.3 1.1 .0 1.3 1.3
Other 1.3 1.3 1.3
E 22 ARC2 -------- 42.9 39.6 1.1 42.9 42.9
Other 41.9 41.9 41.9
Freeable 1.1 .0 1.1
PL/SQL .0 .0 .0 .0
20 ARC0 -------- 32.3 29.6 1.1 32.3 32.3
Other 31.3 31.3 31.3
Freeable 1.1 .0 1.1
PL/SQL .0 .0 .0 .0
21 ARC1 -------- 32.3 29.6 1.1 32.3 32.3
Other 31.3 31.3 31.3
Freeable 1.1 .0 1.1
PL/SQL .0 .0 .0 .0
10 DBW0 -------- 21.1 7.1 13.8 21.1 21.1
Freeable 13.8 .0 13.8
Other 7.3 7.3 7.3
PL/SQL .0 .0 .0 .0
23 ARC3 -------- 17.9 16.8 .0 17.9 17.9
Other 17.9 17.9 17.9
PL/SQL .0 .0 .0 .0
Top Process Memory (by component) DB/Inst: INFORH/inforh Snaps: 32974-32975
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 19 SHAD -------- 11.9 9.2 1.8 11.9 48.4
Other 9.0 9.0 24.8
Freeable 1.8 .0 1.8
PL/SQL 1.0 .5 1.0 1.1
SQL .0 .0 .0 20.6
25 CJQ0 -------- 11.4 1.3 9.9 11.4 11.4
Freeable 9.9 .0 9.9
Other 1.5 1.5 1.5
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 5.3
26 SHAD -------- 11.3 9.1 1.1 11.3 40.1
Other 9.1 9.1 17.1
Freeable 1.1 .0 1.1
PL/SQL 1.0 .4 1.0 1.2
SQL .0 .0 .0 20.7
29 SHAD -------- 10.2 8.2 1.2 10.2 16.9
Other 7.9 7.9 10.4
Freeable 1.2 .0 1.2
PL/SQL 1.0 .9 1.0 1.1
SQL .1 .0 .1 4.2
27 SHAD -------- 8.2 1.7 6.2 8.2 8.6
Freeable 6.2 .0 6.2
Other 1.7 1.7 1.7
PL/SQL .2 .2 .2 .2
SQL .1 .1 .1 6.3
11 LGWR -------- 5.7 4.8 .6 5.7 5.7
Other 5.1 5.1 5.1
Freeable .6 .0 .6
PL/SQL .0 .0 .0 .0
28 Q001 -------- 5.2 3.5 .6 5.2 5.2
Other 4.5 4.5 4.5
Freeable .6 .0 .6
PL/SQL .1 .1 .1 .1
SQL .0 .0 .0 .6
15 MMON -------- 4.5 2.0 2.0 4.5 4.5
Other 2.4 2.4 2.4
Freeable 2.0 .0 2.0
PL/SQL .1 .1 .1 .1
SQL .0 .0 .0 1.1
33 SHAD -------- 3.5 2.0 .9 3.5 7.3
Other 1.9 1.9 1.9
Freeable .9 .0 .9
PL/SQL .6 .6 .6 .6
SQL .0 .0 .0 4.1
13 SMON -------- 3.0 1.1 1.8 3.0 3.0
Freeable 1.8 .0 1.8
Other 1.2 1.2 1.2
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 1.4
12 CKPT -------- 1.7 .8 .7 1.7 1.7
Other 1.0 1.0 1.0
Freeable .7 .0 .7
PL/SQL .0 .0 .0 .0
Top Process Memory (by component) DB/Inst: INFORH/inforh Snaps: 32974-32975
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 8 DIA0 -------- 1.5 1.3 .1 1.5 1.5
Other 1.4 1.4 1.4
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
14 RECO -------- 1.4 .8 .3 1.4 1.4
Other 1.1 1.1 1.1
Freeable .3 .0 .3
PL/SQL .0 .0 .0 .0
SQL .0 .0 .0 .5
17 D000 -------- 1.3 1.1 .0 1.3 1.3
Other 1.3 1.3 1.3
37 Q002 -------- 1.1 1.0 .0 1.1 1.1
Other 1.1 1.1 1.1
SQL .0 .0 .0 .0
PL/SQL .0 .0 .0 .0
-------------------------------------------------------------
Undo Segment Summary DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out Of Space count
-> Undo segment block stats:
uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/
TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU
---- ---------- --------------- -------- ---------- --------- ----- -----------
2 .2 349 530 3 20,9/20,9 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Most recent 35 Undostat rows, ordered by End Time desc
Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/
End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- -----------
24-Févr. 13: 164 349 530 3 21 0/0 0/0/0/0/0/0
01
-------------------------------------------------------------
Latch Activity DB/Inst: INFORH/inforh Snaps: 32974-32975
->"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 Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
active checkpoint queue 158 0.0 0 0
.
.
.
-------------------------------------------------------------
Latch Sleep breakdown DB/Inst: INFORH/inforh Snaps: 32974-32975
-> ordered by misses desc
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
messages 3,062 1 1 0
-------------------------------------------------------------
Latch Miss Sources DB/Inst: INFORH/inforh Snaps: 32974-32975
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
messages ksarcv 0 1 0
-------------------------------------------------------------
Dictionary Cache Stats DB/Inst: INFORH/inforh Snaps: 32974-32975
->"Pct Misses" should be very low (< 2% in most cases)
->"Final Usage" is the number of cache entries being used in End Snapshot
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 8 0.0 0 2 1
dc_global_oids 28 0.0 0 0 339
dc_object_grants 35 0.0 0 0 358
dc_objects 672 0.1 0 1 4,316
dc_profiles 25 0.0 0 0 2
dc_rollback_segments 66 0.0 0 0 22
dc_segments 11 0.0 0 1 1,693
dc_tablespaces 586 0.0 0 0 8
dc_users 1,712 0.0 0 0 177
global database name 235 0.0 0 0 2
outstanding_alerts 4 0.0 0 0 4
sch_lj_oids 1 0.0 0 0 22
-------------------------------------------------------------
Library Cache Activity DB/Inst: INFORH/inforh Snaps: 32974-32975
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
ACCOUNT_STATUS 28 0.0 0 0 0
BODY 141 0.0 161 0.0 0 0
DBLINK 31 0.0 0 0 0
EDITION 23 0.0 44 0.0 0 0
QUEUE 6 0.0 139 0.0 0 0
SCHEMA 23 0.0 0 0 0
SQL AREA 349 0.6 1,686 0.5 0 1
SQL AREA BUILD 2 100.0 0 0 0
SQL AREA STATS 2 100.0 2 100.0 0 0
SUBSCRIPTION 1 0.0 1 0.0 0 0
TABLE/PROCEDURE 71 1.4 662 0.2 0 0
TRIGGER 16 0.0 16 0.0 0 0
-------------------------------------------------------------
Rule Sets DB/Inst: INFORH/inforh Snaps: 32974-32975
-> * indicates Rule Set activity (re)started between Begin/End snaps
-> Top 25 ordered by Evaluations desc
No-SQL SQL
Rule * Eval/sec Reloads/sec Eval % Eval %
----------------------------------- - ------------ ----------- ------ ------
SYS.ALERT_QUE_R 0 0 0 0
-------------------------------------------------------------
Streams Pool Advisory DB/Inst: INFORH/inforh End Snap: 32975
Streams Pool Streams Pool Est Spill Est Spill Est Unspill Est Unspill
Size (M) Size Factor Count Time (s) Count Time (s)
------------ ------------ --------- --------- ----------- -----------
16.0 1.0 0 0 0 0
32.0 2.0 0 0 0 0
48.0 3.0 0 0 0 0
64.0 4.0 0 0 0 0
80.0 5.0 0 0 0 0
96.0 6.0 0 0 0 0
112.0 7.0 0 0 0 0
128.0 8.0 0 0 0 0
144.0 9.0 0 0 0 0
160.0 10.0 0 0 0 0
176.0 11.0 0 0 0 0
192.0 12.0 0 0 0 0
208.0 13.0 0 0 0 0
224.0 14.0 0 0 0 0
240.0 15.0 0 0 0 0
256.0 16.0 0 0 0 0
272.0 17.0 0 0 0 0
288.0 18.0 0 0 0 0
304.0 19.0 0 0 0 0
320.0 20.0 0 0 0 0
-------------------------------------------------------------
Shared Pool Advisory DB/Inst: INFORH/inforh End Snap: 32975
-> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid
Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
320 .6 22 2,633 32,627 .7 17,743 22.7 2,460,898
352 .7 53 3,614 36,681 .7 13,689 17.6 2,596,875
368 .7 69 4,110 42,398 .9 7,972 10.2 2,847,656
384 .8 85 4,497 45,206 .9 5,164 6.6 2,878,085
400 .8 101 5,112 46,070 .9 4,300 5.5 2,885,294
416 .8 117 5,410 46,575 .9 3,795 4.9 2,888,566
432 .8 133 5,668 47,042 .9 3,328 4.3 2,891,462
448 .9 149 6,094 47,535 1.0 2,835 3.6 2,894,266
464 .9 165 6,675 48,032 1.0 2,338 3.0 2,896,740
480 .9 181 7,224 48,551 1.0 1,819 2.3 2,899,680
496 1.0 196 7,943 49,065 1.0 1,305 1.7 2,902,944
512 1.0 212 8,555 49,590 1.0 780 1.0 2,906,192
528 1.0 228 8,767 49,609 1.0 761 1.0 2,907,184
544 1.1 244 8,979 49,630 1.0 740 .9 2,908,194
560 1.1 260 9,191 49,646 1.0 724 .9 2,908,982
576 1.1 276 9,403 49,657 1.0 713 .9 2,909,699
592 1.2 292 9,615 49,691 1.0 679 .9 2,911,176
608 1.2 308 9,827 49,722 1.0 648 .8 2,912,609
624 1.2 324 10,039 49,729 1.0 641 .8 2,912,980
640 1.3 340 10,251 49,734 1.0 636 .8 2,913,392
656 1.3 356 10,463 49,741 1.0 629 .8 2,913,857
704 1.4 404 11,101 49,764 1.0 606 .8 2,914,653
768 1.5 468 13,070 49,817 1.0 553 .7 2,917,249
832 1.6 532 14,675 49,822 1.0 548 .7 2,917,573
896 1.8 592 17,813 49,823 1.0 547 .7 2,917,777
960 1.9 592 17,813 49,823 1.0 547 .7 2,917,887
1,024 2.0 592 17,813 49,823 1.0 547 .7 2,917,887
-------------------------------------------------------------
SGA Target Advisory DB/Inst: INFORH/inforh End Snap: 32975
SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
1,000 .5 36,841 1.1 13,133,997
1,500 .8 33,471 1.0 12,743,666
2,000 1.0 32,131 1.0 12,591,311
2,500 1.3 31,408 1.0 12,510,727
3,000 1.5 29,217 .9 12,178,316
3,500 1.8 28,458 .9 12,178,316
4,000 2.0 28,458 .9 12,178,316
-------------------------------------------------------------
SGA Memory Summary DB/Inst: INFORH/inforh Snaps: 32974-32975
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 1,476,395,008
Fixed Size 2,258,192
Redo Buffers 19,300,352
Variable Size 1,174,407,920
-------------------- --------------------
sum 2,672,361,472
-------------------------------------------------------------
SGA breakdown difference DB/Inst: INFORH/inforh Snaps: 32974-32975
-> Top 35 rows by size, ordered by Pool, Name (note rows with null values for
Pool column, or Names showing free memory are always shown)
-> Null value for Begin MB or End MB indicates the size of that Pool/Name was
insignificant, or zero in that snapshot
Pool Name Begin MB End MB % Diff
------ ------------------------------ -------------- -------------- --------
java p free memory 16.0 16.0 0.00
large free memory 15.5 15.5 0.00
large PX msg pool .5 .5 0.00
shared free memory 93.5 93.5 0.02
shared KGH: NO ACCESS 79.7 79.7 0.00
shared KGLHD 16.0 16.0 0.02
shared KGLH0 115.3 115.4 0.07
shared KGLS 15.0 15.0 0.00
shared PLMCD 12.2 12.2 0.00
shared row cache 7.2 7.2 0.00
shared SQLA 124.0 124.0 0.01
shared write state object 7.2 7.2 0.00
shared XDBSC 14.1 14.1 0.00
stream free memory 15.4 15.4 0.00
stream KTG hash buckets .6 .6 0.00
buffer_cache 1,408.0 1,408.0 0.00
fixed_sga 2.2 2.2 0.00
log_buffer 18.4 18.4 0.00
-------------------------------------------------------------
SQL Memory Statistics DB/Inst: INFORH/inforh Snaps: 32974-32975
Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 25.45 25.45 -.01
Cursor to Parent ratio: 1.89 1.89 -.01
Total Cursors: 6,596 6,597 .02
Total Parents: 3,490 3,491 .03
-------------------------------------------------------------
init.ora Parameters DB/Inst: INFORH/inforh Snaps: 32974-32975
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_file_dest D:\ADMIN\INFORH\ADUMP
audit_trail DB
compatible 11.2.0.0.0
control_files O:\BASE\INFORH\CONTROL01.CTL, O:\
BASE\INFORH\CONTROL02.CTL
db_block_size 8192
db_domain
db_name INFORH
diagnostic_dest D:\
dispatchers (PROTOCOL=TCP) (SERVICE=INFORHXDB
)
log_archive_dest_1 LOCATION=P:\Archivelogs
log_archive_format log_%t_%s_%r.arc
memory_target 3221225472
open_cursors 300
processes 150
remote_login_passwordfile EXCLUSIVE
sga_max_size 2684354560
statistics_level ALL
undo_tablespace UNDOTBS1
-------------------------------------------------------------
End of Report ( sp_32974_32975.lst ) |
Conseil de tuning sur une base avec une grosse table
Répondre avec citation
Envoyé par Le-DOC
Partager