1 / 83

Moscone South Room 301 San Francisco, CA October 13, 2009

Oracle Open World 2009 Measure, Interpret, and Analyze Oracle I/O Performance Data Presenter: John Moffett of BIAS Corporation. Moscone South Room 301 San Francisco, CA October 13, 2009. Agenda. Examine Four (4) Main Oracle Read Operations Cost Based Optimizer (CBO) and I/O Cost Computations

lazaro
Download Presentation

Moscone South Room 301 San Francisco, CA October 13, 2009

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 Today’s Disk Drive Technology • Case Studies • Takeaways

  3. Introduction to BIAS • 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 www.biascorp.com john.moffett@biascorp.com My Background

  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#

  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 Can’t Exceed • DB_FILE_MULTIBLOCK_READ_COUNT (MBRC) • Max I/O Size Supported by OS • Oracle Reads Around Blocks in the Buffer Cache

  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)

  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

  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)| 00:18:11 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 20 | 77864 (3)| 00:18:11 | | | Q1,01 | P->S | QC (RAND) | |* 3 | HASH JOIN | | 1 | 20 | 77864 (3)| 00:18:11 | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 1890K| 9230K| 91 (4)| 00:00:02 | | | Q1,01 | PCWP | | | 5 | PX SEND BROADCAST | :TQ10000 | 1890K| 9230K| 91 (4)| 00:00:02 | | | Q1,00 | P->P | BROADCAST | | 6 | PX BLOCK ITERATOR | | 1890K| 9230K| 91 (4)| 00:00:02 | | | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| CLM_SDI_UOW_AGNS | 1890K| 9230K| 91 (4)| 00:00:02 | | | Q1,00 | PCWP | | | 8 | PX BLOCK ITERATOR | | 482M| 6897M| 77207 (2)| 00:18:01 | 29 | 29 | Q1,01 | PCWC | | | 9 | TABLE ACCESS FULL | CLM_CLAIM_ENTITY_ASSOC | 482M| 6897M| 77207 (2)| 00:18:01 | 29 | 29 | Q1,01 | PCWP | | ----------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("CEA"."UOW_AGN"="AGNS"."U_AGN") 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@whltlg2 (TNS V1-V3) SELECT /*+ Q607000 NO_EXPAND ROWID(A1) */ A1.ROWID,A1."BAN",A1." ACCOUNT_TYPE",A1."BILL_CYCLE",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 ->ordered 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

  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

  17. Important StatsPack/AWR Tables Derived from Timed Statistic V$ Views

  18. PERFSTAT.STATS$FILESTATXS I/O Interpretation All Times in Centi-Seconds (One-Hundredth of a Second)

  19. DBA_HIST_FILESTATXS (AWR) I/O Interpretation All Times in Centi-Seconds (One-Hundredth of a Second)

  20. Timed Statistics Read Wait Events

  21. SQL to Capture StatsPack/AWR Data Query to Capture I/O Data

  22. SQL to Capture StatsPack/AWR Data Wait Event Query

  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 • 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 Data Warehouse and Reporting

  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 < 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 Loaiza’s 2000 OOW Paper on S.A.M.E

  38. Automatic Storage Management (ASM) Configuration Overview • Oracle’s 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 • Step 1: Preparation

  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)

  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 Don’t Move

More Related