Oracle Open World 2009 Measure, Interpret, and Analyze Oracle I/O Performance Data Presenter: John Moffett of BIAS Corporation - PowerPoint PPT Presentation

About This Presentation
Title:

Oracle Open World 2009 Measure, Interpret, and Analyze Oracle I/O Performance Data Presenter: John Moffett of BIAS Corporation

Description:

... Oracle 10.2.0.3 Enterprise ... Very important Planning ... Plot Data Using EXCEL Plot Data Using EXCEL Plot Data Using EXCEL Travel and Hospitality ... – PowerPoint PPT presentation

Number of Views:165
Avg rating:3.0/5.0
Slides: 84
Provided by: Ashish91
Category:

less

Transcript and Presenter's Notes

Title: Oracle Open World 2009 Measure, Interpret, and Analyze Oracle I/O Performance Data Presenter: John Moffett of BIAS Corporation


1
Oracle Open World 2009Measure, Interpret, and
Analyze Oracle I/O Performance DataPresenter
John Moffett of BIAS Corporation
Moscone South Room 301San Francisco, CAOctober
13, 2009
2
Agenda
  • Examine Four (4) Main Oracle Read Operations
  • Cost Based Optimizer (CBO) and I/O Cost
    Computations
  • How to Tap Into Oracle AWR and STATSPACK I/O
    Tables to Create I/O Profiles
  • ASM Striping Configurations
  • Quick Dive into Todays Disk Drive Technology
  • Case Studies
  • Takeaways

3
Introduction to BIAS
www.biascorp.com
john.moffett_at_biascorp.com
My Background
  • Director of Architecture/Infrastructure for BIAS
  • Started Working With Oracle Version 5 Beta in
    1985
  • 13 Years of Oracle Performance Tuning
  • 8 Years with Oracle
  • 5 Years with BIAS

4
Four Primary Oracle Read Operations
  • Single Block Read
  • Multi-Block Read
  • Direct Path Read
  • Parallel Read

5
Single Block Read
On-Line Transaction Processing (OLTP) Workhorse
  • Index Range Scans and Index Full Scans
  • Row Fetch Based on Index Lookup
  • Requestor Supplies
  • File, Start Block

select from emp where empno42
6
Multi-Block Read
Batch Job Workhorse
  • Full Table Scan (Up to High Water Mark)
  • Index Fast Full Scan (Up To High Water Mark)
  • Requestor Supplies
  • File, Start Block, and Num of Contiguous
    Blocks
  • Block Count Cant Exceed
  • DB_FILE_MULTIBLOCK_READ_COUNT (MBRC)
  • Max I/O Size Supported by OS
  • Oracle Reads Around Blocks in the Buffer Cache

select from emp
select count() from emp
7
Multi-Block Read
DB_FILE_MULTIBLOCK_READ_COUNT (MBRC)
  • Set By DBA in Oracle 7, 8, 9, 10
  • Set By Oracle in 11g
  • Formula is (Max IO Size)/(Oracle Block Size)

If Max IO Size is 1 MByte (Operating System and
Storage I/O Stripe Size)
Oracle Block Size MBRC Setting
4K 256
8K 128
16K 64
32K 32
8
Multi-Block Read
MBRC Myths
  • Setting MBRC High Encourages CBO to Choose
    Multi-Block Reads Instead of Single Block Reads

MBRC Truths
  • Setting MBRC Higher Than 8 For RAC (9i,10g) Might
    Trigger Excessive gc cr multi block request Waits
  • Proper Setting Maximizes Large I/O Request
    Processing
  • Going to Read All Up to the HWM
  • Best Practice is to Minimize Number of I/O
    Requests

9
Direct Path Read
Intense I/O Workhorse for Batch and BI/DW
  • Sort I/O or Hash Join I/O (TEMP Tablespace)
  • Parallel Query (PX)
  • Requestor Supplies
  • File, Start Block, num blocks
  • Oracle Reads Directly into PGA Cursor
  • Bypasses Buffer Cache

INSERT / APPEND / into MYTABLE SELECT FROM EMP
SELECT / parallel (cea,8) parallel(agns,8) / clm_entity_agn,uow_agn from CLAIM_ENTITY_ASSOC PARTITION(p200905) cea, clm_sdi_uow_agns agns where cea.uow_agn agns.u_agn
10
Direct Path Read
Execution Plan Parallel Query and Partition
  • Execution Plan
  • --------------------------------------------------
    --------
  • Plan hash value 293734533
  • --------------------------------------------------
    --------------------------------------------------
    -------------------------------------------
  • Id Operation Name
    Rows Bytes Cost (CPU) Time
    Pstart Pstop TQ IN-OUT PQ Distrib
  • --------------------------------------------------
    --------------------------------------------------
    -------------------------------------------
  • 0 SELECT STATEMENT
    1 20 77864 (3) 001811
  • 1 PX COORDINATOR

  • 2 PX SEND QC (RANDOM) TQ10001
    1 20 77864 (3) 001811
    Q1,01 P-gtS QC (RAND)
  • 3 HASH JOIN
    1 20 77864 (3) 001811
    Q1,01 PCWP
  • 4 PX RECEIVE
    1890K 9230K 91 (4) 000002
    Q1,01 PCWP
  • 5 PX SEND BROADCAST TQ10000
    1890K 9230K 91 (4) 000002
    Q1,00 P-gtP BROADCAST
  • 6 PX BLOCK ITERATOR
    1890K 9230K 91 (4) 000002
    Q1,00 PCWC
  • 7 TABLE ACCESS FULL
    CLM_SDI_UOW_AGNS 1890K 9230K 91
    (4) 000002 Q1,00 PCWP
  • 8 PX BLOCK ITERATOR
    482M 6897M 77207 (2) 001801
    29 29 Q1,01 PCWC
  • 9 TABLE ACCESS FULL
    CLM_CLAIM_ENTITY_ASSOC 482M 6897M 77207
    (2) 001801 29 29 Q1,01 PCWP
  • --------------------------------------------------
    --------------------------------------------------
    -------------------------------------------
  • Predicate Information (identified by operation
    id)
  • --------------------------------------------------
    -

Partition 29 Equi-Join Method Hash Join
11
Direct Path Read
Impact on Oracle Read Statistics (8i and 9i)
  • STATSPACK report for
  • DB Name DB Id Instance Inst Num
    Release Cluster Host
  • ------------ ----------- -------- ----
    ----------- ----------- -------
  • T2BL85A 3434773797 T2BL85A 1 9.2.0.6.0
    NO dc1-beta
  • CPU
    Elapsed
  • Buffer Gets Executions Gets per Exec Total Time
    (s) Time (s) Hash Value
  • ----------- ---------- ------------- ------
    -------- -------- ----------
  • 856,070 16 53,504.4 15.9 77.69
    2201.99 1061718588
  • Module oracle_at_whltlg2 (TNS V1-V3)
  • SELECT / Q607000 NO_EXPAND ROWID(A1) /
    A1.ROWID,A1."BAN",A1." ACCOUNT_TYPE",A1."BILL_CYCL
    E",A1."BAN_STATUS",A1."STATUS_LAST_DA
    TE",A1."START_SERVICE_DATE",A1."BL_CUR_BILL_SEQ_NO
    ",A1."BL_LAST_ PROD_DATE",A1."BL_LAST_CYC_RUN_YEAR
    ",A1."BL_LAST_CYC_RUN_MNTH",A 1."BL_LAST_CYC_CODE"
    ,A1."BL_COMPLT_STATUS",A1."BL_SPECIAL_CYCLE"
  • Statistic
    Total per Second per Trans
  • ---------------------------------
    ------------------ -------------- -------------
  • physical reads
    4,614,109 5,121.1 3,841.9
  • physical reads direct
    1,123,055 1,246.5 935.1
  • physical writes
    113,268 125.7 94.3
  • physical writes direct

12
Direct Path Read
Impact on Oracle Read Statistics (8i and 9i)
  • Tablespace IO Stats for DB T2BL85A Instance
    T2BL85A Snaps 71727 -71728
  • -gtordered by IOs (Reads Writes) desc
  • Av Av Av
    Av Buffer Av Buf
  • Tablespace Reads Reads/s Rd(ms)
    Blks/Rd Writes Writes/s Waits Wt(ms)
  • -------------------- ------- ------- -------
    ------- -------- -------- ------- ------
  • TLGP03M 291,562 324 1.9
    8.6 24 0 0 0.0
  • TBS01M 28,592 32 49.9
    29.3 1,077 1 0 0.0
  • TLGP05M 27,737 31 5.9
    10.3 2 0 0 0.0
  • TBS01L 19,828 22 11.1
    28.7 0 0 0 0.0
  • TBS01S 7,838 9 13.9
    31.9 800 1 0 0.0

PX Query Coordinator Wait Times Included in the
Read Wait Statistics
13
Parallel Read
Chosen at Runtime - Ever Mysterious
  • Proprietary Oracle Algorithm
  • Common Operation for Recovery
  • Also Used For Index Scans and Pre-Fetch
    Operations
  • More Prevalent in 11g
  • Issue Parallel Reads Against Multiple Files
  • Blocks are Not Contiguous (Unlike Multi-block)
  • Wait Event Reports
  • Files, Blocks, Requests
  • Waits for Last Request to Mark I/O as Completed

14
Cost Based Optimizer
Read Operation Selection in Execution Plan
(Default)
  • DB_FILE_MULTIBLOCK_READ_COUNT 32
  • ORACLE BLOCK SIZE 16K

ALTER SESSION SET EVENTS 10053 TRACE NAME CONTEXT FOREVER, LEVEL 4
SYSTEM STATISTICS INFORMATION Using WORKLOAD Stats CPUSPEED 840 millions instructions/sec SREADTIM 6 milliseconds MREADTIM 13 milliseconds MBRC 23.000000 blocks MAXTHR 185,306,112 bytes/sec SLAVETHR 751,616 bytes/sec
15
Cost Based Optimizer
Override Default CBO System Statistics
  • DBMS_STATS.GATHER_SYSTEM_STATS( )
  • START
  • STOP
  • SYS.AUX_STATS

16
Timed Statistic Views




















Commonly Used Views for Performance Analysis
View Name Description
VFILESTAT File Level Summary of the I/O Activity
VLATCH Latch Activity Summary
VLATCH_CHILDREN Latch Activity for Child Latches
VMYSTAT Resource Usage Summary for Your Own Session
VSESSION_EVENT Waits for an event by a session
VSESSION_WAIT Current wait status of each session
VSESSTAT Session Level Summary of Resource Usage Since Session Startup
VSQLAREA Shared Pool Details for Statements/Anonymous Blocks
VSYSSTAT Summary of Resource usage
VSYSTEM_EVENT Information on total waits for an event

17
Important StatsPack/AWR Tables
Derived from Timed Statistic V Views
Statistic 8i/9i 10g/11g Statistics Table
I/O a PERFSTAT.STATSFILESTATXS
a DBA_HIST_FILESTATXS
Wait a PERFSTAT.STATSSYSTEM_EVENT
a DBA_HIST_SYSTEM_EVENT
Background a PERFSTAT.STATSBG_EVENT_SUMMARY
a DBA_HIST_BG_EVENT_SUMMARY
System a PERFSTAT.STATS
a DBA_HIST_SYSSTAT
Latches a PERFSTAT.STATSLATCH
a DBA_HIST_LATCH
LibraryCache a PERFSTAT.STATSLIBRARYCACHE
a DBA_HIST_LIBRARYCACHE
SQL a PERFSTAT.STATSSQL_SUMMARY
a DBA_HIST_SQLSTAT
18
PERFSTAT.STATSFILESTATXS
I/O Interpretation
Column Description
FILENAME Tablespace Data File Name
PHYRDS Number of Physical Reads Done (Includes Direct Path)
PHYWRTS Number of Physical Write Done (DBWRDirect Path)
PHYBLKRD Number of Physical Blocks Read
PHYBLKWRT Number of Physical Blocks Written
SINGLEBLKRDS (9i and higher) Number of Single Block Reads Done
SINGLEBLKRDTIM (9i and higher) Time Spent Doing Single Block Reads
READTIM Time Spent Doing Reads
WRITETIME Time Spent Doing Writes
WAIT_COUNT Number of Times Waited on a Block in Buffer Cache
TIME Time Waiting on Block in Buffer Cache
All Times in Centi-Seconds (One-Hundredth of a
Second)
19
DBA_HIST_FILESTATXS (AWR)
I/O Interpretation
Column Description
FILENAME Tablespace Data File Name
PHYRDS Number of Physical Reads Done (Includes Direct Path)
PHYWRTS Number of Physical Write Done (DBWRDirect Path)
PHYBLKRD Number of Physical Blocks Read
PHYBLKWRT Number of Physical Blocks Written
SINGLEBLKRDS Number of Single Block Reads Done
SINGLEBLKRDTIM Time Spent Doing Single Block Reads
READTIM Time Spent Doing Reads
WRITETIME Time Spent Doing Writes
WAIT_COUNT Number of Times Waited on a Block in Buffer Cache
TIME Time Waiting on Block in Buffer Cache
All Times in Centi-Seconds (One-Hundredth of a
Second)
20
Timed Statistics
Read Wait Events
Read Type Wait Event
Single Block Read DB FILE SEQUENTIAL READ
Multi-Block Read DB FILE SCATTERED READ
Direct Path Read DIRECT PATH READ
Parallel Read DB FILE PARALLEL READ
21
SQL to Capture StatsPack/AWR Data
Query to Capture I/O Data
select 'SINGLE_BLOCK'chr(9) A.SNAP_IDchr(9) to_char(B.BEGIN_INTERVAL_TIME,'DD-MON-YYYYHH24MISS')chr(9) SUM(A.SINGLEBLKRDS)chr(9) SUM(A.PHYWRTS)chr(9) SUM(A.SINGLEBLKRDTIM)chr(9) SUM(A.PHYBLKRD)chr(9) SUM(A.PHYBLKWRT)chr(9) SUM(A.WAIT_COUNT)chr(9) SUM(A.TIME)chr(9) SUM(A.WRITETIM) FROM DBA_HIST_SNAPSHOT B, DBA_HIST_FILESTATXS A WHERE A.SNAP_ID B.SNAP_ID AND A.INSTANCE_NUMBER1 AND B.INSTANCE_NUMBERA.INSTANCE_NUMBER AND B.BEGIN_INTERVAL_TIME gt SYSDATE-21 GROUP BY A.SNAP_ID, to_char(B.BEGIN_INTERVAL_TIME,'DD-MON-YYYYHH24MISS')order by A.SNAP_ID
22
SQL to Capture StatsPack/AWR Data
Wait Event Query
SELECT B.EVENT_NAMEchr(9) B.SNAP_IDchr(9) to_char(A.BEGIN_INTERVAL_TIME,'DD-MON-YYYYHH24MISS')chr(9) B.TIME_WAITED_MICRO FROM DBA_HIST_SNAPSHOT A, DBA_HIST_SYSTEM_EVENT B WHERE A.SNAP_ID B.SNAP_ID AND A.INSTANCE_NUMBER1 AND B.INSTANCE_NUMBERA.INSTANCE_NUMBER AND A.BEGIN_INTERVAL_TIME gt SYSDATE-21 AND B.EVENT_NAME IN ('PX Deq Credit send blkd', 'SQLNet message from client', 'SQLNet message to client', 'SQLNet more data from client', 'SQLNet more data to client', 'buffer busy waits', 'db file parallel read', 'db file parallel write', 'db file scattered read', 'db file sequential read', 'db file single write', 'direct path read', 'direct path write', '')ORDER BY B.EVENT_NAME,B.SNAP_ID
23
I/O Analysis
Plot Data Using EXCEL
24
I/O Analysis
Plot Data Using EXCEL
25
I/O Analysis
Plot Data Using EXCEL
26
Case Study 1 EMC Clariion CX-500
Travel and Hospitality Industry
Data Warehouse and Reporting
  • SUN V890 6 Dual-Core CPUs and 32 GB of RAM
  • Clock Speed is 2100 MHz
  • Operating System is Solaris 10 (64-bit)
  • Oracle 10.2.0.3 Enterprise Single Instance
  • Oracle Block Size 8K
  • Oracle Buffer Cache 2.7G
  • Oracle SGA 3 GB
  • ASM
  • Raid-5

27
Case Study 1 EMC Clariion CX-500
Transactions Per Second
28
Case Study 1 EMC Clariion CX-500
Single Block Read Performance (Avg Read Time 8.9
ms)
29
Case Study 1 EMC Clariion CX-500
Multi Block Read Performance (Avg Read Time 2.13
ms)
30
Case Study 1 EMC Clariion CX-500
System Event Statistics
31
Case Study 1 EMC Clariion CX-500
Read Waits (Single Block and Multi-Block)
32
Case Study 1 EMC Clariion CX-500
Redo Write Time Analysis (ms/block)
  • Redo Write Time Suspect for Oracle Versions lt
    9.2.0.8
  • Use Log File Sync Waits for Sanity Check
  • Good Metric to Check for
  • San Fabric Waits
  • NAS Network Waits

33
Case Study 1 EMC Clariion CX-500
Redo Write Time Analysis for CX-340 (OLTP
Database)
34
Case Study 1 EMC Clariion CX-500
ADDM Analysis
35
Case Study 1 EMC Clariion CX-500
Server CPU Activity
36
Case Study 1 EMC Clariion CX-500
Next Steps
  • No Obvious I/O Bottlenecks
  • If Business Users Were Complaining, Initiate
    Method R Analysis of Poor Performing Business
    Functions
  • 10046 Trace of Poorly Performing Application
    Processes
  • See Book by Cary Millsap and Jeffrey Holt
    Optimizing Oracle Performance
  • For the Functions Identified Above, Isolate the
    Hot SQL
  • Tune the SQL for Better Performance
  • Better Execution Plan
  • Better Algorithm

37
Automatic Storage Management (ASM)
Juan Loaizas 2000 OOW Paper on S.A.M.E
38
Automatic Storage Management (ASM)
Configuration Overview
  • Oracles Logical Volume Manager
  • Special ASM Instance (in its own ORACLE_HOME)
  • Uses RAW Disks
  • 11gR2 Adds Cluster File System Support
  • Can Support any Oracle Database (RAC, Non-RAC)
  • Users Typically Create Two Logical Volumes
  • DATA (Schema Objects, Redo, Control)
  • FLASH (Archives, RMAN bkups, FlashBack Logs,
    Control)
  • Other Common Logical Volumes Include
  • REDO (Redo Logs in own volume group)
  • TEMP (If Running Big Sorts and/or Big Hash Joins)

39
Automatic Storage Management (ASM)
Disk Configuration (S.A.M.E)
  • If We Present These Four Disks to ASM
  • Which Configuration is Best?

40
Automatic Storage Management (ASM)
Disk Configuration (Striping)
  • Very important Planning Consideration
  • ASM Always Stripe Across All Members of Each Disk
    Group
  • Striping Option 1 Coarse Striping
  • Set at 1 MByte (Largest single I/O operation that
    Oracle currently performs up through and
    including version 11g)
  • Recommended for OLTP
  • Striping Option 2 - Fine Grain Striping
  • Fine grain stripe size that is 128K
  • Appropriate for redo logs
  • Can Be Appropriate for DSS or DW Tablespaces

41
Automatic Storage Management (ASM)
Disk Configuration (Stripe Width and Stripe Depth)
  • Which is Correct If using Coarse Striping (1 MB)?

Stripe Depth 256K Stripe Width 1 MB
Stripe Depth 1M Stripe Width 4 MB

42
Automatic Storage Management (ASM)
Writing the Stripe
ALTER SESSION SET EVENTS 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 CREATE TABLESPACE ABC datafile 'DATA01/ractst/abc.dbf size 1000M extent management local uniform size 1M segment space management auto
  • Step 1 Preparation

WAIT 4 nam'Data file init write' ela 534 count1 intr256 timeout4294967295 obj-1 tim14093356333941 WAIT 4 nam'Data file init write' ela 471 count1 intr256 timeout4294967295 obj-1 tim14093356334587 WAIT 4 nam'Data file init write' ela 524 count1 intr256 timeout4294967295 obj-1 tim14093356335266 WAIT 4 nam'Data file init write' ela 468 count1 intr256 timeout4294967295 obj-1 tim14093356335882
43
Automatic Storage Management (ASM)
Writing the Stripe
  • Step 2 Writing the Stripe
  • Above Repeated 250 times
  • One for each of the 4 Mbyte wide ASM Stripes used
    to Create the 1 GByte tablespace.
  • Step 3 Two Final Write(s)

WAIT 4 nam'Data file init write' ela 17234 count1 intr32 timeout2147483647 obj-1 tim14093356353180
WAIT 4 nam'Data file init write' ela 79 count1 intr256 timeout4294967295 obj-1 tim14093361349183 WAIT 4 nam'Data file init write' ela 313 count4294967295 intr32 timeout2147483647 obj-1 tim14093361349516
44
Automatic Storage Management (ASM)
Disk Configuration (Plaid Stripe)
  • ASM is Going to Stripe
  • Striping on Storage Side and ASM Side Perfectly
    okay Creates Plaid Stripe
  • I/O performance tests from SAN Vendors and Others
    Confirm that Plaid Stripe Gives Best Overall I/O
    Performance
  • How Much Benefit Depends on the I/O Mix
  • If Its Potential to Boost I/O is Modest, is Plaid
    Striping worth doing?
  • ASM and Not Plaid Very Safe Configuration
  • ASM and Plaid Not Done Properly Adds Risk to I/O
    Performance

45
Automatic Storage Management (ASM)
Disk Configuration 1 (No Stripe on I/O Subsystem)
  • Present Complete Raw Disks To Oracle
  • Raid 1 Mirroring is used on Storage System
  • ASM Stripes Go Across Different Disks
  • Good Performance Low Risk

46
Automatic Storage Management (ASM)
Disk Configuration 2 (Plaid Stripe Configuration
Error)
ASM Plaid Stripe
  • Configuration Mismatch
  • ASM Stripes Not Evenly DistributedAcross All
    Disks
  • Stripe Across Same Physical Disk
  • Less Than Optimal Design

Raw Disks In I/O Subsystem Carved into LUNs

47
Automatic Storage Management (ASM)
Disk Configuration 3 (Plaid Stripe Applied
Correctly)
  • Optimum Design
  • Create two hardware disks groups from the four
    disks
  • Carve 2 LUNs on each disk group as follows
  • ASM Creates Two Disk Groups
  • DATA1 (A1 and A2)
  • DATA2 (B1 and B2)
  • ASM Stripes Across Different Disks
  • Optimal Design

48
Modern Disk Drive Technology
Disk Drive Components
Spindle
Platter
Actuator
Read/Write Heads
49
Modern Disk Drive Technology
Specs for Seagate Cheetah 15K.5-Internal-3.5in-U
  • Capacity 36.7 300 GB
  • 36.7 GB Per Platter Surface
  • Cylinders 73,340
  • Density 1/2 MB/Cylinder/Surface
  • Discs Up to 4
  • Heads Up to 8
  • Buffer Size 16 MB
  • Avg Seek (R/W) 3.4/4 ms
  • Track-to-Track (R/W) 0.2/0.4 ms
  • Only One Set of Heads Active at any One time
  • Stripe Downward from One Platter to Another
    without Moving Heads
  • Drives Read Entire Cylinder and return requested
    sectors
  • Disk Buffer Cache Supplements I/O Storage System
    Read Cache

50
Modern Disk Drive Technology
Oracle Specifics
  • Single Block Read
  • Disk Hardware Reads Entire Cylinder Containing
    Block
  • Just the Requested Oracle Block is return through
    the I/O Subsystem Read Cache
  • Random I/O Performance is Enhanced by the 16MB
    Disk Buffer Cache
  • Multi-Block and Direct Path Reads
  • Fetches Consecutive Blocks up to MBRC Setting (1
    MB Max)
  • Reading Down a Cylinder Maximizes Throughput
    Since Heads Dont Move

51
Modern Disk Drive Technology
Raid 10 or Raid 5?
  • Raid 5 Has Historically Had a Lot of Bad Press
    from Oracle Veterans
  • Oak Table
  • BAARF
  • Todays Modern Storage Systems Successfully
    Mitigate Raid 5 Performance Issues
  • Raid 10 Is Still Recommended for Heavy OLTP
    Systems
  • Use Raid 5 for Everything Else
  • Consider Solid State To Handle Heavy Redo Log
    Activity

52
Case Study 2 EMC Symmetrix DMX-4
Insurance Claim Network
Claims Processing Database
  • IBM 9117-570 with 12 CPUs and 24 GB of RAM
  • Tier 1 Storage (Raid 10)
  • CPUs are PowerPC_POWER5 (SMT_Enabled)
  • Clock Speed is 1.9 GHz
  • Operating System is AIX 5.3 (64-bit)
  • Oracle 9.2.0.6
  • Oracle Block Size 16K
  • Oracle Buffer Cache 2.048G
  • Oracle SGA 3.12G
  • Veritas File System

53
Case Study 2 EMC Symmetrix DMX-4
Transactions Per Second (Day and Night Shifts)
54
Case Study 2 EMC Symmetrix DMX-4
Single Block Reads (Avg Read Time is 3.72 ms)
55
Case Study 2 EMC Symmetrix DMX-4
Multi-Block Reads (Avg Read Time is 10 ms)
56
Case Study 2 EMC Symmetrix DMX-4
Average Redo Write Time
57
Case Study 3 EMC Symmetrix DMX-4
Insurance Provider
Customer Facing Claims Processing Database
  • IBM pSeries 660 with 4 CPUs and 12 GB of RAM
  • CPUs are PowerPC
  • Clock Speed is 600 MHz
  • Operating System is AIX 5.2 (64-bit)
  • Oracle 9.2.0.6
  • Oracle Block Size 4K
  • Oracle Buffer Cache 3.6G
  • Oracle SGA 5.1G
  • Raid 5

58
Case Study 3 EMC Symmetrix DMX-4
Transactions Per Second (Avg 10/sec)
59
Case Study 3 EMC Symmetrix DMX-4
Single Block Reads (Avg Read Time is 8.45 ms)
60
Case Study 3 EMC Symmetrix DMX-4
Multi-Block Reads (Avg Read Time is 19.59 ms)
61
Case Study 3 EMC Symmetrix DMX-4
System Statistics
62
Case Study 3 EMC Symmetrix DMX-4
Average Redo Write Time
63
Case Study 3 EMC Symmetrix DMX-4
Log File Sync Waits
64
Case Study 3 EMC Symmetrix DMX-4
DB File Sequential Scattered Read Waits Per
Second
65
Case Study 3 EMC Symmetrix DMX-4
DB File Parallel Reads
66
Case Study 3 EMC Symmetrix DMX-4
AIX I/O Request Size in Oracle Blocks (4K Block
Size)
Should See a Value gt 1AIX File System Mis-Match
with EMC Storage Layout
67
Case Study 4 SUN Hitachi 9970
Sales and Settlement Solutions for Airline Travel
Industry
Ticket Processing Engine
  • SUN E12K 8 CPUs 8 GB of RAM
  • Operating System is Solaris 8 (64-bit)
  • Oracle 9.2.0.6 Enterprise Single Instance
  • Oracle Block Size 8K
  • Oracle Cache Buffer 3.5G
  • Oracle SGA 4G
  • Standard Veritas Cooked File System
  • Raid 5

68
Case Study 4 SUN Hitachi 9970
Background Info
Problem
  • Converting from IBM Green Screen to Oracle,
    Web/Java
  • Load Process on Oracle Taking Too Long
  • Step 6 (Load Taxes) Never Finishing
  • VLONGOPS (2 Hrs, 140 Hrs, 2 Hrs, 3 Hrs, 4 Hrs)

Solution
  • Redo (Raid 5) Triggering Cache Cramming on
    Hitachi
  • Switch to Solid State
  • Add More Cache to Hitachi
  • I/O Pipe Too Small on Solaris 8
  • Configure Disks Using S.A.M.E (1 MB Stripe)
  • Configure Kernel to Align with Hitachi Stripe
  • Oracle
  • Turn-Off Tablespace Compression

69
Case Study 4 SUN Hitachi 9970
Transactions Per Second
70
Case Study 4 SUN Hitachi 9970
Single Block Read Performance (Avg Read Time 1.79
ms)
71
Case Study 4 SUN Hitachi 9970
Multi Block Read Performance (Avg Read Time 5.04
ms)
72
Case Study 4 SUN Hitachi 9970
System Event Statistics
73
Case Study 4 SUN Hitachi 9970
Read Waits (Single Block and Multi-Block)
74
Case Study 4 SUN Hitachi 9970
Redo Write Time Analysis (ms/block)
  • Redo Write Time Suspect for Oracle Versions lt
    9.2.0.8
  • Use Log File Sync Waits for Sanity Check
  • Good Check for
  • San Fabric Waits
  • NAS Network Waits

75
Case Study 4 SUN Hitachi 9970
Log File Sync Wait Analysis (3 Years Later)
76
Case Study 5 NetApps
Tax Preparation and Electronic Filing
Tax Processing Engine
  • HP Blade with (4) AMD Dual Core CPUs 32 GB of
    RAM
  • Clock Speed is xxx MHz
  • Operating System is Linux (64-bit)
  • Oracle 11.1.0.6 Enterprise Four Node RAC
  • Oracle Block Size 8K
  • Oracle Cache Buffer 7.4G
  • Oracle SGA 9.6G
  • Clustered File System
  • Raid 5

77
Case Study 5 NetApps
Transactions Per Second
78
Case Study 5 NetApps
Single Block Read Performance (Avg Read Time 8.31
ms)
79
Case Study 5 NetApps
Multi Block Read Performance (Avg Read Time 7.62
ms)
80
Case Study 6 EMC Symmetrix DMX-4
Government Agency
Incident Tracking Database
  • SUN E25K with 42 Dual Core CPUs and 32 GB RAM
  • CPUs are US-IV
  • Clock Speed is 1500 MHz
  • Operating System is Solaris 10 (64-bit)
  • Oracle 10.2.0.2
  • Oracle Block Size 32K
  • Oracle Buffer Cache 50G
  • Oracle SGA 65G
  • Raid 5

81
Case Study 6 EMC Symmetrix DMX-4
Multi Block Read Performance (Avg Read Time 991
ms)
82
Takeaways
  • Reviewed Primary Oracle Read I/O Requests
  • Single Block, Multi-Block, Direct Path Read,
    Parallel Read
  • Should How To Capture I/O Data by Querying
  • PERFSTAT.STATSFILESTATXS (StatsPack)
  • DBA_HIST_FILESTATXS (AWR)
  • Presented Some Key I/O Metrics to Capture and
    Examine
  • Reviewed ASM Configuration Issues
  • Presented a High Level Overview of Todays Disk
    Technology
  • Presented 6 Case Studies
  • Discussed Acceptable I/O Ranges for Real World
    Applications
  • Described Problems and How to Fix Them

83
To Ask Questions
www.biascorp.com
john.moffett_at_biascorp.com
Write a Comment
User Comments (0)
About PowerShow.com