breaking oracle io bottleneck 121609

8/12/2019 Breaking Oracle IO Bottleneck 121609

http://slidepdf.com/reader/full/breaking-oracle-io-bottleneck-121609 1/25

IBM NA Advanced Technical Skills (ATS) –Oracle Solutions Team

Breaking the Oracle I/O Performance Bottleneck

© 2009 IBM Corporation12/16/2009



IBM NA Advanced Technical Skills (ATS) - Oracle Solutions Team

ega n orma on

The information in this presentation is provided by IBM on an "AS IS"basis without an warrant uarantee or assurance of an kind. IBM also does not provide any warranty, guarantee or assurance that theinformation in this paper is free from errors or omissions. Information isbelieved to be accurate as of the date of publication. You should checkwith the appropriate vendor to obtain current product information.

Any proposed use of claims in this presentation outside of the UnitedStates must be reviewed by local IBM country counsel prior to such use.

AIX, BladeCenter, GPFS, HACMP, IBM, Micro-Partitioning, POWER, Power, , ,

registered trademarks of the International Business MachinesCorporation.

Oracle is a registered trademark of Oracle Corporation and/or its

.All other products or company names are used for identificationpurposes only, and may be trademarks of their respective owners.

© 2009 IBM Corporation2




Presentation Overview

In most Oracle database environments, I/O related wait events areresponsible for a substantial portion of the total DB processing

me. oor su sys em per ormance can unnecessar y

contribute to poor end user response time, long running batch jobsand/or system throughput capacity issues.

This presentation will discuss storage subsystem industry trends

that have impacted Oracle database performance historically. It willalso discuss one rapidly emerging storage technology – Solid StateDisk (SSD).

A number of ractical su estions will be rovided for “breakinthe Oracle I/O performance bottleneck” using available tools andtechniques.





CPU Performance Trends 2000-2007

“Per core” performance increased 13x ~ 40% CAGR*

~ *

80TPC-C High Water Mark (2000-2007)

Total

40

50

60

r f o r m a n c System

"Per Core"

10

20

30

e l a t i v e P e

0

2000 2001 2002 2003 2004 2005 2006 2007

© 2009 IBM Corporation

Based on 2000 – 2007 TPC-C benchmark results

Source: http://www.tpc.orgCAGR* = Compound Average Growth Rate

4




Hard Disk Drive HDD Ca acit HDD storage capacity is growing at 40% per year, similar to CPU

performance advances

Areal Density


Source: “Storage Class Memory: The next storage system technology” by R.F. Freitas and W.W. Wilcke, ”,IBM Journal of Research and Development, July/September 2008

5




Hard Disk Drive HDD Performance

Sequential Bandwidth only improving ~ 15% per year largely drivenby areal density increases

Sequential Bandwidth

Random I/O latency improving very slowly – Rotational speed topped out at 15,000 RPM – ~


Source: “Storage Class Memory: The next storage system technology” by R.F. Freitas and W.W. Wilcke, IBM

Journal of Research and Development, July/September 20086




The I/O Performance Challen e

Exploding data volumes are driving the need for ever larger, fasterservers

– Thankfully, Moore’s Law still applies to processor technology

Evolving business requirements are driving demand for ever fasteraccess to data

HDD performance hasn’t kept pace

Plus… HDD consumes a lot of energy and takes up a lot of space





Meetin the Challen e

I/O avoidance

– Lar er server memor cache

– Larger I/O subsystem cache – Application redesign

More Efficient I/O

– Data Layout Strategies

– Data Compression

– Smarter cache management algorithms

– Application redesign

Getting beyond HDD – In Memory Database

– Selective use of Solid State Disk (SSD)





Im rovin Oracle Database I/O Performance -- TODAY

Striping Data to avoid I/O hotspots

Implementing Data Compression Using Solid State Disk

Using faster HDD based storage subsystems – r ves

– Ample read/write cache

– Enterprise Class cache management algorithms





Data Stri in to Avoid I/O Hots ots

Old Wisdom: Isolate files based on function and/or usage

– Manually intensive effort

– Leads to I/O hotspots that impact throughput capacity and performance

New Wisdom: Stripe objects across as many physical disks as possible

– Minimal manual intervention

–

– Good average I/O response time object throughput capacity with no hotspots

Implementation Options:

– ASM and GPFS do this automatically within a given disk group or filesystem

– Can be implemented with conventional Volume Managers and filesystems:http://www-1.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100319

AIX StorageHW StripingVolume (Disk) Group

LUN 1

LUN 2

LUN 3

hdisk 1

hdisk 2

hdisk 3 i s k G r o u p

S t r i p i n g


LUN 4hdisk 4 S

10




Tunin Oracle DB Buffer Cache

Buffer Cache is the primary database I/O avoidance option

’

New Wisdom: Depending on workload, a higher hit% may be optimal – For a given workload with a buffer hit% of 98%, a 1% increase (to 99%) will

reduce physical I/O requests by 50%

– Reducing IOPS will also improve response time for remaining I/Os

– In many cases, adding server memory may be cheaper than adding I/O

subsystem cache memory or short-stroking disks _ _

– Oracle AWR “Buffer Cache Advisory” report is a good place to start

Monitor for and address potential impacts:

– Increased eak CPU demand

– Increased logical read rates

– System paging (add physical memory as necessary)





Instance Efficiency Percentages

Buffer Nowait %: 99.97 Redo NoWait %: 100.00

Buffer Hit %: 98.89 In-memory Sort %: 100.00

Library Hit %: 70.53 Soft Parse %: 26.01

Execute to Parse %: 28.44 Latch Hit %: 99.96

Parse CPU to Parse Elapsd %: 30.81 % Non-Parse CPU: 89.14

u er = .

Buffer Pool Advisory ReportP Size for Est (M)

SizeFactor

Buffers for Estimate Est Phys Read Factor Estimated Physical Reads

D 256 0.64 16,080 1.11 97,368,882

D 288 0.72 18 090 1.11 96 868 286

• Predicts 29 (of 87)

million block readscould be eliminated. , . , ,

D 320 0.80 20,100 1.08 94,323,210

D 352 0.88 22,110 1.05 91,776,695

D 384 0.96 24,120 1.02 89,228,794

D 400 1.00 25,125 1.00 87,480,193

D 416 1.04 26,130 0.98 85,731,549

over m nuteperiod by adding 240MB of buffer poolcache:

D 448 1.12 28,140 0.94 82,232,582D 480 1.20 30,150 0.90 78,731,330

D 512 1.28 32,160 0.86 75,225,110

D 544 1.36 34,170 0.82 71,715,825

D 576 1.44 36,180 0.78 68,209,778

, /second

• 16,000 blocks /second


D 608 1.52 38,190 0.72 63,357,042

D 640 1.60 40,200 0.67 58,494,659

• secon

• A 33% savings

12




Oracle Data Com ression

Sequential scans typically limited by sequential I/O bandwidth ofbackend disk storage

Data Compression increases the number of rows stored perphysical data block

If sufficient CPU capacity is available, the potential increase in

Ratio (CR)

– Standard table compression CR: up to 4x (2x typical)• Only supported for bulk loads/inserts

– Advanced Compression (11g) CR: up to 4x (2x typical)

• Supports OLTP as well as DW workloads• Separately licensed feature @ $11,500/processor license

– Hybrid Columnar Compression (HCC) CR: up to 10x for Query Compression• Intended for predominantly read only DW workloads


• Only available on Sun/Oracle proprietary 11gR2 Exadata Storage Servers

13




S L Statement Tunin

AWR “SQL ordered by Reads” report is a good place to look forSQL statements that may benefit from tuning

– SQL ordered by ReadsPhysicalReads

ExecutionsReads perExec

%TotalCPU Time(s)

ElapsedTime (s)

SQL IdSQLModule

SQL Text

51,014,572 27 1,889,428.59 10.62 4512.75 20648.44 cpb5vktsrxxnpBEGIN

APPS.IKN_FS_TRAC_SR_ PKG....

49,905,589 1,913 26,087.61 10.39 6777.33 13408.17 9kfwnug5cbszdSELECT CONTACT_IDFROM IKN_O...

22,956,796 26 882,953.69 4.78 2002.24 9528.41 52rzwx2917kd2SELECT SUMMARY FROM

_ ...

20,329,085 23 883,873.26 4.23 1817.61 8203.42 am77sx96jujqkUPDATECS_INCIDENTS_ALL_BSET ...

16,650,797 5 3,330,159.40 3.47 1564.46 9668.13 f9zpsu61dq7cy SQL*PlusSELECTwdd.delivery_name||...

If desired, semi-automatic tools are available to evaluate SQL

_


14




Solid State Disk SSD

A rapidly emerging technology that uses solid state electronicsinstead of spinning disks

– No moving parts

– High random IOPS capacity

– Low latency

– ow energy consump on

– Low capacity (relative to HDDs)

– High $/GB –

Recently announced for several IBM products, including:

– Internal drives for Power Systems and System x servers

– DS5000

– DS8000

– SAN Volume Controller

Within 5 years, may well replace HDDs for Tier 1 disk storage


– According to IDC, SSD revenue is expected to have a 78% CAGR through 2012

Source: http://searchstorage.techtarget.com/news/article/0,289142,sid5_gci1357860,00.html

15




Usin SSD With Oracle

80:20 Rule

– –

data, optimizing performance for that 20% can provide substantialreturns

– Therefore… put the hottest or most response time critical files

Best Candidates:

– “Read mostly” files with a high random I/O Access Density• For example, moderately sized control tables, indexes

Access Density = IOPS/Gigabyte

– Write latency sensitive files

• Online redo logs• Control files• Undo Tablespaces• Temp Tablespaces


– Files with high average (read/write) wait times are better candidates

than those with low average wait times16




AWR Wait Events

Top 5 Timed Events

3

Event Waits Time(s)

Wait(ms)

TimeWait Class

db file sequential read 188,158 2,757 15 61.8 User I/O

DB CPU 1,259 28.2

log file sync 20,320 325 16 7.3 Commit

1

“ ” “ ”

db file scattered read 6,135 54 9 1.2 User I/O

read by other session 1,203 13 11 .3 User I/O

. for a high percentage of total Oracle wait time and Average Waittime is high, selective use of SSD may improve performance

2. If “log file sync” event appears in top 5 list and Average Wait time,

3. The higher the “Average Wait” time for these events, the greaterthe potential SSD benefit





AWR I/O Statistics Tablespace IO Stats

Tablespace ReadsAvReads/s

AvRd(ms)

AvBlks/Rd

WritesAvWrites/s

Buffer WaitsAv Buf Wt(ms)

Total Read Time(seconds)

13

APPS_TS_TX_DATA 200,399,314 7,952 4.96 1.84 1,489,381 59 153,852,375 0.95 993,981

APPS_TS_TX_IDX 42,294,848 1,678 4.93 1.00 825,266 33 10,495,582 0.87208,514

UNDO_RBS3 11,684,361 464 4.53 1.00 144 0 239,596 1.50 52,930

UNDO_RBS1 5,176,461 205 5.30 1.00 951,372 38 271,908 1.29 27,435

_ , , . . , . ,

APPS_TS_NOLOGGING 1,861,280 74 2.79 1.05 454,678 18 0 0.00 5,193

APPS_TS_SUMMARY 1,046,663 42 5.24 1.68 520,912 21 2 0.00 5,485

TEMP 209,066 8 21.66 15.00 268,191 11 195 1.49 4,528

1. Total Read Time = “Reads” x “Average Read Time (ms)” / 1000

– Could be used to prioritize candidates if Average Read time isn’t uniform

2

2. High “Average Blocks/Read” values indicate sequential scanactivity, which might be a reason to leave on HDD

3. High “Buffer Waits” values might indicate Write Latency sensitive


18




AWR I/O Statistics…

TablespaceFilename Reads

AvAv Rd ms

AvWrites

Av Buffer Av Buf

File IO Stats (ordered by Tablespace Name)

ea s s

s r es s a s ms

APPS_TS_TX _DATA

/oradata01/EPRODR/apps_ ts_tx_data01.dbf

2,360,196 94 7.38 2.08 17,331 1 1,375,004 1.37

APPS_TS_TX _DATA


2,455,028 97 7.35 2.10 17,308 1 1,387,878 1.38

APPS_TS_TX _DATA


2,451,324 97 7.41 2.10 16,151 1 1,412,479 1.38

_ _ _DATA

_ ts_tx_data04.dbf

, , . . , , , .

APPS_TS_TX _DATA


2,386,389 95 7.69 2.12 17,182 1 1,272,999 1.49

APPS_TS_TX

_DATA

/oradata01/EPRODR/apps_

ts_tx_data06.dbf

2,399,096 95 7.60 2.12 16,772 1 1,267,411 1.46

APPS_TS_TX /oradata01/EPRODR/apps_ 2,447,058 97 7.44 2.09 16,600 1 1,324,944 1.45 _ _ _ .

APPS_TS_TX _DATA


2,474,975 98 7.38 2.08 16,123 1 1,377,729 1.42

APPS_TS_TX _DATA


2,481,775 98 7.32 2.07 15,843 1 1,406,400 1.37

APPS_TS_TX _DATA


2,460,570 98 7.29 2.08 15,507 1 1,364,264 1.40

_ _ _DATA

_ ts_tx_data11.dbf

, , . . , , , .

APPS_TS_TX _DATA


2,446,896 97 7.38 2.08 14,893 1 1,324,884 1.46

… …. … … … … … … … …

APPS_TS_TX _DATA


1,722,356 68 3.72 1.36 26,159 1 2,087,133 0.51


APPS_TS_TX _DATA


1,034,959 41 3.94 1.45 24,194 1 1,324,884 0.59

19




Factorin in I/O Access Densit

If data files are of significantly different sizes, it may be appropriateto factor I/O access density into the candidate selection process

– If 2 files are of different sizes, but are responsible for equal amounts of read

I/O wait time, the smaller file will be a better SSD candidate because it has ahigher I/O access density

– Stated in another way, since SSD has a high $/GB, the “smallest” files with theg es wa me o er e gges ang or e uc

File size may be determined from filesystem statistics, or OracleDB views such as V$DATAFILE





Sam le Scri t: File Name b “Read Wait Densit ”select bt.tsname, bt.filename,

1024*1024*1024*(et.readtim - bt.readtim) / df.bytes rd_tm_per_GB,

et.readtim - bt.readtim read time

_

et.writetim - bt.writetim write_time,

et.phyrds - bt.phyrds phys_reads,

et.phywrts - bt.phywrts phys_writes,

Total read wait time perGigabyte

et.phyblkrd - bt.phyblkrd blocks_read,

et.phyblkwrt - bt.phyblkwrt blocks_wrt,

et.singleblkrds - bt.singleblkrds single_reads,

et.singleblkrdtim - bt.singleblkrdtim single_read_time,

“Order by” priority based on:

•High I/O access density

•High average read wait time

df.bytes / 1024/1024 M_Bytes

from sys.dba_hist_filestatxs bt, sys.dba_hist_filestatxs et, v$datafile df

where bt.snap_id = :bid and

e .snap_ = :e an

bt.dbid = :dbid and bt.dbid = et.bdid and

bt.instance_number = :inst_num and bt.instance_number = et.instance_number and

bt.file# = et.file# and bt.file# = df.file#


order by 3 desc, 4 desc;

21




SSD Confi uration O tions

Hardware RAID-5 based SSD LUNs

– Provides rotection a ainst sin le SSD com onent failure

– RAID-10 could be used, but at higher $/GB

– Suitable for most purposes, e.g. .dbf files with high I/O access density

ASM SSD/HDD hybrid disk group

– Create “HYBRID” ASM disk group with Normal Redundancy

– “SSD” failure group is composed of one or more SSD LUNs (not RAID-

protected)“ ” – optional)

– alter system set ASM_PREFERRED_READ_FAILURE_GROUPS =‘HYBRID.SSD’;

– “ ”

HDD)

“JBOD” SSD LUNs

– SSD LUNS with no hardware RAID-protection


– Suitable for multiplexed redo logs or non-mirrored temp objects at even lower$/GB

22




Im lementation Ste s General

1. Evaluate existing data layout strategy

– If not alread stri in across a minimal number of “disk ools” create a

strategy to get there, e.g. ASM, GPFS, File System striping…

2. Evaluate existing Buffer Pool allocation

– If Buffer Pool Advisory recommends increasing Buffer Pool size, acquire _ _

appropriate

– Re-evaluate performance and address an unanticipated side effects

– Repeat

3. Evaluate possible use of Oracle data compression for sequentialread workloads

– Implement as appropriate

. total Read I/O wait time

– Tune as appropriate





Im lementation Ste s SSD

1. Acquire a minimum of 2 SSD drives

– For RAC environments must be shared stora e e. . DS5000 DS8000 SVC

– For single instance environments, may use either internal system or externalsubsystem based

2. Review AWR or other Oracle performance data to determine

– High random read I/O Access Density:

• High read IOPS, with high average read wait times and low “Blocks per read” average

in AWR “Tablespace IO Stats” or “File IO Stats” report• “ ”

– Write Latency Sensitive:

• High ‘log file sync’ wait time (with high average wait time) in AWR “Top 5 TimedEvents” report

• High “buffer busy waits” (with high average read wait time) in AWR “Tablespace IOtats or e tats report

3. Implement strategies based on candidate file type

– RAID protected SSD LUNs

– “ ”


– JBOD based SSD LUNs

24




Q U E S T I O N SQ U E S T I O N S


breaking oracle io bottleneck 121609

Documents