1 PACKAGE dbms_workload_capture AS
2
3 -- ***********************************************************
4 -- START_CAPTURE
5 -- Initiates a database wide workload capture.
6 --
7 -- All user requests sent to database after a successful
8 -- DBMS_WORKLOAD_CAPTURE.START_CAPTURE() will be recorded in the
9 -- given "dir" directory for the given duration, if one was specified.
10 -- If no duration was specified, then the capture will last indefinitely
11 -- until DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE() is executed.
12 --
13 -- One can use workload filters (see DBMS_WORKLOAD_CAPTURE.ADD_FILTER)
14 -- to only capture a subset of the user requests sent to the database.
15 -- By default, when no workload filters are defined, all user requests
16 -- will be captured.
17 --
18 -- Workload that is initiated from Oracle Database background
19 -- processes (such as SMON, PMON, MMON etc) and Oracle Database Scheduler
20 -- Jobs (DBMS_SCHEDULER/DBMS_JOB) will not be captured, no matter how
21 -- the workload filters are defined. These activities should happen
22 -- automatically on an appropriately configured replay system.
23 --
24 -- By default, all database instances that were started up in
25 -- RESTRICTED mode using STARTUP RESTRICT will be UNRESTRICTED upon a
26 -- successful START_CAPTURE. Use FALSE for the "auto_unrestrict"
27 -- input parameter, if you do not want this behavior.
28 --
29 -- NOTE:
30 -- It is important to have a well-defined starting point for the
31 -- workload, so that the replay system could be restored to that
32 -- point before initiating a replay of the captured workload.
33 -- In order to have a well-defined starting point for the workload
34 -- capture, it is preferable to not have any sessions that were
35 -- in-flight when START_CAPTURE is executed. If those in-flight
36 -- sessions had in-flight transactions, then those in-flight
37 -- transactions will not be replayed properly in subsequent
38 -- database replays, since only the part of the transaction
39 -- whose calls were executed after START_CAPTURE will actually
40 -- be replayed.
41 -- That said, not replaying transactions that were in-flight when
42 -- START_CAPTURE was executed is not an issue in many (if not most)
43 -- database systems. Please evaluate whether this might be an issue
44 -- in your database system and take appropriate action to avoid
45 -- in-flight sessions during START_CAPTURE.
46 --
47 -- The procedure will take as input the following parameters:
48 -- name - name of the workload capture
49 -- (MANDATORY)
50 --
51 -- dir - name of the DIRECTORY object (case sensitive)
52 -- where all the workload capture files
53 -- will be written to.
54 -- Should contain enough space to hold
55 -- all the workload capture files.
56 -- (MANDATORY)
57 --
58 -- duration - Optional input to specify
59 -- the duration (in seconds) for which
60 -- the workload needs to be captured.
61 -- DEFAULT VALUE: NULL or in other words
62 -- workload will be captured until the user
63 -- executes DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE
64 --
65 -- default_action - Can be either 'INCLUDE' or 'EXCLUDE'.
66 -- Determines whether, by default, every user
67 -- request should be captured or not. Also determines,
68 -- whether the workload filters specified
69 -- should be considered as INCLUSION filters or
70 -- EXCLUSION filters.
71 --
72 -- If it is 'INCLUDE' then by default all user
73 -- requests to the database will be captured, except
74 -- for the part of the workload defined by the
75 -- filters. In this case, all the filters that were
76 -- specified using the ADD_FILTER() API
77 -- will be treated as EXCLUSION filters, and will
78 -- determine the workload that WILL NOT BE captured.
79 --
80 -- If it is 'EXCLUDE' then by default no user
81 -- request to the database will be captured, except
82 -- for the part of the workload defined by the
83 -- filters. In this case, all the filters that were
84 -- specified using the ADD_FILTER() API
85 -- will be treated as INCLUSION filters, and will
86 -- determine the workload that WILL BE captured.
87 --
88 -- DEFAULT VALUE: 'INCLUDE' and all the filters
89 -- specified will be assumed to be EXCLUSION filters.
90 --
91 -- auto_unrestrict - If this parameter is TRUE, then all instances
92 -- that were started up in RESTRICTED mode using
93 -- STARTUP RESTRICT will be automatically
94 -- unrestricted upon a successful START_CAPTURE.
95 --
96 -- If this parameter is FALSE, then no database
97 -- instance will be automatically unrestricted.
98 --
99 -- DEFAULT VALUE: TRUE
100 --
101 -- capture_sts - If this parameter is TRUE, a SQL tuning set
102 -- capture is also started in parallel with workload
103 -- capture. The resulting SQL tuning set can be
104 -- exported using DBMS_WORKLOAD_CAPTURE.EXPORT_AWR
105 -- along with the AWR data.
106 --
107 -- Currently, parallel STS capture
108 -- is not supported in RAC. So, this parameter has
109 -- no effect in RAC.
110 --
111 -- Furthermore capture filters defined using the
112 -- dbms_workload_capture APIs do not apply to the
113 -- sql tuning set capture.
114 --
115 -- The calling user must have the approriate
116 -- privileges ('administer sql tuning set').
117 --
118 -- If starting SQL set capture fails, workload capture
119 -- is stopped. The reason is stored in
120 -- DBA_WORKLOAD_CAPTURES.ERROR_MESSAGE
121 --
122 -- DEFAULT VALUE: FALSE
123 --
124 -- sts_cap_interval - This parameter specifies the capture interval
125 -- of the SQL set capture from the cursor cache in
126 -- seconds. The default value is 300.
127 --
128 --
129 -- ***********************************************************
130 PROCEDURE START_CAPTURE( name IN VARCHAR2,
131 dir IN VARCHAR2,
132 duration IN NUMBER DEFAULT NULL,
133 default_action IN VARCHAR2 DEFAULT 'INCLUDE',
134 auto_unrestrict IN BOOLEAN DEFAULT TRUE,
135 capture_sts IN BOOLEAN DEFAULT FALSE,
136 sts_cap_interval IN NUMBER DEFAULT 300);
137
138 -- ***********************************************************
139 -- FINISH_CAPTURE
140 -- Signals all connected sessions to stop the workload capture
141 -- and then stops future requests to the database from being
142 -- captured.
143 --
144 -- By default, FINISH_CAPTURE will wait for 30 secs to
145 -- receive a successful acknowledgement from all sessions
146 -- in the database cluster, before timing out.
147 --
148 -- All sessions that either were in the middle of executing a
149 -- user request or received a new user request, while FINISH_CAPTURE
150 -- was waiting for acknowledgements, will flush their buffers and
151 -- send back their acknowledgement to FINISH_CAPTURE.
152 --
153 -- If a database session remains idle (waiting for the next user request)
154 -- throughout the duration of FINISH_CAPTURE, then that session
155 -- might have unflushed capture buffers and will not send it's
156 -- acknowledgement to FINISH_CAPTURE.
157 --
158 -- In order to avoid such situations, do not have sessions that
159 -- remain idle (waiting for the next user request) throughout the
160 -- duration of FINISH_CAPTURE; either close such database sessions
161 -- before running FINISH_CAPTURE or send new database requests
162 -- to those sessions during FINISH_CAPTURE.
163 --
164 -- The procedure will take as input the following parameters:
165 -- timeout - Specify in seconds for how long FINISH_CAPTURE
166 -- should wait before it times out.
167 -- Pass 0 if you want to CANCEL the current workload
168 -- capture and not wait for any sessions to
169 -- flush it's capture buffers.
170 -- DEFAULT VALUE: 30 seconds
171 --
172 -- reason - Specify a reason for calling finish capture. The
173 -- reason will appear in the column ERROR_MESSAGE of the
174 -- view DBA_WORKLOAD_CAPTURES.
175 --
176 -- ***********************************************************
177 PROCEDURE FINISH_CAPTURE(timeout IN NUMBER DEFAULT 30,
178 reason IN VARCHAR2 DEFAULT NULL);
179
180 -- ***********************************************************
181 -- GET_CAPTURE_INFO
182 -- Looks into the workload capture present in the given directory
183 -- and retrieves all the information regarding that capture,
184 -- imports the information into the DBA_WORKLOAD_CAPTURES and
185 -- DBA_WORKLOAD_FILTERS views and returns the appropriate
186 -- DBA_WORKLOAD_CAPTURES.ID
187 --
188 -- If an appropriate row describing the capture in the given directory
189 -- already exists in DBA_WORKLOAD_CAPTURES, then GET_CAPTURE_INFO
190 -- will simply return that row's DBA_WORKLOAD_CAPTURES.ID
191 -- If no existing row matches the capture present in the
192 -- given directory a new row will be inserted to DBA_WORKLOAD_CAPTURES
193 -- and that rows ID will be returned.
194 --
195 -- The procedure will take as input the following parameters:
196 -- dir - name of the DIRECTORY object (case sensitive)
197 -- where all the workload capture files
198 -- are present.
199 -- (MANDATORY)
200 --
201 -- ***********************************************************
202 FUNCTION GET_CAPTURE_INFO(dir IN VARCHAR2)
203 RETURN NUMBER;
204
205 -- ***********************************************************
206 -- DELETE_CAPTURE_INFO
207 -- Deletes the rows in DBA_WORKLOAD_CAPTURES and DBA_WORKLOAD_FILTERS
208 -- that corresponds to the given workload capture id.
209 --
210 -- The procedure will take as input the following parameters:
211 -- capture_id - ID of the workload capture that needs
212 -- to be deleted.
213 -- Corresponds to DBA_WORKLOAD_CAPTURES.ID
214 -- (MANDATORY)
215 --
216 -- ***********************************************************
217 PROCEDURE DELETE_CAPTURE_INFO(capture_id IN NUMBER);
218
219 -- ***********************************************************
220 -- REPORT
221 -- Generates a report on the given workload capture.
222 --
223 -- The function will take as input the following parameters:
224 -- capture_id - ID of the workload capture
225 -- whose capture report is required.
226 -- (MANDATORY)
227 -- format - Specifies the report format
228 -- Valid values are
229 -- DBMS_WORKLOAD_CAPTURE.TYPE_TEXT,
230 -- DBMS_WORKLOAD_CAPTURE.TYPE_HTML and
231 -- (internal) DBMS_WORKLOAD_CAPTURE.TYPE_XML
232 -- (MANDATORY)
233 -- ***********************************************************
234
235 --
236 -- report type (possible values) constants
237 --
238 TYPE_TEXT CONSTANT VARCHAR2(4) := 'TEXT' ;
239 TYPE_HTML CONSTANT VARCHAR2(4) := 'HTML' ;
240 TYPE_XML CONSTANT VARCHAR2(3) := 'XML' ;
241
242 FUNCTION REPORT( capture_id IN NUMBER,
243 format IN VARCHAR2 )
244 RETURN CLOB;
245
246 -- ***********************************************************
247 -- ADD_FILTER
248 -- Adds a filter to capture only a subset of the workload.
249 --
250 -- The workload capture filters work in either
251 -- the DEFAULT INCLUSION or the DEFAULT EXCLUSION mode
252 -- as determined by the "default_action" input to the
253 -- START_CAPTURE() API.
254 --
255 -- The ADD_FILTER() API adds a new filter that will
256 -- affect the next workload capture, and whether the filters
257 -- will be considered as "INCLUSION" filters or "EXCLUSION" filters
258 -- depends on the value of the "default_action" input to
259 -- DBMS_WORKLOAD_CAPTURE.START_CAPTURE()
260 --
261 -- *****************************
262 -- SCOPE of the filter specified
263 -- *****************************
264 -- Filters once specified are valid only for the next workload
265 -- capture. If the same set of filters need to be used for
266 -- subsequent capture, they need to be specified each time before
267 -- START_CAPTURE is executed. Filters used for past captures can
268 -- be queried from the DBA_WORKLOAD_FILTERS view.
269 --
270 -- The function will take as input the following parameters:
271 -- fname - Name of the filter. Can be used to delete
272 -- the filter later if it is not required.
273 -- (MANDATORY)
274 -- fattribute - Specifies the attribute on which the filter is
275 -- defined. Should be one of the following values:
276 -- INSTANCE_NUMBER - type NUMBER
277 -- USER - type STRING
278 -- MODULE - type STRING
279 -- ACTION - type STRING
280 -- PROGRAM - type STRING
281 -- SERVICE - type STRING
282 -- (MANDATORY)
283 -- fvalue - Specifies the value to which the given
284 -- 'attribute' should be equal to for the
285 -- filter to be considered active.
286 -- Wildcards like '%' are acceptable for all
287 -- attributes that are of type STRING.
288 -- (MANDATORY)
289 --
290 -- In other words, the filter for a NUMBER attribute will be
291 -- equated as:
292 -- "attribute = value"
293 -- And, the filter for a STRING attribute will be equated as:
294 -- "attribute like value"
295 --
299 fvalue IN VARCHAR2);
296 -- ***********************************************************
297 PROCEDURE ADD_FILTER( fname IN VARCHAR2,
298 fattribute IN VARCHAR2,
300 PROCEDURE ADD_FILTER( fname IN VARCHAR2,
301 fattribute IN VARCHAR2,
302 fvalue IN NUMBER);
303
304 -- ***********************************************************
305 -- DELETE_FILTER
306 -- Deletes the filter with the given name.
307 --
308 -- The function will take as input the following parameters:
309 -- fname - Name of the filter that should be deleted.
310 -- (MANDATORY)
311 --
312 -- ***********************************************************
313 PROCEDURE DELETE_FILTER( fname IN VARCHAR2);
314
315 -- ***********************************************************
316 -- EXPORT_AWR/EXPORT_PERFORMANCE_DATA
317 -- Exports the AWR snapshots associated with a given
318 -- capture_id as well as the SQL set that may have been
319 -- captured along with the workload.
320 --
321 -- NOTE: This procedure will work only if the corresponding
322 -- workload capture was performed in the current database
323 -- (meaning that the corresponding row in
324 -- DBA_WORKLOAD_CAPTURES was not created by calling
325 -- DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO()) and the
326 -- AWR snapshots that correspond to the original capture
327 -- time period are still available.
328 --
329 -- The function will take as input the following parameters:
330 -- capture_id - ID of the capture whose AWR snapshots
331 -- should be exported.
332 -- (MANDATORY)
333 --
334 -- DEFAULT VALUE: NULL
335 -- EXPORT_PERFORMANCE_DATA and EXPORT_AWR are equivalent
336 -- ***********************************************************
337 PROCEDURE EXPORT_AWR( capture_id IN NUMBER);
338 PROCEDURE EXPORT_PERFORMANCE_DATA( capture_id IN NUMBER);
339
340 -- ***********************************************************
341 -- IMPORT_AWR/IMPORT_PERFORMANCE_DATA
342 -- Imports the AWR snapshots from a given capture, provided
343 -- those AWR snapshots were exported earlier from the original
344 -- capture system using DBMS_WORKLOAD_CAPTURE.EXPORT_AWR().
345 -- If a sql tuning set was captured along with the workload and
346 -- was successfully exported it will be imported also. The name
347 -- and owner of the sql tuning sets can be obtained form the
348 -- DBA_WORKLOAD_CAPTURES view.
349 --
350 -- In order to avoid DBID conflicts, this function will generate
351 -- a random DBID and use that DBID to populate the SYS AWR schema.
352 -- The value used for DBID can be found in
353 -- DBA_WORKLOAD_CAPTURES.AWR_DBID.
354 --
355 -- The function will take as input the following parameters:
356 -- capture_id - ID of the capture whose AWR snapshots
357 -- should be imported.
358 -- (MANDATORY)
359 -- staging_schema - Name of a valid schema in the current database
360 -- which can be used as a staging area
361 -- while importing the AWR snapshots
362 -- from the capture directory to the SYS AWR schema.
363 -- The 'SYS' schema cannot be used as a staging
364 -- schema and is not a valid input.
365 -- (MANDATORY)
366 -- force_cleanup - TRUE => any AWR data present in the given
367 -- staging_schema will be removed before
368 -- the actual import operation. All tables
369 -- with names that match any of the tables in AWR
370 -- will be dropped before the actual import.
371 -- This will typically be equivalent to
372 -- dropping all tables returned by the
373 -- following SQL:
374 -- SELECT table_name FROM dba_tables
375 -- WHERE owner = staging_schema
376 -- AND table_name like 'WR_$%';
377 -- Use this option only if you are sure that there
378 -- are no important data in any such tables in the
379 -- staging_schema.
380 -- FALSE => no tables will be dropped from
381 -- the staging_schema prior to the import operation.
382 -- DEFAULT VALUE: FALSE
383 --
384 -- NOTE: IMPORT_AWR will fail if the given staging_schema contains
385 -- any tables with a name that match any of the tables in AWR.
386 --
387 -- Returns the new randomly generated dbid that was used to
388 -- import the AWR snapshots. The same value can be found in
389 -- the AWR_DBID column in the DBA_WORKLOAD_CAPTURES view.
390 --
391 -- ***********************************************************
392 FUNCTION IMPORT_AWR( capture_id IN NUMBER,
393 staging_schema IN VARCHAR2,
394 force_cleanup IN BOOLEAN DEFAULT FALSE )
395 RETURN NUMBER;
396 FUNCTION IMPORT_PERFORMANCE_DATA(
397 capture_id IN NUMBER,
398 staging_schema IN VARCHAR2,
402 -- ***********************************************************
399 force_cleanup IN BOOLEAN DEFAULT FALSE )
400 RETURN NUMBER;
401
403 -- END OF PUBLIC FUNCTIONS
404 -- ***********************************************************
405
406
407 -- ***********************************************************
408 -- BEGIN PRIVATE FUNCTIONS and CONSTANTS
409 -- The following functions are not supported and
410 -- will not be documented.
411 -- The usage of the following functions is strictly
412 -- prohibited and their use will cause unpredictable behaviour
413 -- in the RDBMS server.
414 -- ***********************************************************
415
416 -- ***********************************************************
417 -- PRIVATE FUNCTIONS: USED INTERNALLY (not supported)
418 -- No documentation required!
419 -- ***********************************************************
420
421 /* Type used by user_calls_graph */
422 TYPE uc_graph_record IS RECORD(time NUMBER, user_calls NUMBER, flags NUMBER);
423 TYPE uc_graph_table IS TABLE OF uc_graph_record;
424
425 PROCEDURE export_uc_graph(capture_id NUMBER);
426 PROCEDURE import_uc_graph(capture_id NUMBER);
427 FUNCTION user_calls_graph(capture_id IN NUMBER)
428 RETURN uc_graph_table PIPELINED;
429
430
431 -- ***********************************************************
432 -- GET_CAPTURE_PATH
433 -- return the full path to the directory
434 --
435 -- The function will take as input the following parameters:
436 -- capture_id - ID of the workload capture
437 -- (MANDATORY)
438 -- ***********************************************************
439 FUNCTION get_capture_path(capture_id IN NUMBER)
440 RETURN VARCHAR2;
441
442 -- ************************************************************
443 -- get_perf_data_export_status
444 -- populates awr_data and sts_data with the filenames of the
445 -- exported performance data. If no data exists, NULL is set
446 -- to the appropriate output variable
447 -- ************************************************************
448 PROCEDURE get_perf_data_export_status( capture_id IN NUMBER,
449 awr_data OUT VARCHAR2,
450 sts_data OUT VARCHAR2);
451
452 END DBMS_WORKLOAD_CAPTURE;
[Home] [Help]