top 10 tips and tricks to get the best database performance from solaris systems con7526...

Top 10 Tips and Tricks to Get the Best Database Performance from Solaris SystemsCON7526Intercontinental - Grand Ballroom

Ken Kutzer, Ritu Kamboj, Viraj NrOracle, Systems DivisionOctober 2, 2014

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

11

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. | 3

Safe Harbor StatementThe following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.


Program Agenda

Introduction

Deployment Tips and Practices

Operating Tips and Tunings

Summary and Resources

1

2

3

4


IntroductionKen Kutzer


Customers are Choosing Oracle SolarisLarge European Telco Moved to Solaris

Replaced:• HP x86• Red Hat Linux• VMware

• 2X Performance for the same number of licenses

• 10X more customer transactions per day

“ We moved to SPARC systems for 3 main reasons: Improved performance, reliability and scalability, and great integration features of Oracle Solaris 11 and Oracle Database.”


One Engineering Team

Engineered TogetherBest performance, availability, security, efficiency

Tested Together150,000 machine hours every week10M+ machine hours annually

Certified TogetherWe take the risk for you

Deployed TogetherYour application up in hours, not months

Upgraded TogetherRisk free patching/updates

Managed Together

3x lower administration and maintenance costsSupported TogetherCoordinated response for your deployment


The Ultimate Software Optimizations: HardwareSoftware in Silicon

8

Database Query Acceleration

Data Decompression

Application Data Integrity

Low-Latency Clustering

Software in Silicon

Moving Oracle Database & Data Functions into Hardware

T5 Baseline(1 Thread)

M7(1 Query Pipeline)

Preliminary Results(Single Stream Decompression)


Deployment Tips and PracticesRitu Kamboj


Rapid, Trouble Free Oracle Database Installation

• Single Solaris package contains everything needed to install Database• Installs and verifies all packages needed for Oracle Database 12c are

present on the system• Reduce preparation effort, avoid errors and installation delays

Simplified Installation on Oracle Solaris

#1

#pkg install oracle-rdbms-server-12-1-preinstall

Available For

Oracle Database 12.1.0.1Oracle Solaris 11.2


Monitor Oracle Database Health on Oracle Solaris

• Reports system health risks with the ability to drill down into specific problems and understand their resolutions

• Provides a single dashboard to view collections across entire enterprise• Additions in latest Orachk tool (version 2.2.5)

– Runs checks for multiple databases in parallel– New profile options to run only pre and post upgrade checks

Proactively reports problems for Oracle Database and Oracle Solaris by OraChk

#2

Available For

Oracle Database 11.1.0.1 or greaterOracle Solaris 11.1 or greater

For more information please seeMOS Note 1268927.1


Reduce Oracle Database Large Memory Startup Time

• Oracle Solaris parallelizes shared memory allocation– Oracle Solaris auto tunes degree of parallelism per operation

• Oracle Database brings the database up before allocating entire SGA• Up to 21x faster database startup time

Large Memory Enables the Real Time Enterprise

Available For

Oracle Database 12.1.0.1 or greaterOracle Solaris 11.1 SRU 10 or greater

S11.1+11g (S-tarting point)

S11.1 SRU10+11g

S11.1 SRU10+12c

0

5

10

15

20

Spee

dup

#3


Leverage SPARC Critical Thread Technology

• Assign exclusive access to a core’s hardware resources to a process• Up to 30% reduction in Oracle LGWR latency• For Oracle 12.1.0.1, used for LGWR and LMS processes by default on start up• For Oracle 11g, can be enabled with the command

Oracle’s SPARC M6, T5 and T4 Processors running Oracle Software

#4

#priocntl -s -c FX -m 60 -p 60 -pid <process id for lgwr>

Available For



Leverage On-line SGA Resizing Through OSM

• Background– Oracle 12c built for massive consolidation– Demands a platform which facilitates dynamic resource provisioning

• Combines the benefits of ISM and DISM– Robust mechanism for dynamic resizing of SGA (growing and shrinking)– Physically locked memory with large pages and shared Page Table entries

• Additional benefits– Optimized for memory architecture of Oracle Database – No scope for memory paging & no need for swap space configuration

Optimized Shared Memory (OSM)

Background Information


Leverage On-line SGA Resizing Through OSM

• For Oracle 12.1.0.2, OSM is used by default

• For Oracle 12.1.0.1, OSM is only used when you configure SGA to grow dynamically

• Monitor using “pmap –xs”

OSM Makes Large Memory Solaris Platforms Ready for Consolidation

Available For


#5

memory_max_target = 400G


Use a Single Database Version for Higher Efficiency

• Install shared Oracle home in global zone• Shared background processes across instances, simplified patching and

upgrades• Mount Oracle home from global zone to non-global zone

Enable Higher Consolidation Density with Solaris Zones

#6

#zonecfg -z my-zonezonecfg:my-zone> add fszonecfg:my-zone:fs> set dir=/u01zonecfg:my-zone:fs> set special=/u01zonecfg:my-zone:fs> set type=lofszonecfg:my-zone:fs> end


When Practical, Create Solaris Zone along Core Boundary

• Create a processor set with a full core and assign it to a zone

• Ensure memory assignment is prioritized from local– Enable processor set aware lgrp memory allocation– In /etc/system

• Apply patch for bug id 18499306 for higher consolidation densities

Best Practice for Database Consolidation

#7

# poolcfg -c ‘create pset dbPset_set(uint pset.min=8;uint pset.max=8)’# poolcfg -c ‘create pool dbPool’# poolcfg -c ‘associate pool dbPool(pset dbPset)’# poolcfg -c ‘transfer to pset dbPset(cpu 0;cpu 1;cpu 2;cpu 3;cpu 4;cpu 5;cpu

6;cpu 7)‘

set lgrp_mem_pset_aware=1


Operating Tips and TuningsMemory Allocation and CPU EfficiencyViraj Nr


Performance MethodologyPerformance below expectation, variance, degradation over time, etc.

Systemic Analysis

Performance Optimization Process

Symptoms

DiagnosisTuning Tips

Apply known “Best Practices” Follow Performance optimization process



Oracle Solaris Memory Allocation Concepts• Solaris Memory Organization - Size

– Supports multiple pagesizes for Database instances– Tries to allocate SGA using largest available pagesize– Coalesces smaller pages into larger pages as required

• Solaris Memory Organization - Placement– CPU-Memory affinity abstracted as locality groups (lgroup)– lgroup aware memory allocation

• Database instance startup time depends on availability of large pages



Slow DB Startup Related to Large-Page Availability

Symptoms •Slow database instance startup (affects Oracle 11g more)

Diagnosis • Memory fragmentation due to several database startups/shutdowns, i/o to file systems, other types of i/o, etc.

• Mixed size pages due to page coalescing issues• Monitor pagesizes using “pmap –xs `pgrep –f ora_dbw0` ”

#8


Slow DB Startup Related to Large-Page Availability

Tuning Tips • Limit ZFS Cache size to 20% of available memory, this allows remaining memory to be available for Oracle Database use

• Add the following to /etc/system (e.g. to set it to 2GB) (requires reboot)

• If ZFS is used heavily, refer to ZFS tuning guide for recommendation• Consider system reboot as a last resort

(Cont.)

#8

OSM helps to addresses some of these concerns– Helps improve Instance Startup– Helps Database Availability


Performance Variance due to Uneven Memory Allocation Across lgroupsSymptoms • 5% to 10% performance variance for OLTP

• Cause - multiple DB instances started over time on the same Solaris instanceDiagnosis • Monitor memory allocations across lgroups using “lgrpinfo –cm”

#9


Performance Variance due to Uneven Memory Allocation Across lgroups

Tuning Tips • Use new Oracle 12c Processor Group Name Feature to ensure• All database processes are bound to the processor group• All memory is allocated locally from the processor group

• Integrates with Oracle Solaris resource pools to isolate Oracle Database instances • Oracle provides easy to use script to create Solaris Resource pools • Set processsor_group_name parameter to Resource pool name• In init.ora

• Processor_group_name=<resourcePoolName>

Use Oracle 12c Processor Group Name Feature

#9

(Cont.)


Performance Variance Due to Load Imbalance

Symptoms • Uneven CPU Utilization• Dynamic creation/deletion of processor sets, on-lining and off-lining of CPUs• Short lived applications creating load imbalance

Diagnosis • Monitor load average using kstat, correlate with mpstat• Monitor uneven home lgroup assignment using plgrp

Tuning Tips •Create controlled environment, remove unwanted processor sets•Use MCB feature of Solaris (pbind -c <cpu list>)

Check and Monitor CPU Utilization

#10


Monitor CPU Efficiency

Symptoms • Lower than expected scaling for certain OLTP, DSS or batch workload

Diagnosis •Monitor S/W and H/W core utilization using pgstat (S11.1 SRU 11 or later)•Core utilization is represented by Integer_Pipeline utilization

Tuning Tips •Core utilization approaching saturation means operating at maximum capacity

Core Efficiency – Software + Hardware Utilization

#11


Summary and Resources


Oracle Database Runs Best on Oracle Solaris

• Use shared release area where applicable to enable higher database consolidation density

• Limit ZFS ARC to improve large page availability and database startup time• Leverage Oracle 12c processor_group_name for consolidation on NUMA

servers• Use pgstat for complete understanding of core utilization• Leverage Solaris performance tools to observe and diagnose performance

issues

Key Tip Summary


Questions?

References• Oracle Solaris 11—The Optimal Platform for Deploying Oracle Database

• Administering Resource Management in Oracle® Solaris 11.2

• Oracle Solaris Studio

• Materials for CON6583: Be a Hero with Your DBA: Database Performance Tuning for Admins

THURSDAY Intercontinental Hotel

12:00 CON7818 Guarantee SLAs with Software-Defined Networking Grand Ballroom A

12:00 CON8009 Code Analysis Tools for Consistent/Secure/Reliable Quality Grand Ballroom B

1:15 CON7843 Best Practices for Oracle Solaris Lifecycle Management Grand Ballroom A

2:30 CON4606 Efficient Scalable/Flexible Data Center Cloud Virtualization Grand Ballroom C

http://www.oracle.com/technetwork/server-storage/solaris11/documentation/oracle-database-on-solaris-wp-2298802.pdf

http://docs.oracle.com/cd/E36784_01/html/E37631/rmpool-2.html

http://www.oracle.com/us/products/servers-storage/solaris/studio/overview/index.html


AppendixAvailable Tools and Techniques


Tools and Techniques

System Performance • Solaris + PlatformProcess and Thread Analysis • lockstat, plockstat, truss, prstat and pstackMemory Placement Optimization •plgrp, pmap, lgrpinfoInterrupt Assignment and Load Balancing •mpstat, mdb, intrstatHardware Utilization, Capacity Planning •cpustat, corestat, pgstat, pginfoAdvanced Debugging •DtraceProfiling Techniques •Oracle Solaris Studio


Tools and Techniques

prtpicl • Provides platform information (architecture details including TLB, cache size etc)

pginfo • Displays info about the Processor Group (PG) hierarchy• PGs are used by the operating system to represent the CPUs that

share performance relevant hardware including integer pipeline• pginfo –c 0; pginfo –v –c 0

mpstat, cpustat, trapstat

• Enhanced to handle large CPU count on large systems via filters and aggregation options


I/O Performance Diagnosability with Solaris and Oracle 12c

• v$kernel_io_outlier reports I/O outlier response time breakup (ASM & RAW). Threshold for I/O outlier is > 500 msec latency

DTrace Tightly Integrated with Database Monitoring Framework

Description of v$kernel_io_outlier view

TIMESTAMP Number of seconds elapsed since 00:00 UTC, January 1, 1970 IO_SIZE Size of the I/O, in KB.IO_OFFSET Offset into the device of the I/O DEVICE_NAME Name of the device to which the I/O was targetedPROCESS_NAME Name of the process that issued the I/O TOTAL_LATENCY Total time in us the I/O spent in the kernelSETUP_LATENCY Time in us spent during initial I/O setup before sending to SCSI target device driverQUEUE_TO_HBA_LATENCY Time in us spent in the SCSI target device driver before being sent to the Host Bus AdaptorTRANSFER_LATENCY Time in us spent in the Host Bus Adaptor and physically transferring the I/O to the storage deviceCLEANUP_LATENCY Time in us spent freeing resources used by the completed I/OPID Process ID that issued the I/OCON_ID The Container ID

top 10 tips and tricks to get the best database performance from solaris systems con7526...

Documents

oracle andor

oracle solaris11

oracle database12

oracle database data

viraj nr oracle

sole discretion of oracle

deployment slide

ritu kamboj slide