1 PACKAGE dbms_perf AUTHID CURRENT_USER
2 AS
3
4
5 -- -------------------- report_perfhub_xml ------------------------------
6 -- NAME:
7 -- report_perfhub_xml
8 --
9 -- DESCRIPTION
10 -- retrieves perfhub XML for all tabs
11 -- should be used for generating active report
12 --
13 -- PARAMETERS
14 --
15 -- is_realtime (IN) - if 1 then realtime
16 -- if NULL (default) or 0, then historical mode
17 --
18 -- outer_start_time (IN) - start time of outer period shown in the
19 -- time selector
20 -- Only applicable if is_realtime=0
21 -- if NULL (default)
22 -- . is_realtime=0, 24 hours before outer_end_time
23 -- . is_realtime=1, this is ignored
24 --
25 -- outer_end_time (IN) - end time of outer period show in the time
26 -- selector
27 -- Only applicable if is_realtime=0
28 -- if NULL (default)
29 -- . is_realtime=0, latest AWR snapshot
30 -- . is_realtime=1, this is ignored
31 --
32 -- selected_start_time(IN) - time period of selection
33 -- if NULL (default)
34 -- . is_realtime=0, 1 hour before selected_end_time
35 -- . is_realtime=1, 5 minutes before selected_end_time
36 --
37 -- selected_end_time(IN) - end time of selection
38 -- if NULL (default)
39 -- . is_realtime=0, latest AWR snapshot
40 -- . is_realtime=1, current_time
41 --
42 -- only valid if duration is not set
43 --
44 -- inst_id (IN) - instance_id to retrieve data for
45 -- if -1 then current instance
46 -- if number is specified, then for that instance
47 -- if NULL (default) all instances
48 --
49 -- dbid (IN) - dbid to query (if NULL, then current dbid)
50 -- if is_realtime=1, dbid has to be the local dbid
51 --
52 -- monitor_list_detail (IN) - top N in sql monitor list to retrieve
53 -- sql monitor details
54 -- if NULL (default) will retrieve top 10
55 -- if 0, will not retrieve monitor list details
56 --
57 -- workload_sql_detail (IN) - top N in Workload Top SQL list to retrieve
58 -- sql details for
59 -- if NULL (default) will retrieve top 10
60 -- if 0, will not retrieve monitor list details
61 --
62 -- addm_task_detail (IN) - maximum N latest ADDM tasks to retrieve
63 -- if NULL (default), will retrieve available data
64 -- but no more than N
65 -- if 0, will not retrieve addm task details
66 --
67 -- compress_xml (IN) - if 1 or NULL (default) will compress xml
68 -- if 0 will not compress xml
69 --
70 -- report_reference (IN) - should be NULL when used from SQL*Plus
71 --
72 -- report_level (IN) - 'typical' will get all tabs in perfhub
73 --
74 -- base_path (IN) - the URL path for HTML resources since flex HTML
75 -- requires access to external files
76 --
77 FUNCTION report_perfhub_xml (
78 is_realtime IN number default null,
79 outer_start_time IN date default null,
80 outer_end_time IN date default null,
81 selected_start_time IN date default null,
82 selected_end_time IN date default null,
83 inst_id IN number default null,
84 dbid IN number default null,
85 monitor_list_detail IN number default null,
86 workload_sql_detail IN number default null,
87 addm_task_detail IN number default null,
88 compress_xml IN binary_integer default null,
89 report_reference IN varchar2 default null,
90 report_level IN varchar2 default null,
91 base_path IN varchar2 default null)
92 RETURN xmltype;
93
94 -- ---------------------------- report_perfhub ----------------------------
95 -- see arguments in report_perfhub_xml
96 --
97 -- type - report type
98 -- - 'active' (default)
99 -- - 'xml' - returns xml
100 --
101 FUNCTION report_perfhub(
102 is_realtime IN number default null,
103 outer_start_time IN date default null,
104 outer_end_time IN date default null,
105 selected_start_time IN date default null,
106 selected_end_time IN date default null,
107 inst_id IN number default null,
108 dbid IN number default null,
109 monitor_list_detail IN number default null,
110 workload_sql_detail IN number default null,
111 addm_task_detail IN number default null,
112 report_reference IN varchar2 default null,
113 report_level IN varchar2 default null,
114 type IN varchar2 default 'ACTIVE',
115 base_path IN varchar2 default null)
116 RETURN clob;
117
118
119 -- -------------------- report_sql_xml ------------------------------
120 -- NAME:
121 -- report_sql_xml
122 --
123 -- DESCRIPTION
124 -- retrieves SQL Details XML for all tabs
125 -- should be used for generating active report
126 --
127 -- PARAMETERS
128 -- sql_id (IN) - sql_id to retrieve performance for
129 -- if null, will get sql details for last executed
130 -- SQL statement
131 --
132 -- is_realtime (IN) - if 1 then realtime
133 -- if NULL (default) or 0, then historical mode
134 --
135 -- outer_start_time (IN) - start time of outer period shown in the
136 -- time selector
137 -- Only applicable if is_realtime=0
138 -- if NULL (default)
139 -- . is_realtime=0, 24 hours before outer_end_time
140 -- . is_realtime=1, this is ignored
141 --
142 -- outer_end_time (IN) - end time of outer period show in the time
143 -- selector
144 -- Only applicable if is_realtime=0
145 -- if NULL (default)
146 -- . is_realtime=0, latest AWR snapshot
147 -- . is_realtime=1, this is ignored
148 --
149 -- selected_start_time(IN) - time period of selection
150 -- if NULL (default)
151 -- . is_realtime=0, 1 hour before selected_end_time
152 -- . is_realtime=1, 5 minutes before selected_end_time
153 --
154 -- selected_end_time(IN) - end time of selection
155 -- if NULL (default)
156 -- . is_realtime=0, latest AWR snapshot
157 -- . is_realtime=1, current_time
158 --
159 -- only valid if duration is not set
160 --
161 -- inst_id (IN) - instance_id to retrieve data for
162 -- if -1 then current instance
163 -- if number is specified, then for that instance
164 -- if NULL (default) all instances
165 --
166 -- dbid (IN) - dbid to query (if NULL, then current dbid)
167 -- if is_realtime=1, dbid has to be the local dbid
168 --
169 -- monitor_list_detail (IN) - top N in sql monitor list to retrieve
170 -- sql monitor details
171 -- if NULL (default) will retrieve top 10
172 -- if 0, will not retrieve monitor list details
173 --
174 -- compress_xml (IN) - if 1 or NULL (default) will compress xml
175 -- if 0 will not compress
176 --
177 -- report_reference (IN) - should be NULL when used from SQL*Plus
178 --
179 -- report_level (IN) - 'typical' will get all tabs in perfhub
180 --
181 -- base_path (IN) - the URL path for HTML resources since flex HTML
182 -- requires access to external files
183 --
184 FUNCTION report_sql_xml (
185 sql_id IN varchar2 default null,
186 is_realtime IN number default null,
187 outer_start_time IN date default null,
188 outer_end_time IN date default null,
189 selected_start_time IN date default null,
190 selected_end_time IN date default null,
191 inst_id IN number default null,
192 dbid IN number default null,
193 monitor_list_detail IN number default null,
194 compress_xml IN binary_integer default null,
195 report_reference IN varchar2 default null,
196 report_level IN varchar2 default null,
197 base_path IN varchar2 default null)
198 RETURN xmltype;
199
200 -- ---------------------------- report_sql ----------------------------
201 -- see parameters in report_perfhub_xml
202 --
203 -- type - report type
204 -- - 'ACTIVE' (default)
205 -- - 'xml' - returns xml
206 --
207 FUNCTION report_sql(
208 sql_id IN varchar2 default null,
209 is_realtime IN number default null,
210 outer_start_time IN date default null,
211 outer_end_time IN date default null,
212 selected_start_time IN date default null,
213 selected_end_time IN date default null,
214 inst_id IN number default null,
215 dbid IN number default null,
216 monitor_list_detail IN number default null,
217 report_reference IN varchar2 default null,
218 report_level IN varchar2 default null,
219 type IN varchar2 default 'ACTIVE',
220 base_path IN varchar2 default null)
221 RETURN clob;
222
223 -- -------------------- report_session_xml ------------------------------
224 -- NAME:
225 -- report_ssession_xml
226 --
227 -- DESCRIPTION
228 -- retrieves Ssession Details XML for all tabs
229 -- should be used for generating active report
230 --
231 -- a session is identified by inst_id, sid, serial_num
232 -- if any of those parameters are missing, we will default to
233 -- the currently connected session
234 --
235 -- PARAMETERS
236 -- inst_id (IN) - instance_id to retrieve data for
237 -- if NULL (default), instance of current session
238 --
239 -- sid (IN) - sql_id to retrieve performance for
240 -- if null, will use current session
241 --
242 -- serial_num (IN) - serial# of session
243 -- if null, will use serial# of the specified sid
244 -- only if the session is still connected
245 --
246 -- is_realtime (IN) - if 1 then realtime
247 -- if NULL (default) or 0, then historical mode
248 --
249 -- outer_start_time (IN) - start time of outer period shown in the
250 -- time selector
251 -- Only applicable if is_realtime=0
255 --
252 -- if NULL (default)
253 -- . is_realtime=0, 24 hours before outer_end_time
254 -- . is_realtime=1, this is ignored
256 -- outer_end_time (IN) - end time of outer period show in the time
257 -- selector
258 -- Only applicable if is_realtime=0
259 -- if NULL (default)
260 -- . is_realtime=0, latest AWR snapshot
261 -- . is_realtime=1, this is ignored
262 --
263 -- selected_start_time(IN) - time period of selection
264 -- if NULL (default)
265 -- . is_realtime=0, 1 hour before selected_end_time
266 -- . is_realtime=1, 5 minutes before selected_end_time
267 --
268 -- selected_end_time(IN) - end time of selection
269 -- if NULL (default)
270 -- . is_realtime=0, latest AWR snapshot
271 -- . is_realtime=1, current_time
272 --
273 -- dbid (IN) - dbid to query (if NULL, then current dbid)
274 -- if is_realtime=1, dbid has to be the local dbid
275 --
276 -- monitor_list_detail (IN) - top N in sql monitor list to retrieve
277 -- sql monitor details
278 -- if NULL (default) will retrieve top 10
279 -- if 0, will not retrieve monitor list details
280 --
281 -- compress_xml (IN) - if 1 or NULL (default) will compress xml
282 -- if 0 will not compress
283 --
284 -- report_reference (IN) - should be NULL when used from SQL*Plus
285 --
286 -- report_level (IN) - 'typical' will get all tabs in perfhub
287 --
288 -- base_path (IN) - the URL path for HTML resources since flex HTML
289 -- requires access to external files
290 --
291 --
292 FUNCTION report_session_xml (
293 inst_id IN number default null,
294 sid IN number default null,
295 serial IN number default null,
296 is_realtime IN number default null,
297 outer_start_time IN date default null,
298 outer_end_time IN date default null,
299 selected_start_time IN date default null,
300 selected_end_time IN date default null,
301 dbid IN number default null,
302 monitor_list_detail IN number default null,
303 compress_xml IN binary_integer default null,
304 report_reference IN varchar2 default null,
305 report_level IN varchar2 default null,
306 base_path IN varchar2 default null)
307 RETURN xmltype;
308
309 -- ---------------------------- report_sql ----------------------------
310 -- see parameters in report_perfhub_xml
311 --
312 -- type - report type
313 -- - 'ACTIVE' (default)
314 -- - 'xml' - returns xml
315 --
316 FUNCTION report_session(
317 inst_id IN number default null,
318 sid IN number default null,
319 serial IN number default null,
320 is_realtime IN number default null,
321 outer_start_time IN date default null,
322 outer_end_time IN date default null,
323 selected_start_time IN date default null,
324 selected_end_time IN date default null,
325 dbid IN number default null,
326 monitor_list_detail IN number default null,
327 report_reference IN varchar2 default null,
328 report_level IN varchar2 default null,
329 type IN varchar2 default 'ACTIVE',
330 base_path IN varchar2 default null)
331 RETURN clob;
332
333 -- ------------------- report_addm_watchdog_xml --------------------------
334 -- NAME:
335 -- report_addm_watchdog_xml
336 --
337 -- DESCRIPTION
338 -- retrieves addm_watchdog_xml from the reporting framework
339 -- repository
340 --
341 -- PARAMETERS
342 -- report_id (IN) - a valid report id in the report framework repository
343 --
344 FUNCTION report_addm_watchdog_xml(report_id in number)
345 RETURN xmltype;
346
347 END dbms_perf;