Download as PDF, PPTX
Oracle Performance Tuning Fundamentals
- 1. Oracle Performance Tuning Fundamentals Carlos Sierra
- 2. Carlos Sierra • SQLTXPLAIN + SQL Health-‐Check SQLHC + • Consultant/Developer/DBA/Design/+ • Oracle Performance + SQL Tuning • Oracle Database Health-‐Check • Tools + Scripts • Speaker Enkitec © 2014 2
- 3. Oracle Performance Tuning Fundamentals • Concepts • Time • Waits • StaLsLcs • Dynamic Views Enkitec © 2014 3
- 4. What is Performance Tuning? • Provide correct mix and amount of Resources • Reduce User’s Wait Times • Changing System components to achieve Goals • Increase Throughput • Improve Response Time Enkitec © 2014 4
- 5. Why Performance Tuning is hard? • Concurrency • Dynamism • Complexity – Too many knobs to our disposal – Too many soXware layers Enkitec © 2014 5
- 6. Performance Tuning Approaches • “Top-‐down” • “Boom-‐up” • “HolisLc” • “Random” a.k.a. “Trial and Error” • “Silver Bullets” a.k.a. “One Size Fits All” Enkitec © 2014 6
- 7. Back to Basics • Understand your OperaLng System • Understand your Database • Understand your Business • Understand your Users • Understand your OpLons Enkitec © 2014 7
- 8. OS Components • CPU • Memory • I/O Subsystem – Disk • Capacity and Throughput • Network Enkitec © 2014 8
- 9. OS Performance Monitoring Tools • sar • top and htop • mpstat and vmstat • iotop and iostat • dtrace and strace Enkitec © 2014 9
- 10.
- 11. OS Tools Strategy • Define your own subset of Tools to use • Learn them well • Create some Scripts or use a GUI • Correlate DB Performance to OS Monitoring • Work with your System Administrator Enkitec © 2014 11
- 12. OS StaLsLcs within Oracle • Provides a high-‐level view of OS • CumulaLve Metrics • Views – V$OSSTAT – DBA_HIST_OSSTAT Enkitec © 2014 12
- 13.
- 14. Oracle Architecture • Database – Data Files + Temp Files + Redo Log + Control Files • Instance – SGA + PGA + Processes • RAC Enkitec © 2014 14
- 15.
- 16.
- 17.
- 18. Performance is about Time • Performance is beer understood as Time • Which Time? – User? – Clock? – Elapsed? – CPU? Enkitec © 2014 18
- 19. Service Time • Service Time = CPU Time • User Time = CPU Time + Wait Time • User Time = Service Time + Wait Time • Time = Service + Wait Enkitec © 2014 19
- 20. Wait Time • Overhead • Two flavors – Non-‐Idle • Inside the database – Idle • Outside the database Enkitec © 2014 20
- 21. Non-‐idle Waits • Overhead • AcLvely WaiLng inside the database • Examples – Reading a Block from Disk – Index Rebuild – ApplicaLon Row level Lock Enkitec © 2014 21
- 22. Idle Waits • Overhead • InacLve • WaiLng for work • Outside the database Enkitec © 2014 22
- 23. What is a Wait Event? • V$EVENT_NAME • 1,152 on 11.2.0.3 • P1, P2, P3 Parameters – Oracle Database Reference • C Oracle Wait Events • Categorized into 12 Wait Classes Enkitec © 2014 23
- 24. Wait Class (1) • AdministraLve • ApplicaLon • Cluster • Commit Enkitec © 2014 24
- 25. Wait Class (2) • Concurrency • ConfiguraLon • Network • Other Enkitec © 2014 25
- 26. Wait Class (3) • Queue • Scheduler • System I/O • User I/O Enkitec © 2014 26
- 27. Timed Event • CPU + • Non-‐idle Wait Events Enkitec © 2014 27
- 28. Performance StaLsLcs • If Wait Events refer to Time and Times • Then StaLsLcs refer to Counters – How many of “X” so far (from Instance startup) – Examples • Sorts • Consistent Gets Enkitec © 2014 28
- 29. StaLsLcs a.k.a. Counters • V$STATNAME • 638 Counters on 11.2.0.3 • DescripLon – Oracle Database Reference • E StaLsLcs DescripLons • Categorized into 8 StaLsLcs Classes Enkitec © 2014 29
- 30. StaLsLcs Classes (1) • 1, User • 2, Redo • 4, Enqueue • 8, Cache Enkitec © 2014 30
- 31. StaLsLcs Classes (2) • 16, OS • 32, Real ApplicaLon Clusters • 64, SQL • 128, Debug Enkitec © 2014 31
- 32. StaLsLcs Classes (3) • A Counter belongs to 1 or more Class Enkitec © 2014 32
- 33. Session Type and State • Foreground Type – User Session • Background Type – Database Programs • On CPU State • WaiLng State Enkitec © 2014 33
- 34. Database Time • Includes all Foreground Sessions – Currently “On CPU” or on a “Non-‐idle Wait Event” • Be aware – Database Time in 1hr interval can add to > 1hr – It can also add to > (Lme interval x CPU count) Enkitec © 2014 34
- 35. Database Time Example • Wall Clock Time: 60 minutes • CPU_COUNT: 12 • CPU Time: 300 minutes (< 12 x 60 = 720) ~42% • 30 users waiLng on a Lock for 50 minutes each • Thus: DB Time of 1,800 minutes (> 720) – 300 + (30 x 50) Enkitec © 2014 35
- 36. Background Time • Includes all Background Sessions – Currently “On CPU” or on a “Non-‐idle Wait Event” Enkitec © 2014 36
- 37. System Time Model (1) • V$SYS_TIME_MODEL • V$SESS_TIME_MODEL • CumulaLve Time with no wrapping • Tree with 19 nodes – Two root nodes Enkitec © 2014 37
- 38. System Time Model (2) • Two root nodes – DB (Elapsed) Time – Background Elapsed Time – Notes: • Children do not necessarily add up to the parent • Children are not necessarily exclusive • The union of children does not cover the whole of the parent Enkitec © 2014 38
- 39. System Time Model (3) Enkitec © 2014 39 1) background elapsed time 2) background cpu time 3) RMAN cpu time (backup/restore) 1) DB time 2) DB CPU 2) connection management call elapsed time 2) sequence load elapsed time 2) sql execute elapsed time 2) parse time elapsed 3) hard parse elapsed time 4) hard parse (sharing criteria) elapsed time 5) hard parse (bind mismatch) elapsed time 3) failed parse elapsed time 4) failed parse (out of shared memory) elapsed time 2) PL/SQL execution elapsed time 2) inbound PL/SQL rpc elapsed time 2) PL/SQL compilation elapsed time 2) Java execution elapsed time 2) repeated bind elapsed time
- 40. Response Time • Time a Query takes to return First Row(s) • Important for Online oriented processing – OLTP behavior Enkitec © 2014 40
- 41. Throughput • Amount of Work performed in a given Time – How many Rows this Query returns per Second? – Time a Query takes to return All Rows • Important for Batch oriented processing – Data warehouse behavior Enkitec © 2014 41
- 42. User Time • A measure of User Experience – Time it takes for a User to get some Result • Includes – Database Time – Scheduling Time – Network Time Enkitec © 2014 42
- 43. Which Time is more important? • Database? • Background? • Service? • Wait? • Response? • User? Enkitec © 2014 43
- 44. Processes and Sessions • ConnecLon is a pathway to the Database • A ConnecLon can have 0, 1 or more Sessions • A Process may serve 1 or more Sessions • Default Sessions – (1.5 x Processes) + 22 Enkitec © 2014 44
- 45. How many Processes to have? • Ideally between 2 and 5 Lmes the number of CPUs – Example: 16 CPUs • Ideally: between 32 and 80 • Common: ~1000 • Advice: – Keep “processes” parameter as small as possible Enkitec © 2014 45
- 46. Average AcLve Session (AAS) • Common unit to compare Performance • What is an “AcLve Session”? – One “On CPU” or on a “Non-‐idle Wait Event” • Two ways to compute AAS 1. Using count of AcLve Sessions on a Snapshot 2. Database Time divided over Wall Clock Time Enkitec © 2014 46
- 47. WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Startup Time Release RAC -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐ XXX 1319103893 XXX 1 10-‐Apr-‐14 14:55 11.2.0.3.0 NO Host Name Platform CPUs Cores Sockets Memory(GB) -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐ -‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ xxxxxxxx AIX-‐Based Systems (64-‐bit) 16 4 72.00 Snap Id Snap Time Sessions Curs/Sess -‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐-‐ Begin Snap: 139 11-‐Apr-‐14 15:00:58 217 144.9 End Snap: 140 11-‐Apr-‐14 15:15:58 218 145.7 Elapsed: 15.01 (mins) DB Time: 83.46 (mins) Cache Sizes Begin End ~~~~~~~~~~~ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ Buffer Cache: 23,360M 23,360M Std Block Size: 8K Shared Pool Size: 1,664M 1,664M Log Buffer: 11,848K Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ DB Time(s): 5.6 1.7 0.02 0.11 DB CPU(s): 0.2 0.1 0.00 0.00 Redo size: 238,783.6 72,491.8 Logical reads: 2,300.9 698.5 Block changes: 626.3 190.2 Physical reads: 3.9 1.2 Physical writes: 28.8 8.7 User calls: 48.4 14.7 Parses: 14.3 4.3 Hard parses: 0.0 0.0 W/A MB processed: 0.1 0.0 Logons: 0.1 0.0 Executes: 317.3 96.3 Rollbacks: 1.1 0.3 Transactions: 3.3 Enkitec © 2014 47
- 48. Performance Tuning Goals • Minimize amount of Business required work • OpLmize CPU Time – Avoid wasLng cycles • OpLmize Wait Time – Minimize Wait Time • Judged by Business and by User community Enkitec © 2014 48
- 49. Basic DB Performance CollecLon • System-‐wide Waits – CumulaLve • Session Waits – CumulaLve and Current • Session and System-‐wide StaLsLcs Counters – CumulaLve Enkitec © 2014 49
- 50. Dynamic Performance Views (1) • V$SESSION_WAIT – Current or last Wait • Only one row per Session • P1, P2 and P3 provide details of Wait – Available also on V$SESSION Enkitec © 2014 50
- 51.
- 52. Dynamic Performance Views (2) • V$SESSION_EVENT – Total Waits and Time Waited per Event – Only those Events the Session has waited on Enkitec © 2014 52
- 53.
- 54.
- 55. Dynamic Performance Views (3) • V$SESSION_WAIT_CLASS – Total Waits and Time Waited per Wait Class – Aggregate of V$SESSION_EVENT on Wait Class Enkitec © 2014 55
- 56.
- 57. Dynamic Performance Views (4) • V$SESSTAT – StaLsLcs a.k.a. Counters (not Waits) – SID, staLsLcs# and value – Join to V$STATNAME Enkitec © 2014 57
- 58.
- 59.
- 60. Dynamic Performance Views (5) • V$SYSTEM_EVENT – Total Waits and Time Waited per Event – Aggregate of V$SESSION_EVENT – Instance-‐wide Enkitec © 2014 60
- 61. Dynamic Performance Views (6) • V$SYSTEM_WAIT_CLASS – Total Waits and Time Waited per Wait Class – Aggregate of V$SYSTEM_EVENT on Wait Class; or – Aggregate of V$SESSION_WAIT_CLASS Enkitec © 2014 61
- 62. Dynamic Performance Views (7) • V$SYSSTAT – staLsLcs#, name, class and value – No need to join to V$STATNAME – Aggregate of V$SESSTAT Enkitec © 2014 62
- 63. Dynamic Performance Views (8) • V$SESS_TIME_MODEL • V$SESSMETRIC • V$SESS_IO • V$SESSION_LONGOPS Enkitec © 2014 63
- 64.
- 65. Dynamic Performance Views (9) • V$SYS_TIME_MODEL • V$SYSMETRIC • V$SYSMETRIC_SUMMARY • V$SYSMETRIC_HISTORY Enkitec © 2014 65
- 66. Dynamic Performance Views (10) • V$EVENT_HISTOGRAM • V$EVENTMETRIC • V$WAITCLASSMETRIC • V$WAITCLASSMETRIC_HISTORY Enkitec © 2014 66
- 67.
- 68. Dynamic Performance Views (11) • V$FILEIO • V$FILE_HISTOGRAM • V$FILE_METRIC • V$FILEMETRIC_HISTORY Enkitec © 2014 68
- 69. Dynamic Performance Views (12) • V$OSSTAT • V$PGASTAT • V$SGASTAT • And many more… Enkitec © 2014 69
- 70.
- 71. Dynamic Views Summary • V$SYSTEM_EVENT – Total Waits for Events (system-‐wide) • V$SESSION_EVENT – Total Waits for Events (session specific) • V$SESSION_WAIT – Current or Last Wait (session specific) Enkitec © 2014 71
- 72. Real-‐life use of Dynamic Views • SQL*Plus Scripts – Your own or Tanel Poder’s “snapper.sql” • Current and legacy Tools – OEM, AWR, ADDR, ASH, Statspack, bstat/estat • Other Scripts and Tools Enkitec © 2014 72
- 73. AutomaLc Workload Repository (AWR) • Requires Oracle DiagnosLcs Pack License • 112 DBA_HIST Views on 11.2.0.3 • Snapshots – One hour interval (default) – One week retenLon (default) Enkitec © 2014 73
- 74. AcLve Session History (ASH) • Requires Oracle DiagnosLcs Pack License • MulL-‐dimension • V$ACTIVE_SESSION_HISTORY – One snapshot (sample_id) every second • DBA_HIST_ACTIVE_SESS_HISTORY – One sample_id out of every 10 Enkitec © 2014 74
- 75. Methodology in a Nut Shell • Listen to the voice of the Business • Collect DiagnosLcs and compare to Baselines • IdenLfy Pain and Bolenecks • MiLgate Pain by addressing Bolenecks – Reach out when needed – Learn and document Enkitec © 2014 75
- 76. Database Temperature Rule of Thumb • If (Database + Background) Time < Interval è ice cold < Interval x CPU count è cold < 5 x Interval x CPU count è warm < 10 x Interval x CPU count è hot > 10 x Interval x CPU count è melLng Enkitec © 2014 76
- 77. Warning • Take “Rules of Thumb” with a pinch of salt and do not confuse them with “Myths” • Rule of Thumb “a principle with broad applica7on that is not intended to be strictly accurate or reliable for every situa7on” • Myth “a widely held but false belief or idea” Enkitec © 2014 77
- 78. Some Myths (1) • Change nothing and Performance will remain the same (i.e. Freeze CBO StaLsLcs) • Parallelize as much as possible and everything will run faster • Improve Buffer Hit RaLo and Performance will improve Enkitec © 2014 78
- 79. Some Myths (2) • An Index Access operaLon is beer than a Full Table Scan • Placing Tables and Indexes on separate Tablespaces provides beer performance • Reorganize all Indexes periodically for beer performance Enkitec © 2014 79
- 80. Some Myths (3) • Upgrading to faster CPUs results on beer performance • Allocate to Oracle more Memory and Processes run faster • Segments (Tables or Indexes) with one Extent perform beer Enkitec © 2014 80
- 81. Some Myths (4) • SQL cannot be modified (canned applicaLon) • A truncated SQL Trace cannot be used for DiagnosLcs • Top-‐5 Wait Events show the root cause of the poor Performance • Silver Bullets are sound and reliable Enkitec © 2014 81
- 82. Conclusions • User Experience should be the driver – AuthenLc Business Requirements • QuesLon Everything – Apply ScienLfic Method: Test, Prove or Debunk • Balance between broad with deep analysis • Diagnose with sound Scripts and Tools Enkitec © 2014 82
- 83. References • Oracle Database Reference 11g Release 2 • Oracle Database Concepts 12c Release 1 • Snapper -‐ Tanel Poder – hp://blog.tanelpoder.com/files/scripts/ snapper4.sql Enkitec © 2014 83
- 84. Contact InformaLon • carlos.sierra@enkitec.com • carlos-‐sierra.net • @csierra_usa Enkitec © 2014 84