/
plancap_collector.sql
109 lines (92 loc) · 2.6 KB
/
plancap_collector.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
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
CREATE OR REPLACE PACKAGE plancap_collector AS
PROCEDURE snap_data_files;
PROCEDURE snap_free_space;
PROCEDURE snap_segment_space;
PROCEDURE snap_service_stats;
END plancap_collector;
/
CREATE OR REPLACE PACKAGE BODY plancap_collector AS
PROCEDURE snap_data_files AS
BEGIN
INSERT INTO plancap$data_files
SELECT
sysdate
, tablespace_name
, file_id
, SUM(bytes)
, SUM(blocks)
, relative_fno
FROM
dba_data_files
GROUP BY
sysdate, tablespace_name, file_id, relative_fno;
COMMIT;
END snap_data_files;
PROCEDURE snap_free_space AS
BEGIN
INSERT INTO plancap$free_space
SELECT
sysdate
, tablespace_name
, file_id
, SUM(bytes)
, SUM(blocks)
, relative_fno
FROM
dba_free_space
GROUP BY
sysdate, tablespace_name, file_id, relative_fno;
COMMIT;
END snap_free_space;
PROCEDURE snap_segment_space AS
BEGIN
INSERT INTO plancap$segment_space
SELECT
sysdate
, owner
, segment_name
, NVL(partition_name,'-') partition_name
, segment_type
, tablespace_name
, SUM(bytes) bytes
, SUM(blocks) blocks
, SUM(extents) extents
FROM
dba_segments
GROUP BY
sysdate, owner, segment_name, NVL(partition_name,'-'), tablespace_name, segment_type;
COMMIT;
END snap_segment_space;
PROCEDURE snap_service_stats AS
BEGIN
INSERT INTO plancap$service_stats
WITH sq AS (
SELECT service_name,stat_name,value FROM v$service_stats
)
SELECT
sysdate
, a.service_name
, a.value DB_CPU
, b.value EXECUTE_COUNT
, c.value USER_COMMITS
, d.value USER_CALLS
FROM
sq a
, sq b
, sq c
, sq d
WHERE
a.service_name = b.service_name
AND b.service_name = c.service_name
AND c.service_name = d.service_name
AND a.stat_name = 'DB CPU'
AND b.stat_name = 'execute count'
AND c.stat_name = 'user commits'
AND d.stat_name = 'user calls';
COMMIT;
END;
END plancap_collector;
/
SHOW ERR