#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 63Jacques.kostic@trivadis.com

Confirmez votre présence et évaluez la session avec ce QRC.

Un vol en montgolfière à gagner !