1 PACKAGE dbms_workload_replay AS
2
3 -- ***********************************************************
4 -- PROCESS_CAPTURE
5 -- Processes the workload capture found in capture_dir in place.
6 --
7 -- Analyzes the workload capture found in the capture_dir and
8 -- creates new workload replay specific metadata files that are
9 -- required to replay the given workload capture.
10 -- This procedure can be run multiple times on the same
11 -- capture directory - useful when this procedure encounters
12 -- unexpected errors or is cancelled by the user.
13 --
14 -- Once this procedure runs successfully, the capture_dir can be used
15 -- as input to INITIALIZE_REPLAY() in order to replay the captured
16 -- workload present in capture_dir.
17 --
18 -- Before a workload capture can be replayed in a particular database
19 -- version, the capture needs to be "processed" using this
20 -- PROCESS_CAPTURE procedure in that same database version.
21 -- Once created, a processed workload capture can be used to replay
22 -- the captured workload multiple times in the same database version.
23 --
24 -- For example:
25 -- Say workload "foo" was captured in "rec_dir" in Oracle
26 -- database version 10.2.0.4
27 --
28 -- In order to replay the workload "foo" in version 11.1.0.1
29 -- the workload needs to be processed in version 11.1.0.1
30 -- The following procedure needs to be executed in a 11.1.0.1 database
31 -- in order to process the capture directory "rec_dir"
32 --
33 -- DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE('rec_dir');
34 --
35 -- Now, rec_dir contains a valid 11.1.0.1 processed workload capture
36 -- that can be used to replay the workload "foo" in 11.1.0.1 databases
37 -- as many number of times as required.
38 --
39 -- The procedure will take as input the following parameters:
40 -- capture_dir - name of the workload capture directory object.
41 -- (case sensitive)
42 -- The directory object must point to a valid OS
43 -- directory that has appropriate permissions.
44 -- New files will be added to this directory.
45 -- (MANDATORY)
46 -- parallel_level - number of oracle processes used to process the
47 -- capture in a parallel fashion.
48 -- The NULL default value will auto-compute the
49 -- parallelism level, whereas a value of 1 will enforce
50 -- serial execution.
51 -- is_as_replay - Flag for special purpose AS Replay capture
52 -- processing. Is set to 1 in the as_replay package,
53 -- as_replay.process_as_capture.
54 -- ***********************************************************
55 PROCEDURE PROCESS_CAPTURE( capture_dir IN VARCHAR2,
56 parallel_level IN NUMBER DEFAULT NULL,
57 is_as_replay IN BINARY_INTEGER DEFAULT 0);
58
59 -- ********************************************************************
60 -- PROCESS_CAPTURE_COMPLETION
61 -- While a process_capture is running on the Database, this function
62 -- will return the percentage of the capture files that have already
63 -- been processed. That value is updated every minute or so.
64 -- ********************************************************************
65 FUNCTION PROCESS_CAPTURE_COMPLETION
66 RETURN NUMBER;
67
68 -- ********************************************************************
69 -- PROCESS_CAPTURE_REMAINING_TIME
70 -- While a process_capture is running on the Database, this function
71 -- will return an estimate of the time remaining (in minutes) before
72 -- processing is done.
73 --
74 -- We cannot get a correct estimate before the first minute of
75 -- processing has passed. In that case, this function will return
76 -- NULL.
77 -- This will also return NULL if no processing is in progress.
78 -- ********************************************************************
79 FUNCTION PROCESS_CAPTURE_REMAINING_TIME
80 RETURN NUMBER;
81
82 -- ***********************************************************
83 -- INITIALIZE_REPLAY
84 -- Puts the DB state in INIT for REPLAY mode. The input replay_dir
85 -- should point to a valid capture directory processed by
86 -- DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE().
87 --
88 -- Loads data into the replay system that is required before preparing
89 -- for the replay (i.e. calling PREPARE_REPLAY). Such data are:
90 -- 1) Connection data
91 -- During capture we record the connection string each session
92 -- used to connect to the server. INITIALIZE_REPLAY loads this
93 -- data and allows the user to re-map the recorded connection
94 -- string to new connection strings or service points.
95 --
96 -- EXAMPLE
97 -- Continuing with the example from PROCESS_CAPTURE, one
98 -- would invoke the following:
99 --
100 -- DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY('replay foo #1', 'rec_dir');
101 --
102 -- This command will load up the connection map and by default
103 -- will set all replay time connection strings to be equal to
104 -- NULL. A NULL replay time connection string means that the workload
105 -- replay client's (WRC's) will connect to the default host as
106 -- determined by the replay client's runtime environment settings.
107 -- The user can change a particular connection string to a new one
108 -- (or a new service point) for replay by using
109 -- DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION
110 --
111 -- The procedure takes the following input parameter:
112 -- replay_name - name of the workload replay.
113 -- Every replay of a processed workload capture
114 -- can be given a name.
115 -- (MANDATORY)
116 -- replay_dir - name of the directory object that points to the
117 -- (case sensitive)
118 -- OS directory that contains processed capture
119 -- data
120 --
121 -- Prerequisites:
122 -- -> Workload capture was already processed using
123 -- DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE in the same
124 -- database version.
125 -- -> Database state has been logically restored to
126 -- what it was at the beginning of the original workload capture.
127 --
128 -- ***********************************************************
129 PROCEDURE INITIALIZE_REPLAY( replay_name IN VARCHAR2,
130 replay_dir IN VARCHAR2 );
131
132 -- ***********************************************************
133 -- SET_ADVANCED_PARAMETER
134 -- Sets an advanced parameter for replay besides the ones used with
135 -- PREPARE_REPLAY. The advanced parameters control aspects of the replay
136 -- that are more specialized. The advanced parameters are reset to
137 -- their default values after the replay has finished.
138 --
139 -- The current parameters and the values that can be used are:
140 --
141 -- 'DO_NO_WAIT_COMMITS': (default: FALSE)
142 -- This parameter controls whether the commits issued by replay
143 -- sessions will be NOWAIT. The default value for this parameter is
144 -- FALSE. In this case all the commits are issued with the mode they
145 -- were captured (wait, no-wait, batch, no-batch). If the parameter is
146 -- set to TRUE then all commits are issued in no-wait mode. This is
147 -- useful in cases where the replay is becoming noticably slow because
148 -- of a high volume of concurrent commits. Setting the parameter to
149 -- true will significantly decrease the waits on the 'log file sync'
150 -- event during the replay with respect to capture.
151 --
152 --
153 -- The procedures take the following parameters:
154 -- pname - The name of the parameter (case insensitive)
155 -- pvalue - The value of the parameter
156 --
157 -- Prerequisites:
158 -- --> DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY must have been
159 -- called.
160 -- --> The database must not be in PREPARE mode
161 -- --> No replay must be currently ongoing
162 -- ***********************************************************
163 PROCEDURE SET_ADVANCED_PARAMETER( pname IN VARCHAR2,
164 pvalue IN VARCHAR2);
165 PROCEDURE SET_ADVANCED_PARAMETER( pname IN VARCHAR2,
166 pvalue IN NUMBER);
167 PROCEDURE SET_ADVANCED_PARAMETER( pname IN VARCHAR2,
168 pvalue IN BOOLEAN);
169
170 -- ***********************************************************
171 -- GET_ADVANCED_PARAMETER
172 -- Gets the value of an advanced parameter(see SET_ADVANCED_PARAMETER).
173 -- This can be called at anytime. This function returns the value
174 -- of the parameters in VARCHAR2 regardless of the parameter type.
175 -- For boolean parameters either 'TRUE' or 'FALSE' is returned.
176 --
177 -- The function takes the following parameters:
178 -- pname - The name of the parameter (case insensitive)
179 --
180 -- The function returns the value of the parameter in VARCHAR2.
181 --
182 -- Prerequisites:
183 -- NONE
184 -- ***********************************************************
185 FUNCTION GET_ADVANCED_PARAMETER( pname IN VARCHAR2)
186 RETURN VARCHAR2;
187
188 -- ***********************************************************
189 -- RESET_ADVANCED_PARAMETERS
190 -- Resets all the advanced parameters to their default values.
191 --
192 -- The procedure does not accept any parameters
193 -- The procedure does not return any value
194 -- ***********************************************************
195 PROCEDURE RESET_ADVANCED_PARAMETERS;
196
197 -- ***********************************************************
198 -- SET_REPLAY_TIMEOUT
199 -- Set up replay timeout action. The purpose is to abort user calls that
200 -- might make the replay much slower or even cause a replay hang.
201 -- Once a replay timeout action is enabled, a user call will exit with
202 -- ORA-15569 if it has been delayed more than the condition specified by
203 -- the replay action. The call and its error will be reported as error
204 -- divergence.
205 -- Here is how the replay timeout action works:
206 -- 1) The timeout action won't do anything if it is not enabled.
207 -- 2) If the call delay in minutes is less than a lower bound
208 -- specified by parameter min_delay, the timeout action won't
209 -- do anything.
210 -- 3) If the delay in minutes is more than a upper bound specified by
211 -- parameter max_delay, the timeout action will abort the user call
212 -- and throw ORA-15569.
213 -- 4) For delay that is between the lower bound and upper bound, the
214 -- user call will abort with ORA-15569 only when the current
215 -- replay elapsed time is more than the multiplication of capture
216 -- elapsed time and parameter delay_factor.
217 -- The parameters are reset to the default value after the replay has
218 -- finished.
219 --
220 -- The procedure takes the following input parameters:
221 -- enabled - TRUE to enable the timeout action and FALSE to disable.
222 -- DEFAULT VALUE: TRUE.
223 -- min_delay - lower bound of call delay in minutes. The replay action
224 -- is activated only when the delay is more than min_delay.
225 -- DEFAULT VALUE: 10 minutes.
226 -- max_delay - upper bound of call delay in minutes. The timeout action
227 -- throws ORA-15569 when the delay is more than max_delay.
228 -- DEFAULT VALUE: 120 minutes.
229 -- delay_factor - factor for the call delay that is between min_delay and
230 -- max_delay. The timeout action throws ORA-15569 when the
231 -- current replay elapsed time is more than the multiplication
232 -- of capture elapsed time and delay_factor.
233 -- DEFAULT VALUE: 8
234 --
235 -- NOTE: Call delay is defined as the difference of call elapsed time
236 -- between replay and capture if replay elapsed time is larger.
237 -- SET_REPLAY_TIMEOUT can be called anytime during replay.
238 --
239 -- ***********************************************************
240 PROCEDURE SET_REPLAY_TIMEOUT(enabled IN BOOLEAN DEFAULT TRUE,
241 min_delay IN NUMBER DEFAULT 10,
242 max_delay IN NUMBER DEFAULT 120,
243 delay_factor IN NUMBER DEFAULT 8);
244
245 -- ***********************************************************
246 -- GET_REPLAY_TIMEOUT
247 -- Get the replay timeout setting.
248 --
249 -- The procedure returns the following output parameters:
250 -- enabled - TRUE if the timeout action is enabled and FALSE otherwise.
251 -- min_delay - lower bound of call delay in minutes. The replay action
252 -- is activated only when the delay is equal or more than
253 -- min_delay.
254 -- max_delay - upper bound of call delay in minutes. The timeout action
255 -- throw ORA-15569 when the delay is more than max_delay.
256 -- delay_factor - the factor for the call delay that is between min_delay
257 -- and max_delay. The timeout action throws ORA-15569 when
258 -- the current replay elapsed time is more than the
259 -- multiplication of capture elapsed time and delay_factor.
260 --
261 -- NOTE: GET_REPLAY_TIMEOUT can be called anytime during replay.
262 --
263 -- ***********************************************************
264 PROCEDURE GET_REPLAY_TIMEOUT(enabled OUT BOOLEAN,
265 min_delay OUT NUMBER,
266 max_delay OUT NUMBER,
267 delay_factor OUT NUMBER);
268
269 -- ***********************************************************
270 -- PREPARE_REPLAY
271 -- Puts the DB state in PREPARE mode. The database
272 -- should have been initialized for replay using
273 -- DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(), and optionally any
274 -- capture time connection strings that require remapping have been
275 -- already done using DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION().
276 --
277 -- One or more external replay clients (WRC) can be started
278 -- once the PREPARE_REPLAY procedure has been executed.
279 --
280 -- The procedure will take as input the following parameters:
281 -- synchronization - Turns synchronization to the given scheme during
282 -- workload replay.
283 -- When synchronization is SCN, the COMMIT order
284 -- observed during the original workload capture
285 -- will be preserved during replay.
286 -- Every action that is replayed will be executed
287 -- ONLY AFTER all of it's dependent COMMITs (all
291 -- When synchronization is OBJECT_ID, a more advanced
288 -- COMMITs that were issued before the given action
289 -- in the original workload capture) have finished
290 -- execution.
292 -- synchronization scheme is used.
293 -- Every action that is replayed will be executed
294 -- ONLY AFTER the RELEVANT COMMITs have finished
295 -- executing. The relevant commits are the ones that
296 -- were issued before the given action in the
297 -- orginal workload capture and that had modified
298 -- at least one of the database objects the given
299 -- action is referencing (either implicitely or
300 -- explicitely).
301 -- This OBJECT_ID scheme has the same logical
302 -- property of making sure that any action will see
303 -- the same data it saw during capture, but will
304 -- allow more concurrency during replays for the
305 -- actions that do not touch the same objects/tables.
306 -- DEFAULT VALUE: OBJECT_ID.
307 -- For legacy reason, there is a boolean version of
308 -- this procedure:
309 -- TRUE means 'OBJECT_ID'
310 -- FALSE means 'OFF'
311 --
312 -- connect_time_scale - Scales the time elapsed between the
313 -- instant the workload capture was started
314 -- and session connects with the given value.
315 -- The input is interpreted as a % value.
316 -- Can potentially be used to increase or
317 -- decrease the number of concurrent
318 -- users during the workload replay.
319 -- DEFAULT VALUE: 100
320 --
321 -- For example, if the following was observed
322 -- during the original workload capture:
323 -- 12:00 : Capture was started
324 -- 12:10 : First session connect (10m after)
325 -- 12:30 : Second session connect (30m after)
326 -- 12:42 : Third session connect (42m after)
327 --
328 -- If the connect_time_scale is 50, then the
329 -- session connects will happen as follows:
330 -- 12:00 : Replay was started
331 -- with 50% connect time scale
332 -- 12:05 : First session connect ( 5m after)
333 -- 12:15 : Second session connect (15m after)
334 -- 12:21 : Third session connect (21m after)
335 --
336 -- If the connect_time_scale is 200, then the
337 -- session connects will happen as follows:
338 -- 12:00 : Replay was started
339 -- with 200% connect time scale
340 -- 12:20 : First session connect (20m after)
341 -- 13:00 : Second session connect (60m after)
342 -- 13:24 : Third session connect (84m after)
343 --
344 -- think_time_scale - Scales the time elapsed between two
345 -- successive user calls from the same
346 -- session.
347 -- The input is interpreted as a % value.
348 -- Can potentially be used to increase or
349 -- decrease the number of concurrent
350 -- users during the workload replay.
351 -- DEFAULT VALUE: 100
352 --
353 -- For example, if the following was observed
354 -- during the original workload capture:
355 -- 12:00 : User SCOTT connects
356 -- 12:10 : First user call issued
357 -- (10m after completion of prevcall)
358 -- 12:14 : First user call completes in 4mins
359 -- 12:30 : Second user call issued
360 -- (16m after completion of prevcall)
361 -- 12:40 : Second user call completes in 10m
362 -- 12:42 : Third user call issued
363 -- ( 2m after completion of prevcall)
364 -- 12:50 : Third user call completes in 8m
365 --
366 -- If the think_time_scale is 50 during the
367 -- workload replay, then the user calls
368 -- will look something like below:
369 -- 12:00 : User SCOTT connects
370 -- 12:05 : First user call issued 5 mins
371 -- (50% of 10m) after the completion
375 -- 12:18 : Second user call issued 8 mins
372 -- of prev call
373 -- 12:10 : First user call completes in 5m
374 -- (takes a minute longer)
376 -- (50% of 16m) after the completion
377 -- of prev call
378 -- 12:25 : Second user call completes in 7m
379 -- (takes 3 minutes less)
380 -- 12:26 : Third user call issued 1 min
381 -- (50% of 2m) after the completion
382 -- of prev call
383 -- 12:35 : Third user call completes in 9m
384 -- (takes a minute longer)
385 --
386 -- think_time_auto_correct - Auto corrects the think time between calls
387 -- appropriately when user calls takes longer
388 -- time to complete during replay than
389 -- how long the same user call took to
390 -- complete during the original capture.
391 -- DEFAULT VALUE: TRUE, reduce
392 -- think time if replay goes slower
393 -- than capture.
394 --
395 -- For example, if the following was observed
396 -- during the original workload capture:
397 -- 12:00 : User SCOTT connects
398 -- 12:10 : First user call issued
399 -- (10m after completion of prevcall)
400 -- 12:14 : First user call completes in 4m
401 -- 12:30 : Second user call issued
402 -- (16m after completion of prevcall)
403 -- 12:40 : Second user call completes in 10m
404 -- 12:42 : Third user call issued
405 -- ( 2m after completion of prevcall)
406 -- 12:50 : Third user call completes in 8m
407 --
408 -- If the think_time_scale is 100 and
409 -- the think_time_auto_correct is TRUE
410 -- during the workload replay, then
411 -- the user calls will look something
412 -- like below:
413 -- 12:00 : User SCOTT connects
414 -- 12:10 : First user call issued 10 mins
415 -- after the completion of prev call
416 -- 12:15 : First user call completes in 5m
417 -- (takes 1 minute longer)
418 -- 12:30 : Second user call issued 15 mins
419 -- (16m minus the extra time of 1m
420 -- the prev call took) after the
421 -- completion of prev call
422 -- 12:44 : Second user call completes in 14m
423 -- (takes 4 minutes longer)
424 -- 12:44 : Third user call issued immediately
425 -- (2m minus the extra time of 4m
426 -- the prev call took) after the
427 -- completion of prev call
428 -- 12:52 : Third user call completes in 8m
429 -- scale_up_multiplier - Defines the number of times the query workload
430 -- is scaled up during replay. Each captured session
431 -- is replayed concurrently as many times as the
432 -- value of the scale_up_multiplier. However, only
433 -- one of the sessions in each set of identical
434 -- replay sessions executes both queries and updates.
435 -- The remaining sessions only execute queries.
436 --
437 -- More specifically note that:
438 -- 1. One replay session (base session) of each set
439 -- of identical sessions will replay every call
440 -- from the capture as usual
441 -- 2. The remaining sessions (scale-up sessions) will
442 -- only replay calls that are read-only.
443 -- Thus, DDL, DML, and PLSQL calls that
444 -- modified the database will be
445 -- skipped. SELECT FOR UPDATE statements are also skipped.
446 -- 3. Read-only calls from the scale-up are
447 -- synchronized appropriately and obey the
448 -- timings defined by think_time_scale, connect_time_scale,
449 -- and think_time_auto_correct. Also the queries
453 -- scale-up sessions.
450 -- are made to wait for the appropriate commits.
451 -- 4. No replay data or error divergence
452 -- records will be generated for the
454 -- 5. All base or scale-up sessions that
455 -- replay the same capture file will connect
456 -- from the same workload replay client.
457 --
458 -- capture_sts - If this parameter is TRUE, a SQL tuning set
459 -- capture is also started in parallel with workload
460 -- capture. The resulting SQL tuning set can be
461 -- exported using DBMS_WORKLOAD_REPLAY.EXPORT_AWR
462 -- along with the AWR data.
463 --
464 -- Currently, parallel STS capture
465 -- is not supported in RAC. So, this parameter has
466 -- no effect in RAC.
467 --
468 -- Furthermore capture filters defined using the
469 -- dbms_workload_replay APIs do not apply to the
470 -- sql tuning set capture.
471 --
472 -- The calling user must have the approriate
473 -- privileges ('administer sql tuning set').
474 --
475 -- DEFAULT VALUE: FALSE
476 --
477 -- sts_cap_interval - This parameter specifies the capture interval
478 -- of the SQL set capture from the cursor cache in
479 -- seconds. The default value is 300.
480 --
481 --
482 -- Prerequisites:
483 -- -> The database has been initialized for replay using
484 -- DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY().
485 -- -> Any capture time connections strings that require remapping
486 -- during replay have already been remapped using
487 -- DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION().
488 --
489 -- ***********************************************************
490 PROCEDURE PREPARE_REPLAY(synchronization IN BOOLEAN,
491 connect_time_scale IN NUMBER DEFAULT 100,
492 think_time_scale IN NUMBER DEFAULT 100,
493 think_time_auto_correct IN BOOLEAN DEFAULT TRUE,
494 scale_up_multiplier IN NUMBER DEFAULT 1,
495 capture_sts IN BOOLEAN DEFAULT FALSE,
496 sts_cap_interval IN NUMBER DEFAULT 300);
497
498 PROCEDURE PREPARE_REPLAY(
499 synchronization IN VARCHAR2 DEFAULT 'OBJECT_ID',
500 connect_time_scale IN NUMBER DEFAULT 100,
501 think_time_scale IN NUMBER DEFAULT 100,
502 think_time_auto_correct IN BOOLEAN DEFAULT TRUE,
503 scale_up_multiplier IN NUMBER DEFAULT 1,
504 capture_sts IN BOOLEAN DEFAULT FALSE,
505 sts_cap_interval IN NUMBER DEFAULT 300);
506
507 -- ***********************************************************
508 -- START_REPLAY
509 -- Starts the workload replay. All the external replay clients (WRC)
510 -- that are currently connected to the replay database will
511 -- automatically be notified and those replay clients (WRC) will
512 -- begin issuing the captured workload.
513 --
514 -- NOTE: Once the START_REPLAY command has been executed,
515 -- new replay clients will not be able to connect to
516 -- the database and only clients that were started up
517 -- before the START_REPLAY command was issued will be
518 -- used to replay the captured workload.
519 --
520 -- The procedure does not accept any input parameters.
521 --
522 -- If a SQL set capture was requested and the start of the SQL set
523 -- capture failed, replay is cancelled. The reason is stored in
524 -- DBA_WORKLOAD_REPLAYS.ERROR_MESSAGE.
525 --
526 -- Prerequisites:
527 -- -> DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY was already issued.
528 -- -> Enough number of external replay clients (WRC)
529 -- that can faithfully replay the captured workload
530 -- have already been started. The status of such
531 -- external replay clients can be monitored using
532 -- V$WORKLOAD_REPLAY_CLIENTS.
533 -- Use the WRC's CALIBRATE mode to determine the number of
534 -- replay clients that might be required to faithfully replay
535 -- the captured workload.
536 -- Example:
537 -- $ wrc mode=calibrate replaydir=./capture
538 --
539 -- ***********************************************************
540 PROCEDURE START_REPLAY;
541
542 -- ***********************************************************
543 -- PAUSE_REPLAY
544 -- Pauses the in-progress workload replay. All subsequent
545 -- user calls from the replay clients will be stalled until
546 -- either DBMS_WORKLOAD_REPLAY.RESUME_REPLAY is issued
547 -- or the replay is cancelled.
548 --
549 -- Note: User calls that were already in-progress
550 -- when PAUSE_REPLAY was issued will be allowed to run
554 -- The procedure does not accept any input parameters.
551 -- to completion. Only subsequent user calls, when issued,
552 -- will be paused.
553 --
555 --
556 -- Prerequisites:
557 -- -> DBMS_WORKLOAD_REPLAY.START_REPLAY was already issued.
558 --
559 -- ***********************************************************
560 PROCEDURE PAUSE_REPLAY;
561
562 -- ***********************************************************
563 -- RESUME_REPLAY
564 -- Resumes a paused workload replay.
565 --
566 -- Prerequisites:
567 -- -> DBMS_WORKLOAD_REPLAY.PAUSE_REPLAY was already issued.
568 --
569 -- ***********************************************************
570 PROCEDURE RESUME_REPLAY;
571
572 -- ***********************************************************
573 -- IS_REPLAY_PAUSED
574 -- Returns whether the replay is currenty paused.
575 -- It returns TRUE if and only if PAUSE_REPLAY has been called
576 -- successfully and RESUME_REPLAY has not been called yet.
577 --
578 -- The procedure does not accept any input parameters.
579 --
580 -- Prerequisites:
581 -- -> DBMS_WORKLOAD_REPLAY.START_REPLAY was already issued.
582 --
583 -- ***********************************************************
584 FUNCTION IS_REPLAY_PAUSED
585 RETURN BOOLEAN;
586
587 -- ***********************************************************
588 -- CANCEL_REPLAY
589 -- Cancels the workload replay in progress.
590 -- All the external replay clients (WRC) will automatically
591 -- be notified to stop issuing the captured workload and exit.
592 --
593 -- The procedure will take as input the following parameters:
594 -- error_msg - an optional reason for cancelling the replay
595 -- can be passed which will be recorded
596 -- into DBA_WORKLOAD_REPLAYS.ERROR_MESSAGE
597 -- DEFAULT VALUE: NULL
598 --
599 -- Prerequisites:
600 -- -> DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY or PREPARE_REPLAY
601 -- or START_REPLAY was already issued.
602 --
603 -- ***********************************************************
604 PROCEDURE CANCEL_REPLAY(reason IN VARCHAR2 DEFAULT NULL);
605
606 -- ***********************************************************
607 -- GET_REPLAY_INFO
608 -- Look in the given directory object and retrieve information about the
609 -- workload captures and the history of workload replays.
610 --
611 -- The function skips loading the replay divergence data by default. To
612 -- load the data, set the parameter load_divergence to TRUE.
613 --
614 -- Replay information can be deleted by calling DELETE_REPLAY_INFO.
615 --
616 -- Arguments:
617 -- replay_dir - name of the directory object (case sensitive)
618 -- load_divergence - TRUE: load divergence data; FALSE: don't load
619 --
620 -- RETURNS
621 -- the capture id for a single capture directory; 0 for a
622 -- consolidated capture directory
623 -- ***********************************************************
624 FUNCTION GET_REPLAY_INFO(replay_dir IN VARCHAR2,
625 load_divergence IN BOOLEAN DEFAULT FALSE)
626 RETURN NUMBER;
627
628 -- ***********************************************************
629 -- GET_REPLAY_INFO
630 -- Look in the given directory object and retrieve information about the
631 -- workload captures and the history of workload replays.
632 --
633 -- The function skips loading the replay divergence data by default. To
634 -- load the data, set the parameter load_divergence to 'TRUE'.
635 --
636 -- Replay information can be deleted by calling DELETE_REPLAY_INFO.
637 --
638 -- Arguments:
639 -- replay_dir - name of the directory object (case sensitive)
640 -- load_divergence - 'TRUE': load divergence data; 'FALSE': don't load
641 --
642 -- RETURNS
643 -- the capture id for a single capture directory; 0 for a
644 -- consolidated capture directory
645 -- ***********************************************************
646 FUNCTION GET_REPLAY_INFO(replay_dir IN VARCHAR2,
647 load_divergence IN VARCHAR2)
648 RETURN NUMBER;
649
650 -- ***********************************************************
651 -- DELETE_REPLAY_INFO
652 -- Delete information about one replay. If the parameter permanent is
653 -- TRUE, also delete the data from disk.
654 --
655 -- Deleted replay information can be retrieved back by calling
656 -- GET_REPLAY_INFO if the replay data is not deleted from disk.
657 --
658 -- Arguments:
659 -- replay_id - ID of the workload replay that needs to be deleted
660 -- permanent - TRUE: delete data from disk; FALSE: don't delete data
661 -- from disk
662 -- ***********************************************************
663 PROCEDURE DELETE_REPLAY_INFO(replay_id IN NUMBER,
664 permanent IN BOOLEAN DEFAULT FALSE);
665
666 -- ***********************************************************
667 -- DELETE_REPLAY_INFO
668 -- Delete information for all replays in the given directory object. If
669 -- the parameter permanent is TRUE, also delete the data from disk.
670 --
674 -- Arguments:
671 -- Deleted replay information can be retrieved back by calling
672 -- GET_REPLAY_INFO if the replay data is not deleted from disk.
673 --
675 -- replay_dir - name of the directory object (case sensitive)
676 -- permanent - TRUE: delete data from disk; FALSE: don't delete data
677 -- from disk
678 -- ***********************************************************
679 PROCEDURE DELETE_REPLAY_INFO(replay_dir IN VARCHAR2,
680 permanent IN BOOLEAN DEFAULT FALSE);
681
682 -- ***********************************************************
683 -- LOAD_DIVERGENCE
684 -- Load divergence data for a given replay id.
685 --
686 -- Arguments:
687 -- replay_id - ID of the workload replay
688 -- ***********************************************************
689 PROCEDURE LOAD_DIVERGENCE(replay_id IN NUMBER);
690
691 -- ***********************************************************
692 -- LOAD_DIVERGENCE
693 -- Load divergence data for all replays in a given directory object.
694 --
695 -- Arguments:
696 -- replay_dir - name of the directory object (case sensitive)
697 -- ***********************************************************
698 PROCEDURE LOAD_DIVERGENCE(replay_dir IN VARCHAR2);
699
700 -- ***********************************************************
701 -- REMAP_CONNECTION
702 -- Remap the captured connection to a new one so that the
703 -- user sessions can connect to the database in a desired
704 -- way during workload replay.
705 --
706 -- By default, all replay_connections will be equal to NULL.
707 -- When the replay_connection is NULL (default), then the
708 -- replay sessions will connect to the default host as
709 -- determined by the replay client's runtime environment.
710 -- So, if no capture time connect strings are remapped, then
711 -- all the replay sessions will simply connect to the default host
712 -- to replay the workload.
713 --
714 -- A valid replay_connection should specify a connect identifier or
715 -- a service point. Please refer to the Oracle Database
716 -- Net Services Admin guide for various ways using which one
717 -- can specify connect identifiers (such as net service names,
718 -- database service names, and net service aliases) and various
719 -- naming methods that can be used to resolve a connect identifier
720 -- to a connect descriptor.
721 --
722 -- An error is returned if no row matches the given
723 -- connection_id.
724 --
725 -- The procedure will take as input the following parameters:
726 -- connection_id - ID of the connection to be remapped.
727 -- Corresponds to
728 -- DBA_WORKLOAD_CONNECTION_MAP.CONN_ID
729 -- replay_connection - new connection string to be used during replay.
730 --
731 -- NOTE:
732 -- Use the DBA_WORKLOAD_CONNECTION_MAP view to review all the
733 -- connection strings that will be used by the subsequent workload
734 -- replay, and also to look at connection string remappings
735 -- used for previous workload replays.
736 --
737 -- ***********************************************************
738 PROCEDURE REMAP_CONNECTION(connection_id IN NUMBER,
739 replay_connection IN VARCHAR2);
740
741 /***********************************************************************
742 * REMAP_CONNECTION
743 * This procedure remaps the recorded connection to a new one for a
744 * given capture in a multiple-capture replay
745 *
746 * The first parameter schedule_cap_id specifies capture in the replay
747 * schedule. It maps the connection in that capture.
748 *
749 * Arguments:
750 * schedule_cap_id - (IN) pointing to a capture in the schedule.
751 * It's the ID returned by ADD_CAPTURE
752 * (MANDATORY)
753 * connection_id - (IN) ID of the connection to be remapped
754 * (MANDATORY)
755 * replay_connection - (IN) new connection string to be used
756 * (MANDATORY)
757 *
758 ************************************************************************/
759 PROCEDURE REMAP_CONNECTION(schedule_cap_id IN NUMBER,
760 connection_id IN NUMBER,
761 replay_connection IN VARCHAR2);
762
763 /************************************************************************
764 * SET_USER_MAPPING
765 * This procedure sets a new schema/user name to be used during replay
766 * instead of the captured user.
767 *
768 * Arguments:
769 * schedule_cap_id - (IN) the id of the a capture in the scedule.
770 * caputre_user - (IN) the user name during the time of the
771 * workload capture
772 * replay_user - (IN) the user name to which captured user is
773 * remapped during replay
774 * Notes: - a schdule_cap_id of NULL is used for regular non-consolidated
775 * replay.
776 * - The replay must be initialized but not prepared in order
777 * to use this API
778 * - if replay_user is set to NULL the mapping is disabled
779 * - after multiple calls with the same capture_user, the last
783 * select * from dba_workload_active_user_map
780 * call always takes effect
781 * - to list all the mappings that will be in effect during
782 * the subsequent replay execute the following:
784 * - the overloaded version without the schedule_cap_id calls
785 * the one with the schedule_cap_id argument by passing in NULL
786 * - mappings are stored in a table made public through the view
787 * dba_workload_user_map. To remove old mappings execute
788 * delete * from dba_workload_user_map.
789 ***********************************************************************/
790 PROCEDURE SET_USER_MAPPING(schedule_cap_id IN NUMBER,
791 capture_user IN VARCHAR2,
792 replay_user IN VARCHAR2);
793 PROCEDURE SET_USER_MAPPING(capture_user IN VARCHAR2,
794 replay_user IN VARCHAR2);
795
796
797 -- ***********************************************************
798 -- REPORT
799 -- Generates a report on the given workload replay.
800 --
801 -- The function will take as input the following parameters:
802 -- replay_id - ID of the workload replay whose report
803 -- is requested.
804 -- (MANDATORY)
805 -- format - Specifies the report format
806 -- Valid values are
807 -- DBMS_WORKLOAD_REPLAY.TYPE_TEXT,
808 -- DBMS_WORKLOAD_REPLAY.TYPE_HTML,
809 -- (internal) DBMS_WORKLOAD_REPLAY.TYPE_XML and
810 -- (internal) DBMS_WORKLOAD_REPLAY.TYPE_XML_CC
811 -- (MANDATORY)
812 -- ***********************************************************
813
814 --
815 -- report type (possible values) constants
816 --
817 TYPE_XML CONSTANT VARCHAR2(3) := 'XML' ;
818 TYPE_HTML CONSTANT VARCHAR2(4) := 'HTML' ;
819 TYPE_TEXT CONSTANT VARCHAR2(4) := 'TEXT' ;
820 TYPE_XML_CC CONSTANT VARCHAR2(6) := 'XML_CC' ;
821
822 FUNCTION REPORT( replay_id IN NUMBER,
823 format IN VARCHAR2 )
824 RETURN CLOB;
825
826 -- ***********************************************************
827 -- COMPARE_PERIOD_REPORT
828 -- Generates a report comparing a replay to its capture or
829 -- to another replay of the same capture.
830 --
831 -- The function will take as input the following parameters:
832 -- replay_id1 - First ID of the workload replay whose
833 -- report is requested.
834 -- replay_id2 - Second ID of the workload replay whose
835 -- report is requested. If this is NULL,
836 -- the comparison is done with the capture.
837 -- format - Specifies the report format
838 -- Valid values are
839 -- DBMS_WORKLOAD_CAPTURE.TYPE_HTML and
840 -- DBMS_WORKLOAD_CAPTURE.TYPE_XML.
841 -- result - output of the report (CLOB).
842 -- Note, this procedure commits while running ADDM, so it
843 -- can not be used as a function inside a SELECT
844 -- ***********************************************************
845 PROCEDURE COMPARE_PERIOD_REPORT( replay_id1 IN NUMBER,
846 replay_id2 IN NUMBER,
847 format IN VARCHAR2,
848 result OUT CLOB );
849
850 -- ***********************************************************
851 -- COMPARE_SQLSET_REPORT
852 --
853 -- Generates a report comparing a sqlset captured during replay
854 -- replay to one captured during workload capture or to one
855 -- captured during another replay of the same capture.
856 --
857 -- The function will take as input the following parameters:
858 -- replay_id1 - First ID of the workload replay after
859 -- a change.
860 -- replay_id2 - Second ID of the workload replay before
861 -- a change. If this is NULL, the comparison
862 -- is done with the capture.
863 -- format - Specifies the report format
864 -- Valid values are
865 -- DBMS_WORKLOAD_CAPTURE.TYPE_HTML,
866 -- DBMS_WORKLOAD_CAPTURE.TYPE_XML and
867 -- DBMS_WORKLOAD_CAPTURE.TYPE_TEXT
868 -- r_level - see level parameter in
869 -- dbms_sqltune.report_analysis_task
870 -- r_sections - see section parameter in
871 -- dbms_sqltune.report_analysis_task
872 -- result - output of the report (CLOB).
873 --
874 -- RETURNS: the SPA task name for use later to retrieve the cached
875 -- report.
876 --
877 -- If no sqlset was captured the procedure returns NULL in the
878 -- result output variable. To enable sqlset capture during
879 -- workload capture and replay see DBMS_WORKLOAD_CAPTURE.START_CAPTURE
880 -- and DBMS_WORKLOAD_REPLAY.START_REPLAY.
881 -- ***********************************************************
882 FUNCTION COMPARE_SQLSET_REPORT( replay_id1 IN NUMBER,
883 replay_id2 IN NUMBER,
887 result OUT CLOB )
884 format IN VARCHAR2,
885 r_level IN VARCHAR2 := 'ALL',
886 r_sections IN VARCHAR2 := 'ALL',
888 RETURN VARCHAR2;
889
890 -- ***********************************************************
891 -- WORKLOAD_INTELLIGENCE_REPORT
892 --
893 -- Generates a report that displays the results of Workload
894 -- Intelligence.
895 --
896 -- The function will take as input the following parameters:
897 -- wi_job_name - Name that uniquely identifies the Workload
898 -- Intelligence job.
899 -- top_results - A number that indicates the maximum number
900 -- of top patterns to appear in the report.
901 -- format - Specifies the report format
902 -- Valid values are
903 -- DBMS_WORKLOAD_CAPTURE.TYPE_HTML and
904 -- DBMS_WORKLOAD_CAPTURE.TYPE_XML
905 --
906 -- RETURNS: the report
907 -- ***********************************************************
908 FUNCTION WORKLOAD_INTELLIGENCE_REPORT(wi_job_name IN VARCHAR2,
909 top_results IN NUMBER,
910 format IN VARCHAR2)
911 RETURN CLOB;
912
913
914 -- ***********************************************************
915 -- EXPORT_AWR / EXPORT_PERFORMANCE_DATA
916 -- Exports the AWR snapshots associated with a given
917 -- replay_id as well as any SQL Tuning sets captured along
918 -- with the replay.
919 --
920 -- At the end of each replay, the corresponding AWR snapshots
921 -- are automatically exported. So, there is no need to do this
922 -- manually after a workload replay is complete, unless the
923 -- automatic EXPORT_AWR() invocation failed.
924 --
925 -- NOTE: This procedure will work only if the corresponding
926 -- workload replay was performed in the current database
927 -- (meaning that the corresponding row in
928 -- DBA_WORKLOAD_REPLAYS was not created by calling
929 -- DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO()) and the
930 -- AWR snapshots that correspond to that replay
931 -- time period are still available.
932 --
933 -- The function will take as input the following parameters:
934 -- replay_id - ID of the replay whose AWR snapshots
935 -- should be exported.
936 -- (MANDATORY)
937 --
938 -- EXPORT_PERFORMANCE_DATA and EXPORT_AWR are equivalent.
939 -- ***********************************************************
940 PROCEDURE EXPORT_AWR( replay_id IN NUMBER );
941 PROCEDURE EXPORT_PERFORMANCE_DATA( replay_id IN NUMBER);
942
943 -- ***********************************************************
944 -- IMPORT_AWR/IMPORT_PERFORMANCE_DATA
945 -- Imports the AWR snapshots from a given replay, provided
946 -- those AWR snapshots were successfully exported earlier
947 -- from the original replay system.
948 --
949 -- If a sql tuning set was captured during the replay and
950 -- was successfully exported it will be imported also. The name
951 -- and owner of the sql tuning sets can be obtained form the
952 -- DBA_WORKLOAD_REPLAYS view.
953 --
954 -- In order to avoid DBID conflicts, this function will generate
955 -- a random DBID and use that DBID to populate the SYS AWR schema.
956 --
957 -- The function will take as input the following parameters:
958 -- replay_id - ID of the replay whose AWR snapshots
959 -- should be imported.
960 -- (MANDATORY)
961 -- staging_schema - Name of a valid schema in the current database
962 -- which can be used as a staging area
963 -- while importing the AWR snapshots
964 -- from the replay directory to the SYS AWR schema.
965 -- The 'SYS' schema cannot be used as a staging
966 -- schema and is not a valid input.
967 -- (MANDATORY)
968 -- force_cleanup - TRUE => any AWR data present in the given
969 -- staging_schema will be removed before
970 -- the actual import operation. All tables
971 -- with names that match any of the tables in AWR
972 -- will be dropped before the actual import.
973 -- This will typically be equivalent to
974 -- dropping all tables returned by the
975 -- following SQL:
976 -- SELECT table_name FROM dba_tables
977 -- WHERE owner = staging_schema
978 -- AND table_name like 'WR_$%';
979 -- Use this option only if you are sure that there
980 -- are no important data in any such tables in the
981 -- staging_schema.
982 -- FALSE => no tables will be dropped from
983 -- the staging_schema prior to the import operation.
984 -- DEFAULT VALUE: FALSE
985 --
986 -- NOTE: IMPORT_AWR will fail if the given staging_schema contains
987 -- any tables with a name that match any of the tables in AWR.
988 --
992 --
989 -- Returns the new randomly generated dbid that was used to
990 -- import the AWR snapshots. The same value can be found in
991 -- the AWR_DBID column in the DBA_WORKLOAD_REPLAYS view.
993 -- ***********************************************************
994 FUNCTION IMPORT_AWR( replay_id IN NUMBER,
995 staging_schema IN VARCHAR2,
996 force_cleanup IN BOOLEAN DEFAULT FALSE )
997 RETURN NUMBER;
998 FUNCTION IMPORT_PERFORMANCE_DATA(
999 replay_id IN NUMBER,
1000 staging_schema IN VARCHAR2,
1001 force_cleanup IN BOOLEAN DEFAULT FALSE )
1002 RETURN NUMBER;
1003
1004 -- ***********************************************************
1005 -- CALIBRATE
1006 -- Compute the estimated number of replay clients and cpu
1007 -- needed to replay a given workload.
1008 --
1009 -- The procedure will take as input the following parameters:
1010 -- capture_dir - name of the directory object that points to the
1011 -- (case sensitive)
1012 -- OS directory that contains processed capture
1013 -- data
1014 -- process_per_cpu - Maximum number of process allowed per CPU
1015 -- (default is 4)
1016 -- threads_per_process - Maximum number of threads allowed per
1017 -- process (default is 50)
1018 --
1019 -- Returns a CLOB formatted as XML, that contains:
1020 -- o capture information,
1021 -- o current database version,
1022 -- o the input to this function,
1023 -- o the number of cpus and replay clients needed to replay the
1024 -- given workload,
1025 -- o some information about the sessions captured
1026 -- (total number and maximum concurrency).
1027 --
1028 -- ***********************************************************
1029 FUNCTION CALIBRATE (capture_dir IN VARCHAR2,
1030 process_per_cpu IN BINARY_INTEGER DEFAULT 4,
1031 threads_per_process IN BINARY_INTEGER DEFAULT 50)
1032 RETURN CLOB;
1033
1034 -- ***********************************************************
1035 -- GET_CAPTURED_TABLES
1036 -- Extract from the capture files the list of Database objects that
1037 -- have been accessed by the captured workload on the capture system.
1038 --
1039 -- The procedure will take as input the following parameters:
1040 -- capture_dir - name of the directory object that points to the
1041 -- (case sensitive)
1042 -- OS directory that contains processed capture
1043 -- data
1044 --
1045 -- Returns a CLOB formatted as XML, that contains:
1046 -- o capture information,
1047 -- o current database version,
1048 -- o the list of Database objects that have been accessed by the
1049 -- captured workload on the capture system.
1050 --
1051 -- NOTES
1052 -- o This function needs to be run on a system with an identical
1053 -- schema definition as the capture system. The data contained
1054 -- in the schema is irrelevant.
1055 -- o This function will NOT extract the objects accessed by PL/SQL
1056 -- blocks, functions or procedures.
1057 --
1058 -- ***********************************************************
1059 FUNCTION GET_CAPTURED_TABLES(capture_dir IN VARCHAR2)
1060 RETURN CLOB;
1061
1062
1063 -- ***********************************************************
1064 -- GET_DIVERGING_STATEMENT
1065 -- Get some information on a diverging call, inluding the statement
1066 -- text, the SQL id and the binds.
1067 --
1068 -- The procedure will take as input the following parameters:
1069 -- replay_id - id of the replay in which that call diverged
1070 -- stream_id - stream_id of the diverging call
1071 -- call_counter - call_counter of the diverging call
1072 --
1073 -- You can get all these information about the diverging call from
1074 -- dba_workload_replay_divergence
1075 --
1076 -- Returns a CLOB formatted as XML, that contains:
1077 -- o SQL ID
1078 -- o SQL Text
1079 -- o Bind information: position, name and value
1080 --
1081 -- ***********************************************************
1082 FUNCTION GET_DIVERGING_STATEMENT(replay_id IN NUMBER,
1083 stream_id IN NUMBER,
1084 call_counter IN NUMBER)
1085 RETURN CLOB;
1086
1087 -- ***********************************************************
1088 -- POPULATE_DIVERGENCE
1089 -- Precompute the divergence information for the given call,
1090 -- stream or the whole replay, so that GET_DIVERGING_STATEMENT
1091 -- returns almost instantly for the precomputed calls.
1092 --
1093 -- The procedure will take as input the following parameters:
1094 -- replay_id - id of the replay
1095 -- stream_id - stream_id of the diverging call
1096 -- If NULL is provided, divergence information will
1097 -- be precomputed for all diverging calls in the given
1098 -- replay
1099 -- call_counter - call_counter of the diverging call
1100 -- If NULL is provided, divergence information will
1101 -- be precomputed for all diverging calls in the given
1102 -- stream
1103 --
1107 call_counter IN NUMBER DEFAULT NULL);
1104 -- ***********************************************************
1105 PROCEDURE POPULATE_DIVERGENCE(replay_id IN NUMBER,
1106 stream_id IN NUMBER DEFAULT NULL,
1108
1109 /**************************************************************************
1110 * POPULATE_DIVERGENCE_STATUS
1111 *
1112 * Status of the divergence detailed information for the given replay
1113 * - LOADED: all statement divergence information for this replay is
1114 * loaded
1115 * - LOADING: the RDBMS is currently undertaking a bulk load of all of
1116 * the statement divergence data for the given replay
1117 * - NOT LOADED: neither of the above, i.e., not LOADING and at least
1118 * 1 statement's divergence data has not been loaded
1119 **************************************************************************/
1120 FUNCTION POPULATE_DIVERGENCE_STATUS(replay_id IN NUMBER)
1121 RETURN VARCHAR2;
1122
1123 /**************************************************************************
1124 * DIVERGING_STATEMENT_STATUS
1125 *
1126 * For a single diverging call in a given replay, has its detailed
1127 * divergence information be loaded.
1128 * The possible results are:
1129 * - LOADED (statement divergence data for this statement is loaded)
1130 * - NOT LOADED (statement divergence data is not loaded yet)
1131 **************************************************************************/
1132 FUNCTION DIVERGING_STATEMENT_STATUS(replay_id IN NUMBER,
1133 stream_id IN NUMBER,
1134 call_counter IN NUMBER)
1135 RETURN VARCHAR2;
1136
1137 -- ***********************************************************
1138 -- ADD_FILTER
1139 -- Adds a filter to replay only a subset of the captured workload.
1140 --
1141 -- The ADD_FILTER() API adds a new filter that will
1142 -- be used in the next replay filter set that will be created using
1143 -- CREATE_FILTER_SET().
1144 -- This filter will be considered an "INCLUSION" or "EXCLUSION" filter
1145 -- based on the argument passed to CREATE_FILTER_SET() when creating
1146 -- the filter set.
1147 --
1148 -- *****************************
1149 -- SCOPE of the filter specified
1150 -- *****************************
1151 -- Filters once specified are valid only for the next succesful
1152 -- call to CREATE_FILTER_SET().
1153 -- After that, they will be part of the newly created set that can be
1154 -- used for any replay by calling USE_FILTER_SET().
1155 -- Filters used for past replays via a filter set can be queried from
1156 -- the DBA_WORKLOAD_FILTERS view.
1157 --
1158 -- The function will take as input the following parameters:
1159 -- fname - Name of the filter. Can be used to delete
1160 -- the filter later if it is not required.
1161 -- (MANDATORY)
1162 -- fattribute - Specifies the attribute on which the filter is
1163 -- defined. Should be one of the following values:
1164 -- USER - type STRING
1165 -- MODULE - type STRING
1166 -- ACTION - type STRING
1167 -- PROGRAM - type STRING
1168 -- SERVICE - type STRING
1169 -- CONNECTION_STRING - type STRING
1170 -- (MANDATORY)
1171 -- fvalue - Specifies the value to which the given
1172 -- 'attribute' should be equal to for the
1173 -- filter to be considered active.
1174 -- Wildcards like '%' are acceptable for all
1175 -- attributes that are of type STRING.
1176 -- (MANDATORY)
1177 --
1178 -- In other words, the filter for a NUMBER attribute will be
1179 -- equated as:
1180 -- "attribute = value"
1181 -- And, the filter for a STRING attribute will be equated as:
1182 -- "attribute like value"
1183 --
1184 -- Also, please note that the PROGRAM and SERVICE filters are just
1185 -- looking at the captured connection string that is used during replay,
1186 -- There are equivalent to:
1187 -- CONNECTION_STRING LIKE '%(PROGRAM=fvalue)%
1188 -- or CONNECTION_STRING LIKE '%(SERVICE=fvalue)%
1189 --
1190 -- ***********************************************************
1191 PROCEDURE ADD_FILTER( fname IN VARCHAR2,
1192 fattribute IN VARCHAR2,
1193 fvalue IN VARCHAR2);
1194 PROCEDURE ADD_FILTER( fname IN VARCHAR2,
1195 fattribute IN VARCHAR2,
1196 fvalue IN NUMBER);
1197
1198 -- ***********************************************************
1199 -- DELETE_FILTER
1200 -- Deletes the filter with the given name.
1201 --
1202 -- The function will take as input the following parameters:
1203 -- fname - Name of the filter that should be deleted.
1204 -- (MANDATORY)
1205 --
1206 -- ***********************************************************
1207 PROCEDURE DELETE_FILTER( fname IN VARCHAR2);
1208
1209 -- ***********************************************************
1210 -- REUSE_REPLAY_FILTER_SET
1211 -- Reuse filters in the specified filter set as if each of them
1212 -- were added using add_filter(). More than one filter sets can be added
1216 --
1213 -- through this procedure. Also, new filter rule can be added, existing
1214 -- filter can be deleted before invoking create_filter_set() to
1215 -- create new filter set.
1217 -- The function takes as input the following parameters:
1218 -- replay_dir - Capture id the existing filter set associated to
1219 -- filter_set - name of the filter set to be reused
1220 --
1221 -- ***********************************************************
1222 PROCEDURE REUSE_REPLAY_FILTER_SET(replay_dir IN VARCHAR2,
1223 filter_set IN VARCHAR2);
1224
1225
1226 -- ***********************************************************
1227 -- CREATE_FILTER_SET
1228 -- Uses all the replay filters that have been added (since the previous
1229 -- succesful call to CREATE_FILTER_SET) to create a set of filters to
1230 -- use against the replay in 'replay_dir'.
1231 -- This operation needs to be done when no replay is initialized,
1232 -- prepared or in progress.
1233 -- After that procedure completed successfully and the filter set has
1234 -- created, it can be used to filter the replay in 'replay_dir' by calling
1235 -- USE_FILTER_SET() after the replay has been initialized.
1236 --
1237 -- The procedure will take as input the following parameters:
1238 -- replay_dir - object directory of the replay to be filtered
1239 -- filter_set - name of the filter set to create
1240 -- (to use in USE_FILTER_SE)
1241 -- default_action - Can be either 'INCLUDE' or 'EXCLUDE'.
1242 -- Determines whether, by default, every captured call
1243 -- should be replayed or not. Also determines,
1244 -- whether the workload filters specified
1245 -- should be considered as INCLUSION filters or
1246 -- EXCLUSION filters.
1247 --
1248 -- If it is 'INCLUDE' then by default all captured calls
1249 -- will be replayed, except for the part of the
1250 -- workload defined by the filters.
1251 -- In this case, all the filters that were
1252 -- specified using the ADD_FILTER() API
1253 -- will be treated as EXCLUSION filters, and will
1254 -- determine the workload that WILL NOT BE replayed.
1255 --
1256 -- If it is 'EXCLUDE' then by default no captured
1257 -- call to the database will be replayed, except
1258 -- for the part of the workload defined by the
1259 -- filters. In this case, all the filters that were
1260 -- specified using the ADD_FILTER() API
1261 -- will be treated as INCLUSION filters, and will
1262 -- determine the workload that WILL BE replayed.
1263 --
1264 -- DEFAULT VALUE: 'INCLUDE' and all the filters
1265 -- specified will be assumed to be EXCLUSION filters.
1266 --
1267 -- ***********************************************************
1268 PROCEDURE CREATE_FILTER_SET(replay_dir IN VARCHAR2,
1269 filter_set IN VARCHAR2,
1270 default_action IN VARCHAR2 DEFAULT 'INCLUDE');
1271
1272 -- ***********************************************************
1273 -- USE_FILTER_SET
1274 -- Uses the given filter set that has been created by calling
1275 -- CREATE_FILTER_SET() to filter the current replay.
1276 -- This procedure should be called after the replay has been initialized,
1277 -- and before it is prepared.
1278 --
1279 -- The procedure will take as input the following parameters:
1280 -- filter_set - name of the filter set use in this replay
1281 --
1282 -- ***********************************************************
1283 PROCEDURE USE_FILTER_SET(filter_set IN VARCHAR2);
1284
1285 /***************************************************************************
1286 * GENERATE_CAPTURE_SUBSET
1287 * This procedure creates a new capture from an existing
1288 * workload capture.
1289 *
1290 * Auguments:
1291 * input_capture_dir - (IN) name of directory object pointing to
1292 * an existing workload capture
1293 * (MANDATORY)
1294 * output_capture_dir - (IN) directory object pointing to an empty
1295 * directory where the output workload
1296 * capture will be stored
1297 * (MANDATORY)
1298 * new_capture_name - (IN) name of the new output capture
1299 * (MANDATORY)
1300 * begin_time - (IN) begin time of a time range. It is the
1301 * time offset in seconds from the start
1302 * of the input workload capture
1303 * Default value is zero.
1304 * begin_include_incomplete - (IN) include incomplete calls caused by
1305 * begin_time. Default value is TRUE.
1306 * end_time - (IN) end time of a time range. It is the time
1307 * offset in seconds from the start of the
1308 * input workload capture. Zero is a special
1309 * value indicating the end of the capture.
1310 * Default value is zero.
1311 * end_include_incomplete - (IN) include incomplete calls caused by
1315 * Default value is 1
1312 * end_time. Default value is FALSE
1313 * parallel_level - (IN) number of Oracle processes used to generate
1314 * the new capture in a parallel fashion.
1316 ***************************************************************************/
1317 PROCEDURE GENERATE_CAPTURE_SUBSET(
1318 input_capture_dir IN VARCHAR2,
1319 output_capture_dir IN VARCHAR2,
1320 new_capture_name IN VARCHAR2,
1321 begin_time IN NUMBER DEFAULT 0,
1322 begin_include_incomplete IN BOOLEAN DEFAULT TRUE,
1323 end_time IN NUMBER DEFAULT 0,
1324 end_include_incomplete IN BOOLEAN DEFAULT FALSE,
1325 parallel_level IN NUMBER DEFAULT 1);
1326
1327 /***********************************************************************
1328 * SET_REPLAY_DIRECTORY
1329 * Set directory object to be used by a consolidated replay. This
1330 * procedure is deprecated. Use SET_CONSOLIDATED_DIRECTORY.
1331 *
1332 * Arguments:
1333 * replay_dir - directory object pointing to an OS directory that contains
1334 * multiple captures for workload consolidation
1335 * (case sensitive)
1336 ***********************************************************************/
1337 PROCEDURE SET_REPLAY_DIRECTORY(replay_dir IN VARCHAR2);
1338
1339 /***********************************************************************
1340 * SET_CONSOLIDATED_DIRECTORY
1341 * Set directory object to be used by a consolidated replay.
1342 *
1343 * Arguments:
1344 * replay_dir - directory object pointing to an OS directory that contains
1345 * multiple captures for workload consolidation
1346 * (case sensitive)
1347 ***********************************************************************/
1348 PROCEDURE SET_CONSOLIDATED_DIRECTORY(replay_dir IN VARCHAR2);
1349
1350 /***************************************************************************
1351 * GET_REPLAY_DIRECTORY
1352 * return a directory object name that is the current replay directory set
1353 * by SET_REPLAY_DIRECTORY;
1354 * return NULL if no replay directory has been set
1355 ***************************************************************************/
1356 FUNCTION GET_REPLAY_DIRECTORY RETURN VARCHAR2;
1357
1358 /***************************************************************************
1359 * BEGIN_REPLAY_SCHEDULE
1360 * Initiate the creation of a reusable replay schedule.
1361 *
1362 * Arguments:
1363 * replay_dir_obj - (IN) directory object pointing to the replay directory
1364 * that contains all the capture directories
1365 * involved in the schedule
1366 * schedule_name - (IN) identifier for this schedule
1367 ***************************************************************************/
1368 PROCEDURE BEGIN_REPLAY_SCHEDULE(schedule_name IN VARCHAR2);
1369
1370 /****************************************************************************
1371 * ADD_CAPTURE
1372 * Add the given capture to the current schedule.
1373 *
1374 * Arguments:
1375 * capture_dir - (IN) directory object pointing to the workload
1376 * capture under the top-level replay directory
1377 * (MANDATORY)
1378 * start_delay_secs - (IN) when the replay of this capture is ready to
1379 * start, this is the delay time in seconds that
1380 * the replay will wait before it starts
1381 * stop_replay - (IN) stop the whole replay after the replay of
1382 * this capture runs into completion
1383 * take_begin_snapshot - (IN) take an AWR snapshot when the replay of
1384 * this capture starts
1385 * take_end_snapshot - (IN) take an AWR snapshot when the replay of
1386 * this capture finishes
1387 * query_only - (IN) replay only the read-only queries of this
1388 * workload capture
1389 *
1390 * Returns:
1391 * A unique ID that identifies this capture within this schedule.
1392 */
1393 FUNCTION ADD_CAPTURE(capture_dir_name IN VARCHAR2,
1394 start_delay_seconds IN NUMBER DEFAULT 0,
1395 stop_replay IN BOOLEAN DEFAULT FALSE,
1396 take_begin_snapshot IN BOOLEAN DEFAULT FALSE,
1397 take_end_snapshot IN BOOLEAN DEFAULT FALSE,
1398 query_only IN BOOLEAN DEFAULT FALSE)
1399 RETURN NUMBER;
1400
1401 /******************************************************************
1402 * ADD_CAPTURE
1403 * Allow to add a given capture to the current schedule.
1404 * This function overloads the above one so that the ADD_CAPTURE
1405 * function can be used in a SELECT
1406 *
1407 * Arguments:
1408 * capture_dir - (IN) directory object pointing to the workload
1409 * capture under the top-level replay directory
1410 * (MANDATORY)
1411 * start_delay_secs - (IN) when the replay of this capture is ready to
1412 * start, this is the delay time in seconds that
1413 * the replay will wait before it starts
1417 * (MANDATORY)
1414 * (MANDATORY)
1415 * stop_replay - (IN) stop the whole replay after the replay of
1416 * this capture runs into completion
1418 * value 'Y' or 'N'
1419 * take_begin_snapshot - (IN) take an AWR snapshot when the replay of
1420 * this capture starts
1421 * (MANDATORY)
1422 * value 'Y' or 'N'
1423 * take_end_snapshot - (IN) take an AWR snapshot when the replay of
1424 * this capture finishes
1425 * (MANDATORY)
1426 * value 'Y' or 'N'
1427 * query_only - (IN) replay only the read-only queries of this
1428 * workload capture
1429 * (MANDATORY)
1430 * value 'Y' or 'N'
1431 *
1432 * Returns:
1433 * A unique ID that identifies this capture within this schedule.
1434 ******************************************************************/
1435 FUNCTION ADD_CAPTURE(capture_dir_name IN VARCHAR2,
1436 start_delay_seconds IN NUMBER,
1437 stop_replay IN VARCHAR2,
1438 take_begin_snapshot IN VARCHAR2 DEFAULT 'N',
1439 take_end_snapshot IN VARCHAR2 DEFAULT 'N',
1440 query_only IN VARCHAR2 DEFAULT 'N')
1441 RETURN NUMBER;
1442
1443 /****************************************************************************
1444 * REMOVE_CAPTURE
1445 * Remove the given capture from the current schedule.
1446 *
1447 * Arguments:
1448 * schedule_capture_id - (IN) unique ID that identifies this capture
1449 * within this schedule
1450 */
1451 PROCEDURE REMOVE_CAPTURE(schedule_capture_id IN NUMBER);
1452
1453 /****************************************************************************
1454 * ADD_SCHEDULE_ORDERING
1455 * Add a wait-for dependency between two captures in the replay schedule.
1456 *
1457 * Arguments:
1458 * schedule_capture_id - (IN) unique ID pointing to a capture that has
1459 * been added to the current replay schedule
1460 * waitfor_capture_id - (IN) pointing to a capture that has been added
1461 * to the current replay schedule
1462 * NULL means it does not wait for any capture.
1463 */
1464 PROCEDURE ADD_SCHEDULE_ORDERING(
1465 schedule_capture_id IN NUMBER,
1466 waitfor_capture_id IN NUMBER);
1467
1468 /****************************************************************************
1469 * REMOVE_SCHEDULE_ORDERING
1470 * Remove a wait-for dependency from a replay schedule.
1471 *
1472 * Arguments:
1473 * schedule_capture_id - (IN) unique ID pointing to a capture that has
1474 * been added to the current replay schedule
1475 * waitfor_capture_id - (IN) pointing to a capture that has been added
1476 * to the current replay schedule
1477 * NULL means it does not wait for any capture.
1478 */
1479 PROCEDURE REMOVE_SCHEDULE_ORDERING(
1480 schedule_capture_id IN NUMBER,
1481 waitfor_capture_id IN NUMBER);
1482
1483 /****************************************************************************
1484 * END_REPLAY_SCHEDULE
1485 * Wraps up the creation of the current schedule.
1486 * The schedule is now saved and associated with the replay directory
1487 * and can be used for a replay.
1488 */
1489 PROCEDURE END_REPLAY_SCHEDULE;
1490
1491 /****************************************************************************
1492 * REMOVE_REPLAY_SCHEDULE
1493 * This procedure removes an existing replay schedule. All the records
1494 * about its captures and the wait-for capture orders are also deleted.
1495 * The WMD file for replay schedule is modified accordingly.
1496 *
1497 * Arguments:
1498 * schedule_name - (IN) identifier for this schedule
1499 */
1500 PROCEDURE REMOVE_REPLAY_SCHEDULE(schedule_name IN VARCHAR2);
1501
1502 /**************************************************************************
1503 * INITIALIZE_CONSOLIDATED_REPLAY
1504 * initialize_replay for workload consolidation
1505 *
1506 * This procedure puts the DB state in INIT for a multiple-capture replay.
1507 * It uses the replay_dir which has already been defined by
1508 * SET_REPLAY_DIRECTORY, pointing to a directory that contains all the
1509 * capture directories involved in the schedule. It further read data
1510 * about schedule schedule_name from the directory. Similar to the
1511 * initialize_replay, it loads connection data etc. into the replay system
1512 * that is required before preparing the replay.
1513 *
1514 * Arguments:
1515 * replay_name - (IN) name of the workload replay. Every replay must
1516 * be given a name.
1517 * (MANDATORY)
1518 * schedule_name - (IN) identifier for this replay schedule
1519 * (MANDATORY)
1520 *************************************************************************/
1524 -- ***********************************************************
1521 PROCEDURE INITIALIZE_CONSOLIDATED_REPLAY(replay_name IN VARCHAR2,
1522 schedule_name IN VARCHAR2);
1523
1525 -- PREPARE_CONSOLIDATED_REPLAY
1526 -- Puts the DB state in PREPARE mode. This API is for
1527 -- a multiple-capture replay. The database should have been
1528 -- initialized for replay using
1529 -- DBMS_WORKLOAD_REPLAY.INITIALIZE_CONSOLIDATED_REPLAY(), and
1530 -- optionally any capture time connection strings that require
1531 -- remapping have been already done using
1532 -- DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION().
1533 -- ***********************************************************
1534 PROCEDURE PREPARE_CONSOLIDATED_REPLAY(
1535 synchronization IN BOOLEAN,
1536 connect_time_scale IN NUMBER DEFAULT 100,
1537 think_time_scale IN NUMBER DEFAULT 100,
1538 think_time_auto_correct IN BOOLEAN DEFAULT TRUE,
1539 capture_sts IN BOOLEAN DEFAULT FALSE,
1540 sts_cap_interval IN NUMBER DEFAULT 300);
1541
1542 PROCEDURE PREPARE_CONSOLIDATED_REPLAY(
1543 synchronization IN VARCHAR2 DEFAULT 'OBJECT_ID',
1544 connect_time_scale IN NUMBER DEFAULT 100,
1545 think_time_scale IN NUMBER DEFAULT 100,
1546 think_time_auto_correct IN BOOLEAN DEFAULT TRUE,
1547 capture_sts IN BOOLEAN DEFAULT FALSE,
1548 sts_cap_interval IN NUMBER DEFAULT 300);
1549
1550 /********************************************
1551 * START_CONSOLIDATED_REPLAY
1552 * start_replay for workload consolidation
1553 *
1554 * Prerequisites:
1555 * . DBMS_WORKLOAD_REPLAY.PREPARE_CONSOLIDATED_REPLAY has been issued
1556 * . Enough number of external replay clients (WRC) has been started
1557 *
1558 ********************************************/
1559 PROCEDURE START_CONSOLIDATED_REPLAY;
1560
1561 -- ***********************************************************
1562 -- GET_CLOCK
1563 -- Returns the current value of the replay clock
1564 -- via a kernel callout to kecpGetClock
1565 -- ***********************************************************
1566 FUNCTION GET_CLOCK RETURN NUMBER;
1567
1568 -- ***********************************************************
1569 -- END OF PUBLIC FUNCTIONS
1570 -- ***********************************************************
1571
1572
1573 -- ***********************************************************
1574 -- BEGIN PRIVATE FUNCTIONS and CONSTANTS
1575 -- The following functions are not supported and
1576 -- will not be documented.
1577 -- The usage of the following functions is strictly
1578 -- prohibited and their use will cause unpredictable behaviour
1579 -- in the RDBMS server.
1580 -- ***********************************************************
1581
1582 -- ***********************************************************
1583 -- PRIVATE FUNCTIONS: USED INTERNALLY (not supported)
1584 -- No documentation required!
1585 -- ***********************************************************
1586
1587 --
1588 -- CONSTANTS for CLIENT_CONNECT
1589 --
1590 KECP_CLIENT_CONNECT_LOGIN CONSTANT NUMBER := 1;
1591 KECP_CLIENT_CONNECT_ADMIN CONSTANT NUMBER := 2;
1592 KECP_CLIENT_CONNECT_GOODBYE CONSTANT NUMBER := 3;
1593 KECP_CLIENT_CONNECT_THRDFAIL CONSTANT NUMBER := 4;
1594 KECP_CLIENT_CONNECT_CHKPPID CONSTANT NUMBER := 5;
1595 KECP_CLIENT_CONNECT_CLOCK_TICK CONSTANT NUMBER := 6;
1596 KECP_CLIENT_CONNECT_CHK_VSN CONSTANT NUMBER := 7;
1597
1598 KECP_CMD_END_OF_REPLAY CONSTANT NUMBER := 1;
1599 KECP_CMD_REPLAY_CANCELLED CONSTANT NUMBER := 2;
1600
1601 FUNCTION CLIENT_CONNECT(who IN NUMBER,
1602 arg IN NUMBER DEFAULT 0)
1603 RETURN NUMBER;
1604
1605 PROCEDURE CLIENT_VITALS(id IN BINARY_INTEGER,
1606 name IN VARCHAR2,
1607 value IN NUMBER);
1608
1609 PROCEDURE CLIENT_GET_REPLAY_SUBDIR(replay_subdir OUT VARCHAR2,
1610 sched_cap_id OUT VARCHAR2);
1611
1612 FUNCTION PROCESS_REPLAY_GRAPH(parallel_level IN NUMBER DEFAULT 1)
1613 RETURN NUMBER;
1614
1615 /* Type used by current_uc_graph */
1616 TYPE uc_graph_record IS RECORD(time NUMBER, user_calls NUMBER, flags NUMBER);
1617 TYPE uc_graph_table IS TABLE OF uc_graph_record;
1618
1619 PROCEDURE export_uc_graph(replay_id NUMBER);
1620 PROCEDURE import_uc_graph(replay_id NUMBER);
1621 FUNCTION user_calls_graph(replay_id IN NUMBER)
1622 RETURN uc_graph_table PIPELINED;
1623 FUNCTION stop_sts_c(sts_name IN VARCHAR2,
1624 sts_owner IN VARCHAR2,
1625 in_db_caprep OUT BOOLEAN)
1626 RETURN BOOLEAN;
1627
1628 -- ***********************************************************
1629 -- GET_PROCESSING_PATH
1630 -- return the full path to the directory
1631 --
1632 -- The function will take as input the following parameters:
1633 -- capture_id - ID of the workload capture
1634 -- (MANDATORY)
1635 -- ***********************************************************
1636
1637 FUNCTION get_processing_path(capture_id IN NUMBER)
1638 RETURN VARCHAR2;
1639
1640 -- ***********************************************************
1641 -- GET_REPLAY_PATH
1642 -- return the full path to the directory
1643 --
1644 -- The function will take as input the following parameters:
1645 -- replay_id - ID of the workload replay
1646 -- (MANDATORY)
1647 -- ***********************************************************
1648
1649 FUNCTION get_replay_path(replay_id IN NUMBER)
1650 RETURN VARCHAR2;
1651
1652 PROCEDURE initialize_replay_internal( replay_name IN VARCHAR2,
1653 replay_dir IN VARCHAR2,
1654 replay_type IN VARCHAR2);
1655
1656 -- ************************************************************
1657 -- get_perf_data_export_status
1658 -- populates awr_data and sts_data with the filenames of the
1659 -- exported performance data. If no data exists, NULL is set
1660 -- to the appropriate output variable
1661 -- ************************************************************
1665
1662 PROCEDURE get_perf_data_export_status( replay_id IN NUMBER,
1663 awr_data OUT VARCHAR2,
1664 sts_data OUT VARCHAR2);
1666 -- ************************************************************
1667 -- Capture and Replay Attributes for EM
1668 -- For internal use only and subject to change in future releases
1669 -- ************************************************************
1670 PROCEDURE set_attribute(capture_id IN NUMBER,
1671 replay_id IN NUMBER,
1672 name IN VARCHAR2, -- VARCHAR2(50)
1673 value IN VARCHAR2); -- VARCHAR2(200)
1674
1675 FUNCTION get_attribute(capture_id IN NUMBER,
1676 replay_id IN NUMBER,
1677 name IN VARCHAR2)
1678 RETURN VARCHAR2;
1679
1680 PROCEDURE delete_attribute(capture_id IN NUMBER,
1681 replay_id IN NUMBER,
1682 name IN VARCHAR2);
1683
1684 -- persists all attributes across all captures and replays
1685 PROCEDURE persist_attributes(capture_id IN NUMBER);
1686
1687 -- load the latest attributes from the os file and upsert the changes
1688 -- in the existing attributes. Changes to the file are given priority
1689 PROCEDURE sync_attributes_from_file(capture_id IN NUMBER);
1690
1691 -- adjust dbtimezone based start and end time using the timezone offset
1692 -- recorded by AWR (internal use only).
1693 PROCEDURE adjust_times_to_snap_timezone(btime in out date,
1694 awrbsnap in number,
1695 etime in out date,
1696 awresnap in number,
1697 dbid in number);
1698
1699 END dbms_workload_replay;
[Home] [Help]