Oracle Database performance diagnostics: crash course

The following topics are covered in the workshop:
- overview of performance issues, where to look at the outside and the inside of database. How to overcome the most popular slowdown reasons;
- session concept in Oracle Database, session states, wait events, event classes;
- built-in diagnostic tools — dynamic performance views (v$ views), real world examples of what data can you get from them;
- Active Session History (ASH) — load capturing mechanism and its implications;
- Automatic Workload Repository (AWR) as an extension to ASH, AWR performance reports;
- Integrated automatic advisors — ADDM and SQL Advisor;
- Real-time SQL execution monitoring.

The following topics are covered in the presentation:
- overview of performance issues, where to look at the outside and the inside of database. How to overcome the most popular slowdown reasons;
- session concept in Oracle Database, session states, wait events, event classes;
- built-in diagnostic tools — dynamic performance views (v$ views), real world examples of what data can you get from them;
- Active Session History (ASH) — load capturing mechanism and its implications;
- Automatic Workload Repository (AWR) as an extension to ASH, AWR performance reports;
- Integrated automatic advisors — ADDM and SQL Advisor;
- Real-time SQL execution monitoring.

Detailed plan:
1. Introduction – overview of basic concepts:
- response time and factors that matter;
- symptoms based analysis – most typical bottlenecks;
- v$ views – a comprehensive multipurpose tool;
- oracle session concept.

2. Practice: using v$sess_time_model to understand time spent by application in different states. Diagnosing underperforming application, extensive SQL parsing issues, time taken by SQL execution.

3. Practice: collecting and understanding cumulative session and system statistics – v$sessstat / v$sysstat. Understanding various statistics used for identifying session key metrics.

4. Theory: oracle session states explained, wait event and wait classes.

5. Practice: v$session_event. Investigating what a session was waiting for in database. Cases: badly configured network, over-commiting, data racing issues.

6. Practice: v$session – all database sessions at a glance. Understanding the importance of instrumenting your code properly. Sneaking into system sessions.

7. Theory: past analysis v$ views limitation. Active session sampling mechanism explained. Automatic workload repository. StatPack – a free alternative.

8. Practice: investigating historical session activity. Generating and studying an AWR report.

9. Theory: self managing database features – ADDM, advisory framework.

10. Practice: running ADDM report and implementing its findings. Enchaining SQL with SQL Tuning Adviser.

11. Practice: using v$sql_monitor, v$session_longops, report_sql_monitor to understand the progress of SQL execution.