<Insert Picture Here> Performance, performance, performance!!!

(1)

(2)

<Insert Picture Here>

Performance

, performance,

performance !!!

John Abrahams

Technology Sales Consultant Oracle Nederland

(3)

Important Trends

•

Highest QoS for all applications

•

24 x 7 availability

•

Predictable Service Levels

(4)

Agenda

•

Automated Performance Management

•

Automatic Workload Management

•

Large Databases – Oracle ILM

•

Oracle DWH – Balanced Configurations

•

When milliseconds counts - Oracle TimesTen

(5)

Agenda

•

When milliseconds counts - Oracle TimesTen

In-memory database

(6)

Traditional Performance Tuning

• Performance and Workload Data Capture

• System Statistics, Wait Information, SQL Statistics, etc.

• Analysis

• What types of operations database is spending most time on? • Which resources is the database bottlenecked on?

• What is causing these bottlenecks?

• What can be done to resolve the problem?

• Problem Resolution

• If multiple problems identified, which is most critical?

• How much performance gain expected if is solution implemented?

Oracle Database 10g Automates All Steps to Addresses Main Issues & Challenges

(7)

• Built-in Workload and Performance Statistics Repository Within Database

• Part of Oracle Database 10g Intelligent Self-Management Infrastructure • Automatically Captures Workload Data

• Every 60 minutes, or manually, saves data for 7 days by default

• Resides in Newly Introduced SYSAUX Tablespace • Server Automatically Manages Space Requirements

• Old Data is automatically purged

• Stores different classes of data:

• BASE STATISTICS e.g. physical reads

• SQL STATISTICS e.g. disk reads (per sql stmt)

• METRICS e.g. physical reads / sec

• ACTIVE SESSION HISTORY

(8)

Proactive Performance Tuning

• Self-Diagnostic Engine In the Database

• Integrate all components together • Automatically provides database-wide performance diagnostic • On-Demand Performance Analysis

• Provides impact and benefit analysis, non problem areas

• Runs proactively out of the box, reactively when required

SQL Advisor High-load

SQL IO / CPU issues RAC issues

Automatic Diagnostic Engine

Snapshots in Automatic Workload

Repository

Self-Diagnostic Engine inside DB

System Resource Advice Network + DB config Advice

Automatic Database Diagnostic Monitor (ADDM)

(9)

Manual Tuning Challenges

Requires expertise in several domains

–

SQL optimization: adjust the execution plan

–

Access design: provide fast data access

–

SQL design: use appropriate SQL constructs

Time consuming

–

Each SQL statement is unique

–

Potentially large number of statements to tune

Never ending task

–

SQL workload always evolving

(10)

Oracle 10

g

Automates the SQL

Tuning Process

I can do it for you ! SQL Tuning Advisor DBA High-Load SQL ADDM SQL Workload

(11)

Automatic Tuning Optimizer (ATO)

It is the query optimizer running in tuning mode

–

Uses same plan generation process but performs

additional steps that require lot more time

It performs verification steps

–

To validate statistics and its own estimates

Uses dynamic sampling and partial executions to validate

It performs exploratory steps

–

To investigate the use of new indexes that could

provide significant speed-up

–

To analyze SQL constructs that led to expensive

(12)

Automatic SQL Tuning Overview

Add Missing Indexes Modify SQL Constructs Create a SQL Profile

Automatic Tuning Optimizer

SQL Structure Analysis Access Path Analysis SQL Profiling Statistics

Analysis Gather Missing or _{Stale Statistics}

DBA

SQL Tuning

Recommendations

SQL Tuning Advisor

(13)

SQL Tuning Usage Scenarios

SQL Tuning Advisor ADDM _{High-load SQL} Cursor Cache AWR SQL Tuning Set (STS) User-defined Filter / Rank

SQL Sources Manual Selection Automatic Selection

(14)

SQL Tuning Set (STS)

Motivation

–

Enable user to tune a custom set of SQL statements

It is a new object in Oracle10g for capturing SQL

workload

It stores SQL statements along with..

–

Execution context: parsing user, bind values, etc.

–

Execution statistics: buffer gets, CPU time, elapse

time, number of executions, etc.

It is created from a SQL source

–

Sources: AWR, cursor cache, user-defined SQL

(15)

SQL Tuning Set Benefits

Allows selective, on-demand, custom SQL

workload tuning

It simplifies tuning of large number of SQL

statements

It is persistent

Provides a common infrastructure for dealing

with SQL workloads

(16)

(17)

(18)

(19)

(20)

SQLAccess Advisor

Workload

Indexes _{Materialized Views}

Materialized View Logs SQL Access

(21)

Usage Scenarios

Workload

User Defined Hypothetical Cursor Cache Filter Options STS SQL Access Advisor

(22)

Conclusion

•

SQL Tuning and Access Advisors

automate SQL tuning

•

Provide targeted, quick, easy tuning

advice

•

Fully integrated with CBO

(23)

Agenda

•

Oracle DWH – Balanced Configurations

•

In-memory database

(24)

Oracle Real Application Clusters 10g

•

AWM is an abstraction that

customers use to divide work

into logical workloads.

•

Services is the first stage in

AWM

•

Each service represents a

workload with

• common function

• common service level thresholds

• common priority

• (common resource footprint)

• e.g. mail server – iMap, postman, garbage collector, monitor

(25)

Node-4

Service Distribution Example

Critical-1

Node-3 Node-2

Node-1

Critical-2

Using RAC Automatic Workload Management

Critical-4 Batch Low Medium 1 Critical-3 Medium 2

(26)

Sample Work Sheet

Service Usage Priority Response time

(sec) – warning / critical

Preferred

Instances Available Instances

ERP Client service 1 0.5, 0.75 RAC01, RAC02 RAC03, RAC04

CRM Client service 2 0.5, 1.0 RAC03, RAC04 RAC01, RAC02

SELF_SERVIC

E Client service 2 1.0, 1.5 RAC01, RAC02,

RAC03, RAC04

-HOT_BATCH Job scheduler 3 RAC01 RAC02,

RAC03, RAC04

STD_BATCH Job scheduler 4 RAC01,

RAC02,

RAC03, RAC04

(27)

-Creating Services

•

Services are maintained in the data dictionary

•

Each service has attributes

• globally unique name

• service thresholds (response time, CPU/service)

• resource consumer group (ratios or priorities)

•

HA business rules are maintained in OCR

• preferred configuration for availability

(28)

Creating Services

•

Oracle 10

g

single instance

• DBMS_SERVICE to create and administer services

•

Oracle 10

g

RAC

• DBCA, NETCA or SRVCTL to create and administer services

•

EM or PL/SQL to create thresholds, consumer

(29)

Database Resource Manager

•

DBMS_RESOURCE_MANAGER.

SET_CONSUMER_GROUP_MAPPING

• Automatically sets consumer groups for services at connect time.

• alter session to change consumer groups within service

•

Using ratios

• e.g. two-thirds of resource to payroll and one-third to CRM.

•

Using priorities

• satisfy the highest priority services first, followed by the next priority services, and so on.

(30)

Services are a unit for performance

•

A new level dimension for performance tuning

• workloads are visible and measurable

• tuning by “service and SQL” replaces “session and SQL” in most systems where sessions are shared.

• Performance measures for real transactions

(31)

Service Thresholds and Alerts

•

DBMS_SERVER_ALERT.SET_THRESHOLD

• SERVICE_ELAPSED_TIME

• SERVICE_CPU_TIME

• Warning and critical levels for observed periods

• Import from EM baselines

•

Comparison of response time against accepted

minimum levels

(32)

30

service, module, action statistics

• user calls

• DB time – response time • DB CPU – CPU/service • parse count (total)

• parse time elapsed • parse time cpu

• execute count

• sql execute elapsed time • sql execute cpu time

• opened cursors cumulative • session logical reads

• physical reads • physical writes • redo size

• user commits

workarea executions - optimal workarea executions - onepass workarea executions - multipass session cursor cache hits

user rollbacks db block changes gc cr blocks received gc cr block receive time gc current blocks received gc current block receive time cluster wait time

concurrency wait time application wait time user I/O wait time

(33)

Automatic Workload Management

Provides Visibility

0 5 10 15 20 25 30 9 10 11 12 13 14 15 16 17 18 D C B A

(34)

Provides Visibility

(35)

AWR Automatically Measures Service

•

AWR measures response time, resource used

• Automatically for work done in every service

•

AWR monitors thresholds, sends AWR alerts

• response time, cpu used

• maintains runtime history every 30 minutes

•

Statistics collection and tracing are HA

• persistent for service location / instance restart.

(36)

Getting the best out of Oracle

- Configuration

•

Plan your services

• application to service, data range to service

• global name, HA configuration, priority, response time

•

Use service: not SID, not Instance, not Host

• Use service to connect

• Use virtual IP for database access

• Use cluster alias to eliminate address lists.

(37)

Agenda

•

In-memory database

(38)

Information Lifecycle Management

“The policies, processes, practices, and tools used to align the

business value of information with the most appropriate and cost effective IT infrastructure from the time information is conceived through its final disposition.”

Storage Networking Industry Association (SNIA) Data Management Forum

Historical Data Active

(39)

Oracle ILM

• Low Cost

• Can use low cost storage to reduce the cost of retaining data

• Transparent to Applications

• Applications are unchanged

• Enforceable Compliance Policies

• Sophisticated techniques to define and enforce data policies

• Fine Grained Control

• Lifecycle of groups of

business data is managed down to individual rows

• Hardware Independent Active

Oracle Database 10g

with Partitioning Option Less

Active Historical

Applications

(40)

Implementing Oracle ILM

• Define

Data Classes

• Create Storage Tiers for the Data Classes

• Create Data Access and Migration Policies • Define and Enforce Compliance Policies

(41)

Implementing Oracle ILM

• Oracle ILM Assistant Manages the ILM environment • Define Lifecycle Definitions

• Illustrates Storage Costs & Savings • Manage Compliance & Security

• Calendar of Events

• Simulates the impact of partitioning on a table

• Advises how to • Partition a Table

(42)

Separate Data by Class

• Apply different policies to different classes

of data

• To treat data classes differently, you must

physically separate data by class

• Data classes must be mapped to data attributes e.g. Order date

• Table Partitions enable you to separate data by data attribute

• Can manage each class (Partition) as a unit • Store, move, archive, search, query

• Partitions are transparent to the application

• Use ILM Assistant to define a Lifecycle

All Orders

Q1 Orders Q2 Orders Q3 Orders Q4 Orders Older Orders

(43)

(44)

Create Physical Storage Tiers

• Create separate storage areas for high performance and low cost storage

• High Performance Storage Tier uses

• High performance storage arrays

• Disks optimised for throughput

• Low Cost Storage Tier uses

• Modular arrays for reduced cost

• Large capacity commodity ATA disks

High Performance Storage Tier

Low Cost Storage Tier

(45)

Automatic Storage Management (ASM)

• ASM & ILM

• Partitions are located in different diskgroups

• Each tier uses ASM for load balancing

Jan Feb Mar

Diskgroup H Diskgroup A 1997-2001 Diskgroup L 2005 2004 2002 2003

High Performance Tier

Low Cost Tier

(46)

Online Archive Storage Tier

• The Online Archive Storage Tier is • Very large

• Low Activity

• Read-only or Read-mostly

• Store in low cost storage tier

• Information is still online and always readable

• No delay when data needed, always available

• Storage cost is almost same as tape

• Leverage usage patterns to further reduce size and cost

• Defragment and Compress • Declare read-only

(47)

All Orders

Q1 Orders Q2 Orders Q3 Orders Q4 Orders Older Orders

Assign Classes/Partitions to Storage Tiers

Active High Performance Storage Tier Less Active Low Cost Storage Tier Historical Online Archive Storage Tier

(48)

Enforcing Compliance Policies

• Oracle ILM provides an

intelligent central location to store data and enforce

compliance data policies

• Policies are customisable for businesses-specific needs • Centralised policy definition

and enforcement

• Flexibility to respond to changing regulations

Oracle Database 10g

with Partitioning Option

Applications

Oracle Desktop _Apps _BrowsersPortals & _AppsISV

(49)

Agenda

•

In-memory database

(50)

DW Configuration Problem: I/O

Database CPUs Memory Actuators LUNs Disks Raid

Database CPUs Memory Actuators LUNs Disks Raid

An unbalanced configuration A balanced configuration 100% Possible Efficiency 100% Possible Efficiency 100% Achieved Efficiency < 50% Achieved Efficiency

(51)

Oracle Information Appliance Foundation

What is it?

•

Documented

balanced system configurations

for

pre-defined DWBI environments

• Starting point for sizing a system

• Balanced system consists of CPU, memory, I/O, and cabling

• Sizing factors are raw data, avg. concurrent users and workload complexity

•

Leverages

scalable, modular components

• Enables incremental growth (scale-in, scale-out)

• Mitigates implementation risks

•

Different OS and HW configurations

•

Different workload configurations

(52)

•

Balanced system configurations are built on modular

balanced

building blocks

• Add building blocks as you grow

• Each building block is a balanced unit

Disks

Disks_Disks_Disk_DiskDisksDisks_Disk_Disk

DisksDisk_Disk DisksDisksDiskDiskDisksDisksDisksDisksDisksDisksDiskDisk DiskDisk

•

Different building

blocks provide “guided

choices”

•

Many small nodes

versus few large nodes

Oracle Information Appliance Foundation

(53)

What is a balanced unit ?

•“The weakest link” defines the throughput

•Components to consider:

• CPU: Quantity and speed • HBA (Host Bus Adapter):

Quantity and speed • Switch speed

• Controller: Quantity and speed

• Disk: Quantity and speed FC-Switch1 FC-Switch2

Disk

Array 1 Array 2Disk Array 3Disk Array 4Disk Array 5Disk Array 6Disk Array 7Disk Array 8Disk

H B A 1 H B A 2 H B A 1 H B A 2 H B A 1 H B A 2 H B A 1 H B A 2

(54)

Throughput Performance

Component theory (Bit/s) maximal Byte/s

HBA 1/2Gbit/s 100/200 Mbytes/s

16 Port Switch 8 x 2Gbit/s 1600 Mbytes/s

Fibre Channel 2Gbit/s 200 Mbytes/s

Disk Controller 2Gbit/s 200 Mbytes/s

GigE NIC 1Gbit/s 80 Mbytes/s

Infiniband 10Gbit/s 890 Mbytes/s

CPU 200MB/s

Grid Components

Rough Sizing numbers

(55)

Sample Balanced Unit

Disk

Each machine has 2 CPUs

All four servers drive about 2 * 200MB/s * 4 = 1600 MB/s H B A 1 H B A 2 H B A 1 H B A 2 H B A 1 H B A 2 H B A 1 H B A 2

(56)

Sample Balanced Unit

• Disk: Quantity and speed

H B A 1 H B A 2 H B A 1 H B A 2 H B A 1 H B A 2 H B A 1 H B A 2 FC-Switch1 FC-Switch2 Disk

Each machine has 2 Gb HBAs

All 8 HBAs can sustain

8 * 200MB/s = 1600 MB/s

Each machine has 2 CPUs

All four servers drive about 2 * 200MB/s * 4 = 1600 MB/s

(57)

Sample Balanced Unit

Disk

Each machine has 2 Gb HBAs

8 * 200MB/s = 1600 MB/s

Each switch needs to support 800MB/s to guarantee a total system throughput of1600 MB/s

(58)

Sample Balanced Unit

Disk

Each machine has 2 Gb HBAs

8 * 200MB/s = 1600 MB/s

Each switch needs to support 800MB/s to guarantee a total system throughput of1600 MB/s

Each disk array has one 2Gbit controller

All 8 disk arrays can sustain 8 * 200MB/s = 1600 MB/s

(59)

Agenda

•

In-memory database

(60)

Oracle TimesTen In-Memory Database

In-memory RDBMS

in the middle-tier..

enables the

Real-time Enterprise

Application Application

(61)

Oracle TimesTen In-Memory Database

• In-Memory RDBMS

• Entire database is in memory

• Low latency + high throughput fast

• Data access via ODBC, JDBC, SQL-92

standard

• Shared by multi-threaded and multi-user applications

• Direct-linked and client-server

• Transactions are logged and recoverable

• Transaction logs and checkpoint files persist on disk safe

• Thousands of customers proven

• Compatible with Oracle database

Checkpoints Tx Logs Network Client-Server Cache Tables Cache Agent Direct-linked Application Application TimesTen Libraries TimesTen Client lib Application

(62)

Cache Connect to Oracle

•

Cache tables from Oracle database

•

User configured cache groups

• Cache individual tables and related tables

• Cache all or subset of rows and columns

•

Read-only or updatable

• Access cached tables like regular database tables

•

Automatic data synchronization

• TimesTen to Oracle • Oracle to TimesTen Checkpoints Tx Logs Network Client-Server Cache Tables Cache Agent Direct-linked Application Application TimesTen Libraries TimesTen Client lib Application

(63)

Caching Data from Oracle Database

Cache Group

–

describes the Oracle data to be cached in the

TimesTen in-memory database, via a SQL WHERE clause

TimesTen Cache Groups Root Table Child Table Child Table Child Table

Example: Caching name and

address of premier customers

who have had more than 500

purchase orders

CREATE CACHE GROUP PremierUsers FROM CUSTOMER (

NAME VARCHAR2(100) NOT NULL, ADDR VARCHAR2(100) )

(64)

On-Demand Caching

•

On-demand loading of data

from Oracle if data not found in

TimesTen

•

All updates to the cache

write-through to Oracle database

•

Set data aging policy to age

out the data that’s no longer

needed

Oracle Load data from Oracle upon SELECT Application Clients On Demand Cache Group IMDB Application Client/Server C/S Applications

Age out when no longer needed

(65)

Sliding Window Caching

Cache data from a specific time window for real-time

management, monitoring and optimization

• Cache 5 days of shipments

for real-time optimization of routes & schedules

• Cache last 15 minutes of

RFID data for real-time process monitoring

• Cache last 90 days of orders

to speed searching by call agents or self-service portals

• Cache last 30 days of market data for analytics & simulation

WED TUE

(66)

Benchmark example

Oracle 10g Oracle 10g Presentation Benchmark Program Benchmark Program • 3-tier architecture • Benchmark app runs in mid-tier • TimesTen embedded in benchmark app • Updatable cache contains benchmark data • 3-tier architecture • Benchmark app runs in mid-tier

(67)

(68)

TimesTen and J2EE Application

Servers

•

Customers use TimesTen database with their J2EE

applications

•

TimesTen supports industry leading Application Servers

• Oracle Application Server

• IBM Websphere Application Server

• BEA WebLogic Application Server

• Sun Application Server

• JBOSS Application Server

•

Refer to TimesTen Application Server Guide for more

(69)

Java Middleware Tested with TimesTen

•

Oracle Application Server 10.1.2 & 10.1.3

•

Oracle BPEL Process Manager 10.1.3

•

Oracle TopLink 9.0.4 & 10.1.3

•

Oracle JDeveloper 10.1.3

•

JBoss Application Server 4

•

Hibernate 3

•

WebSphere Application Server 6

•

Weblogic Application Server 8

(70)

Product Features

In the Pipeline..

•

Integration with Oracle SQL Developer tool

• TimesTen will be supported in next release

•

Support Oracle Call Interface (OCI) and Pro*C

embedded SQL

•

Support for PL/SQL stored-procedures

(71)