Transcript Viscosity-RATBestPractices-v1

Viscosity is a team of experts
specializing in Oracle and
Engineered Systems
vv
Oracle
Real Application Testing
Nitin Vengurlekar
Justin Nugent
vv
Team Viscosity
• Oracle Gold Partner
•
Oracle Certified Professionals
•
Oracle Certified RAC Experts
•
Oracle Certified SOA Experts
•
Oracle Certified E-Business Suite Experts
•
Oracle Certified Engineered Systems
2
2
We Wrote the Books!
3
Program Agenda
Slide 1 of 118
1
Real Application Testing (RAT) Review
2
Use case: SQL Performance Analyzer (SPA) for
Optimizer Statistics
3
Use case: SPA for Schema Optimization
4
Use case: RAT for Validate HCC and Flash Cache with
Query Only Replay
How to ensure stability with system changes?
• Operational changes
– Implement SQL Profiles, Tuning Advisor
– Refresh statistics on table, schema or
database level
– Change optimizer related init.ora
parameters like OPTIMIZER_MODE…
– Change memory related init.ora like
PGA_AGGREGATE_TARGET…
• Non-operational changes
– Adding or dropping indexes,
table partitioning...
– New features like Compression,
In-Memory...
– Infra structure changes like server,
storage, interconnect...
– Consolidation
– Exadata, SuperCluster
– Upgrades and patching 10g ->11g ->
12c, 12.1.0.1 -> 12.1.0.2, PSU 2...
5
Program Agenda
1
Real Application Testing Review
2
Use case: SPA for Optimizer Statistics
3
Use Case: SPA for Schema Optimization
4
Use case: RAT for Validate HCC and Flash Cache with
Query Only Replay
Real Application Testing Features
End-to-end testing with real workloads
Capture
Workload
Replay
Workload
• SQL Performance Analyzer (SPA)
• SQL unit testing for response time
Create Test
System
Deploy Replay
Clients
• Database Replay
• Load, performance testing for throughput
• Identify and tune regressed SQL
• Always use SPA first
• Remediate application concurrency problems
SPA Challenges
Running SPA on:
Test System: Safe but…
•
•
Test
Database
Production
Database
Production System: Easier but…
•
Requires separate HW
Could be resource intensive
and impact production
performance
Data in test system should be
same as production
•
• Changes needs to be
manually scoped to private
session
Lengthy, error-prone task
DBA
•
•
Could take a long time to
finish
No resource control by default
8
SPA Best Practices
Optimized
Controlled
ChangeAware
• Optimized for use on prod systems
• Optimal Trial or Explain Plan mode
• Disable multi-executions, full DML execute disabled
• Per SQL time limits
• Testing scoped to private session
• Associate with Resource Consumer Group
• Context-aware change testing workflows, such as,
• Optimizer gather statistics
• Init.ora parameter changes
SPA Methodology
Optimal
Trial Mode,
no DML
execute
Controlled/S
coped
Optimized
Change-Aware
Pre-selected
STS and
default SPA
settings
Per SQL Time
Limits, Limits
testing scope to
private session
Context-aware
change testing
Production
Database
DBA
9
SPA Best Practices
Optimized
Identifies subset
SQL workload with
plan changes first
Test-executes only
SQLs with plan
changes
Minimizes use of
production
resources
dramatically – up
to 10x reduction
Multiple executions
disabled
No full DML
(execute Select part
of workload)
10
SPA Best Practices
Controlled
Per-SQL time limit – protects from runaway SQL
Resource throttling - Associate with Resource Consumer Group
Testing scope limited to private session
11
SPA Best Practices
Change-aware
Change-aware: Knows what change is being tested
In-line with routine DBA tasks such as statistics gathering, init.ora
parameter changes
Intelligently limits impact to private test session
12
Consolidated Databases with SPA
Consolidation Testing using Real SQL Workload
SALES
HR
CRM
Validates SQL performance
for consolidated database
•
SQL workload captured for
each database in STS
•
SPA executes all workloads
together in consolidated
environment
SALES STS
HR STS
HR
SALES
ERP
CRM
ERP
•
ERP STS
•
Identifies SQL regressions and
helps remediate them
•
Existing SPA capability in DB
11.1 works for schema
consolidation
CRM STS
•
Will support Pluggable DB
consolidation in DB12
Database Replay Best Practices
• Database load and performance
testing with real production workloads
– Production workload characteristics
such as timing, transaction
dependency, think time, etc., fully
maintained
• Test and measure transaction
throughput improvements
• Identify application scalability and
concurrency problems
• Use for server and OS consolidation
Production
Test
Clients
Replay Driver
Storage
Storage
– Capture individual workloads
– Replay workloads concurrently
14
Consolidated Database Replay
• Workload captured on different databases
(including different supported platforms,
versions) can be replayed concurrently
Production
Clients
Test
Replay Driver
• Works for schema consolidation and
Pluggable Databases
• Identify and remediate inter-application
scalability and concurrency problems
Storage
Storage
• Allows scale-up, subsetting, scheduling of
multiple workloads
• Available for 11.2.0.2 and above, MOS
Note: 1453789.1
15
DB Replay: Workload Scale Up for Capacity Planning
SALES
Time-Shifting
HR
ERP
CRM
• Enables capacity planning by scaling up workload replay
• Time-shifting: Align workload peaks for maximum concurrency
Copyright © 2013, Oracle and/or its affiliates. All rights
reserved.
DB Replay: Workload Scale Up for Capacity Planning
Workload Folding
8
9
10
11
12
13
14
15
16
• Enables capacity planning by scaling up workload replay
• Time-shifting: Align workload peaks for maximum concurrency
• Workload folding: Split single capture into multiple pieces and replay them concurrently
Copyright © 2013, Oracle and/or its affiliates. All rights
reserved.
DB Replay: Workload Scale Up for Capacity Planning
Schema Duplication
SALES01
SALES
SALES02
• Enables capacity planning by scaling up workload replay
• Time-shifting: Align workload peaks for maximum concurrency
• Workload folding: Split single capture into multiple pieces and replay them concurrently
• Schema duplication: Duplicate and replay workload in each schema concurrently
Copyright © 2013, Oracle and/or its affiliates. All rights
reserved.
Query only Replay
• Query Only load and performance testing with production
workloads
Production
Test
Clients
Replay Driver
– Production workload characteristics such as timing, think time,
etc., fully maintained
– No DML
• Test and measure SQL throughput improvements
SQL
• Identify application scalability and concurrency problems
• Use for server and OS consolidation
– Capture individual workloads
• Database state unchanged after Query only replay
– No database restore required
Storage
Storage
• No limitation
– Can be used for any application
• Allows scale-up, subsetting, scheduling of multiple workloads
• Optimal state of the database: Post capture or Active DG
19
Which feature to use?
Change
Description
SQL Profiles
Implement SQL profiles
Schema Tuning
Adding or droping indexes, Partitioning...
Optimizer Statistics
Refresh statistics on Table,schema or database level
Init.ora Optimizer
DB_FILE_MULTIBLOCK_READ_COUNT, OPTIMIZER_MODE…
Init.ora Memory
SGA_MAX_SIZE, PGA_AGGREGATE_TARGET (Concurrency related)
Features/Options
Compression, In-Memory...
Infra structure
Server, storage, Interconnect...
Upgrades
11g -> 12c, 12.1.0.1 -> 12.1.0.2...
Consolidation
Server Consolidation, Multitenant...
Capacity planning
Server Consolidation, Increasing user activity...
Reactive SQL Performance
regression analysis
Find changes in plans and workloads between different
days by using Baseline SQL tuning set.
Proactive Identification of high
risk SQL statements
Find SQL statements whare SQL plans can change on
increasing Data Volumes...
SPA
Query
Only
Replay
Database
Replay
Concurrent
Replay
Program Agenda
1
Real Application Testing Review
2
Use case: SPA for Optimizer Statistics
3
Use case: SPA for Schema Optimization
4
Use case: RAT for Validate HCC and Flash Cache with
Query Only Replay
Optimizer Statistics
• Data growth
– Nee to update statistics for optimal query plans
• New statistics?????????
– Can lead to regression
• Which way to go?????
– Stale statistics (Slowly degenerated performance)
– New statistics (Will there be any regression?)
• Optimizer Statistics – Best Practices
– Make sure there are no regression
22
Optimizer Statistics Refresh
Gather Statistics
• Go to Optimizer Statistics
Page
• Choose Gather Statistics
• Choose the extent
• Choose to validate with
SPA
• Choose object
according to dialog
• Submit
23
Optimizer Statistics Refresh
SPA Validation
• Select your SPA Task
• Select the comparison
report
– Report between First
and Second Trial
identifies queries with
plan changes
– Report between Third
and Fourth trial identifies
differences during
execution
• Identify
regression
24
Optimizer Statistics Refresh
Remedy and publish
• Remedy regression
– SQL Plan Baseline
– Tuning advisor
• Implement (for this
example)
– SQL Plan Baseline
• Validate again
• Publish Statistics
25
Program Agenda
1
Real Application Testing Review
2
Use case: SPA for Statistics Refresh
3
Use case: SPA for Schema Optimization
4
Use case: RAT for Validate HCC and Flash Cache with
Query Only Replay
Schema Optimization
Retain existing indexes after migration to SuperCluster?
• Workload on SuperCluster is not running
faster than on old servers
• How to make it go faster?
• Should queries use Exadata Storage smart
scan or Index range scan?
• Similarly, should index be dropped with Exadata
Storage
• Test with invisible indexes!
27
Schema Optimization
• Drop Indexes
– May impact workload performance
– Time consuming to recreate
• No custom SPA workflow available in
Enterprise Manager 12c
– Test manually with SPA Quick Check method
using invisible indexes
• Only be done during maintenance
window
– Will change query plans during testing
28
Schema Optimization
• Create Analysis Task
Step1
• DBMS_SQLPA.CREATE_ANALYSIS_TASK
• Run Explain Plan on all statements with current indexing
Step2
• EXECUTE_ANALYSIS_TASK….execution_type => 'EXPLAIN PLAN‘….
• Hide indexes
Step 3
• alter index Index_name1 INVISIBLE;
• Run Explain Plan on All statements without current indexing
Step 4
• EXECUTE_ANALYSIS_TASK….execution_type => 'EXPLAIN PLAN‘….
29
Schema Optimization
• Create report on statements with plan changes
Step5
• EXECUTE_ANALYSIS_TASK….execution_type => 'compare performance’
• Create filter for SQL statements with new execution plans
Step6
• Apply filters to target SQL with new plans
• Expose indexes to session
Step 7
• alter session SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE';
• Execute All statements with plan changes with current indexing
Step 8
• EXECUTE_ANALYSIS_TASK….execution_type => 'TEST EXECUTE'….
30
Schema Optimization
• Hide indexes
Step9
• alter session SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE';
• Execute All statements with plan changes without current indexing
Step10
• EXECUTE_ANALYSIS_TASK….execution_type => 'TEST EXECUTE'….
• Generate compare analysis
Step 11
• EXECUTE_ANALYSIS_TASK…execution_type => 'COMPARE PERFORMANCE‘…
• Generate SPA Active Report
Step 12
• spool spa_active.html, SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK… type => 'active', …
31
Schema Optimization
View the result
32
Program Agenda
1
Real Application Testing
2
Use case: SPA for Statistics Refresh
3
Use case: SPA for Schema Optimization
4
Use case: RAT for Validate HCC and Flash Cache with
Query Only Replay
Validate HCC with Query Only Replay
• How to configure In-memory?
– Which tables to cache and HCC?
– HCC
– Exadata Storage Cache
• Large production Environment
– Time consuming to clone
– No extra hardware
• Standby database
– Standby database???????
34
Validate HCC with Query Only Replay
Snapshot Standby
Physical Standby
• Works in conjunction with Real
Application Testing
– Provides a simple way to test and
maintain protection
– Hardware available
– Current data set
Apply Logs
Open
Databa
se
Back
out
Change
s
Snapshot Standby
Perform Testing
Continuous Redo Shipping
while in Snapshot mode
35
Validate HCC with Query Only Replay
Production
Clients
Storage
Snapshot Standby
Replay Driver
Storage
36
Validate HCC with Query Only Replay
• Capture Workload
Step1
• Use Enterprise manager or API (DBMS_WORKLOAD_CAPTURE.START_CAPTURE)
• Convert to Snapshot Standby
Step2
• Use Enterprise manager or API (convert database to snapshot standby)
• Move and process capture on Snapshot Standby
Step 3
• Use Enterprise manager or API (DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE)
37
Validate HCC with Query Only Replay
• Run baseline replay – No DML are executed so cannot compare with production
• Repeat to warm the cache - Need to use consolidated replay API
Step4
•
•
•
•
Query Only Replay flag
exec dbms_workload_replay.set_replay_directory(’HCCFC');
exec dbms_workload_replay.begin_replay_schedule('S1');
exec dbms_workload_replay.add_capture(capture_dir_name => ‘HCCFC’,…, query_only => 'Y');
exec dbms_workload_replay.end_replay_schedule…
• Make changes
Step5
• alter table DISTRIBUTION_DEPT_TAB2 … MOVE COMPRESS FOR QUERY HIGH;
• alter table DISTRIBUTION_DEPT_TAB2 … STORAGE (CELL_FLASH_CACHE KEEP);
38
Validate HCC with Query Only Replay
• Run Query Only replay – No DML is executed so we can not compare with production (Repeat to
Step6
warm the flash cache)
• Need to use consolidated replay API
• exec dbms_workload_replay.start_consolidated_replay;
• Generate reports and analyze result
Step 7
• Use Enterprise Manager or API
• exec DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT…
39
Use Replay Compare Period Report
40
Real Application Testing Best Practices
• Check for & Apply latest RAT related patches before getting started
• Ensure storage for Capture files are on high-thru/low latency storage subsystem. RAC, it will need to be on 10GBe NFS
• Ensure no references to external types on production systems during replay
• Filter to only needed sessions\users\programs using dbms_workload_capture.add_filter, eg,EM agents, etc sessions
• Avoid workload interference if possible
• First test with a small capture duration and validate the capture/replay works
• Get comfortable with Workload Analyzer
• Time/Date Management – sysdate references, disable NTP , etc.
• Calibrate clients
• Be careful comparing DB Time between replay
41
Summary
•SPA – Used for Validation
•Operation Fixes
•Performance Fixes
•Structural changes
•Becomes business as usual
•Replay
•Final Validation – After SPA
•Concurrency
42
Which feature to use?
Change
Description
SQL Profiles
Implement SQL profiles
Schema Tuning
Adding or droping indexes, Partitioning...
Optimizer Statistics
Refresh statistics on Table,schema or database level
Init.ora Optimizer
DB_FILE_MULTIBLOCK_READ_COUNT, OPTIMIZER_MODE…
Init.ora Memory
SGA_MAX_SIZE, PGA_AGGREGATE_TARGET (Concurrency related)
Features/Options
Compression, In-Memory...
Infra structure
Server, storage, Interconnect...
Upgrades
10->11g -> 12c, 12.1.0.1 -> 12.1.0.2...
Consolidation
Server Consolidation, Multitenant...
Capacity planning
Server Consolidation, Increasing user activity...
Reactive SQL Performance
regression analysis
Find changes in plans and workloads between different
days by using Baseline SQL tuning set.
Proactive Identification of high
risk SQL statements
Find SQL statements whare SQL plans can change on
increasing Data Volumes...
SPA
Query
Only
Replay
Database
Replay
Concurrent
Replay
Our clients
4
4