• Photos
  • Schedule
  • Talks
  • Speakers
  • Contacts
  • F.A.Q.
  • Profile
  • En Ru

    Alexander Khalukhin

    Alexander Khalukhin Deutsche Bank — Russian Technology Center

    5 July

    Tutorial

    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.

    Commercial databases


    11

    Back to the list of talks

    Buy tutorial:

    Price:
    Enter your e-mail and we will send you payment details soon.