–Test version: 11.1.0.6
–
–1.Capture workload
–
Before capturing a database workload, carefully consider the following options:
*Restarting the Database
–to avoid capture partial transaction
*Defining the Workload Filters
–inclusion filters or exclusion filters, not both
*Setting Up the Capture Directory
–capturing a database workload using APIs, Adding and Removing Workload Filters
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
fname => ‘user_t’,
fattribute => ‘USER’,
fvalue => ‘T’);
END;
/
–this will filte out all sessions beloings to user T
–fattribute should be PROGRAM, MODULE, ACTION, SERVICE, INSTANCE_NUMBER, and USER
–wildcard can be used in fvalue, such as %
–to delete a filter
BEGIN
DBMS_WORKLOAD_CAPTURE.DELETE_FILTER (fname => ‘user_ichan’);
END;
/
–start_capture
SYS@ORA11G>create directory testdir as ‘D:\oracle\reorg’;
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => ‘mytest’,
dir => ‘TESTDIR’,
duration => 600);
END;
/
–it will stop automatically after 600 seconds
–if duration is not specified, call the following statement to stop it
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE ();
END;
/
–export AWR data for workload capture
Exporting AWR data enables detailed analysis of the workload.
This data is also required if you plan to run the AWR Compare Period report
on a pair of workload captures or replays.
–get the capture id
SYS@ORA11G>select id from DBA_WORKLOAD_CAPTURES;
ID
———-
1
–export awr of this id
BEGIN
DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id => 1);
END;
/
–
–2.Preprocessing workload
–
–This is very resource contention, should not run on production system
–copy the dirctory files to the replay db, and run the procedure
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => ‘TESTDIR’);
END;
/
–
–3.Replaying a Database Workload
–
–step1. setup test system
Using rman restore/duplicate to test system
do the change you want, such as parameter change, hardware change
resetting system time, set the system time to capture start time
–step2. replaying
copy the preprocessed files to a db directory
resolving references to external system: db links, external tables, directories,URL, Email
remapping connections: the connection string used to connect the production system are captured
options:
synchronization
connect_time_scale
think_time_scale
–setting up replay clients
replay program: $ORACLE_HOME/bin/wrc, a multithreaded program
wrc [user/password[@server]] MODE=[value] [keyword=[value]]
–Calibrating Replay Clients
C:\Documents and Settings\Zengw>wrc t/t mode=calibrate replaydir=D:\oracle\reorg
Workload Replay Client: Release 11.1.0.6.0 – Production on Wed Dec 10 16:15:37 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Report for Workload in: D:\oracle\reorg
———————–
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s).
Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 1
Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
–OEM is the primary tools to do replay
--initializing replay data
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => ‘mytest_replay’,
replay_dir => ‘TESTDIR’);
END;
/
–setting workload replay options
BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE);
END;
/
–start replay clients
C:\Documents and Settings\Zengw>wrc t/t mode=replay replaydir=D:\oracle\reorg
Workload Replay Client: Release 11.1.0.6.0 – Production on Wed Dec 10 16:23:07 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (16:23:07)
–i get the below output after the below procedure is done
Replay started (16:23:48)
Replay finished (16:25:39)
–starting a workload replay in another window
BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/
–Exporting AWR Data for Workload Replay
–get the replay id
SYS@ORA11G>select id from dba_workload_replays;
ID
———-
1
BEGIN
DBMS_WORKLOAD_REPLAY.EXPORT_AWR (replay_id => 1);
END;
/
–workload replay views
DBA_WORKLOAD_CAPTURES
DBA_WORKLOAD_FILTERS
DBA_WORKLOAD_REPLAYS
DBA_WORKLOAD_REPLAY_DIVERGENCE
DBA_WORKLOAD_CONNECTION_MAP
–
–4.Analyzing replay workload
–
–generating a workload capture report, OEM/procedure
set serveroutput on
DECLARE
cap_id NUMBER;
cap_rpt CLOB;
rpt_len NUMBER;
BEGIN
cap_id := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir => ‘TESTDIR’);
cap_rpt := DBMS_WORKLOAD_CAPTURE.REPORT(capture_id => cap_id,format => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT);
rpt_len :=DBMS_LOB.GETLENGTH(cap_rpt);
dbms_output.put_line(‘length:’||rpt_len);
dbms_output.put_line(DBMS_LOB.SUBSTR(cap_rpt,32767,1)); –if rpt_len < 32767
END;
/
length:11623
Database Capture Report For ORA11G
DB Name DB Id Release RAC Capture Name Status
————
———– ———– — ————————– ———-
ORA11G 2333620726 11.1.0.6.0 NO mytest COMPLETED
Start time: 10-Dec-08 15:43:05 (SCN = 237383)
End time: 10-Dec-08 15:48:25
(SCN = 238531)
Duration: 5 minutes 20 seconds
Capture size: 1.99 KB
Directory object: TESTDIR
Directory path: D:\oracle\reorg
Directory shared in RAC: TRUE
Filters used: 1 EXCLUSION filter
Captured Workload Statistics DB: ORA11G Snaps: 13-14
-> ‘Value’ represents the corresponding statistic aggregated across the entire captured database workload.
-> ‘% Total’ is the percentage of ‘Value’ over the corresponding system-wide aggregated total.
Statistic Name Value %Total
—————————————- ————- ———
DB time (secs) 0.06 0.22
Average Active Sessions 0.00
User calls captured 4 8.00
User calls captured with Errors 0
Session logins 0 0.00
Transactions 0 0.00
————————————————————-
Top Events Captured DB:ORA11G Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top Service/Module Captured DB:ORA11G Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top SQL Captured DB:ORA11G Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top Sessions Captured DB:ORA11G Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top Events containing Unreplayable Calls DB:ORA11G Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top Service/Module containing Unreplayable Calls DB:ORA11G Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top SQL containing Unreplayable Calls DB:ORA11G Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top Sessions containing Unreplayable Calls DB:ORA11G Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top Events Filtered Out DB:ORA11G Snaps: 13-14
Avg Active Event Event Class % Event Sessions
———————————– ————— ———- ———-
CPU + Wait for CPU
CPU 37.70 0.07
db file scattered read User I/O 1.64 0.00
db file sequential
read User I/O 1.64 0.00
————————————————————-
Top Service/Module Filtered Out DB: ORA11G Snaps: 13-14
Service Module % Activity
Action % Action
————– ———————— ———- —————— ———-
SYS$USERS
SQL*Plus 39.34 UNNAMED 39.34
sqlplus.exe 1.64 UNNAMED
1.64
————————————————————-
Top SQL Filtered Out DB: ORA11G Snaps: 13-14
SQL ID % Activity Event % Event
———————–
————– —————————— ——-
a6ta5bs4qwy6p 31.15 CPU + Wait for CPU
31.15
select count(*) from t,t2
2jpf0qbj9vxq3 3.28 CPU + Wait for CPU 3.28
** SQL Text Not Available **
fcwj05qyp0f6w 3.28 db file scattered read 1.64
** SQL Text Not Available **
db file sequential read 1.64
bs8bzd41vz3×0 1.64 CPU + Wait for CPU 1.64
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => ‘mytest’,
dir => ‘TESTDIR’, duration => 600); END;
g9u5j7y6cfxxq
1.64 CPU + Wait for CPU 1.64
** SQL Text Not Available **
————————————————————-
Top Sessions Filtered Out DB:
ORA11G Snaps: 13-14
-> ‘# Samples Active’ shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity.
-> ‘XIDs’ shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event
-> For sessions running Parallel Queries, this section will NOT aggregate the PQ slave activity into the session issuing the PQ. Refer to the ‘Top Sessions running PQs’ section for such statistics.
Sid, Serial# % Activity
Event % Event
————— ———- —————————— ———-
User
Program # Samples Active XIDs
——————– —————————— ——————
——–
88, 7 39.34 CPU + Wait for CPU 36.07
T sqlplus.exe
22/320 [ 7%] 2
db file scattered read 1.64
1/320 [ 0%] 1
db file sequential read 1.64
1/320 [ 0%] 1
61, 27 1.64 CPU + Wait for CPU 1.64
SYS sqlplus.exe
1/320 [ 0%] 0
————————————————————-
Top Events (Jobs and
Background Activity) DB: ORA11G Snaps: 13-14
Avg
Active
Event Event Class % Event Sessions
———————————– —————
———- ———-
db file sequential read User I/O 14.75 0.03
CPU + Wait for CPU
CPU 9.84 0.02
control file parallel write System I/O 9.84 0.02
null event
Other 6.56 0.01
control file sequential read System I/O 4.92 0.01
————————————————————-
Top Service/Module (Jobs and Background Activity) DB:ORA11G Snaps: 13-14
Service Module % Activity Action % Action
————–
———————— ———- —————— ———-
SYS$BACKGROUND UNNAMED 37.70 UNNAMED
37
PL/SQL procedure successfully completed.
–generating a workload replay report , OEM/procedure
set serveroutput on
DECLARE
cap_id NUMBER;
rep_id NUMBER;
rep_rpt CLOB;
rpt_len NUMBER;
BEGIN
cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(dir => ‘TESTDIR’);
/* Get the latest replay for that capture */
SELECT max(id)
INTO rep_id
FROM dba_workload_replays
WHERE capture_id = cap_id;
rep_rpt := DBMS_WORKLOAD_REPLAY.REPORT(replay_id => rep_id,
format => DBMS_WORKLOAD_REPLAY.TYPE_TEXT);
rpt_len :=DBMS_LOB.GETLENGTH(rep_rpt);
dbms_output.put_line(‘length:’||rpt_len);
dbms_output.put_line(DBMS_LOB.SUBSTR(rep_rpt,32767,1)); –if rpt_len < 32767
END;
/
–format can be DBMS_WORKLOAD_REPLAY.TYPE_TEXT, DBMS_WORKLOAD_REPLAY.TYPE_HTML, and DBMS_WORKLOAD_REPLAY.TYPE_XML.
length:6603
DB Replay Report for mytest_replay
————————————————————————–
—————–
———————————————————-
| DB Name | DB Id | Release | RAC | Replay Name | Replay Status
|
————————————————————————–
| ORA11G | 2333620726 | 11.1.0.6.0 | NO |
mytest_replay | COMPLETED |
————————————————————————–
Replay Information
—————————————————————-
| Information | Replay | Capture
|
—————————————————————-
| Name | mytest_replay | mytest
|
—————————————————————-
| Status | COMPLETED | COMPLETED
|
—————————————————————-
| Database Name | ORA11G | ORA11G
|
—————————————————————-
| Database Version | 11.1.0.6.0 | 11.1.0.6.0
|
—————————————————————-
| Start Time | 10-DEC-08 16:23:38 | 10-DEC-08 15:43:05
|
—————————————————————-
| End Time | 10-DEC-08 16:24:02 | 10-DEC-08 15:48:25
|
—————————————————————-
| Duration | 24 seconds | 5 minutes 20 seconds
|
—————————————————————-
| Directory Object | TESTDIR | TESTDIR
|
—————————————————————-
| Directory Path | D:\oracle\reorg | D:\oracle\reorg
|
—————————————————————-
Replay Options
———————————————————
| Option Name | Value
|
———————————————————
| Synchronization | TRUE
|
———————————————————
| Connect Time | 100%
|
———————————————————
| Think Time | 100%
|
———————————————————
| Think Time Auto Correct | TRUE
|
———————————————————
| Number of WRC Clients | 1 (1 Completed, 0 Running )
|
———————————————————
Replay Statistics
———————————————————–
| Statistic | Replay | Capture
|
———————————————————–
| DB Time | 4.388 seconds | 0.061 seconds
|
———————————————————–
| Average Active Sessions | .18 | 0
|
———————————————————–
| User calls | 4 | 4
|
———————————————————–
| Network Time | 0.000 seconds | .
|
———————————————————–
| Think Time | 0.000 seconds | .
|
———————————————————–
Replay Divergence Summary
——————————————————————-
| Divergence Type | Count
| % Total |
——————————————————————-
| Session Failures During Replay |
0 | 0.00 |
——————————————————————-
| Errors No Longer Seen During Replay |
0 | 0.00 |
——————————————————————-
| New Errors Seen During Replay |
0 | 0.00 |
——————————————————————-
| Errors Mutated During Replay |
0 | 0.00 |
——————————————————————-
| DMLs with Different Number of Rows Modified |
0 | 0.00 |
——————————————————————-
| SELECTs with Different Number of Rows Fetched |
0 | 0.00
|
——————————————————————-
————————————————————-
————————————–
Workload Profile Top Events
————————————————–
| No data
exists for this section of the report. |
————————————————–
Top
Service/Module/Action
————————————————–
| No data exists for this section of the report.
|
————————————————–
Top SQL with Top Events
————————————————–
| No
data exists for this section of the report. |
————————————————–
Top Sessions with Top
Events
————————————————–
| No data exists for this section of the report.
|
————————————————–
Replay Divergence Session Failures By
Application
————————————————–
| No data exists for this section of the report.
|
————————————————–
Error Divergence By
Application
————————————————–
| No data exists for this section of the report.
|
————————————————–
By SQL
————————————————–
| No data exists for
this section of the report. |
————————————————–
By
Session
————————————————–
| No data exists for this section of the report.
|
————————————————–
DML Data Divergence By
Application
————————————————–
| No data exists for this section of the report.
|
————————————————–
By SQL
————————————————–
| No data exists for
this section of the report. |
————————————————–
SELECT Data Divergence By
Application
————————————————–
| No data exists for this section of the report.
|
————————————————–
End of Report.
PL/SQL procedure successfully completed.
–cause I am doing the capture and replay on same db, there is no much divergence
–appendix: sql statements tested
T@ORA11G>create table t as select * from dba_objects;
Table created.
T@ORA11G>select count(*) from dba_objects;
COUNT(*)
———-
12091
T@ORA11G>create index t_oid on t(object_id);
Index created.
T@ORA11G>create index t_oname on t(object_name);
Index created.
T@ORA11G>update t set object_id=99999999-object_id;
12091 rows updated.
T@ORA11G>commit;
Commit complete.
T@ORA11G>select min(object_id) from t;
MIN(OBJECT_ID)
————–
99987491
T@ORA11G>create table t2 as select * from dba_objects;
Table created.
T@ORA11G>select count(*) from t,t2 where t.object_id=t2.object_id;
COUNT(*)
———-
0
T@ORA11G>select count(*) from t,t2 where t.object_name=t2.object_name;
COUNT(*)
———-
18629
T@ORA11G>create index t2_oname on t2(object_name);
Index created.
T@ORA11G>select count(*) from t,t2 where t.object_name=t2.object_name;
COUNT(*)
———-
18629
T@ORA11G>update t set object_id=99999999-object_id;
12091 rows updated.
T@ORA11G>select count(*) from t,t2 where t.object_id=t2.object_id;
COUNT(*)
———-
12091
T@ORA11G>commit;
Commit complete.
T@ORA11G>select count(*) from t,t2;
COUNT(*)
———-
146228554
Filed under: Performance Management | Tagged: 11g, scratch
