1 / 119

To Do

To Do. ADDM – add chart showing advice over time Client id – draw sessions and client bouncing from session to session Services – show connection diagram Get John’s screen shot Talk about SQL tuning on SQL details Show group bys with ASH by various axes

kaitlyn
Download Presentation

To Do

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. To Do • ADDM – add chart showing advice over time • Client id – draw sessions and client bouncing from session to session • Services – show connection diagram • Get John’s screen shot • Talk about SQL tuning on SQL details • Show group bys with ASH by various axes • Show examples of setting client_id, service, turning on • More test cases, what does ADDM find

  2. Performance Tuning in Oracle 10g Kyle Hailey

  3. EM Product Layout for Performance Database Home Page Database Performance Page Drilldowns Session SQL

  4. Home Page Perf Page ADDM Top Session Wait Detail Top SQL ADDM Details SQL Detail Session Detail EM Pages Layout

  5. Buffer Busy Waits Use Case

  6. Three Paths Home Page Perf Page ADDM Top Session Wait Detail Top SQL ADDM Details SQL Detail Session Detail

  7. Home Page Perf Page ADDM Top Session Wait Detail Top SQL ADDM Details SQL Detail Session Detail ADDM Path

  8. Database Home Page

  9. Home Page Perf Page ADDM Top Session Wait Detail Top SQL ADDM Details SQL Detail Session Detail ADDM Home

  10. ADDM Home

  11. Home Page Perf Page ADDM Top Session Wait Detail Top SQL ADDM Details SQL Detail Session Detail ADDM Details

  12. ADDM Details

  13. Home Page Perf Page ADDM Top Session Wait Detail Top SQL ADDM Details SQL Detail Session Detail Manual Path

  14. Database Home Page

  15. Database Home Page

  16. Database Home Page

  17. Home Page Perf Page ADDM Top Session Top SQL Wait Detail ADDM Details SQL Detail Session Detail Performance Page

  18. Database Performance Page

  19. Database Performance Page highlight

  20. Home Page Perf Page ADDM Top Session Wait Detail Top SQL ADDM Details SQL Detail Session Detail Wait Drill Down

  21. Wait Drill Down

  22. Wait Drill Down

  23. Wait Drill Down

  24. Wait Drill Down highlight

  25. Wait Drill Down – Top SQL

  26. Home Page Perf Page ADDM Top Session Wait Detail Top SQL ADDM Details SQL Detail Session Detail SQL Details

  27. SQL Details

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

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

  30. Metrics Motivation • Performance Statistics • Indicators of Database performance • Cumulative Counters since DB Start • Not Much use in raw form

  31. Querying Statistics Select value from v$sysstat where name=‘physical reads’; SQL> / VALUE --------------- 1,533,787 SQL>

  32. Statistics are Cumulative v$sysstat physical reads Statistics just keep growing

  33. How many Physical Reads/Sec v$sysstat physical reads 1.5M What is the rate here? Statistics just keep growing

  34. Low IO v$sysstat physical reads 1.5M 30 minutes IO’s time

  35. High IO - same cumulative v$sysstat physical reads 1.5M IO’s 30 minutes time

  36. Need Deltas to get Rates • Take value at time A • Take value at time B • Rate = (B-A)/elapsed time

  37. Current Methods • Utlbstat.sql/Utlestat.sql • Creates tables, inserts, deletes • Statspack • needs to be set up • Customized Scripts • Take time to write, no standards Bit overkill for just a quick statistic rate query

  38. Solution : Metrics Rates are Automated in 10g • Deltas at set intervals • 15 second • 60 second • Current Rates • per second • per transaction • Ratios and percentages

  39. Which Statistics have Metrics? • Wait Events • V$EVENTMETRIC (60 secs ) • V$WAITCLASSMETRIC (60 secs) • Statistics • V$SESSMETRIC (15/60 secs) • V$SYSMETRIC (15/60 secs) • Files • V$FILEMETRIC ( 10 minutes)

  40. Short Term History: What happened in the Past? v$sysstat physical reads Low rate Little IO High rate Lots of IO

  41. v$sysstat physical reads 60 secs 60 secs 60 secs 60 secs 60 secs 60 secs 60 secs Solution: Metric History Tables • Last 60 minutes of history delta delta delta delta delta delta delta

  42. Metric History Tables In memory • Statistics • V$SYSMETRIC_HISTORY (15 and 60 seconds) • File IO • V$FILEMETRIC_HISTORY (10 minutes) • Waits • V$WAITCLASSMETRIC_HISTORY ( 60 seconds)

  43. v$sysstat physical reads 60 secs 60 secs 60 secs 60 secs 60 secs 60 secs 60 secs More Detail for Last 3 minutes delta delta delta delta delta delta delta

  44. Statistics have 15 sec deltas v$sysstat physical reads 3 Minutes 15 second deltas

  45. v$sysmetric_history 3 minutes of 15 second deltas 60 minutes of 1 minute deltas Not saved to disk but summary is

  46. Long Term History, 7 days On Disk • Statistics • DBA_HIST_SYSMETRIC_SUMMARY • DBA_HIST_SYSSTAT (cumulative) • DBA_HIST_SYSMETRIC_HISTORY (alerts) • Waits • WAITCLASSMETRIC_HISTORY (alerts) • DBA_HIST_SYSTEM_EVENT (cumulative) • File IO • DBA_HIST_FILEMETRIC_HISTORY(alerts) • DBA_HIST_FILESTATXS (cumulative)

  47. EM Exposing Metrics

  48. In Resume: Statistics • Raw : v$sysstat • Now : v$sysmetric • 1 Hour : v$sysmetric_history (in memory) • 7 Days : dba_hist_sysmetric_summary (with AWR) There is also v$sessmetric

  49. Exposed in EM

  50. Session Metrics 15 Ses 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

More Related