1 / 54

Anomaly Detection in Database Workload

Anomaly Detection in Database Workload. Jaromir D.B. Nemec. Who am I. www.db-nemc.com. 15 years Oracle experience 10g Oracle Certified Associate both OLTP and DWH Oracle conference speaker Oracle Magazine Peer 09/2006 Oracle Beta-Tester NoSQL, Big Data Machine Learning. Overview.

alafave
Download Presentation

Anomaly Detection in Database Workload

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. Anomaly Detection inDatabase Workload Jaromir D.B. Nemec Autor / Thema der Präsentation

  2. Who am I www.db-nemc.com 15 years Oracle experience 10g Oracle Certified Associate both OLTP and DWH Oracle conference speaker Oracle Magazine Peer 09/2006 Oracle Beta-Tester NoSQL, Big Data Machine Learning

  3. Overview

  4. Overview

  5. Overview

  6. Overview

  7. Overview

  8. Motivation What is „anomaly detection“?

  9. Motivation What is „anomaly detection“? Source: Coursera.org Prof. Ng, Machine Learning Course

  10. Motivation What is „anomaly detection“? Source: Coursera.org Prof. Ng, Machine Learning Course

  11. Database Workload Temperature? Vibrations?

  12. Database Workload Temperature? Vibrations?

  13. DB Time in Active Session History - ASH Measures Database Time Active Session Count

  14. DB Time in v$active_session_history SESSION_STATE ON CPU WAITING

  15. Normal Distribution AVG STDEV

  16. Normal Distribution +/- 1 STDDEV 68%

  17. Normal Distribution +/- 2 STDDEV 95%

  18. Normal Distribution +/- 3 STDDEV 99.7%

  19. DB Time in v$active_session_history

  20. DB Time in v$active_session_history

  21. Model Based Monitoring 1) Visualize data Model 3) Apply model to data 2) Create model

  22. Data Visualization

  23. Data Visualization DB Time in minutes per minute

  24. Data Visualization Frans Francken Der Mensch zwischen Tugenden und Laster ...

  25. Data Visualization Frans Francken Der Mensch zwischen Tugenden und Laster ...

  26. Data Visualization

  27. DB Time Overview ylim = c(-20,100)

  28. DB Time Overview

  29. DB Time Overview

  30. DB Time Overview

  31. DB Time Distribution of Hour Slices Histogram of a Workday 10 hour

  32. DB Time Distribution of Hour Slices Log Transformation c = -0.185 c = 1 c = 0 p = 0.129 p = 0.758 p = 0.002

  33. Kolmogorov-Smirnov Test Source: wikipedia ks.test(x, "pnorm", mean, sd) optimize(f, c(-20, 20), maximum = T )

  34. DB Time Distribution of Hour Slices

  35. DB Time Distribution of Hour Slices Inverse transformation

  36. DB Time Distribution per Workday

  37. DB Time Distribution per Workday

  38. Additional Dimensions User (Class) Machine (Class) Wait Event Access Type

  39. Additional Dimensions - Access Type SELECT FULL TABLE SCAN INDEX ACCESS JOIN DML REMOTE BLOCKED OTHER

  40. Application

  41. Influx

  42. Influx

  43. Grafana Demo

  44. Future Directions • Alerting • Trend & Predictions • Support for Diagnostics

  45. Future Directions Support for Diagnostics calculated with Twitter R package BreakoutDetection

  46. Future Directions Support for Diagnostics calculated with Twitter R package BreakoutDetection

  47. References • Detecting Unusual Events in ASH, UKOUG 2011 • https://www.slideshare.net/jberesni/ash-outliers-ukoug2011 • Thoughts on TM_DELTA_TIME • https://orastory.wordpress.com/2012/07/06/thoughts-on-tm_delta_time/ • Optimize Log Transformation on Normal Distribution • http://www.db-nemec.com/nd/NormDistTrans.html

  48. Q & A ?

  49. Backup – Grafana Demo

  50. Grafana Demo

More Related