Oracle Database 11g: Capture & Replay

Oracle Database 11g : Database Capture and Database Replay Feature.
A.1 Capture the workload from production database.

BEGIN
  DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'TEST_CAPTURE01',
                           dir => 'DUMPDIR',
                           duration => 60);
END;
/

B.1  Process the Workload. You must process the captured workload before replay. Its recommended to perform this activity on Test system to avoid any adverse impact on production db performance.

BEGIN
  DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'DUMPDIR');
END;
/


Following steps would be require to replay a captured load on Test System.
1) Initialize the Captured workload you like to replay
Execute   DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => 'REPLAY001', replay_dir => 'DUMPDIR');


2) Query DBA_WORKLOAD_CONNECTION_MAP and ensure you happy with Captured and Replay mapping. update the Remap Connection if necessary using below  procedure.

Execute   DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (connection_id => 101,replay_connection => 'dlsun244:3434/bjava21');


3) Update any Parameter to non default value.
EXECUTE  DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE);


4) Connect to client, you want to use as Replay client and cd to location where capture & processed files are stored.
cd /u01/app/oracle/dumpdir

5) Run the replay client.
$ORACLE_HOME/bin/wrc username/password


6) Come back to previous session and start the replay.
 EXECUTE  DBMS_WORKLOAD_REPLAY.START_REPLAY ();

7) use Oracle dictionary Views or Enterprise manager to review progress.

Gather Table statistics without without invalidating cursors

Gather stats for EMP table in Scott schema without invalidating the Cursor and Explain plan stored in memory.

execute DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP',NO_INVALIDATE=>TRUE);

PL/SQL procedure successfully completed.

Setting NO_INVALIDATE to TRUE will ensure plan/cursor doesn't get invalidated after stats collection.

Initializing and Tuning Parameters for Parallel Execution

PARALLEL_MAX_SERVERS <=> CPU_COUNT x PARALLEL_THREADS_PER_CPU x 2x 5 <=>Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance.

PARALLEL_EXECUTION_MESSAGE_SIZE <=> 2 KB (port specific) <=> Increase to 4k or 8k to improve parallel execution performance if sufficient SGA memory exists.

PARALLEL_ADAPTIVE_MULTI_USER <=> TRUE <=> Causes parallel execution SQL to throttle DOP requests to prevent system overload.

The following example shows a statement that sets the DOP to 4 on a table:

ALTER TABLE orders PARALLEL 4;
This next example sets the DOP on an index to 4:

ALTER INDEX iorders PARALLEL 4;

This last example sets a hint to 4 on a query:

SELECT /*+ PARALLEL(orders, 4) */ COUNT(*) FROM orders;


The initialization parameter PARALLEL_MIN_PERCENT specifies the desired minimum percentage of requested parallel execution servers. This parameter affects DML and DDL operations as well as queries