EXADATA HEALTH AND RESOURCE USAGE MONITORING

2. EXADATA HEALTH AND RESOURCE USAGE MONITORING Including the Benefits of Administration Tuning Methods Kellyn Pot’Vin Consulting Member of Enterprise Manager Technical Team Strategic Customer Program Mike Chafin Principal Member of Technical Staff Maximum Availability Architecture Oracle Open World 2014

4. Program Agenda Oracle Tuning Methodologies Preparing for Tuning Working through Tuning Exercise Digging Deeper Miscellaneous Tips 1 2 3 4 5

5. Program Agenda with Highlight Oracle Tuning Methodologies Preparing for Tuning Working through Tuning Exercise Digging Deeper Miscellaneous Tips 1 2 3 4 5

6. Evolution of Methodologies Documented in the Oracle Documentation Set: Database 2 Day + Performance Tuning Guide Oracle Database Performance Tuning Guide Exadata Performance Tuning White Paper Each are similar, in that they offer a structural approach to problem solving using the latest tools and technologies.

7. Tuning Level Legend What level is the Issue? Hardware Database Schema/Design SQL

8. Important Tuning Concepts Tuning a database by “DBTime” DBTime is Oracle’s term for total time used by active CPU, IO and other foreground processes from the total time elapsed to calculate wait events that are performed within the database. “Tune for Time or You’re Wasting Time…”

9. Program Agenda with Highlight Oracle Tuning Methodologies Preparing for Tuning Working through Tuning Exercise Digging Deeper Miscellaneous Tips 1 2 3 4 5

10. Tuning Exercise Steps Prepare Compare Review (research, investigate) Rule out, Test Implement/Resolve

11. Prepare • Collect hardware and software healthchecks • exachk, cluvfy, emdiag • Healthchecks • AWR Baseline • Verify and/or set AWR retention period • Copy configuration files • Automate collection and base-lining (EM jobs) • Create AWR, ASH and ADDM reports for research.

12. Stopping the Insanity Flowchart

13. The Value of Quick Check Lists Old School, but VALUABLE.

14. Program Agenda with Highlight Oracle Tuning Methodologies Preparing for Tuning Working through Tuning Exercise Digging Deeper Miscellaneous Tips 1 2 3 4 5

15. Review Errors in the system? EM Incident Manager Alert Log(s) Grid Infrastructure ASM Database OS logs / system logs

16. AND Rule Out- Hardware Compute Node CPU I/O Bound / at known limits Exadata – Flash cache Mixed Workloads creating contention? ALL Components up!

17. Exachk- See Oracle Support Document 1070954.1: “Oracle Exadata Database Machine Exachkor HealthCheck”

18. Exachk and Healthcheck Utility Checks… Incorrect configurations Parameter Issues Recommendations, steps to correct, along with current value. See Oracle Support Document 1070954.1: “Oracle Exadata Database Machine Exachkor HealthCheck” and Exadatabest practices in Oracle Support Document 757552.1: “Oracle Exadata Best Practices”

19. Automate Exachk via Enterprise Manager Automating will populate healthchecks to UI! Need to insert screenshot

20. Storage Grid Overview Storage Cell monitoring and administration support Cell Home page and performance pages Actions supported: Start/stop Cell, verify connectivity, setup SSH Automatic discovery of Cells Management by Cell Group All cells used by a database automatically placed in a group Cell Group level administration operations (batch job monitoring)

21. More Storage Grid Overview

22. Max Cell Disk Limits

23. Hardware- Cell Server View

24. Hardware- DB Node View

25. A Review of Hardware/Network troubleshooting steps HA can mask problems, reduced capacity even while meeting service levels Hardware Utilization Issues CPU is not what it may seem I/O Subsystem (Cell screens) Network Congestion (IB screens)

26. Comparison Tools Incredible Power in Knowing What’s Changed.

27. Compare If there is a problem what has changed? And who might know? Considerations Patch levels (everywhere!) Schema Tunable OS parameters Resource Management Plans Code Changes ADDM Comparison Report

28. Compare Configurations- Exadata Level

29. Compare Configuration- Database Level EM Job to compare one ‘reference’ database against one or more other databases Job can be scheduled on a repetitive basis, or run ad-hoc

30. Compare Configurations- Schema Level Capture Schema baselines Compare schema’s With a baseline Between different databases Synchronize schema’s

31. Compare ADDM AWR Snapshot Period 1 SQL Commonality Regressed SQL AWR Snapshot Period 2 Analysis Report I/O Bound Undersized SGA Compare Period ADDM • Full ADDM analysis across two AWR snapshot periods • Detects causes, measure effects, then correlates them • Causes : Workload changes, Configuration changes • Effects : Regressed SQL, Reach resource limits (CPU, I/O, memory, interconnect) • Makes actionable recommendations along with quantified impact

32. IO Issues What type of IO Issues are addressed with Exadata features? How can temp tablespace usage be an impact to performance in an Exadata? Identify IO Issues- ASH Analytics, ADDM Comparison Report Isolate IO Issues- Search SQL, ASH/ADDM Reports, Address IO Issues- IO Resource Manager, SQL Tuning Advisor,

33. Exadata and SQL Monitor Offload Percent Efficiency Displayed in Report and UI. Automatically monitors long running SQL Enabled out-of-the-box with no performance overhead Monitors each SQL and PL/SQL execution Shows global PL/SQL and SQL level statistics Drill-down to slow SQL for diagnosing unexpected PL/SQL behavior Guides tuning efforts

34. IO Bound Dashboard in ADDM Comparison • Base vs. comparison period • Temp reads/writes specified • Single block read latency

35. Setting Thresholds Thresholds with incident rule sets to notify when growth in resource occurs and impacts the environment.

36. IO Resource Manager IO Resource Manager, (IORM) offers ability to maintain consistent performance levels across an engineered system. Allocates and limits IO per settings configured Simple configuration access via Enterprise Manager

37. IO Resource Management

38. Enterprise Manager Exadata Resource Management Exadata plugin provides access to manage IO Resources for db and across cell nodes. What IO resources on Exadata are shared vs. on a non-Exadata RAC environment?

39. Monitoring IORM Allows for throttling by service and IO type. Ensures that resources that require the IO, get the IO.

40. Monitoring Disk Utilization Ability to monitor all disk usage. Broken up by type

41. Why Setting Limits are Important No Hard Limits equate to IO being freely redistributed Setting limits ensures that resources are available for critical databases Less high priority databases aren’t able to allocate more than necessary

42. Program Agenda with Highlight Oracle Tuning Methodologies Preparing for Tuning Working through Tuning Exercise Digging Deeper Miscellaneous Tips 1 2 3 4 5

43. AWR Baselines Collection of snapshots used for performance comparisons. Baselines are retained within the AWR even after the retention time for the data has been reached. Exadata should have a moving and a static baseline in place to capture different workloads.

44. AWR Baselines

45. Details of Baseline Creation in EM Highlight a snapshot range to use for your static baseline or use a time range to base it from.

46. Completing Baseline Creation in EM Schedule the job to run in a non-impacting schedule and choose not to purge.

47. SQL Issues SQL Tuning – what kind of problem? Reactive Transient Long Running SQL Proactive Persistent Comparative

48. Proactive SQL Tuning Choices SQL Performance Analyzer SQL Access advisor ASH and ADDM to prioritize further work Group operations (DB12) Create an SLA / baseline Use SQL Plan Baselines (SPM) Validate with SQL Performance Analyzer

49. Reactive SQL Tuning Choices After all changes, including any environments are ruled out… Start with AWR, ADDM and ASH Identify outliers / worst performing (Pareto) Review top wait events SQL Tuning Advisor If resource constrained consider resource limiting strategies

50. Database Performance Page in EM12c