Transcript Leveraging Oracle 12c Database Real Application Testing (RAT) Suite

Regression Analyzed, Tout de Suite:
Leveraging Oracle 12c Database
Real Application Testing (RAT) Suite
Jim Czuprynski
OnX Enterprise Solutions
March 7, 2016
Assess. Design. Build. Manage.
My Credentials
• 35+ years of database-centric IT experience
• Oracle ACE Director
• Oracle DBA since 2001
• Oracle 9i, 10g, 11g OCP
• 100+ articles on databasejournal.com and ioug.org
• Regular speaker at Oracle OpenWorld, IOUG
COLLABORATE, and OTN ACE Tours
• Oracle-centric blog (Generally, It Depends)
• Taught core Oracle DBA courses (G/I+RAC, Exadata,
Performance Tuning, Data Guard) for Oracle
University
2
Assess. Design. Build. Manage.
Who We Are
> Solution Provider focused on
> Technology Solutions
> Comprehensive Service Offerings
> Extensive reach — U.S., Canada, and the U.K.
> $700M annual revenue with over $100M in
Services
> Industry Certifications across a broad
selection of best-in-class IT manufacturers
and technologies
> Significant investment in technical staff and
facilities to support our clients’ projects
3
Assess. Design. Build. Manage.
Our Agenda
• Real Application Testing (RAT): Concepts and Terminology
–SQL Performance Analyzer (SPA)
–Database Capture (DBC) and Database Replay (DBR)
• Oracle 12cR1 Enhancements
–Workload Intelligence
–Workload Scale-Up
–Consolidated Database Replay
• Leveraging Complementary Features and Use Cases
–Practical Performance Tuning Scenarios
–Database Migration Scenarios
• Q+A
4
Real Application Testing:
Concepts and Architecture
Assess. Design. Build. Manage.
Regression Happens … Often at the Worst Time
How can I determine if even making a minor change to my
database environment will impact application performance?
• Different settings for initialization parameters
–Reversing a non-default setting can often cause
performance deflection
• Different database versions or releases
–Remember, the Oracle query optimizer is “upgraded”
as part of just about every new release
• New versions of hardware, storage, or operating system
– ASM vs. NFS
– Enterprise Systems vs. “home-grown” environments
– Linux vs. Solaris vs. HPUX vs. AIX
6
Assess. Design. Build. Manage.
Regression Happens … Often at the Worst Time
How can I stress-test my current or a future database
environment beyond the point of no return?
• This is also known as testing to destruction
• Usually involves generating an overwhelming number
of users / sessions / requests for similar data
simultaneously
• May also require combining unanticipated
simultaneous workloads that have yet to occur at the
same time
Identifying regression vectors and impacts may be
extremely difficult
• Complex systems mean there’s often multiple
interactions between components that are
triggering regression
• Often degrades to finger-pointing between IT groups
7
Assess. Design. Build. Manage.
RAT Suite: Poor Acronym. Great Toolset!
SQL Performance Analyzer
• Analyzes SQL statements that comprise
application workloads or subsets thereof
• Detects changes in statement execution plans
because of:
–Different initialization parameters settings
–Different database or optimizer versions
–Different hardware, storage, or operating system
• Regressing SQL statements can be analyzed for
better performance via SQL Tuning Advisor
• Aimed primarily at improving query performance,
not DML
8
Assess. Design. Build. Manage.
RAT Suite: Database Workload Capture
• Captures an entire application workload,
including DML
• Workloads are captured in the precise order
in which they occur so that they can be
played back in that same order … or not
• Workloads can be filtered based on
execution attributes
–Database Service, Module, Action, Program, User,
Instance
• AWR data can be captured for later
comparison with replays
9
Assess. Design. Build. Manage.
Database Workload Capture
Shadow
Workload
Capture
Processes
PRODUCTION (11.2.0.4)
SCP
SCP
These shadow workload capture
processes record each user session’s
activity against the production
database with minimal overhead
(usually < 2% extra CPU)
S
C
C
+DATA
SCP
Application
Workloads
Workload
Capture
Directory
DBMS_WORKLOAD_CAPTURE
procedures will start, filter, and stop the
capture of actual application workloads
into shadow workload files containing
binary representation of DML and queries
10
Assess. Design. Build. Manage.
RAT Suite: Database Workload Replay
• Replays all or part of a captured application
workload
• Statements are replayed against test system,
including DML
• By default, workload is replayed in precisely the
same order as it was recorded
• Workloads can be selectively ramped up:
–Overwhelming number of users / sessions / requests for
same data
–Unexpected combination of simultaneous yet normal
workloads
–This enables testing to destruction, i.e. beyond a system’s
current capacity to execute the workload
11
Assess. Design. Build. Manage.
Database Workload Replay
WRC
Workload
Capture
Directory
TEST / QA (12.1.0.2)
(rewound to before
production capture point)
WRC
+DATA
WRC
Actually, there are
two sets of captured
workload files: the
workloads themselves
as well as the order in
which transactions
and queries originally
occurred
Workload Replay
Clients (WRCs) read
the captured
workload files and
play them back in
original order (or not,
if desired!)
DBMS_WORKLOAD_REPLAY
procedures will start, filter,
and stop the replay of
shadow workload files
containing binary versions
representing actual
application workloads
generated
12
Real Application Testing:
New Features in 12cR1
Assess. Design. Build. Manage.
Consolidated Replay
Permits the combination of different workloads
captured from different databases at different times
into one consolidated workload. Use cases include:
• Same workload, but generated in different 10gR2, 11gR1,
and 11gR2 environments
• Same workload generated on same database, but during
completely different timeframes
• Different workloads generated on same database captured
during wildly diverse conditions
14
Assess. Design. Build. Manage.
Workload Scale-Up
Enables the combination of workloads for simultaneous
execution for potential “test to destruction”
• Schema Remapping allows replaying a single workload into
multiple schemas simultaneously
• Time Shifting permits lining up different workloads so that their
peak activity periods occur simultaneously
• Workload Folding separates a single workload captured over a
longer duration into different sub-workloads, then playing those
workloads back simultaneously
15
Assess. Design. Build. Manage.
Workload Scale-Up: Examples
Workload Folding:
Time
Shifting:
Cut
07:00
– 14:00
Combine
07:00
14:00
workload
into –three
and 17:00
– 23:00
separate
sub-workloads
workloads
into a them
single
and then replay
workload
during replay
simultaneously
“Time07:00
– 14:00
“Folded”
17:00 – 23:00
Shifted”
Workload
Workload
16
Assess. Design. Build. Manage.
Workload Intelligence
Workload Intelligence analyzes a captured workload for execution
patterns that aren’t obvious to human brains
• Java-based program
• Requires creation of a
separate schema for
capturing results of analysis
• Returns one or more sets of
SQL statements that
comprise a workload
pattern
17
Real Application Testing:
Real-World Scenarios
Assess. Design. Build. Manage.
Scenario #1: Upgrade from 11gR2 to 12cR1
• Current Database Environment:
–Oracle 11gR2 (11.2.0.4) database on Microsoft Windows
–Supports primarily OLTP workload, some DSS, limited contention
• New Database Environment:
–Oracle 12cR1 (12.1.0.2) database on Oracle Linux
–A large non-partitioned table will now be partitioned
• Does the new environment handle current workloads?
–Queries against newly-partitioned table must perform 2X – 3X faster
–Do any existing queries demonstrate regression?
–Will placing tables in IMCS offer any benefits for analytic queries?
19
Assess. Design. Build. Manage.
Scenario #2: Transitioning to Hybrid Cloud
• Current Database Environment:
–Oracle 11gR2 (11.2.0.4) database
–Supports purely data warehouse / analytic workloads
–Database server is “bare metal” Exadata X2-2
• New Database Environment:
–Oracle 12cR1 (12.1.0.2) database, same application workloads
–Transitioning to Oracle Cloud DBaaS environment (Exadata X5-2)
• Does the new environment handle current workloads?
–Do any existing queries demonstrate regression?
–Is an Oracle Cloud Database Extreme Performance environment
required to handle the application workload?
20
Assess. Design. Build. Manage.
Scenario #3: Test to Destruction
• Current Database Environment:
–Oracle 11gR2 (11.2.0.4) database
–Oracle Enterprise Linux 5.6 virtualized via VirtualBox
–Supports both OLTP and DSS workloads with limited contention:
• Peak workload for OLTP between 10:00 - 11:00, 60 user sessions
• Peak workload for DSS between 19:00 – 21:00, 80 user sessions
• New Database Environment:
–Oracle 12cRx (12.2.x.x) database
–Oracle Enterprise Linux 7.2 virtualized via VMWare ESX
–Must support peak OLTP and DSS workloads simultaneously
–50% more memory available
–Oracle In-Memory Option licensing warily considered
• CIO and CFO must know: Is this new environment sufficient?
21
Assess. Design. Build. Manage.
Capturing a Workload
Create appropriate directory objects:
1
DROP DIRECTORY DBRCONTROL;
CREATE DIRECTORY DBRCONTROL AS '/home/oracle/DBRControl';
GRANT READ, WRITE ON DIRECTORY DBRCONTROL TO PUBLIC;
2
Apply filters
to exclude
any undesired
user sessions,
then start the
capture
process …
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER(
fname => 'SVC_ADHOC'
,fattribute => 'SERVICE'
,fvalue => 'ADHOC');
. . .
DBMS_WORKLOAD_CAPTURE.ADD_FILTER(
fname => 'SVC_TPCH'
,fattribute => 'SERVICE'
,fvalue => 'TPCH');
3
… allow the capture to
continue until all
desired workloads
have been recorded …
4 … and then end the
capture process
once all workloads
are complete:
DBMS_WORKLOAD_CAPTURE.START_CAPTURE(
name => 'DBR_CAPTURE_100'
,dir => 'DBRCONTROL'
,duration =>
NULL
BEGIN
,default_actionDBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(
=> 'EXCLUDE'
,auto_unrestrict => TRUE
timeout => 90
,capture_sts => TRUE
,reason => 'Termination (after 90 seconds)');
,sts_cap_interval
=> 5);
END;
END;
/
/
22
Assess. Design. Build. Manage.
Consolidated Replay: Setup
1
2
Prepare
the Replaydirectory
Scheduleobjects
from and new user account for replay clients:
Create
appropriate
previously-captured workloads:
DROP DIRECTORY DBRCONS;
. . .
CREATE DIRECTORY DBRCONS AS '/home/oracle/DBRControl';
cpt_id :=
GRANT READ, WRITE ON DIRECTORY DBRCONS TO PUBLIC; DBMS_WORKLOAD_REPLAY.ADD_CAPTURE(
DECLARE
DROP
DIRECTORY DBCOLTP;
capture_dir_name => 'DBCOLTP'
NUMBER;
CREATEcpt_id
DIRECTORY
DBCOLTP AS '/home/oracle/DBRControl/OLTP';
,start_delay_seconds => 0
BEGIN
GRANT READ, WRITE ON DIRECTORY DBCOLTP TO PUBLIC;
,stop_replay => FALSE
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(
DROP DIRECTORY
DBCDSS;
,take_begin_snapshot => TRUE
capture_dir
=>
'DBCOLTP');
CREATE DIRECTORY DBCDSS AS '/home/oracle/DBRControl/DSS';
,take_end_snapshot => TRUE
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(
GRANT READ,
WRITE ON DIRECTORY DBCDSS TO PUBLIC;
,query_only => FALSE
capture_dir => 'DBCDSS');
);
DROP USER wrc CASCADE;
CREATE DBMS_WORKLOAD_REPLAY.SET_REPLAY_DIRECTORY(
USER wrc
cpt_id :=
replay_dir
=>
'DBRCONS');
IDENTIFIED BY wrc
DBMS_WORKLOAD_REPLAY.ADD_CAPTURE(
DEFAULT TABLESPACE users
capture_dir_name => 'DBCDSS'
DBMS_WORKLOAD_REPLAY.BEGIN_REPLAY_SCHEDULE
(
TEMPORARY
TABLESPACE temp
,start_delay_seconds => 0
replay_dir_obj
=>
'DBRCONS'
PROFILE DEFAULT
,stop_replay => FALSE
=> 'TEST2DESTRUCT');
QUOTA ,schedule_name
UNLIMITED ON users;
,take_begin_snapshot => TRUE
GRANT CONNECT, DBA TO wrc;
,take_end_snapshot => TRUE
. . .
,query_only => FALSE
);
DBMS_WORKLOAD_REPLAY.END_REPLAY_SCHEDULE;
END;
/
23
Assess. Design. Build. Manage.
Consolidated Replay: Preparation and Startup
3
Initialize the consolidated replay and remap connections as necessary:
BEGIN
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_CONSOLIDATED_REPLAY(
DBMS_WORKLOAD_REPLAY.PREPARE_CONSOLIDATED_REPLAY(
replay_name => 'DBR_CONS_100'
synchronization => TRUE
,schedule_name
=>
'TEST2DESTRUCT‘);
,connect_time_scale
=> 0
Prepare the
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(
,think_time_scale => 0
Consolidated
connection_id => 1
,think_time_auto_correct => FALSE
=>
,capture_sts => TRUE
Replay:,replay_connection
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora12201)(PORT=1521))
,sts_cap_interval => 5
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DSS)))');
);
. . .
END;
END;
/
/
4
5
Kick off the minimum necessary Workload Replay Clients:
$> wrc wrc/wrc mode=replay replaydir=/home/oracle/DBRControl
$> wrc wrc/wrc mode=replay replaydir=/home/oracle/DBRControl
6
Start the Consolidated
Replay task:
BEGIN
DBMS_WORKLOAD_REPLAY.SET_REPLAY_TIMEOUT(FALSE);
DBMS_WORKLOAD_REPLAY.START_CONSOLIDATED_REPLAY;
END;
/
24
Assess. Design. Build. Manage.
Consolidated Replay: Monitoring via SQL*Plus
SELECT
SELECT
id
id
,name
,name
,schedule_name
,dbname
,capture_id
,dbversion
,user_calls
,directory
,status
,capture_id
,divergence_load_status DLS
,user_calls
,TO_CHAR(start_time,'yyyy-mm-dd ,status
hh24:mi:ss') start_dtm
,TO_CHAR(end_time,'yyyy-mm-dd hh24:mi:ss')
end_dtm
,TO_CHAR(prepare_time,'yyyy-mm-dd
hh24:mi:ss') prepare_dtm
,duration_secs
,synchronization
,num_clients
,connect_time_scale
,num_clients_done
,think_time_scale
Think
,(dbtime/1000000) dbtime_ss
,think_time_auto_correct TTAC
#
of
Replay
Think
Time
Scale
,(think_time/1000000) think_time_ss
,scale_up_multiplier SCM
Rply Replay
User
Prepare
Synch
Connect
Time Auto
Up Default
,(network_time/1000000)
,default_action
# of
Replay
Replay
Replay
Total TimeScale
Clnt
Netwk
Pause
ID#
Operation
Calls
Statusnetwork_time_ss
Time
ronized?
Scale Corre Think
Multipler Action
,pause_time
dba_workload_replays;
Rply
User
Start
End FROM
Time
Clnt --------Sess
DB
Time ----- --------Time
Time
Time
------------------------------------------------------------------167dba_workload_replays;
DBR_CONS_100
SCN Sess
0
0 FALSE (s)
1 INCLUDE
FROM
ID#
Calls Status 1943044
TimeCOMPLETEDTime 2016-02-29 (s)
Done
(s)
(s)
(s)
18:22:04------ ------ ------ ---------- ---------- ---------- ------------ -------- --------------------- ----------167 1943044 COMPLETED 2016-02-29 2016-02-29
629
2
2
19667.10 162448.47
3486.92
0
157 DBR_CONS_100 1943044
COMPLETED18:33:33
2016-02-29 SCN
0
0 FALSE
1 INCLUDE
18:23:04
16:17:57
157
113
102
1943044 COMPLETED 2016-02-29
2016-02-29
2
2
35494.17
201475.82
1943044 COMPLETED 2016-02-28
17:37:12
2016-02-28
563
2016-02-28 SCN
17:46:35
2
2
18411.01
174882.85
1943044 COMPLETED 2016-02-28
17:01:22
2016-02-28
17:10:19
2
2
16963.72
168811.75
113 DBR_CONS_100 1943044
COMPLETED16:33:05
2016-02-28
16:18:33
17:36:42
872
SCN
102 DBR_CONS_100 1943044 COMPLETED
0
0
0 FALSE
0 FALSE
4222.84
0
3701.86
0
3852.33
0
1 INCLUDE
1 INCLUDE
17:00:50
537
25
Assess. Design. Build. Manage.
Consolidated Replay: Monitoring via OEM 13c CC
26
Assess. Design. Build. Manage.
Consolidated Replay: Results
VARIABLE replay_report CLOB;
. . .
VARIABLE capture_id NUMBER;
Replay Schedule Information Schedule TEST2DESTRUCT
contains 2 captures:
VARIABLE
replay_id NUMBER;
----------------------------------------------------------------------------------------------------------------------------------|
DB
Name
|
DB
Id
|
Release
|
RAC
|
Replay
Name | Replay Status |
BEGIN
-------------------------------------------------------------------------|
Information
| Capture 1
|
:replay_id := 167;
| NCDB122 | 1289746138 | 12.2.0.0.2 | NO ---------------------------------------------------------| DBR_CONS_100 | COMPLETED
|
:replay_report :=
-------------------------------------------------------------------------| Name
| DBR_CAPTURE_100
|
DBMS_WORKLOAD_REPLAY.REPORT(
Replay
Information
---------------------------------------------------------replay_id => :replay_id
-------------------------------------------------| Status
| COMPLETED
|
|
Information
Replay
|
,format => |'TEXT');
----------------------------------------------------------------------------------------------------------END;
| Database
Name
| ORCL
|
| DBR_CONS_100
|
END; | Name
----------------------------------------------------------------------------------------------------------/
| Database
Version
| 11.2.0.3.0
|
| Status
| COMPLETED
|
SET LONGCHUNKSIZE
1000
----------------------------------------------------------------------------------------------------------. . .30000000
| Start Time
| 24-02-16 12:27:13
|
SET LONG
----------------------------------------------------------------------------------------------------------SET LINESIZE 2000
| AWR DB Id
| 1289746138
|
| End Time
| 24-02-16 12:32:48
|
SET PAGESIZE 0
----------------------------------------------------------------------------------------------------------PRINT |:replay_report
AWR Begin Snap Id
| 371
|
| Duration
| 5 minutes 35 seconds
|
----------------------------------------------------------------------------------------------------------| AWR End Snap Id
| 376
|
| DB Time
| 7363.499 seconds
|
----------------------------------------------------------------------------------------------------------| PL/SQL Capture Mode | TOP_LEVEL
|
| User Calls
| 356556
|
----------------------------------------------------------------------------------------------------------| Replay Schedule Name | TEST2DESTRUCT
|
-------------------------------------------------| Average Active Sessions | 21.98
|
. . .
----------------------------------------------------------
| Directory Object
| DBCOLTP
|
---------------------------------------------------------. . .
27
Assess. Design. Build. Manage.
Consolidated Replay: AWR Delta Comparisons
28
Thank You
Assess. Design. Build. Manage.
Thank You For Your Kind Attention!
If you have any questions or comments, feel free to:
E-mail me at [email protected]
Follow my blog (Generally, It Depends):
http://jimczuprynski.wordpress.com
Follow me on Twitter (@JimTheWhyGuy)
Connect with me on LinkedIn (Jim Czuprynski)
30