Anomaly Detection in Database Workload Jaromir D B
Anomaly Detection in Database Workload Jaromir D. B. Nemec Autor / Thema der Präsentation 30. 11. 2020 1
Who am I 15 years Oracle experience 10 g Oracle Certified Associate both OLTP and DWH Oracle conference speaker Oracle Magazine Peer 09/2006 Oracle Beta-Tester No. SQL, Big Data Machine Learning www. db-nemc. com
Overview
Overview
Overview
Overview
Overview
Motivation What is „anomaly detection“?
Motivation What is „anomaly detection“? Source: Coursera. org Prof. Ng, Machine Learning Course
Motivation What is „anomaly detection“? Source: Coursera. org Prof. Ng, Machine Learning Course
Database Workload Temperature? Vibrations?
Database Workload Temperature? Vibrations?
DB Time in Active Session History - ASH Measures Database Time Active Session Count
DB Time in v$active_session_history SESSION_STATE ON CPU WAITING
Normal Distribution AVG STDEV
Normal Distribution +/- 1 STDDEV 68%
Normal Distribution +/- 2 STDDEV 95%
Normal Distribution +/- 3 STDDEV 99. 7%
DB Time in v$active_session_history
DB Time in v$active_session_history
Model Based Monitoring 1) Visualize data Model 3) Apply model to data 2) Create model
Data Visualization
Data Visualization DB Time in minutes per minute
Data Visualization Frans Francken Der Mensch zwischen Tugenden und Laster. . .
Data Visualization Frans Francken Der Mensch zwischen Tugenden und Laster. . .
Data Visualization
DB Time Overview ylim = c(-20, 100)
DB Time Overview
DB Time Overview
DB Time Overview
DB Time Distribution of Hour Slices Histogram of a Workday 10 hour
DB Time Distribution of Hour Slices Log Transformation c = -0. 185 c=0 p = 0. 129 p = 0. 758 c=1 p = 0. 002
Kolmogorov-Smirnov Test Source: wikipedia ks. test(x, "pnorm", mean, sd) optimize(f, c(-20, 20), maximum = T )
DB Time Distribution of Hour Slices
DB Time Distribution of Hour Slices Inverse transformation
DB Time Distribution per Workday
DB Time Distribution per Workday
Additional Dimensions User (Class) Machine (Class) Wait Event Access Type
Additional Dimensions - Access Type SELECT FULL TABLE SCAN INDEX ACCESS JOIN DML REMOTE BLOCKED OTHER
Application
Influx
Influx
Grafana Demo
Future Directions Alerting Trend & Predictions Support for Diagnostics
Future Directions Support for Diagnostics calculated with Twitter R package Breakout. Detection
Future Directions Support for Diagnostics calculated with Twitter R package Breakout. Detection
References Detecting Unusual Events in ASH, UKOUG 2011 https: //www. slideshare. net/jberesni/ash-outliers-ukoug 2011 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/Norm. Dist. Trans. html
Q&A ?
Backup – Grafana Demo
Grafana Demo
Grafana Demo
Grafana Demo
Grafana Demo
Grafana Demo
- Slides: 54