1 package dbms_monitor is
2 ------------
3 -- OVERVIEW
4 --
5 -- This package provides database monitoring functionality, initially
6 -- in the area of statistics aggregation and SQL tracing
7
8 -- SECURITY
9 --
10 -- runs with SYS privileges.
11
12 -- CONSTANTS to be used as OPTIONS for various procedures
13 -- refer comments with procedure(s) for more detail
14
15 all_modules CONSTANT VARCHAR2(14) := '###ALL_MODULES';
16 all_actions CONSTANT VARCHAR2(14) := '###ALL_ACTIONS';
17
18 -- Indicates that tracing/aggregation for a given module should be enabled
19 -- for all actions
20
21 ----------------------------
22
23 ----------------------------
24 -- PROCEDURES AND FUNCTIONS
25 --
26 PROCEDURE client_id_stat_enable(
27 client_id IN VARCHAR2);
28
29 -- Enables statistics aggregation for the given Client ID
30 -- Input arguments:
31 -- client_id - Client Identifier for which the statistics
32 -- colection is enabled
33
34 PROCEDURE client_id_stat_disable(
35 client_id IN VARCHAR2);
36
37 -- Disables statistics aggregation for the given Client ID
38 -- Input arguments:
39 -- client_id - Client Identifier for which the statistics
40 -- colection is disabled
41
42 PROCEDURE serv_mod_act_stat_enable(
43 service_name IN VARCHAR2,
44 module_name IN VARCHAR2,
45 action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);
46
47 -- Enables statistics aggregation for the given service/module/action
48 -- Input arguments:
49 -- service_name - Service Name for which the statistics
50 -- colection is enabled
51 -- module_name - Module Name for which the statistics
52 -- colection is enabled
53 -- action_name - Action Name for which the statistics
54 -- colection is enabled. The name is optional.
55 -- if omitted, statistic aggregation is enabled
56 -- for all actions in a given module
57
58 PROCEDURE serv_mod_act_stat_disable(
59 service_name IN VARCHAR2,
60 module_name IN VARCHAR2,
61 action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);
62
63 -- Disables statistics aggregation for the given service/module/action
64 -- Input arguments:
65 -- service_name - Service Name for which the statistics
66 -- colection is disabled
67 -- module_name - Module Name for which the statistics
68 -- colection is disabled
69 -- action_name - Action Name for which the statistics
70 -- colection is disabled. The name is optional.
71 -- if omitted, statistic aggregation is disabled
72 -- for all actions in a given module
73
74 PROCEDURE client_id_trace_enable(
75 client_id IN VARCHAR2,
76 waits IN BOOLEAN DEFAULT TRUE,
77 binds IN BOOLEAN DEFAULT FALSE,
78 plan_stat IN VARCHAR2 DEFAULT NULL);
79
80 -- Enables SQL for the given Client ID
81 -- Input arguments:
82 -- client_id - Client Identifier for which SQL trace
83 -- is enabled
84 -- waits - If TRUE, wait information will be present in the
85 -- the trace
86 -- binds - If TRUE, bind information will be present in the
87 -- the trace
88 -- plan_stat - Frequency at which we dump row source statistics.
89 -- Value should be 'never', 'first_execution'
90 -- (equivalent to NULL) or 'all_executions'.
91
92 PROCEDURE client_id_trace_disable(
93 client_id IN VARCHAR2);
94
95 -- Disables SQL trace for the given Client ID
96 -- Input arguments:
97 -- client_id - Client Identifier for which SQL trace
98 -- is disabled
99
100 PROCEDURE serv_mod_act_trace_enable(
101 service_name IN VARCHAR2,
102 module_name IN VARCHAR2 DEFAULT ALL_MODULES,
103 action_name IN VARCHAR2 DEFAULT ALL_ACTIONS,
104 waits IN BOOLEAN DEFAULT TRUE,
105 binds IN BOOLEAN DEFAULT FALSE,
106 instance_name IN VARCHAR2 DEFAULT NULL,
107 plan_stat IN VARCHAR2 DEFAULT NULL);
108
109 -- Enables SQL trace for the given service/module/action
110 -- Input arguments:
111 -- service_name - Service Name for which SQL trace
112 -- is enabled
113 -- module_name - Module Name for which SQL trace
114 -- is enabled. The name is optional.
115 -- if omitted, SQL trace is enabled
116 -- for all modules and actions actions in a given
117 -- service
118 -- action_name - Action Name for which SQL trace
119 -- is enabled. The name is optional.
120 -- if omitted, SQL trace is enabled
121 -- for all actions in a given module
122 -- waits - If TRUE, wait information will be present in the
123 -- the trace
124 -- binds - If TRUE, bind information will be present in the
125 -- the trace
126 -- instance_name - if set, restricts tracing to the named instance
127 -- plan_stat - Frequency at which we dump row source statistics.
128 -- Value should be 'never', 'first_execution'
129 -- (equivalent to NULL) or 'all_executions'.
130
131 PROCEDURE serv_mod_act_trace_disable(
132 service_name IN VARCHAR2,
133 module_name IN VARCHAR2 DEFAULT ALL_MODULES,
134 action_name IN VARCHAR2 DEFAULT ALL_ACTIONS,
135 instance_name IN VARCHAR2 DEFAULT NULL);
136
137 -- Disables SQL trace for the given service/module/action
138 -- Input arguments:
139 -- service_name - Service Name for which SQL trace
140 -- is disabled
141 -- module_name - Module Name for which SQL trace
142 -- is disabled. The name is optional.
143 -- if omitted, SQL trace is disabled
144 -- for all modules and actions actions in a given
145 -- action_name - Action Name for which SQL trace
146 -- is disabled. The name is optional.
147 -- if omitted, SQL trace is disabled
148 -- for all actions in a given module
149 -- the trace
150 -- instance_name - if set, restricts disabling to the named instance
151
152 PROCEDURE session_trace_enable(
153 session_id IN BINARY_INTEGER DEFAULT NULL,
154 serial_num IN BINARY_INTEGER DEFAULT NULL,
155 waits IN BOOLEAN DEFAULT TRUE,
156 binds IN BOOLEAN DEFAULT FALSE,
157 plan_stat IN VARCHAR2 DEFAULT NULL);
158
159 -- Enables SQL trace for the given Session ID
160 -- Input arguments:
161 -- session_id - Session Identifier for which SQL trace
162 -- is enabled. If omitted (or NULL), the
163 -- user's own session is assumed
164 -- serial_num - Session serial number for which SQL trace
165 -- is enabled. If omitted (or NULL), only
166 -- the session ID is used to determine a session
167 -- waits - If TRUE, wait information will be present in the
168 -- the trace
169 -- binds - If TRUE, bind information will be present in the
170 -- the trace
171 -- plan_stat - Frequency at which we dump row source statistics.
172 -- Value should be 'never', 'first_execution'
173 -- (equivalent to NULL) or 'all_executions'.
174
175 PROCEDURE session_trace_disable(
176 session_id IN BINARY_INTEGER DEFAULT NULL,
177 serial_num IN BINARY_INTEGER DEFAULT NULL);
178
179 -- Disables SQL trace for the given Session ID
180 -- Input arguments:
181 -- session_id - Session Identifier for which SQL trace
182 -- is disabled
183 -- serial_num - Session serial number for which SQL trace
184 -- is disabled
185
186 PROCEDURE database_trace_enable(
187 waits IN BOOLEAN DEFAULT TRUE,
188 binds IN BOOLEAN DEFAULT FALSE,
189 instance_name IN VARCHAR2 DEFAULT NULL,
190 plan_stat IN VARCHAR2 DEFAULT NULL);
191
192 -- Enables SQL trace for the whole database or given instance
193 -- Input arguments:
194 -- waits - If TRUE, wait information will be present in the
195 -- the trace
196 -- binds - If TRUE, bind information will be present in the
197 -- the trace
198 -- instance_name - if set, restricts tracing to the named instance
199 -- plan_stat - Frequency at which we dump row source statistics.
200 -- Value should be 'never', 'first_execution'
201 -- (equivalent to NULL) or 'all_executions'.
202
203
204 PROCEDURE database_trace_disable(
205 instance_name IN VARCHAR2 DEFAULT NULL);
206
207 -- Disables SQL trace for the whole database or given instance
208 -- Input arguments:
209 -- instance_name - if set, restricts disabling to the named instance
210 end;