1 / 135

Performance Tuning in Oracle 10g Feel the Power !

Performance Tuning in Oracle 10g Feel the Power !. Kyle Hailey Kyle.hailey@oracle.com http://oraperf.sourceforge.net. Part I Eat your Spinach (Hold on to the seat of your pants … ). Part II Wow – the main course. Part III Dessert – this is cool (And that’s not all). New Features.

ewa
Télécharger la présentation

Performance Tuning in Oracle 10g Feel the Power !

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. Performance Tuning in Oracle 10gFeel the Power ! Kyle Hailey Kyle.hailey@oracle.com http://oraperf.sourceforge.net

  2. Part I Eat your Spinach (Hold on to the seat of your pants … ) Part II Wow – the main course Part III Dessert – this is cool (And that’s not all) New Features • Metrics • Wait Classes • Time Model • ASH • AWR ( DBA_HIST_ ) • ADDM • Misc

  3. Metrics … a new 10g feature to make our lives easier • Metrics • Wait Classes • Time Model • ASH • AWR ( DBA_HIST_ ) • ADDM • Misc Part I Part II Part III

  4. Motivation • What inspired Metrics? • Why should you care ?

  5. Eating your Spinach  • How do we find Performance Problems • With Statistics • Statistics have always been a Pain • How do YOU find bottlenecks with statistics? • YOU DO THE MATH • This is TEDIOUS • (and your boss probably doesn’t even appreciate you for it )

  6. First Tedious Step SQL> Select value from v$sysstat where name=‘physical reads’; VALUE --------------- 1,533,787 Not much help …Why? Let see …

  7. This tells you … Nothing GOOD BAD 1,533,787 30 minutes IO’s 30 minutes time time

  8. How do You find the Delta? Where’s the Beef ?

  9. YOU Need to do MATH to find Out • Take value at time A • Take value at time B • Delta = (B-A) or • Rate = (B-A)/elapsed time

  10. Current Methods • Oracle 6 Utlbstat.sql/Utlestat.sql • Creates tables, inserts, deletes • Oracle 8 introduced Statspack • Improvement, but needs to be set up and administered (by guess who) Another Option • Write your own Scripts • Take time to write, no standards In Summary These options take time … whose time?

  11. YOUR TIME

  12. Solution ! 10g Metrics • Available Immediately at your fingertips … for your enjoyment and relaxation, • Introducing Metrics • Automated • Immediate • Always there • Time saving (whose time? … YOUR time)

  13. Metrics dependability at your fingertips • Pre-Set intervals • 15 second • 60 second • 10 minutes • 30 minutes • Current Value for • Deltas • Rates • per second • per transaction • Ratios • Percentages

  14. Serious Geek Stuff :Our Metric Family • Wait Events • V$EVENTMETRIC (60 secs ) • V$WAITCLASSMETRIC (60 secs) • Statistics • V$SESSMETRIC (15 secs Deltas) • V$SYSMETRIC (15 and 60 secs deltas) • Files • V$FILEMETRIC ( 10 minutes) • SQL (the secret is out) • x$kewrtsqlstat (30 Minutes) “not a metric” cumulates values up to 30 minutes, then snapshots it to dba_hist_sqlstat

  15. The Solution Table v$sysmetric SQL> desc v$sysmetric BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT

  16. Now What’s the IO? No Calculations, just a simple select : SQL> Select VALUE , METRIC_UNIT from v$sysmetric where name=‘Physical Reads’ / (Gives per second and per transaction)

  17. Answer at your fingertips GOOD BAD X 3 IO/sec 513 IO/sec 1,533,787 30 minutes IO’s 30 minutes time time

  18. We solved what’s Happening now… but • What if your problem happened 10 minutes ago? • How do we get History? • What happened in the past? More work, Time and calculations by YOU

  19. That was now. What was then? v$sysstat physical reads Low rate Little IO High rate Lots of IO

  20. v$sysstat physical reads delta delta delta delta delta delta delta 60 secs 60 secs 60 secs 60 secs 60 secs 60 secs 60 secs Solution! Metric History Tables • Last 60 minutes of history, in memory

  21. Family of Metric History Tables At your fingertips : • Statistics • V$SYSMETRIC_HISTORY (60 seconds) (including 3 minutes of 15 second history as a bonus!) • File IO • V$FILEMETRIC_HISTORY (10 minutes) • Waits • V$WAITCLASSMETRIC_HISTORY ( 60 seconds)

  22. v$sysstat physical reads 60 secs 60 secs 60 secs 60 secs 60 secs 60 secs 60 secs 1 hour of 60 second deltas delta delta delta delta delta delta delta

  23. That’s not all Folks, as a bonus: v$sysstat physical reads 3 Minutes 15 second deltas 3 minutes of 15 second deltas

  24. Both Stored in Same Table v$sysmetric_history 3 minutes of 15 second deltas 60 minutes of 1 minute deltas Not saved to disk but summary is

  25. What was IO 30 minutes ago? SQL> Select VALUE , METRIC_UNIT from v$sysmetric_history where METRIC_NAME = ‘Physical Reads’ and END_TIME < ( sysdate - (30/(24*60))) and END_TIME > ( sysdate - (35/(24*60))) / Once again the answer is at your fingertips

  26. How about a 2 days ago? Long Term History, 7 days • Statistics • DBA_HIST_SYSMETRIC_SUMMARY • DBA_HIST_SYSMETRIC_HISTORY (alerts) • DBA_HIST_SYSSTAT (cumulative) • Waits • WAITCLASSMETRIC_HISTORY (alerts) • DBA_HIST_SYSTEM_EVENT (cumulative) • File IO • DBA_HIST_FILEMETRIC_HISTORY(alerts) • DBA_HIST_FILESTATXS (cumulative) • SQL • DBA_HIST_SQLSTAT

  27. EM Exposing Metrics

  28. That was the Introduction to Metrics • We saw • Current deltas : Metrics Tables • Hour History : Metric History Tables • Week of History : DBA_HIST Tables • Now lets Look at the Groupings • Statistics • Waits • File I/O

  29. Statistics • Raw : v$sysstat • Current Rates: v$sysmetric 15 & 60 seconds • 15 Second • 60 Second • Session Stats • 1 Hour : v$sysmetric_history (in memory) • 7 Days : dba_hist_sysmetric_summary (with AWR)

  30. v$sysmetric 15 Secs for 3 minutes Per Sec and Per Transaction Buffer Cache Hit Ratio Memory Sorts Ratio Execute Without Parse Ratio Soft Parse Ratio Database CPU Time Ratio Library Cache Hit Ratio Shared Pool Free % Txns Per Logon Physical Reads Physical Writes Physical Reads Direct Redo Generated Logons User Calls Logical Reads Redo Writes Total Table Scans Full Index Scans DB Block Gets Consistent Read Gets DB Block Changes Consistent Read Changes Executions Per Sec User Transaction Per Sec

  31. v$sysmetric 60 Sec for an hour Buffer Cache Hit Ratio Memory Sorts Ratio Redo Allocation Hit Ratio User Commits Percentage User Rollbacks Percentage Cursor Cache Hit Ratio Rows Per Sort Execute Without Parse Ratio Soft Parse Ratio User Calls Ratio Global Cache Average CR Get Time Global Cache Average Current Get Time Global Cache Blocks Corrupted Global Cache Blocks Lost Current Logons Count Current Open Cursors Count User Limit % SQL Service Response Time Database Wait Time Ratio Database CPU Time Ratio Row Cache Hit Ratio Row Cache Miss Ratio Library Cache Hit Ratio Library Cache Miss Ratio Shared Pool Free % PGA Cache Hit % Process Limit % Session Limit % Txns Per Logon

  32. v$sysmetric 60 Sec rates sec/txn for an hour Per Second and Transaction Per Sec Disk Sort Enqueue Timeouts Enqueue Waits Enqueue Deadlocks Enqueue Requests DB Block Gets Consistent Read Gets DB Block Changes Consistent Read Changes CPU Usage CR Blocks Created CR Undo Records Applied User Rollback Undo Records Applied Leaf Node Splits Branch Node Splits PX downgraded 1 to 25% PX downgraded 25 to 50% PX downgraded 50 to 75% PX downgraded 75 to 99% . Physical Reads Physical Writes Physical Reads Direct Physical Writes Direct Physical Reads Direct Lobs Physical Writes Direct Lobs Redo Generated Logons Open Cursors User Calls Recursive Calls Logical Reads Redo Writes Long Table Scans Total Table Scans Full Index Scans Total Index Scans Total Parse Count Hard Parse Count Parse Failure Count User Commits User Rollbacks User Transaction DBWR Checkpoints Background Checkpoints Network Traffic Volume Per Transaction Response Time

  33. v$sessmetric: Session Metric V$sessmetric BEGIN_TIME END_TIME INTSIZE_CSEC SESSION_ID SESSION_SERIAL_NUM CPU PHYSICAL_READS PGA_MEMORY HARD_PARSES SOFT_PARSES PHYSICAL_READ_PCT LOGICAL_READ_PCT • No History • Only a one 15 second Delta

  34. Stats Family of Tables Statistics Metrics v$sysmetric BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT v$metricgroup GROUP_ID NAME INTERVAL_SIZE MAX_INTERVAL v$sysstat STATISTIC# NAME CLASS VALUE HASH v$sysmetric_history BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT v$sessmetric BEGIN_TIME END_TIME INTSIZE_CSEC SESSION_ID SESSION_SERIAL_NUM CPU PHYSICAL_READS PGA_MEMORY HARD_PARSES SOFT_PARSES PHYSICAL_READ_PCT LOGICAL_READ_PCT v$sessstat STATISTIC# NAME CLASS VALUE HASH v$sysmetric_summary BEGIN_TIME END_TIME INTSIZE_CSEC -> intsize GROUP_ID METRIC_ID METRIC_NAME NUM_INTERVAL MAXVAL MINVAL AVERAGE STANDARD_DEVIATION METRIC_UNIT v$metricname GROUP_ID GROUP_NAME METRIC_ID METRIC_NAME METRIC_UNIT

  35. Waits • Raw : v$system_event • Current Deltas : v$eventmetric (60 seconds) • 1 Hour : n/a • 7 Days : dba_hist_system_event (cumulative)

  36. Desc v$eventmetric SQL> desc v$eventmetric Name Type ---------------------------------- ------ BEGIN_TIME DATE END_TIME DATE INTSIZE_CSEC NUMBER EVENT# NUMBER EVENT_ID NUMBER NUM_SESS_WAITING NUMBER TIME_WAITED NUMBER WAIT_COUNT NUMBER

  37. Select from v$eventmetric SQL> select en.name name, num_sess_waiting WAITERS, time_waited, wait_count from v$eventmetric em, v$event_name en where wait_count > 0 and en.event# = em.event# /

  38. v$eventmetric results NAME WAITERS TIME_WAITED WAIT_COUNT ------------------------------ ---------- ----------- ---------- pmon timer 1 5875 20 process startup 0 13 1 rdbms ipc message 7 41104 168 control file sequential read 0 0 10 control file parallel write 0 2 20 log file parallel write 0 0 1 SQL*Net message to client 0 0 47 SQL*Net more data to client 0 1 4 SQL*Net message from client 4 14721 47 SQL*Net more data from client 0 0 1 queue messages 2 12012 24 Queue Monitor Wait 1 3000 1 Queue Monitor Task Wait 0 0 1

  39. Files • Raw : v$fileio • Current Delta : v$filemetric ( 10 Minutes) • 1 Hour : v$filemetric_history ( 1 hour, 7 points) • 7 Days : dba_hist_filemetric_history (alerts only)

  40. V$FILEMETRIC_HISTORY SQL> select BEGIN_TIME, FILE_ID, PHYSICAL_READS from V$FILEMETRIC_HISTORY; BEGIN_TI FILE_ID PHYSICAL_READS -------- ---------- -------------- 04:12:16 1 208 04:12:16 2 600 04:02:18 1 600 04:02:18 2 189 03:52:15 1 1922 03:52:15 2 2082 ... For the last hour

  41. Metrics – in summary • Current rates automatically calculated • History of Rates for an hour in memory • History kept for a week on disk

  42. Metrics • V$METRIC • V$METRIC_HISTORY • Combined view onto the other • metric tables • V$METRICGROUP • V$METRICNAME Current Values • V$SYSMETRIC • V$SESSMETRIC • V$FILEMETRIC • V$EVENTMETRIC • V$WAITCLASSMETRIC • V$SVCMETRIC Last Hour • V$SYSMETRIC_HISTORY • V$FILEMETRIC_HISTORY • V$WAITCLASSMETRIC_HISTORY • V$SVCMETRIC_HISTORY • V$SYSMETRIC_SUMMARY – avg, std dev, max, min • Week of History • dba_hist_sysmetric_summary • dba_hist_system_event (cumulative) • dba_hist_filemetric_history (alerts)

  43. Metrics Family of Tables

  44. Wait Classes • Metrics • Wait Classes • Time Model • ASH • AWR ( DBA_HIST_ ) • ADDM • Misc Part I Part II Part III

  45. Wait Classes • Configuration(20) • log file size • Enqueues: ST, HW, ITL • Latch: redo copy,shared pool • Idle(56) • Network(25) • System I/O(19) • Scheduler(6) • User I/O(12) • Other(485) • Administrative (39) • switch logfile • rebuild index • Application (11) • enqueues • sqlnet break/reset • Cluster (113) • Commit(1) • Log file Sync • Concurrency (12) • Latches: cbc, lbc, • Lib cache locks • Buffer busy wait

  46. Wait Classes in EM

  47. Wait Class Tables • v$system_wait_class • cumulative • v$waitclassmetric • Current rates • v$waitclassmetric_history • Hour of rates (60 second intervals)

  48. Waits Metrics v$eventmetric BEGIN_TIME END_TIME INTSIZE_CSEC EVENT_ID NUM_SESS_WAITING TIME_WAITED WAIT_COUNT Cumulative Current deltas Last Hour Last 7 days Wait Classes dba_hist_ waitclassmetric_history Only gets populated with alerts v$system_wait_class WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED v$waitclassmetric_history BEGIN_TIME END_TIME INTSIZE_CSEC WAIT_CLASS_ID WAIT_CLASS# NUM_SESS_WAITING TIME_WAITED WAIT_COUNT v$waitclassmetric BEGIN_TIME END_TIME INTSIZE_CSEC WAIT_CLASS_ID WAIT_CLASS# NUM_SESS_WAITING TIME_WAITED WAIT_COUNT v$event_name EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS Wait Events No in memory history dba_hist_system_event (Cumulative) v$system_event EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO EVENT_ID

  49. Time Model • Metrics • Wait Classes • Time Model • ASH • AWR ( DBA_HIST_ ) • ADDM • Misc Part I Part II Part III

  50. Time Model New concept • DB Time Total time for all database calls • cpu time • wait time

More Related