1 PACKAGE dbms_addm
2 authid current_user
3 IS
4 -- PROCEDURE DBMS_ADDM.analyze_db
5 -- PURPOSE: Create and execute an ADDM task to analyze all instances
6 -- of a database for a rango of snapshots.
7 -- PARAMETERS:
8 -- TASK_NAME
9 -- An optional task name. Task names must be unique to the user.
10 -- If not specified, a name will be generated by the system.
11 -- BEGIN_SNAPSHOT
12 -- The snapshot id for the beginning of the analysis period
13 -- END_SNAPSHOT
14 -- The snapshot id for the end of the analysis period
15 -- DB_ID
16 -- The databse to analyze, defaults to current database.
17
18 procedure analyze_db ( task_name in out varchar2,
19 begin_snapshot in number,
20 end_snapshot in number,
21 db_id in number := NULL);
22
23 -- PROCEDURE DBMS_ADDM.analyze_inst
24 -- PURPOSE: Create and execute an ADDM task to analyze a specific instance
25 -- of a database for a rango of snapshots.
26 -- PARAMETERS:
27 -- TASK_NAME
28 -- An optional task name. Task names must be unique to the user.
29 -- If not specified, a name will be generated by the system.
30 -- BEGIN_SNAPSHOT
31 -- The snapshot id for the beginning of the analysis period
32 -- END_SNAPSHOT
33 -- The snapshot id for the end of the analysis period
34 -- INSTANCE_NUMBER
35 -- The number of the instance to analyze, defaults to current
36 -- DB_ID
37 -- The databse to analyze, defaults to current database.
38
39
40 procedure analyze_inst ( task_name in out varchar2,
41 begin_snapshot in number,
42 end_snapshot in number,
43 instance_number in number := NULL,
44 db_id in number := NULL);
45
46 -- PROCEDURE DBMS_ADDM.analyze_partial
47 -- PURPOSE: Create and execute an ADDM task to analyze a partial list
48 -- of instances for a rango of snapshots.
49 -- PARAMETERS:
50 -- TASK_NAME
51 -- An optional task name. Task names must be unique to the user.
52 -- If not specified, a name will be generated by the system.
53 -- INSTANCE_NUMBERS
54 -- A comma separated list of instance numbers that participate
55 -- in the analysis
56 -- BEGIN_SNAPSHOT
57 -- The snapshot id for the beginning of the analysis period
58 -- END_SNAPSHOT
59 -- The snapshot id for the end of the analysis period
60 -- DB_ID
61 -- The databse to analyze, defaults to current database.
62
63 procedure analyze_partial ( task_name in out varchar2,
64 instance_numbers in varchar2,
65 begin_snapshot in number,
66 end_snapshot in number,
67 db_id in number := NULL);
68
69 -- PROCEDURE DBMS_ADDM.insert_finding_directive
70 -- PURPOSE: insert a directive filtering a finding, either for a task or
71 -- system wide.
72 -- PARAMETERS:
73 -- TASK_NAME
74 -- An optional task name. If NULL, this is a system directive.
75 -- DIR_NAME
76 -- A unique name identifying the new directive
77 -- FINDING_NAME
78 -- A name for the finding, will appear in the "FINDING_NAME"
79 -- column of dba_advisor_findings.
80 -- MIN_ACTIVE_SESSIONS
81 -- Minimal value for active sessions. A finding is filtered out
82 -- if it has less active sessions than this value.
83 -- MIN_PERC_IMPACT
84 -- Minimal value for percentage of impact (0-100). A finding
85 -- is filtered out if its impact is less than this percentage of
86 -- the database time of the analysis period.
87
88 procedure insert_finding_directive ( task_name in varchar2,
89 dir_name in varchar2,
90 finding_name in varchar2,
91 min_active_sessions in number := 0,
92 min_perc_impact in number := 0);
93
94 -- PROCEDURE DBMS_ADDM.insert_finding_directive
95 -- PURPOSE: insert a directive filtering sql recommendations,
96 -- either for a task or system wide.
97 -- PARAMETERS:
98 -- TASK_NAME
99 -- An optional task name. If NULL, this is a system directive.
100 -- DIR_NAME
101 -- A unique name identifying the new directive
102 -- SQL_ID
103 -- A string identifying the SQL (13 characters).
104 -- MIN_ACTIVE_SESSIONS
105 -- Minimal value for active sessions. Ain action is filtered out
106 -- if the SQL's total db time is less than this value.
107 -- MIN_PERC_IMPACT
108 -- Minimal value for response time (microseconds).
109 -- A SQL action is filtered out if the response time is less than
110 -- this value.
111
112 procedure insert_sql_directive ( task_name in varchar2,
113 dir_name in varchar2,
114 sql_id in varchar2,
115 min_active_sessions in number := 0,
116 min_response_time in number := 0);
117
118 -- PROCEDURE DBMS_ADDM.insert_segment_directive
119 -- PURPOSE: insert a directive filtering recommendations to run segment
120 -- advisor.
121 -- PARAMETERS:
122 -- TASK_NAME
123 -- An optional task name. If NULL, this is a system directive.
124 -- DIR_NAME
125 -- A unique name identifying the new directive
126 -- OWNER_NAME
127 -- The owner of the segment.
128 -- OBJECT_NAME
129 -- The name of the main object. If NULL, then all objects for the
130 -- specified owner are filtered.
131 -- SUB_OBJECT_NAME
132 -- The name of a partition of the object. If null, than all
133 -- partitions of the object are filtered. Also, if the object is
134 -- not partitioned, leave the value as NULL.
135 --
136
137 procedure insert_segment_directive ( task_name in varchar2,
138 dir_name in varchar2,
139 owner_name in varchar2,
140 object_name in varchar2 := NULL,
141 sub_object_name in varchar2 := NULL);
142
143 -- PROCEDURE DBMS_ADDM.insert_segment_directive
144 -- PURPOSE: insert a directive filtering recommendations to run segment
145 -- advisor.
146 -- PARAMETERS:
147 -- TASK_NAME
148 -- An optional task name. If NULL, this is a system directive.
149 -- DIR_NAME
150 -- A unique name identifying the new directive
151 -- OBJECT_NUMBER
152 -- The data object number (as appears in OBJ$)
153 --
154
155 procedure insert_segment_directive ( task_name in varchar2,
156 dir_name in varchar2,
157 object_number in number);
158
159 -- PROCEDURE DBMS_ADDM.insert_parameter_directive
160 -- PURPOSE: insert a directive filtering a recommendations to change
161 -- a system parameter.
162 -- PARAMETERS:
163 -- TASK_NAME
164 -- An optional task name. If NULL, this is a system directive.
165 -- DIR_NAME
166 -- A unique name identifying the new directive
167 -- PARAMETER_NAME
168 -- The name of the system parameter. All actions to change this
169 -- parameter are filtered out.
170
171 procedure insert_parameter_directive ( task_name in varchar2,
172 dir_name in varchar2,
173 parameter_name in varchar2);
174
175 -- PROCEDURE DBMS_ADDM.delete_finding_directive
176 -- PURPOSE: delete an addm directive
177 -- PARAMETERS:
178 -- TASK_NAME
179 -- An optional task name. If NULL, this is a system directive.
180 -- DIR_NAME
181 -- A unique name identifying the directive
182 procedure delete_finding_directive ( task_name in varchar2,
183 dir_name in varchar2);
184
185 -- PROCEDURE DBMS_ADDM.delete_sql_directive
186 -- PURPOSE: delete an addm directive
187 -- PARAMETERS:
188 -- TASK_NAME
189 -- An optional task name. If NULL, this is a system directive.
190 -- DIR_NAME
191 -- A unique name identifying the directive
192 procedure delete_sql_directive ( task_name in varchar2,
193 dir_name in varchar2);
194
195 -- PROCEDURE DBMS_ADDM.delete_segment_directive
196 -- PURPOSE: delete an addm directive
197 -- PARAMETERS:
198 -- TASK_NAME
199 -- An optional task name. If NULL, this is a system directive.
200 -- DIR_NAME
201 -- A unique name identifying the directive
202 procedure delete_segment_directive ( task_name in varchar2,
203 dir_name in varchar2);
204
205 -- PROCEDURE DBMS_ADDM.delete_parameter_directive
206 -- PURPOSE: delete an addm directive
207 -- PARAMETERS:
208 -- TASK_NAME
209 -- An optional task name. If NULL, this is a system directive.
210 -- DIR_NAME
211 -- A unique name identifying the directive
212 procedure delete_parameter_directive ( task_name in varchar2,
213 dir_name in varchar2);
214
215 -- PROCEDURE DBMS_ADDM.delete
216 -- PURPOSE: delete an ADDM task
217 -- PARAMETERS:
218 -- TASK_NAME
219 -- The name of the task to be deleted.
220
221 procedure delete ( task_name in varchar2);
222
223
224 -- FUNCTION DBMS_ADDM.get_report
225 -- RETURN: a CLOB containing the report.
226 -- PURPOSE: get the report of an ADDM task.
227 -- PARAMETERS:
228 -- TASK_NAME
229 -- The name of the task to report on.
230
231 function get_report ( task_name in varchar2)
232 return clob;
233
234 -- FUNCTION DBMS_ADDM.get_ash_query
235 -- RETURN: a varchar containing an ASH query identifying the rows
236 -- in ASH with impact for the finding.
237 -- PURPOSE: get a SQL text of an ASH query for a finding
238 -- PARAMETERS:
239 -- TASK_NAME
240 -- The name of the task.
241 -- FINDING_ID
242 -- The ID of the finding within the task
243
244 function get_ash_query ( task_name in varchar2, finding_id in number)
245 return varchar2;
246
247 -- FUNCTION DBMS_ADDM.real_time_addm_report
248 -- RETURN: a clob containing a real time addm report for the
249 -- past 5 minutes.
250 -- PURPOSE: Get Real-Time ADDM report of a system.
251 -- Get a full RAC report - assumes SQL over GV$ is possible.
252 function real_time_addm_report
253 return clob;
254
255 -- FUNCTION DBMS_ADDM.compare_databases_report
256 -- RETURN: a clob containing a compare period addm report comparing the
257 -- the performance of a database over two different time periods
258 -- or the performance of two different databases over two different
259 -- time periods
260 -- PURPOSE: Get a compare period addm report for the given two time periods
261 -- PARAMETERS:
262 -- BASE_DBID
263 -- dbid of the base period.Default is NULL in which case
264 -- we choose the current dbid.
265 -- BASE_BEGIN_SNAP_ID
266 -- snapshot id of the beginning of the base period
267 -- BASE_END_SNAP_ID
268 -- snapshot id of the end of the base period
269 -- COMP_DBID
270 -- dbid of the comparison period. Default is NULL in
271 -- which case we choose the current dbid
272 -- COMP_BEGIN_SNAP_ID
273 -- snapshot id of the beginning of the comparison period
274 -- COMP_END_SNAP_ID
275 -- snapshot id of the end of the comparison period
276 -- REPORT_TYPE
277 -- Output type for the report - XML or HTML.Default is HTML
278 function compare_databases ( base_dbid in number := NULL,
279 base_begin_snap_id in number,
280 base_end_snap_id in number,
281 comp_dbid in number := NULL,
282 comp_begin_snap_id in number,
283 comp_end_snap_id in number,
284 report_type in varchar2 := 'HTML')
285 return clob;
286
287 -- FUNCTION DBMS_ADDM.compare_instances
288 -- RETURN: a clob containing a compare period addm report comparing the
289 -- the performance of a single instance over two different
290 -- time periods or the performance of two different instances
291 -- over two different time periods
292 -- PURPOSE: Get a compare period addm report for the given two time periods
293 -- PARAMETERS:
294 -- BASE_DBID
295 -- dbid of the base period.Default is NULL in which case
296 -- we choose the current dbid.
297 -- BASE_INSTANCE_ID
298 -- instance id of the base period
299 -- BASE_BEGIN_SNAP_ID
300 -- snapshot id of the beginning of the base period
301 -- BASE_END_SNAP_ID
302 -- snapshot id of the end of the base period
303 -- COMP_DBID
304 -- dbid of the comparison period. Default is NULL in
305 -- which case we choose the current dbid
306 -- COMP_INSTANCE_ID
307 -- instance id of the comparison period
308 -- COMP_BEGIN_SNAP_ID
309 -- snapshot id of the beginning of the comparison period
310 -- COMP_END_SNAP_ID
311 -- snapshot id of the end of the comparison period
312 -- REPORT_TYPE
313 -- Output type for the report - XML or HTML.Default is HTML
314 function compare_instances ( base_dbid in number := NULL,
315 base_instance_id in number,
316 base_begin_snap_id in number,
317 base_end_snap_id in number,
318 comp_dbid in number := NULL,
319 comp_instance_id in number,
320 comp_begin_snap_id in number,
321 comp_end_snap_id in number,
322 report_type in varchar2 := 'HTML')
323 return clob;
324
325 -- FUNCTION DBMS_ADDM.compare_capture_replay_report
329 -- PARAMETERS:
326 -- RETURN: a clob containing a compare period addm report comparing the
327 -- the performance of a capture to a replay
328 -- PURPOSE: Get a compare period addm report
330 -- REPLAY_ID
331 -- id of the replay to be compared
332 -- REPORT_TYPE
333 -- Output type for the report - XML or HTML.Default is HTML
334 function compare_capture_replay_report(replay_id in number,
335 report_type in varchar2 := 'HTML')
336 return clob;
337
338 -- FUNCTION DBMS_ADDM.compare_replay_replay_report
339 -- RETURN: a clob containing a compare period addm report comparing the
340 -- the performance of a replay to another replay
341 -- PURPOSE: Get a compare period addm report
342 -- PARAMETERS:
343 -- REPLAY_ID1
344 -- id of the replay to be compared
345 -- REPLAY_ID2
346 -- id of the replay to be compared
347 -- REPORT_TYPE
348 -- Output type for the report - XML or HTML.Default is HTML
349
350 function compare_replay_replay_report(replay_id1 in number,
351 replay_id2 in number,
352 report_type in varchar2 := 'HTML')
353 return clob;
354
355 END dbms_addm;