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
|
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET VER OFF
set LINESIZE 200
SET FEEDBACK OFF
COLUMN INST_NO FORMAT 99 HEADING 'INSNO'
COLUMN SNAP_ID FORMAT 99999
COLUMN "DB time" FORMAT 9999
COLUMN "DB CPU" FORMAT 9999
COLUMN Redo FORMAT 9999
COLUMN LogicalR FORMAT 9999
COLUMN BlockChs FORMAT 9999
COLUMN Phyr FORMAT 9999
COLUMN Phyw FORMAT 9999
COLUMN Calls FORMAT 9999
COLUMN Parses FORMAT 9999
COLUMN HParses FORMAT 9999
COLUMN Logons FORMAT 9999
COLUMN Execs FORMAT 9999
COLUMN Rbacks FORMAT 9999
COLUMN Trans FORMAT 9999
PROMPT
BREAK ON INST_NO skip page
ACCEPT DAYS NUMBER PROMPT "ENTER latest snap days: " default 7
SELECT INSTANCE_NUMBER INST_NO,
SNAP_ID,
TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') BEGIN_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > TRUNC(SYSDATE) - &DAYS
ORDER BY INSTANCE_NUMBER,BEGIN_INTERVAL_TIME;
PROMPT
ACCEPT INST_NO NUMBER PROMPT "ENTER INSTANCE_NUMBER: " default 0
ACCEPT BEGIN_SNAP NUMBER PROMPT "ENTER BEGIN_SNAP: "
ACCEPT END_SNAP NUMBER PROMPT "ENTER END_SNAP: "
PROMPT
PROMPT Listing load profile pct by instance no and snap id ...
SELECT *
FROM (SELECT INST_NO
,SNAP_ID
,SNAP_TIME
,STAT_NAME
,ROUND(RATIO_TO_REPORT(VALUE) OVER(PARTITION BY STAT_NAME) * 100,2) VALUE
FROM (SELECT TO_CHAR(HS.BEGIN_INTERVAL_TIME, 'mmdd hh24mi') SNAP_TIME,S.*
FROM (SELECT T.INSTANCE_NUMBER INST_NO
,T.SNAP_ID - 1 SNAP_ID
,STAT_NAME
,NVL(VALUE - LAG(VALUE)
OVER(PARTITION BY T.INSTANCE_NUMBER,STAT_NAME ORDER BY T.INSTANCE_NUMBER,T.SNAP_ID),0) VALUE
FROM DBA_HIST_SYS_TIME_MODEL T
WHERE INSTANCE_NUMBER= CASE WHEN &INST_NO = 0 THEN INSTANCE_NUMBER ELSE &INST_NO END
AND SNAP_ID BETWEEN &BEGIN_SNAP AND &END_SNAP
AND STAT_NAME IN ('DB time', 'DB CPU')
UNION ALL
SELECT T.INSTANCE_NUMBER INST_NO
,T.SNAP_ID - 1 SNAP_ID
,STAT_NAME
,NVL(VALUE - LAG(VALUE)
OVER(PARTITION BY T.INSTANCE_NUMBER
,STAT_NAME ORDER BY T.INSTANCE_NUMBER
,T.SNAP_ID)
,0) VALUE
FROM DBA_HIST_SYSSTAT T
WHERE INSTANCE_NUMBER= CASE WHEN &INST_NO = 0 THEN INSTANCE_NUMBER ELSE &INST_NO END
AND SNAP_ID BETWEEN &BEGIN_SNAP AND &END_SNAP
AND STAT_NAME IN ('redo size'
,'session logical reads'
,'db block changes'
,'physical reads'
,'physical writes'
,'user calls'
,'parse count (total)'
,'parse count (hard)'
,'logons cumulative'
,'execute count'
,'user rollbacks'
,'user commits')) S
,DBA_HIST_SNAPSHOT HS
WHERE S.SNAP_ID = HS.SNAP_ID
AND S.SNAP_ID != &BEGIN_SNAP-1))
PIVOT(SUM(VALUE)
FOR STAT_NAME IN('DB time' AS "DB time"
,'DB CPU' AS "DB CPU"
,'redo size' AS "Redo"
,'session logical reads' AS "LogicalR"
,'db block changes' AS "BlockChs"
,'physical reads' AS "Phyr"
,'physical writes' AS "Phyw"
,'user calls' AS "Calls"
,'parse count (total)' AS "Parses"
,'parse count (hard)' AS "HParses"
,'logons cumulative' AS "Logons"
,'execute count' AS "Execs"
,'user rollbacks' AS "Rbacks"
,'user commits' AS "Trans"))
ORDER BY INST_NO
,SNAP_TIME;
|
