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
|
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
BOAC5 235052694 boac5 1 10.2.0.5.0 NO CIAC5P05
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 233 25-Juil.-11 09:00:5 18 8.3
End Snap: 234 25-Juil.-11 09:20:4 19 6.1
Elapsed: 19.76 (mins)
DB Time: 6.28 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 48M 48M Std Block Size: 8K
Shared Pool Size: 64M 64M Log Buffer: 2,824K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 106,904.26 1,218,573.88
Logical reads: 252,085.11 2,873,452.73
Block changes: 418.23 4,767.27
Physical reads: 2.35 26.77
Physical writes: 8.27 94.22
User calls: 201.78 2,300.02
Parses: 101.71 1,159.35
Hard parses: 0.09 1.00
Sorts: 3.20 36.49
Logons: 0.03 0.37
Executes: 103.56 1,180.48
Transactions: 0.09
% Blocks changed per Read: 0.17 Recursive Call %: 11.79
Rollback per transaction %: 0.00 Rows per Sort: 86.76
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 99.81 Soft Parse %: 99.91
Execute to Parse %: 1.79 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 98.08 % Non-Parse CPU: 97.56
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.92 94.04
% SQL with executions>1: 69.98 81.90
% Memory for SQL w/exec>1: 65.51 80.39
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 356 94.5
CSS initialization 8 13 1647 3.5 Other
db file sequential read 2,428 3 1 0.8 User I/O
control file sequential read 4,547 3 1 0.7 System I/O
log file parallel write 333 2 6 0.5 System I/O
-------------------------------------------------------------
Time Model Statistics DB/Inst: BOAC5/boac5 Snaps: 233-234
-> Total time in database user-calls (DB Time): 376,9s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
DB CPU 356.3 94.5
sql execute elapsed time 346.6 92.0
parse time elapsed 11.0 2.9
PL/SQL execution elapsed time 7.0 1.8
hard parse elapsed time 0.3 .1
PL/SQL compilation elapsed time 0.1 .0
connection management call elapsed time 0.0 .0
hard parse (sharing criteria) elapsed time 0.0 .0
hard parse (bind mismatch) elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 376.9 N/A
background elapsed time 8.5 N/A
background cpu time 0.9 N/A
-------------------------------------------------------------
Avg %Total
%Time Total Wait wait Call
Wait Class Waits -outs Time (s) (ms) Time
-------------------- ---------------- ----- ---------------- -------- -------
CPU time N/A N/A 356 N/A 94.5
Other 30 N/A 13 440 3.5
System I/O 5,313 N/A 6 1 1.6
User I/O 2,497 N/A 3 1 0.8
Network 237,305 N/A 1 0 0.4
Configuration 387 1 0 1 0.1
Concurrency 14 N/A 0 6 0.0
Commit 26 N/A 0 2 0.0
Application 2 N/A 0 0 0.0
------------------------------------------------------------
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ----- ----------- ------- ---------
CSS initialization 8 N/A 13 1647 0.1
db file sequential read 2,428 N/A 3 1 23.3
control file sequential read 4,547 N/A 3 1 43.7
log file parallel write 333 N/A 2 6 3.2
control file parallel write 425 N/A 1 3 4.1
SQL*Net more data from clien 116,223 N/A 1 0 1,117.5
SQL*Net message to client 121,007 N/A 0 0 1,163.5
log buffer space 24 N/A 0 9 0.2
db file scattered read 35 N/A 0 4 0.3
free buffer waits 359 1 0 0 3.5
os thread startup 12 N/A 0 7 0.1
log file sync 26 N/A 0 2 0.3
log file switch completion 4 N/A 0 8 0.0
enq: CF - contention 2 N/A 0 4 0.0
latch free 1 N/A 0 6 0.0
direct path write temp 2 N/A 0 3 0.0
direct path write 20 N/A 0 0 0.2
SQL*Net more data to client 75 N/A 0 0 0.7
log file single write 4 N/A 0 0 0.0
log file sequential read 4 N/A 0 0 0.0
LGWR wait for redo copy 18 N/A 0 0 0.2
SQL*Net break/reset to clien 2 N/A 0 0 0.0
direct path read 12 N/A 0 0 0.1
latch: shared pool 1 N/A 0 0 0.0
latch: library cache lock 1 N/A 0 0 0.0
latch: redo allocation 1 N/A 0 0 0.0
SQL*Net message from client 121,007 N/A 2,585 21 1,163.5
jobq slave wait 426 96 1,269 2978 4.1
Streams AQ: qmn slave idle w 42 N/A 1,151 27414 0.4
Streams AQ: qmn coordinator 84 50 1,151 13707 0.8
-------------------------------------------------------------
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ----- ----------- ------- ---------
log file parallel write 333 N/A 2 6 3.2
control file parallel write 424 N/A 1 3 4.1
control file sequential read 442 N/A 0 1 4.3
os thread startup 12 N/A 0 7 0.1
db file scattered read 1 N/A 0 11 0.0
events in waitclass Other 21 N/A 0 0 0.2
db file sequential read 30 N/A 0 0 0.3
direct path write 12 N/A 0 0 0.1
log file single write 4 N/A 0 0 0.0
log file sequential read 4 N/A 0 0 0.0
direct path read 12 N/A 0 0 0.1
rdbms ipc message 3,929 96 11,255 2864 37.8
pmon timer 411 100 1,181 2872 4.0
Streams AQ: qmn slave idle w 42 N/A 1,151 27414 0.4
Streams AQ: qmn coordinator 84 50 1,151 13707 0.8
smon timer 4 100 1,144 286094 0.0
-------------------------------------------------------------
Statistic Total
-------------------------------- --------------------
AVG_BUSY_TIME 37,588
AVG_IDLE_TIME 80,914
AVG_SYS_TIME 3,307
AVG_USER_TIME 34,281
BUSY_TIME 37,588
IDLE_TIME 80,914
SYS_TIME 3,307
USER_TIME 34,281
RSRC_MGR_CPU_WAIT_TIME 0
VM_IN_BYTES 6,438,912
VM_OUT_BYTES 5,570,560
PHYSICAL_MEMORY_BYTES 4,294,148,096
NUM_CPU_CORES 1
NUM_CPUS 1
-------------------------------------------------------------
Physical Logical
Service Name DB Time (s) DB CPU (s) Reads Reads
-------------------------------- ------------ ------------ ---------- ----------
SYS$USERS 349.4 340.8 2,150 2.99E+08
BOAC5 27.6 15.6 6 5,466
SYS$BACKGROUND 0.0 0.0 596 17,212
-------------------------------------------------------------
Service Name
----------------------------------------------------------------
User I/O User I/O Concurcy Concurcy Admin Admin Network Network
Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time
--------- --------- --------- --------- --------- --------- --------- ---------
SYS$USERS
1841 3 0 0 0 0 231920 1
BOAC5
30 0 0 0 0 0 5397 0
SYS$BACKGROUND
625 1 12 0 0 0 0 0
-------------------------------------------------------------
SQL ordered by Elapsed Time DB/Inst: BOAC5/boac5 Snaps: 233-234
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
-> Total DB Time (s): 377
-> Captured SQL account for 97.7% of Total
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
324 320 115,898 0.0 86.0 4mqs3nt8a4wp5
Module: TEST_ORACLE_NICO.vshost.exe
insert into zartfab2 (csoc, cetab, cart, cdepot, nbjcons, nbjdluo, nbjgares, anc
recmax, qualmode , taffect, iper, ccouts, tofcout, destock , tach, tfab,dfabd,df
abf, cartent, cartsor, typfluxof, txfr, crgesof , tmpdes, trupdp, trupcbn, tepdp
, tecri, tfabjour,heurfin, qdelai,cuqdel, decaldeb, cartref , nbjgarss, qualite,
13 3 4 3.3 3.5 fdhjtr81m6dd7
Module: Spotlight on Oracle
BEGIN quest_soo_archivelog.init(:P_RAC); COMMIT; END;
13 3 4 3.3 3.5 293dpbdd77q4f
Module: Spotlight on Oracle
WITH dest AS ( SELECT dest_id, dest_name, dest.status, BINDING, name_space, tar
get, dest.archiver, schedule, destination, DECODE (UPPER(destinati
on), 'USE_DB_RECOVERY_FILE_DEST', :gv_recovery_dest, destination ) destination_e
nd, log_sequence, reopen_secs, delay_mins, 0 max_connections, net_timeout
7 7 75 0.1 1.9 1sw2bzv4zxbnv
Module: Spotlight on Oracle
SELECT event, quest_soo_pkg.event_category(event, :"SYS_B_00") category,
NVL(total_waits, :"SYS_B_01"), NVL(time_waited*:"SYS_B_02", :"SYS_
B_03") time_waited, total_timeouts From v$system_event WHERE quest_soo
_pkg.event_category(event, :"SYS_B_04") <> DECODE(:ShowIdleEvents,:"SYS_B_05",:"
2 2 1 2.1 0.6 7jvw5zmrmtavj
Module: TEST_ORACLE_NICO.vshost.exe
delete from ZARTFAB2
1 0 39 0.0 0.3 1g7rx2r2tskpd
Module: Spotlight on Oracle
SELECT file_type, ROUND (mb_used, :"SYS_B_00") mb_used, ROUND (mb_reclaim
able, :"SYS_B_01") mb_reclaimable, DECODE(total_mb,:"SYS_B_02",:"SYS_B_03
",ROUND (mb_used * :"SYS_B_04" / total_mb, :"SYS_B_05")) percent_space_used,
DECODE(total_mb,:"SYS_B_06",:"SYS_B_07",ROUND (mb_reclaimable * :"SYS_B_08"
1 0 20 0.0 0.2 fm9wf97c3kjq5
Module: Spotlight on Oracle
SELECT :"SYS_B_00" filetype_name, df.file_name, ts.tablespace_name
, ( (fs.phyblkrd) * ts.block_size) / :"SYS_B_01" / :"SYS_B_02" read_megab
ytes, ( (fs.phyblkwrt) * ts.block_size) / :"SYS_B_03" / :"SYS_B_04" write
_megabytes, fs.phyrds read_reqs, fs.phywrts write_reqs, fs.
1 0 75 0.0 0.2 6mq0qtj9dxpn3
Module: Spotlight on Oracle
SELECT l.total n_logs, l.mb size_mb, DECODE(d.log_mode,:"SYS_B_00",(l.una
rchived*:"SYS_B_01")/l.total,:"SYS_B_02"/l.total) pct_unarchived, DECODE(
d.log_mode,:"SYS_B_03",:"SYS_B_04",:"SYS_B_05") log_mode, :"SYS_B_06" ava
ilable_logs FROM (SELECT SUM(DECODE(archived,:"SYS_B_07",:"SYS_B_08",:"SYS_B_0
1 1 75 0.0 0.2 dj291n1jz5nrm
Module: Spotlight on Oracle
SELECT event, SUM(NVL(total_waits, :"SYS_B_000")) tota
l_waits, SUM(total_timeouts) total_timeouts,
SUM(NVL(time_waited, :"SYS_B_001")) time_waited, RO
UND(SUM(time_waited)/SUM(total_waits),:"SYS_B_002") average_wait FROM (SELECT
1 1 75 0.0 0.2 9wz4aumqt55tp
Module: Spotlight on Oracle
SELECT name, ROUND(SUM(bytes)/:"SYS_B_00") kb, SUM(shared_pool)/:"
SQL ordered by Elapsed Time DB/Inst: BOAC5/boac5 Snaps: 233-234
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
-> Total DB Time (s): 377
-> Captured SQL account for 97.7% of Total
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
SYS_B_01" shared_pool_mb FROM (SELECT DECODE(name, :"SYS_B_02", :"SYS_B_03",
:"SYS_B_04", :"SYS_B_05",
:"SYS_B_06", :"SYS_B_07", :"SYS_B_08",
-------------------------------------------------------------
SQL ordered by CPU Time DB/Inst: BOAC5/boac5 Snaps: 233-234
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total is the CPU Time divided into the Total CPU Time times 100
-> Total CPU Time (s): 356
-> Captured SQL account for 95.3% of Total
CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) % Total DB Time SQL Id
---------- ---------- ------------ ----------- ------- ------- -------------
320 324 115,898 0.00 89.8 86.0 4mqs3nt8a4wp5
Module: TEST_ORACLE_NICO.vshost.exe
insert into zartfab2 (csoc, cetab, cart, cdepot, nbjcons, nbjdluo, nbjgares, anc
recmax, qualmode , taffect, iper, ccouts, tofcout, destock , tach, tfab,dfabd,df
abf, cartent, cartsor, typfluxof, txfr, crgesof , tmpdes, trupdp, trupcbn, tepdp
, tecri, tfabjour,heurfin, qdelai,cuqdel, decaldeb, cartref , nbjgarss, qualite,
7 7 75 0.10 2.0 1.9 1sw2bzv4zxbnv
Module: Spotlight on Oracle
SELECT event, quest_soo_pkg.event_category(event, :"SYS_B_00") category,
NVL(total_waits, :"SYS_B_01"), NVL(time_waited*:"SYS_B_02", :"SYS_
B_03") time_waited, total_timeouts From v$system_event WHERE quest_soo
_pkg.event_category(event, :"SYS_B_04") <> DECODE(:ShowIdleEvents,:"SYS_B_05",:"
3 13 4 0.82 0.9 3.5 fdhjtr81m6dd7
Module: Spotlight on Oracle
BEGIN quest_soo_archivelog.init(:P_RAC); COMMIT; END;
3 13 4 0.82 0.9 3.5 293dpbdd77q4f
Module: Spotlight on Oracle
WITH dest AS ( SELECT dest_id, dest_name, dest.status, BINDING, name_space, tar
get, dest.archiver, schedule, destination, DECODE (UPPER(destinati
on), 'USE_DB_RECOVERY_FILE_DEST', :gv_recovery_dest, destination ) destination_e
nd, log_sequence, reopen_secs, delay_mins, 0 max_connections, net_timeout
2 2 1 1.52 0.4 0.6 7jvw5zmrmtavj
Module: TEST_ORACLE_NICO.vshost.exe
delete from ZARTFAB2
1 1 75 0.01 0.2 0.2 9wz4aumqt55tp
Module: Spotlight on Oracle
SELECT name, ROUND(SUM(bytes)/:"SYS_B_00") kb, SUM(shared_pool)/:"
SYS_B_01" shared_pool_mb FROM (SELECT DECODE(name, :"SYS_B_02", :"SYS_B_03",
:"SYS_B_04", :"SYS_B_05",
:"SYS_B_06", :"SYS_B_07", :"SYS_B_08",
1 1 75 0.01 0.1 0.2 dj291n1jz5nrm
Module: Spotlight on Oracle
SELECT event, SUM(NVL(total_waits, :"SYS_B_000")) tota
l_waits, SUM(total_timeouts) total_timeouts,
SUM(NVL(time_waited, :"SYS_B_001")) time_waited, RO
UND(SUM(time_waited)/SUM(total_waits),:"SYS_B_002") average_wait FROM (SELECT
0 0 75 0.01 0.1 0.1 1j7vr5bbxgqzp
Module: Spotlight on Oracle
SELECT u.username, u.account_status, COUNT (s.sid) num_sessions FROM dba
_users u, v$session s WHERE u.username = :"SYS_B_0" AND s.us
ername(+) = u.username AND LOWER (s.program(+)) LIKE :"SYS_B_1" GROUP
BY u.username, u.account_status
0 0 20 0.02 0.1 0.1 gcysxcrz8bfkr
Module: Spotlight on Oracle
SELECT p.name, --CloseCursor (1856332) SUBSTR(quest_soo_pkg.translate_par
ameter(value),:"SYS_B_0",:"SYS_B_1") value, NVL(quest_soo_pkg.translate_
parameter(value), :"SYS_B_2") num_value, p.isdefault FROM v$system_para
meter p UNION ALL SELECT :"SYS_B_3", TO_CHAR(DECODE(log_mode,:"SYS_B_4",
0 1 39 0.01 0.1 0.3 1g7rx2r2tskpd
Module: Spotlight on Oracle
SELECT file_type, ROUND (mb_used, :"SYS_B_00") mb_used, ROUND (mb_reclaim
SQL ordered by CPU Time DB/Inst: BOAC5/boac5 Snaps: 233-234
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total is the CPU Time divided into the Total CPU Time times 100
-> Total CPU Time (s): 356
-> Captured SQL account for 95.3% of Total
CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) % Total DB Time SQL Id
---------- ---------- ------------ ----------- ------- ------- -------------
able, :"SYS_B_01") mb_reclaimable, DECODE(total_mb,:"SYS_B_02",:"SYS_B_03
",ROUND (mb_used * :"SYS_B_04" / total_mb, :"SYS_B_05")) percent_space_used,
DECODE(total_mb,:"SYS_B_06",:"SYS_B_07",ROUND (mb_reclaimable * :"SYS_B_08"
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: BOAC5/boac5 Snaps: 233-234
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets: 298,839,084
-> Captured SQL account for 100.0% of Total
Gets CPU Elapsed
Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ------------ ------------ ------ -------- --------- -------------
298,660,516 115,898 2,576.9 99.9 319.86 324.20 4mqs3nt8a4wp5
Module: TEST_ORACLE_NICO.vshost.exe
insert into zartfab2 (csoc, cetab, cart, cdepot, nbjcons, nbjdluo, nbjgares, anc
recmax, qualmode , taffect, iper, ccouts, tofcout, destock , tach, tfab,dfabd,df
abf, cartent, cartsor, typfluxof, txfr, crgesof , tmpdes, trupdp, trupcbn, tepdp
, tecri, tfabjour,heurfin, qdelai,cuqdel, decaldeb, cartref , nbjgarss, qualite,
141,870 1 141,870.0 0.0 1.52 2.11 7jvw5zmrmtavj
Module: TEST_ORACLE_NICO.vshost.exe
delete from ZARTFAB2
8,444 4 2,111.0 0.0 0.03 0.08 7kamz2qtmq6m5
Module: sqlplus.exe
select to_char(s.startup_time,:"SYS_B_0") instart_fmt , di.instance_name
inst_name , di.db_name
db_name , s.snap_id
snap_id , to_char(s.end_interval_time,:"SYS_B_1") snapdat , s.snap
2,906 19 152.9 0.0 0.09 0.27 6gvch1xu9ca3g
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date
; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
2,213 2 1,106.5 0.0 0.03 0.26 3bu51464w6s7n
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN BEGIN quest_ppcm_collector.take_snapshot('PM'); END; :mydate := n
ext_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
1,849 2 924.5 0.0 0.02 0.07 c83yb8d1rfxqw
SELECT * FROM ( SELECT * FROM QUEST_PPCM_SNAPSHOT WHERE SNAPSHOT_TIMESTAMP < :B3
AND SNAPSHOT_TYPE = :B2 AND INSTANCE_ID = :B1 ORDER BY SNAPSHOT_TIMESTAMP DESC)
WHERE ROWNUM = 1
1,575 75 21.0 0.0 0.45 0.40 1j7vr5bbxgqzp
Module: Spotlight on Oracle
SELECT u.username, u.account_status, COUNT (s.sid) num_sessions FROM dba
_users u, v$session s WHERE u.username = :"SYS_B_0" AND s.us
ername(+) = u.username AND LOWER (s.program(+)) LIKE :"SYS_B_1" GROUP
BY u.username, u.account_status
1,125 75 15.0 0.0 0.08 0.11 3z6npmwhx2f9q
Module: Spotlight on Oracle
WITH pm_retention AS (SELECT :"SYS_B_00" x, quest_p
pcm_collector.get_ppcm_parameter ( :"SYS_B_01"
) pm_snapshot_retention_days FROM
DUAL), pm_job AS (SELECT :"SYS_B_02" x, owner
1,007 336 3.0 0.0 0.03 0.03 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
1,000 20 50.0 0.0 0.19 0.90 fm9wf97c3kjq5
Module: Spotlight on Oracle
SELECT :"SYS_B_00" filetype_name, df.file_name, ts.tablespace_name
, ( (fs.phyblkrd) * ts.block_size) / :"SYS_B_01" / :"SYS_B_02" read_megab
ytes, ( (fs.phyblkwrt) * ts.block_size) / :"SYS_B_03" / :"SYS_B_04" write
_megabytes, fs.phyrds read_reqs, fs.phywrts write_reqs, fs.
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: BOAC5/boac5 Snaps: 233-234
-> Total Disk Reads: 2,784
-> Captured SQL account for 78.4% of Total
Reads CPU Elapsed
Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ----------- ------------- ------ -------- --------- -------------
1,517 115,898 0.0 54.5 319.86 324.20 4mqs3nt8a4wp5
Module: TEST_ORACLE_NICO.vshost.exe
insert into zartfab2 (csoc, cetab, cart, cdepot, nbjcons, nbjdluo, nbjgares, anc
recmax, qualmode , taffect, iper, ccouts, tofcout, destock , tach, tfab,dfabd,df
abf, cartent, cartsor, typfluxof, txfr, crgesof , tmpdes, trupdp, trupcbn, tepdp
, tecri, tfabjour,heurfin, qdelai,cuqdel, decaldeb, cartref , nbjgarss, qualite,
332 1 332.0 11.9 1.52 2.11 7jvw5zmrmtavj
Module: TEST_ORACLE_NICO.vshost.exe
delete from ZARTFAB2
207 1 207.0 7.4 0.05 0.43 8p908gp3h35z7
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN BEGIN /*Quest PPCM Bottleneck job */ quest_ppcm_bottleneck.colle
ct_data; END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF
; END;
149 1 149.0 5.4 0.02 0.16 8rdjxnask9s4n
SELECT * FROM QUEST_PPCM_SNAPSHOT WHERE SNAPSHOT_ID = (SELECT MAX (SNAPSHOT_ID)
FROM QUEST_PPCM_SNAPSHOT WHERE INSTANCE_ID = :B3 AND SNAPSHOT_ID < :B2 AND SNAPS
HOT_TYPE = :B1 )
67 2 33.5 2.4 0.03 0.26 3bu51464w6s7n
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN BEGIN quest_ppcm_collector.take_snapshot('PM'); END; :mydate := n
ext_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
55 2 27.5 2.0 0.02 0.07 c83yb8d1rfxqw
SELECT * FROM ( SELECT * FROM QUEST_PPCM_SNAPSHOT WHERE SNAPSHOT_TIMESTAMP < :B3
AND SNAPSHOT_TYPE = :B2 AND INSTANCE_ID = :B1 ORDER BY SNAPSHOT_TIMESTAMP DESC)
WHERE ROWNUM = 1
28 1 28.0 1.0 0.02 0.17 8uru9t6frg2mw
WITH TIME_MODEL AS ( SELECT /*+ materialize */ SUM (CASE WHEN STAT_NAME IN ('DB
time', 'background cpu time') THEN VALUE END ) AS CPU_TIME, SUM (CASE WHEN STAT_
NAME IN ('background elapsed time', 'DB time') THEN VALUE END ) AS ACTIVE_TIME F
ROM V$SYS_TIME_MODEL WHERE STAT_NAME IN ('DB time', 'DB CPU', 'background elapse
25 1 25.0 0.9 0.02 0.03 8mrg623amm8rx
select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_
SCHLIM from ( select a.obj# OBJOID, a.class_oid CLSOID, decode(bitand(a.flags
, 16384), 0, a.next_run_date, a.last_enabled_time) RUNTIME, (2*a.priority +
decode(bitand(a.job_status, 4), 0, 0, decode(a.running_instance, :
17 19 0.9 0.6 0.09 0.27 6gvch1xu9ca3g
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date
; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
11 17 0.6 0.4 0.00 0.04 7ng34ruy5awxq
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf
ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl
ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance
s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: BOAC5/boac5 Snaps: 233-234
-> Total Executions: 122,770
-> Captured SQL account for 97.5% of Total
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id
------------ --------------- -------------- ---------- ----------- -------------
115,898 115,898 1.0 0.00 0.00 4mqs3nt8a4wp5
Module: TEST_ORACLE_NICO.vshost.exe
insert into zartfab2 (csoc, cetab, cart, cdepot, nbjcons, nbjdluo, nbjgares, anc
recmax, qualmode , taffect, iper, ccouts, tofcout, destock , tach, tfab,dfabd,df
abf, cartent, cartsor, typfluxof, txfr, crgesof , tmpdes, trupdp, trupcbn, tepdp
, tecri, tfabjour,heurfin, qdelai,cuqdel, decaldeb, cartref , nbjgarss, qualite,
377 300 0.8 0.00 0.00 008av6q4610qn
Module: Spotlight on Oracle
SELECT PARAMETER_VALUE FROM QUEST_PPCM_PARAMETERS WHERE PARAMETER_NAME = :B1
336 235 0.7 0.00 0.00 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
300 300 1.0 0.00 0.00 f6bzjsx1nc0g0
Module: Spotlight on Oracle
SELECT (SYSDATE+(:"SYS_B_0" / (:"SYS_B_1"*:"SYS_B_2")) - sysdate) * :"SYS_B_3" *
:"SYS_B_4" from dual
225 225 1.0 0.00 0.00 fpvnzqxq9fmmg
Module: Spotlight on Oracle
SELECT (SYSDATE+ :"SYS_B_0"-sysdate)*:"SYS_B_1" from dual
105 0 0.0 0.00 0.00 b2gnxm5z6r51n
lock table sys.col_usage$ in exclusive mode nowait
101 101 1.0 0.00 0.00 0v3dvmc22qnam
insert into sys.col_usage$ (obj#, intcol#, equality_preds, equijoin_preds, noneq
uijoin_preds, range_preds, like_preds, null_preds, timestamp) values ( :objn,
:coln, decode(bitand(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decod
e(bitand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode(bitand(:flag
75 0 0.0 0.00 0.00 bz88kjm9pnrmb
Module: Spotlight on Oracle
SELECT TO_CHAR (NULL) name, TO_NUMBER (NULL) VALUE FROM DUAL WHERE :"SY
S_B_0" = :"SYS_B_1"
75 5,250 70.0 0.00 0.00 b6gkk8yb7gkbw
Module: Spotlight on Oracle
SELECT DECODE (NAME, :"SYS_B_00", :"SYS_B_01", NAME) NAME, NVL (VALUE, :"
SYS_B_02") FROM v$sysstat WHERE NAME IN ( :"SYS_B_03",
:"SYS_B_04", :"SYS_B_05", :"SYS_B_06",
:"SYS_B_07", :"SYS_B_08", :"SYS_B_09", :"SYS_
75 525 7.0 0.00 0.00 cr8036agfq35v
Module: Spotlight on Oracle
SELECT stm.stat_name, stm.VALUE / :"SYS_B_0" FROM v$sys_time_model stm WH
ERE stm.stat_name IN (:"SYS_B_1", :"SYS_B_2",
:"SYS_B_3", :"SYS_B_4", :"SYS_B_5
", :"SYS_B_6", :"SYS_B_7")
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: BOAC5/boac5 Snaps: 233-234
-> Total Parse Calls: 120,572
-> Captured SQL account for 98.7% of Total
% Total
Parse Calls Executions Parses SQL Id
------------ ------------ --------- -------------
115,898 115,898 96.12 4mqs3nt8a4wp5
Module: TEST_ORACLE_NICO.vshost.exe
insert into zartfab2 (csoc, cetab, cart, cdepot, nbjcons, nbjdluo, nbjgares, anc
recmax, qualmode , taffect, iper, ccouts, tofcout, destock , tach, tfab,dfabd,df
abf, cartent, cartsor, typfluxof, txfr, crgesof , tmpdes, trupdp, trupcbn, tepdp
, tecri, tfabjour,heurfin, qdelai,cuqdel, decaldeb, cartref , nbjgarss, qualite,
300 300 0.25 f6bzjsx1nc0g0
Module: Spotlight on Oracle
SELECT (SYSDATE+(:"SYS_B_0" / (:"SYS_B_1"*:"SYS_B_2")) - sysdate) * :"SYS_B_3" *
:"SYS_B_4" from dual
225 225 0.19 fpvnzqxq9fmmg
Module: Spotlight on Oracle
SELECT (SYSDATE+ :"SYS_B_0"-sysdate)*:"SYS_B_1" from dual
105 105 0.09 b2gnxm5z6r51n
lock table sys.col_usage$ in exclusive mode nowait
105 101 0.09 0v3dvmc22qnam
insert into sys.col_usage$ (obj#, intcol#, equality_preds, equijoin_preds, noneq
uijoin_preds, range_preds, like_preds, null_preds, timestamp) values ( :objn,
:coln, decode(bitand(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decod
e(bitand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode(bitand(:flag
105 336 0.09 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
75 75 0.06 bz88kjm9pnrmb
Module: Spotlight on Oracle
SELECT TO_CHAR (NULL) name, TO_NUMBER (NULL) VALUE FROM DUAL WHERE :"SY
S_B_0" = :"SYS_B_1"
75 75 0.06 b6gkk8yb7gkbw
Module: Spotlight on Oracle
SELECT DECODE (NAME, :"SYS_B_00", :"SYS_B_01", NAME) NAME, NVL (VALUE, :"
SYS_B_02") FROM v$sysstat WHERE NAME IN ( :"SYS_B_03",
:"SYS_B_04", :"SYS_B_05", :"SYS_B_06",
:"SYS_B_07", :"SYS_B_08", :"SYS_B_09", :"SYS_
75 75 0.06 cr8036agfq35v
Module: Spotlight on Oracle
SELECT stm.stat_name, stm.VALUE / :"SYS_B_0" FROM v$sys_time_model stm WH
ERE stm.stat_name IN (:"SYS_B_1", :"SYS_B_2",
:"SYS_B_3", :"SYS_B_4", :"SYS_B_5
", :"SYS_B_6", :"SYS_B_7")
75 75 0.06 djrc4bspf9bbr
Module: Spotlight on Oracle
SELECT namespace, gets, gethits, pins, pinhits, reloads FROM v$librarycache O
RDER BY namespace
-------------------------------------------------------------
SQL ordered by Sharable Memory DB/Inst: BOAC5/boac5 Snaps: 233-234
No data exists for this section of the report.
-------------------------------------------------------------
SQL ordered by Version Count DB/Inst: BOAC5/boac5 Snaps: 233-234
No data exists for this section of the report.
-------------------------------------------------------------
Instance Activity Stats DB/Inst: BOAC5/boac5 Snaps: 233-234
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
active txn count during cleanout 135,148,002 114,003.8 1,299,500.0
background checkpoints completed 2 0.0 0.0
background checkpoints started 2 0.0 0.0
background timeouts 3,788 3.2 36.4
buffer is not pinned count 21,520 18.2 206.9
buffer is pinned count 261,002 220.2 2,509.6
bytes received via SQL*Net from 289,036,553 243,816.2 2,779,197.6
bytes sent via SQL*Net to client 19,631,022 16,559.7 188,759.8
calls to get snapshot scn: kcmgs 124,981 105.4 1,201.7
calls to kcmgas 2,814 2.4 27.1
calls to kcmgcs 135,148,012 114,003.8 1,299,500.1
change write time 169 0.1 1.6
cleanout - number of ktugct call 135,149,717 114,005.3 1,299,516.5
cleanouts only - consistent read 1,712 1.4 16.5
cluster key scan block gets 2,484 2.1 23.9
cluster key scans 787 0.7 7.6
commit batch/immediate performed 1 0.0 0.0
commit batch/immediate requested 1 0.0 0.0
commit cleanout failures: callba 4 0.0 0.0
commit cleanouts 1,501 1.3 14.4
commit cleanouts successfully co 1,497 1.3 14.4
commit immediate performed 1 0.0 0.0
commit immediate requested 1 0.0 0.0
commit txn count during cleanout 1,729 1.5 16.6
concurrency wait time 8 0.0 0.1
consistent changes 14 0.0 0.1
consistent gets 135,418,004 114,231.6 1,302,096.2
consistent gets - examination 135,157,670 114,012.0 1,299,593.0
consistent gets direct 8 0.0 0.1
consistent gets from cache 135,417,996 114,231.6 1,302,096.1
CPU used by this session 34,495 29.1 331.7
CPU used when call started 34,475 29.1 331.5
CR blocks created 6 0.0 0.1
cursor authentications 72 0.1 0.7
data blocks consistent reads - u 6 0.0 0.1
db block changes 495,796 418.2 4,767.3
db block gets 163,421,080 137,853.5 1,571,356.5
db block gets direct 10 0.0 0.1
db block gets from cache 163,421,070 137,853.5 1,571,356.4
DB time 158,614 133.8 1,525.1
DBWR checkpoint buffers written 973 0.8 9.4
DBWR checkpoints 2 0.0 0.0
DBWR thread checkpoint buffers w 973 0.8 9.4
DBWR transaction table writes 11 0.0 0.1
DBWR undo block writes 6,296 5.3 60.5
deferred (CURRENT) block cleanou 1,141 1.0 11.0
dirty buffers inspected 8,880 7.5 85.4
enqueue conversions 248 0.2 2.4
enqueue releases 9,425 8.0 90.6
enqueue requests 9,425 8.0 90.6
enqueue waits 1 0.0 0.0
execute count 122,770 103.6 1,180.5
free buffer inspected 13,215 11.2 127.1
free buffer requested 12,410 10.5 119.3
heap block compress 2,342 2.0 22.5
hot buffers moved to head of LRU 3,184 2.7 30.6
immediate (CR) block cleanout ap 1,712 1.4 16.5
immediate (CURRENT) block cleano 137 0.1 1.3
IMU commits 94 0.1 0.9
IMU- failed to get a private str 11 0.0 0.1
Instance Activity Stats DB/Inst: BOAC5/boac5 Snaps: 233-234
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
IMU Flushes 4 0.0 0.0
IMU pool not allocated 11 0.0 0.1
IMU Redo allocation size 5,092 4.3 49.0
IMU undo allocation size 197,676 166.8 1,900.7
index crx upgrade (positioned) 1,465 1.2 14.1
index fast full scans (full) 0 0.0 0.0
index fetch by key 4,745 4.0 45.6
index scans kdiixs1 3,152 2.7 30.3
leaf node splits 18 0.0 0.2
leaf node 90-10 splits 10 0.0 0.1
lob reads 90 0.1 0.9
lob writes 24 0.0 0.2
lob writes unaligned 24 0.0 0.2
logons cumulative 38 0.0 0.4
messages received 432 0.4 4.2
messages sent 432 0.4 4.2
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 19,351 16.3 186.1
opened cursors cumulative 120,983 102.1 1,163.3
parse count (failures) 0 0.0 0.0
parse count (hard) 104 0.1 1.0
parse count (total) 120,572 101.7 1,159.4
parse time cpu 870 0.7 8.4
parse time elapsed 887 0.8 8.5
physical read bytes 22,806,528 19,238.4 219,293.5
physical read IO requests 2,469 2.1 23.7
physical read total bytes 97,290,240 82,069.0 935,483.1
physical read total IO requests 7,034 5.9 67.6
physical read total multi block 35 0.0 0.3
physical reads 2,784 2.4 26.8
physical reads cache 2,764 2.3 26.6
physical reads cache prefetch 315 0.3 3.0
physical reads direct 20 0.0 0.2
physical reads direct temporary 0 0.0 0.0
physical write bytes 80,273,408 67,714.5 771,859.7
physical write IO requests 6,291 5.3 60.5
physical write total bytes 231,984,128 195,689.8 2,230,616.6
physical write total IO requests 7,981 6.7 76.7
physical write total multi block 736 0.6 7.1
physical writes 9,799 8.3 94.2
physical writes direct 22 0.0 0.2
physical writes direct (lob) 8 0.0 0.1
physical writes direct temporary 2 0.0 0.0
physical writes from cache 9,777 8.3 94.0
physical writes non checkpoint 8,495 7.2 81.7
pinned buffers inspected 171 0.1 1.6
process last non-idle time 2,105 1.8 20.2
recursive calls 31,964 27.0 307.4
recursive cpu usage 430 0.4 4.1
redo blocks written 255,602 215.6 2,457.7
redo buffer allocation retries 62 0.1 0.6
redo entries 252,335 212.9 2,426.3
redo log space requests 10 0.0 0.1
redo log space wait time 1 0.0 0.0
redo size 126,731,684 106,904.3 1,218,573.9
redo synch time 4 0.0 0.0
redo synch writes 25 0.0 0.2
redo wastage 78,660 66.4 756.4
redo write time 140 0.1 1.4
redo writes 333 0.3 3.2
Instance Activity Stats DB/Inst: BOAC5/boac5 Snaps: 233-234
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
rollback changes - undo records 1 0.0 0.0
rollbacks only - consistent read 6 0.0 0.1
RowCR attempts 4 0.0 0.0
RowCR hits 4 0.0 0.0
rows fetched via callback 2,097 1.8 20.2
session connect time 0 0.0 0.0
session cursor cache hits 4,297 3.6 41.3
session logical reads 298,839,084 252,085.1 2,873,452.7
shared hash latch upgrades - no 1,762 1.5 16.9
sorts (memory) 3,795 3.2 36.5
sorts (rows) 329,237 277.7 3,165.7
sql area evicted 108 0.1 1.0
sql area purged 2 0.0 0.0
SQL*Net roundtrips to/from clien 120,973 102.1 1,163.2
summed dirty queue length 28,656 24.2 275.5
switch current to new buffer 2,048 1.7 19.7
table fetch by rowid 81,454 68.7 783.2
table fetch continued row 41 0.0 0.4
table scan blocks gotten 3,863 3.3 37.1
table scan rows gotten 293,111 247.3 2,818.4
table scans (long tables) 2 0.0 0.0
table scans (short tables) 664 0.6 6.4
total number of times SMON poste 0 0.0 0.0
transaction rollbacks 1 0.0 0.0
undo change vector size 54,293,584 45,799.2 522,053.7
user calls 239,202 201.8 2,300.0
user commits 104 0.1 1.0
user I/O wait time 318 0.3 3.1
workarea executions - optimal 3,231 2.7 31.1
write clones created in foregrou 294 0.3 2.8
-------------------------------------------------------------
Instance Activity Stats - Absolute ValuesDB/Inst: BOAC5/boac5 Snaps: 233-234
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
-------------------------------- --------------- ---------------
session uga memory 1.4174306E+11 2.3623062E+11
opened cursors current 150 115
logons current 18 19
session uga memory max 50,543,828 76,195,316
session pga memory 66,266,840 91,282,912
session pga memory max 100,017,880 125,099,488
session cursor cache count 1,975 3,090
------------------------------------------------------------- |
Temps de traitement 8i et 10g
Répondre avec citation
Envoyé par jlm22
Partager