to do n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
To Do PowerPoint Presentation
Download Presentation
To Do

Loading in 2 Seconds...

play fullscreen
1 / 119

To Do - PowerPoint PPT Presentation


  • 118 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - 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