#SDF16
Oracle MultitenantRetour d'une première expérience en production
Jacques KosticPrincipal Consultant IMS Lausanne
#SDF16
Experience• Oracle DBA since 1990 (from Oracle 4)• High Availability and Backup & Recovery Architect• SQL and Instance Performance & Tuning• License Audit and Consolidation
Certifications• Oracle Certified Master 11g & 12c• Oracle 11g Performance Tuning Certified Expert• Oracle RAC 11g and Grid Infrastructure Administration• Oracle Certified SQL Expert 11g• ITIL Foundation
Teaching Courses at Trivadis• Oracle 11g & 12c Grid Infrastructure & RAC• Oracle 11g & 12c Data Guard• Oracle 11g & 12c Performance & Tuning• Oracle 11g & 12c Administration• SQL – PL-SQL
About me
#SDF16
Agenda1. Customer context
2. Backgrounds
3. Why considering Multitenant?
4. Project execution
5. What’s new in 12.2
6. Conclusion
7. Q/A
#SDF16
Constomer Context
#SDF16
Constomer ContextThe name will not be disclosed but the most relevant characteristics to the project are reported below.
Customer Environment
Medium size customer from financial sector Few databases with different workload types
Virtual Private Databases
High level of automation
Consolidation opportunities with the Multitenant Option
#SDF16
Backgrounds
#SDF16
Backgrounds2013 : Initial setup
Oracle 11.2.0.3
dNFS
6 Cores per nodes2 Production RAC databases
DataGuard
4 Cores per nodes2 Standby RAC databases ADGUp to 5 test databases
ODA V1• 96 GB of RAM per nodes• 24 cores (12 per nodes)• 6 TB in Normal
Redundancy
#SDF16
Backgrounds2015 : Upgrade
Oracle 12.1.0.2 with Singletenant6 Cores per nodes2 Production RAC databases
4 Cores per nodes2 Standby RAC databases ADGUp to 5 test databases
ODA V1• 96 GB of RAM per nodes• 24 cores (12 per nodes)• 6 TB in Normal
Redundancy
dNFS
DataGuard
#SDF16
BackgroundsPerformance challenges after migration to 12c
Execution plan changes Data model Stale statistics
Some queries with huge performance deviations New 12c features
• SQL Plan directives due to miss estimates• Adaptive dynamic sampling triggered automatically when
parallelism is in the game• Some bugs…
How do we fixed
that?
#SDF16
Backgrounds
SQL > Alter system set optimizer_adaptive_features=FALSE;
System altered.
SQL>
Et voila!
#SDF16
Why considering Multitenant?
#SDF16
Why considering Multitenant
New customer to absorb
Double the size of all databases
Have the same performance in test environments
Same automation procedures
Keep eyes to the cost!
#SDF16
Why considering Multitenant
2016 : Second Upgrade Oracle 12.1.0.2 with Multitenant
6 Cores per nodes2 Production RAC databases
DataGuard
6 Cores per nodes2 Standby RAC databases ADGUp to 5 test databases
ODA X5-V2• 256 GB of RAM per nodes• 72 cores (36 per nodes)• 64 TB in Normal Redundancy
#SDF16
Project execution
#SDF16
Project execution
Production databases
One small OLTP database
One mix OLTP-DWH medium database VPD! Financial consolidation can be scheduled at any time
by end users Average execution time is within one hour Parallel executions are used for certain steps
#SDF16
Project execution
Test databases
Can be refreshed on demand Directly from production From any backup
Performance tests before pushing changes into production
Has to be closest to production in term of resource allocation
#SDF16
Project execution
What have we done?
Use more SGA as the ODA X5-2 (256 GB per nodes versus 96 GB)
Remove instance caging and introduce a CDB Resource Plan
Limit the PGA by setting pga_aggregate_limit parameter
#SDF16
Project executionAdjust statistics collection methods
Relocate some tablespaces
Optimize Undo for temporary tables by setting TEMP_UNDO_ENABLED parameter
Setup Result Cache for some queries
Fine tune some queries
#SDF16
Project execution
Result after few days
Customer was happy, jobs are running 55% faster
Refreshes from production are completed 40% faster
Performance in test environment is becoming comparable to production
General end user perception was good!
#SDF16
Project execution
Quiz 1
Production Instance was very slow
Several jobs were running into the two PDBs
Massive database waits: free buffer waits!!
#SDF16
Project execution
Possible causes:
The I/O system is slow. Waiting for resources, such latches. The buffer cache is so small and DBWR spends most
of it's time cleaning out buffers for server processes. The buffer cache is so big and they are not enough
DBWR processes to free enough buffers in the cache to satisfy requests.
#SDF16
Project execution
Quiz 2
Production Instance was very slow
Many small transactions were running into one PDB
Massive database waits: latch free!! Result_cache latch
#SDF16
Project execution
Possible causes:
Some tables have the result_cache property to force.
To many queries with Result_Cache hint. To many concurrent sql plan directives running. There is not enough shared pool to handle all
result_cache requests. The result_cache_mode parameter was set to
FORCE
#SDF16
Project execution
Limitations of the 12.1 version
Cannot clone a PDB online
Cannot flashback or point in time at PDB level
SGA per PDB
IO management per PDB
#SDF16
What’s new in 12.2
#SDF16
What’s new in 12.2
Shared pool
Buffer cache
I/O activity
SGA_MIN_SIZE
MAX_IOPSMAX_MBPS
#SDF16
What’s new in 12.2
Online PDB Clone
Refreshable Clone PDB
Flashback at PDB level
New Snapshot capabilities
Local Undo
#SDF16
What’s new in 12.2
4K PDBs per CDB
Per-PDB Character sets
AWR per PDB
Heat Map per PDB
#SDF16
Conclusion
#SDF16
Conclusion
Multitenant is becoming and will be a key player
It’s just consolidation topic that you have to address
trivadis is your key helper!
#SDF16
trivadis Oracle Multitenant WorkshopThis workshop will give you a complete and comprehensive overview to manage and take profit of the Multitenant option.
Content Concepts and Architecture
Overview What is Consolidation About provisioning and cloning Manage your resources
efficiently Patching in Multitenant
Environment
Language French English
Organization 2 days Theory - Mornings Practice - Afternoons Participants: Minimum 1, Maximum 6
MaterialsYou will be given Workshop PDF, two virtual machines under Linux and scripts of exercises covering all topics.
#SDF16
Questions/RéponsesJacques KosticPrincipal Consultant IMS Lausanne
Tél. +41 79 909 72 [email protected]
Confirmez votre présence et évaluez la session avec ce QRC.
Un vol en montgolfière à gagner !
Top Related