forked from fatdba/OtherScripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
pdhc_oracleendtoendhealthcheck.sql
1982 lines (1697 loc) · 79.7 KB
/
pdhc_oracleendtoendhealthcheck.sql
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
set termout off
set linesize 90
set pagesize 21
ttitle center 'PDHC1.4 - A Quick Health Check' skip 2
btitle center '<span style="background-color:#38761d;color:#ffffff;border:1px solid black;">PART - 1</span>'
set markup html on spool on entmap off
spool DB_Detail_status.html
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set linesize 400 pagesize 400
SET TERMOUT ON;
PROMPT
PROMPT
PROMPT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT----- Script: Healthcheck.sql (pdhc.sql)
PROMPT----- Author: Prashant 'The FatDBA'
PROMPT----- Cat: Performance Management and Issue Identification
PROMPT----- Version: V1.3 (Date: 15-07-2022)
PROMPT~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Database under Observation |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
select s.NAME,s.DB_UNIQUE_NAME AS UNQ_NAME,TO_CHAR(d.STARTUP_TIME, 'DD-MM-YY HH24:MI:SS') AS STARTTIME,s.OPEN_MODE,d.INSTANCE_ROLE, s.LOG_MODE,
to_char(s.current_scn) as SCN,s.DATABASE_ROLE,s.FLASHBACK_ON, d.VERSION,d.LOGINS from v$database s, v$instance d where s.name=UPPER(d.instance_name);
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Database under Observation |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
WITH
rac AS (SELECT /*+ MATERIALIZE NO_MERGE */ COUNT(*) instances, CASE COUNT(*) WHEN 1 THEN 'Single-instance' ELSE COUNT(*)||'-node RAC cluster' END db_type
FROM gv$instance),
mem AS (SELECT /*+ MATERIALIZE NO_MERGE */ SUM(value) target FROM gv$system_parameter2 WHERE name = 'memory_target'),
sga AS (SELECT /*+ MATERIALIZE NO_MERGE */ SUM(value) target FROM gv$system_parameter2 WHERE name = 'sga_target'),
pga AS (SELECT /*+ MATERIALIZE NO_MERGE */ SUM(value) target FROM gv$system_parameter2 WHERE name = 'pga_aggregate_target'),
db_block AS (SELECT /*+ MATERIALIZE NO_MERGE */ value bytes FROM v$system_parameter2 WHERE name = 'db_block_size'),
db AS (SELECT /*+ MATERIALIZE NO_MERGE */ name, platform_name FROM v$database),
inst AS (SELECT /*+ MATERIALIZE NO_MERGE */ host_name, version db_version FROM v$instance),
data AS (SELECT /*+ MATERIALIZE NO_MERGE */ SUM(bytes) bytes, COUNT(*) files, COUNT(DISTINCT ts#) tablespaces FROM v$datafile),
temp AS (SELECT /*+ MATERIALIZE NO_MERGE */ SUM(bytes) bytes FROM v$tempfile),
log AS (SELECT /*+ MATERIALIZE NO_MERGE */ SUM(bytes) * MAX(members) bytes FROM v$log),
control AS (SELECT /*+ MATERIALIZE NO_MERGE */ SUM(block_size * file_size_blks) bytes FROM v$controlfile),
cell AS (SELECT /*+ MATERIALIZE NO_MERGE */ COUNT(DISTINCT cell_name) cnt FROM v$cell_state),
core AS (SELECT /*+ MATERIALIZE NO_MERGE */ SUM(value) cnt FROM gv$osstat WHERE stat_name = 'NUM_CPU_CORES'),
cpu AS (SELECT /*+ MATERIALIZE NO_MERGE */ SUM(value) cnt FROM gv$osstat WHERE stat_name = 'NUM_CPUS'),
pmem AS (SELECT /*+ MATERIALIZE NO_MERGE */ SUM(value) bytes FROM gv$osstat WHERE stat_name = 'PHYSICAL_MEMORY_BYTES')
SELECT /*+ NO_MERGE */ /* 1a.1 */
'Database name:' system_item, db.name system_value FROM db
UNION ALL
SELECT 'Oracle Database version:', inst.db_version FROM inst
UNION ALL
SELECT 'Database block size:', TRIM(TO_CHAR(db_block.bytes / POWER(2,10), '90'))||' KB' FROM db_block
UNION ALL
SELECT 'Database size:', TRIM(TO_CHAR(ROUND((data.bytes + temp.bytes + log.bytes + control.bytes) / POWER(10,12), 3), '999,999,990.000'))||' TB'
FROM db, data, temp, log, control
UNION ALL
SELECT 'Datafiles:', data.files||' (on '||data.tablespaces||' tablespaces)' FROM data
UNION ALL
SELECT 'Database configuration:', rac.db_type FROM rac
UNION ALL
SELECT 'Database memory:',
CASE WHEN mem.target > 0 THEN 'MEMORY '||TRIM(TO_CHAR(ROUND(mem.target / POWER(2,30), 1), '999,990.0'))||' GB, ' END||
CASE WHEN sga.target > 0 THEN 'SGA ' ||TRIM(TO_CHAR(ROUND(sga.target / POWER(2,30), 1), '999,990.0'))||' GB, ' END||
CASE WHEN pga.target > 0 THEN 'PGA ' ||TRIM(TO_CHAR(ROUND(pga.target / POWER(2,30), 1), '999,990.0'))||' GB, ' END||
CASE WHEN mem.target > 0 THEN 'AMM' ELSE CASE WHEN sga.target > 0 THEN 'ASMM' ELSE 'MANUAL' END END
FROM mem, sga, pga
UNION ALL
SELECT 'Hardware:', CASE WHEN cell.cnt > 0 THEN 'Engineered System '||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%5675%' THEN 'X2-2 ' END||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%2690%' THEN 'X3-2 ' END||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%2697%' THEN 'X4-2 ' END||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%2699%' THEN 'X5-2 ' END||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%8870%' THEN 'X3-8 ' END||
CASE WHEN 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' LIKE '%8895%' THEN 'X4-8 or X5-8 ' END||
'with '||cell.cnt||' storage servers'
ELSE 'Unknown' END FROM cell
UNION ALL
SELECT 'Processor:', 'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz' FROM DUAL
UNION ALL
SELECT 'Physical CPUs:', core.cnt||' cores'||CASE WHEN rac.instances > 0 THEN ', on '||rac.db_type END FROM rac, core
UNION ALL
SELECT 'Oracle CPUs:', cpu.cnt||' CPUs (threads)'||CASE WHEN rac.instances > 0 THEN ', on '||rac.db_type END FROM rac, cpu
UNION ALL
SELECT 'Physical RAM:', TRIM(TO_CHAR(ROUND(pmem.bytes / POWER(2,30), 1), '999,990.0'))||' GB'||CASE WHEN rac.instances > 0 THEN ', on '||rac.db_type END FROM
rac, pmem
UNION ALL
SELECT 'Operating system:', db.platform_name FROM db;
prompt**=====================================================================================================**
prompt** **Check Database component status**
prompt**=====================================================================================================**
set line 200;
set pagesize 9999;
col COMP_ID format a15;
col COMP_NAME format a35;
select COMP_ID,COMP_NAME,STATUS, VERSION, modified from dba_registry;
prompt**=====================================================================================================**
prompt** : Contents of DB Registry History :
prompt**=====================================================================================================**
SET LINESIZE 200
COLUMN action_time FORMAT A20
COLUMN action FORMAT A20
COLUMN namespace FORMAT A20
COLUMN version FORMAT A10
COLUMN comments FORMAT A30
COLUMN bundle_series FORMAT A10
SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
action,
namespace,
version,
id,
comments,
bundle_series
FROM sys.registry$history
ORDER by action_time;
prompt**=================================================================================================================================**
prompt** ** LISTENER Status **
prompt**=================================================================================================================================**
show parameter listener
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Standby Gap |
PROMPT | This is a RAC aware script |
PROMPT | Description: This script identifies gap in STBY replication if any |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
select to_char(sysdate,'mm-dd-yyyy hh24:mi:ss') "Current Time" from dual;
SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP ,
(case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or
(APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or
((LOG_ARCHIVED-LOG_APPLIED) > 5))
then 'Error! Log Gap is '
else 'OK!'
end) Status
FROM
(
SELECT INSTANCE_NAME DB_NAME
FROM GV$INSTANCE
where INST_ID = 1
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=1
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
)
UNION
SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP,
(case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or
(APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or
((LOG_ARCHIVED-LOG_APPLIED) > 5))
then 'Error! Log Gap is '
else 'OK!'
end) Status
from (
SELECT INSTANCE_NAME DB_NAME
FROM GV$INSTANCE
where INST_ID = 2
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=2
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2
);
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : RMAN BACKUP DETAILS FOR LAST 7 DAY |
PROMPT | This is a RAC aware script |
PROMPT | Description: This section shows details on RMAN backups |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-8
order by j.start_time;
PROMPT +------------------------------------------------------------------------+
PROMPT | FRA Usage details |
PROMPT | Desc: FRA Usage |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
SELECT NAME,
(SPACE_LIMIT / 1024 / 1024 / 1024) SPACE_LIMIT_GB,
((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE) / 1024 / 1024 / 1024) AS SPACE_AVAILABLE_GB,
ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;
select * from v$flash_recovery_area_usage;
PROMPT +------------------------------------------------------------------------+
PROMPT | Tablespace stats |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (Mb)",
SUM(fs.bytes) / (1024 * 1024) "Free (Mb)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
PROMPT +------------------------------------------------------------------------+
PROMPT | ASM Usage stats |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
prompt**====================================================================================================================================**
prompt** ** Check SGA Utilization and Other Memory Allocation **
prompt**====================================================================================================================================**
col COMPONENT format a26
select component,
current_size/1024/1024 "CURRENT_MB",
min_size/1024/1024 "MIN_MB",
user_specified_size/1024/1024 "USER_SPEC_MB",
last_oper_type "TYPE"
from v$sga_dynamic_components
/
SELECT ROUND (used.bytes / 1024 / 1024 / 1024, 2) sga_used_mb,
ROUND (free.bytes / 1024 / 1024 / 1024, 2) sga_free_mb,
ROUND (tot.bytes / 1024 / 1024 / 1024, 2) sga_total_mb
FROM (SELECT SUM (bytes) bytes
FROM v$sgastat
WHERE name != 'free memory') used,
(SELECT SUM (bytes) bytes
FROM v$sgastat
WHERE name = 'free memory') free,
(SELECT SUM (bytes) bytes FROM v$sgastat) tot;
PROMPT +------------------------------------------------------------------------+
PROMPT | Running Jobs |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
SELECT
*
FROM dba_jobs_running
ORDER BY
job;
--PROMPT +------------------------------------------------------------------------+
--PROMPT | DR Standby database status |
--PROMPT | Instance : ¤t_instance |
--PROMPT +------------------------------------------------------------------------|
--Col APPLIED_TIME format a20
-- Col destination format a20
--Col Status format a20
--SELECT DB_NAME,destination, APPLIED_TIME, LOG_APPLIED,LOG_ARCHIVED,
--(
-- CASE
-- WHEN ((APPLIED_TIME IS NOT NULL AND (LOG_ARCHIVED-LOG_APPLIED) IS NULL)
-- OR (APPLIED_TIME IS NULL AND (LOG_ARCHIVED-LOG_APPLIED) IS NOT NULL)
-- OR ((LOG_ARCHIVED-LOG_APPLIED) > 1))
-- THEN 'Error! Log Gap is '
-- ELSE 'OK!'
-- END) Status,
-- LOG_ARCHIVED-LOG_APPLIED LOG_GAP
--FROM
--( SELECT INSTANCE_NAME DB_NAME FROM GV$INSTANCE WHERE INST_ID = 1 ),
-- (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 ),
-- (select applied_seq# as LOG_APPLIED,destination as destination from v$archive_dest_status WHERE DEST_ID=3 ),
--(SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=1 );
prompt**===================================================================================================================================**
prompt** ** SYSAUX tablespace occupant information. **
prompt**===================================================================================================================================**
select occupant_desc, space_usage_kbytes/1024 MB from v$sysaux_occupants where space_usage_kbytes > 0 order by space_usage_kbytes;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : LONG OPS |
PROMPT | This is RAC aware script |
PROMPT | Description: This view displays the status of various operations that |
PROMPT | run for longer than 6 seconds (in absolute time). These operations |
PROMPT | currently include many backup and recovery functions, statistics gather|
prompt | , and query execution, and more operations are added for every OracleRE|
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
set lines 120
cle bre
col sid form 99999
col start_time head "Start|Time" form a12 trunc
col opname head "Operation" form a12 trunc
col target head "Object" form a30 trunc
col totalwork head "Total|Work" form 9999999999 trunc
col Sofar head "Sofar" form 9999999999 trunc
col elamin head "Elapsed|Time|(Mins)" form 99999999 trunc
col tre head "Time|Remain|(Mins)" form 999999999 trunc
select sid,serial#,to_char(start_time,'dd-mon:hh24:mi') start_time,
opname,target,totalwork,sofar,(elapsed_Seconds/60) elamin,
time_remaining tre
from v$session_longops
where totalwork <> SOFAR
order by 9 desc;
/
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : IN-FLIGHT TRANSACTION |
PROMPT | This is RAC aware script |
PROMPT | Desc: This output gives a glimpse of what all running/waiting in DB |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
set linesize 400 pagesize 400
select x.inst_id,x.sid
,x.serial#
,x.username
,x.sql_id
,plan_hash_value
,sqlarea.DISK_READS
,sqlarea.BUFFER_GETS
,sqlarea.ROWS_PROCESSED
,x.event
,x.osuser
,x.status
,x.BLOCKING_SESSION_STATUS
,x.BLOCKING_INSTANCE
,x.BLOCKING_SESSION
,x.process
,x.machine
,x.OSUSER
,x.program
,x.module
,x.action
,TO_CHAR(x.LOGON_TIME, 'MM-DD-YYYY HH24:MI:SS') logontime
,x.LAST_CALL_ET
--,x.BLOCKING_SESSION_STATUS
,x.SECONDS_IN_WAIT
,x.state
,sql_text,
ltrim(to_char(floor(x.LAST_CALL_ET/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(x.LAST_CALL_ET, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(x.LAST_CALL_ET, 60), '09')) RUNNING_SINCE
from gv$sqlarea sqlarea
,gv$session x
where x.sql_hash_value = sqlarea.hash_value
and x.sql_address = sqlarea.address
and sql_text not like '%select x.inst_id,x.sid ,x.serial# ,x.username ,x.sql_id ,plan_hash_value ,sqlarea.DISK_READS%'
and x.status='ACTIVE'
and x.USERNAME is not null
and x.SQL_ADDRESS = sqlarea.ADDRESS
and x.SQL_HASH_VALUE = sqlarea.HASH_VALUE
order by RUNNING_SINCE desc;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Archive generation per hour basis |
PROMPT | This is RAC aware script |
PROMPT | Desc: This will give an idea about any spike in redo activity or DMLs |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
set linesize 140
set pagesize 1000
col ARCHIVED format a8
col ins format 99 heading "DB"
col member format a80
col status format a12
col archive_date format a20
col member format a60
col type format a10
col group# format 99999999
col min_archive_interval format a20
col max_archive_interval format a20
col h00 heading "H00" format a3
col h01 heading "H01" format a3
col h02 heading "H02" format a3
col h03 heading "H03" format a3
col h04 heading "H04" format a3
col h05 heading "H05" format a3
col h06 heading "H06" format a3
col h07 heading "H07" format a3
col h08 heading "H08" format a3
col h09 heading "H09" format a3
col h10 heading "H10" format a3
col h11 heading "H11" format a3
col h12 heading "H12" format a3
col h13 heading "H13" format a3
col h14 heading "H14" format a3
col h15 heading "H15" format a3
col h16 heading "H16" format a3
col h17 heading "H17" format a3
col h18 heading "H18" format a3
col h19 heading "H19" format a3
col h20 heading "H20" format a3
col h21 heading "H21" format a3
col h22 heading "H22" format a3
col h23 heading "H23" format a3
col total format a6
col date format a10
select * from v$logfile order by group#;
select * from v$log order by SEQUENCE#;
select max( sequence#) last_sequence, max(completion_time) completion_time, max(block_size) block_size from v$archived_log ;
SELECT instance ins,
log_date "DATE" ,
lpad(to_char(NVL( COUNT( * ) , 0 )),6,' ') Total,
lpad(to_char(NVL( SUM( decode( log_hour , '00' , 1 ) ) , 0 )),3,' ') h00 ,
lpad(to_char(NVL( SUM( decode( log_hour , '01' , 1 ) ) , 0 )),3,' ') h01 ,
lpad(to_char(NVL( SUM( decode( log_hour , '02' , 1 ) ) , 0 )),3,' ') h02 ,
lpad(to_char(NVL( SUM( decode( log_hour , '03' , 1 ) ) , 0 )),3,' ') h03 ,
lpad(to_char(NVL( SUM( decode( log_hour , '04' , 1 ) ) , 0 )),3,' ') h04 ,
lpad(to_char(NVL( SUM( decode( log_hour , '05' , 1 ) ) , 0 )),3,' ') h05 ,
lpad(to_char(NVL( SUM( decode( log_hour , '06' , 1 ) ) , 0 )),3,' ') h06 ,
lpad(to_char(NVL( SUM( decode( log_hour , '07' , 1 ) ) , 0 )),3,' ') h07 ,
lpad(to_char(NVL( SUM( decode( log_hour , '08' , 1 ) ) , 0 )),3,' ') h08 ,
lpad(to_char(NVL( SUM( decode( log_hour , '09' , 1 ) ) , 0 )),3,' ') h09 ,
lpad(to_char(NVL( SUM( decode( log_hour , '10' , 1 ) ) , 0 )),3,' ') h10 ,
lpad(to_char(NVL( SUM( decode( log_hour , '11' , 1 ) ) , 0 )),3,' ') h11 ,
lpad(to_char(NVL( SUM( decode( log_hour , '12' , 1 ) ) , 0 )),3,' ') h12 ,
lpad(to_char(NVL( SUM( decode( log_hour , '13' , 1 ) ) , 0 )),3,' ') h13 ,
lpad(to_char(NVL( SUM( decode( log_hour , '14' , 1 ) ) , 0 )),3,' ') h14 ,
lpad(to_char(NVL( SUM( decode( log_hour , '15' , 1 ) ) , 0 )),3,' ') h15 ,
lpad(to_char(NVL( SUM( decode( log_hour , '16' , 1 ) ) , 0 )),3,' ') h16 ,
lpad(to_char(NVL( SUM( decode( log_hour , '17' , 1 ) ) , 0 )),3,' ') h17 ,
lpad(to_char(NVL( SUM( decode( log_hour , '18' , 1 ) ) , 0 )),3,' ') h18 ,
lpad(to_char(NVL( SUM( decode( log_hour , '19' , 1 ) ) , 0 )),3,' ') h19 ,
lpad(to_char(NVL( SUM( decode( log_hour , '20' , 1 ) ) , 0 )),3,' ') h20 ,
lpad(to_char(NVL( SUM( decode( log_hour , '21' , 1 ) ) , 0 )),3,' ') h21 ,
lpad(to_char(NVL( SUM( decode( log_hour , '22' , 1 ) ) , 0 )),3,' ') h22 ,
lpad(to_char(NVL( SUM( decode( log_hour , '23' , 1 ) ) , 0 )),3,' ') h23
FROM (
SELECT thread# INSTANCE ,
TO_CHAR( first_time , 'YYYY-MM-DD' ) log_date ,
TO_CHAR( first_time , 'hh24' ) log_hour
FROM v$log_history
)
GROUP BY
instance,log_date
ORDER BY
log_date ;
select trunc(min(completion_time - first_time))||' Day '||
to_char(trunc(sysdate,'dd') + min(completion_time - first_time),'hh24:mm:ss')||chr(10) min_archive_interval,
trunc(max(completion_time - first_time))||' Day '||
to_char(trunc(sysdate,'dd') + max(completion_time - first_time),'hh24:mm:ss')||chr(10) max_archive_interval
from gv$archived_log
where sequence# <> ( select max(sequence#) from gv$archived_log ) ;
set feedback on
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : SESSION DETAILS |
PROMPT | This is RAC aware script |
PROMPT | Desc: Shows details about all sessions and their states active, inactiv|
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
set linesize 400 pagesize 400
select resource_name, current_utilization, max_utilization, limit_value, inst_id
from gv$resource_limit
where resource_name in ('sessions', 'processes');
select count(s.status) INACTIVE_SESSIONS
from gv$session s, gv$process p
where
p.addr=s.paddr and
s.status='INACTIVE';
select count(s.status) "INACTIVE SESSIONS > 3HOURS "
from gv$session s, gv$process p
where
p.addr=s.paddr and
s.last_call_et > 10800 and
s.status='INACTIVE';
select count(s.status) ACTIVE_SESSIONS
from gv$session s, gv$process p
where
p.addr=s.paddr and
s.status='ACTIVE';
select s.program,count(s.program) Inactive_Sessions_from_1Hour
from gv$session s,gv$process p
where p.addr=s.paddr AND
s.status='INACTIVE'
and s.last_call_et > (10800)
group by s.program
order by 2 desc;
set linesize 400 pagesize 400
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et_Hrs for 9999.99
col sql_hash_value for 9999999999999
col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.serial#,s.last_call_et/3600 last_call_et_3Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.last_call_et > (10800)
order by last_call_et;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Sequence exhaustion for more than 90 percent |
PROMPT | This is RAC aware script |
PROMPT | Desc: Shows details about all sequences used more than 90 percent |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SELECT
ROUND(100 * (s.last_number - s.min_value) / GREATEST((s.max_value - s.min_value), 1), 1) percent_used, /* latest change */
s.*
from dba_sequences s
where
s.sequence_owner not in ('ANONYMOUS','APEX_030200','APEX_040000','APEX_SSO','APPQOSSYS','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','MDSYS','OLAPSYS','
ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS')
and s.sequence_owner not in ('SI_INFORMTN_SCHEMA','SQLTXADMIN','SQLTXPLAIN','SYS','SYSMAN','SYSTEM','TRCANLZR','WMSYS','XDB','XS$NULL','PERFSTAT','STDBYPERF'
,'MGDSYS','OJVMSYS')
and s.max_value > 0
and ROUND(100 * (s.last_number - s.min_value) / GREATEST((s.max_value - s.min_value), 1), 1) > 90
order by
ROUND(100 * (s.last_number - s.min_value) / GREATEST((s.max_value - s.min_value), 1), 1) DESC, /* latest change */
s.sequence_owner, s.sequence_name;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : ORA errors reported in alert log of databases, SYSDATE-1 |
PROMPT | This is RAC aware script |
PROMPT | Desc: Shows all alert log ora errors and log files with locations |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
select TO_CHAR(A.ORIGINATING_TIMESTAMP, 'dd.mm.yyyy hh24:mi:ss') MESSAGE_TIME
,inst_id, message_text
,host_id
,inst_id
,adr_home
from v$DIAG_ALERT_EXT A
where A.ORIGINATING_TIMESTAMP > sysdate-1
and component_id='rdbms'
and message_text like '%ORA-%'
order by 1 desc;
PROMPT +##############################################################################################################################################
PROMPT +##############################################################################################################################################
PROMPT +#############################################################################################################################################
PROMPT +################################################################## PART - 2 ##################################################################
PROMPT +##############################################################################################################################################
PROMPT +##############################################################################################################################################
ttitle center 'PDHC1.4 - A Quick Health Check --- PART 2' skip 2
btitle center '<span style="background-color:#c90421;color:#ffffff;border:1px solid black;">PART - 2</span>'
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Current waits and counts |
PROMPT | This is RAC aware script |
PROMPT | Desc: This script shows what all sessions waits currently their count|
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
select event, state, inst_id, count(*) from gv$session_wait group by event, state, inst_id order by 4 desc;
set numwidth 10
column event format a25 tru
select inst_id, event, time_waited, total_waits, total_timeouts
from (select inst_id, event, time_waited, total_waits, total_timeouts
from gv$system_event where event not in ('rdbms ipc message','smon timer',
'pmon timer', 'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
order by time_waited desc)
where rownum < 11
order by time_waited desc;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Load Profile of any database |
PROMPT | This is not RAC aware script |
PROMPT | Description: This section contains same stats what you will see anytime|
PROMPT | in AWR of database. Few of the imp sections are |
PROMPT | DB Block Changes Per Txn, Average Active Sessions, Executions Per Sec |
PROMPT | User Calls Per Sec, Physical Writes Per Sec, Physical Reads Per Txn etc|
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SELECT
metric_name, inst_id
, ROUND(value,2) w_metric_value
, metric_unit
FROM
gv$sysmetric
WHERE
metric_name IN (
'Average Active Sessions'
, 'Average Synchronous Single-Block Read Latency'
, 'CPU Usage Per Sec'
, 'Background CPU Usage Per Sec'
, 'DB Block Changes Per Txn'
, 'Executions Per Sec'
, 'Host CPU Usage Per Sec'
, 'I/O Megabytes per Second'
, 'I/O Requests per Second'
, 'Logical Reads Per Txn'
, 'Logons Per Sec'
, 'Network Traffic Volume Per Sec'
, 'Physical Reads Per Sec'
, 'Physical Reads Per Txn'
, 'Physical Writes Per Sec'
, 'Redo Generated Per Sec'
, 'Redo Generated Per Txn'
, 'Response Time Per Txn'
, 'SQL Service Response Time'
, 'Total Parse Count Per Txn'
, 'User Calls Per Sec'
, 'User Transaction Per Sec'
)
AND group_id = 2 -- get last 60 sec metrics
ORDER BY
metric_name, inst_id
/
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Undo usage report |
PROMPT | This is RAC aware script |
PROMPT | Desc: This shows details about all undo rollback segments, best for 01555|
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------|
SET LINESIZE 200
COLUMN username FORMAT A15
SELECT s.inst_id,
s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM gv$transaction t,
gv$session s,
gv$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND s.inst_id = t.inst_id
AND t.xidusn = r.usn
AND t.inst_id = r.inst_id
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;
SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(20);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
DBMS_OUTPUT.PUT_LINE('--------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
IF V_UNDO_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize
);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;
SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Who is doing what with TEMP segments or tablespace |
PROMPT | This is RAC aware script |
PROMPT | Desc: Look for cols usage_mb and sql_id and sql_text and username |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SELECT sysdate "TIME_STAMP", vsu.username, vs.sid, vp.spid, vs.sql_id, vst.sql_text, vsu.tablespace,
sum_blocks*dt.block_size/1024/1024 usage_mb
FROM
(
SELECT username, sqladdr, sqlhash, sql_id, tablespace, session_addr,
-- sum(blocks)*8192/1024/1024 "USAGE_MB",
sum(blocks) sum_blocks
FROM gv$sort_usage
HAVING SUM(blocks)> 1000
GROUP BY username, sqladdr, sqlhash, sql_id, tablespace, session_addr
) "VSU",
gv$sqltext vst,
gv$session vs,
gv$process vp,
dba_tablespaces dt
WHERE vs.sql_id = vst.sql_id
-- AND vsu.sqladdr = vst.address
-- AND vsu.sqlhash = vst.hash_value
AND vsu.session_addr = vs.saddr
AND vs.paddr = vp.addr
AND vst.piece = 0
AND dt.tablespace_name = vsu.tablespace
order by usage_mb;
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set linesize 400 pagesize 400
SET TERMOUT ON;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Temp or Sort segment usage |
PROMPT | This is RAC aware script |
PROMPT | Desc: Queies consuming huge sort area from last 2 hrs and more than 5GB| |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select sql_id,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig
from DBA_HIST_ACTIVE_SESS_HISTORY
where
sample_time > sysdate - (120/1440) and
TEMP_SPACE_ALLOCATED > (5*1024*1024*1024)
group by sql_id order by gig desc;
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Sesions Waiting |
PROMPT | Desc: The entries that are shown at the top are the sessions that have |
PROMPT | waited the longest amount of time that are waiting for non-idle wait |
PROMPT | events (event column).
PROMPT | This is RAC aware script |
PROMPT +------------------------------------------------------------------------+
set numwidth 15
set heading on
column state format a7 tru
column event format a25 tru
column last_sql format a40 tru
select sw.inst_id, sa.sql_id,sw.sid, sw.state, sw.event, sw.seconds_in_wait seconds,
sw.p1, sw.p2, sw.p3, sa.sql_text last_sql
from gv$session_wait sw, gv$session s, gv$sqlarea sa
where sw.event not in
('rdbms ipc message','smon timer','pmon timer',
'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
and sw.seconds_in_wait > 0
and (sw.inst_id = s.inst_id and sw.sid = s.sid)
and (s.inst_id = sa.inst_id and s.sql_address = sa.address)