Oracle Performance Tuning

Description:

This course aims to provide an advanced understanding of the core principles and topics of Oracle Performance Tuning.

It will enable the students to acquire specialized knowledge in selected optimization topics – Architecture, Indexes and Execution Plans, Tracing, Generating Reports, Partitioning, Statistics and Real Life Scenarios.

Get a lecture on:

Oracle Performance Tuning

Developers, DBA, QA personnel, project managers, team leaders

Experience with SQL language

Oracle architecture – Overview

  • Database and instance
  • The Buffer cache and the Shared pool
  • Logical storage structure – Tablespaces and segments.

Indexes – introduction

  • The B*Tree Index structure
  • Indexes vs. Full Scans in table data access
  • Unique and non-unique indexes
  • Single column and composite indexes

SQL Tuning and execution plans

  • Identifying the high load SQL statements
  • GUI performance tools (OEM), AWR report, Oracle Tracing
  • Tools for retrieving execution plans and execution statistics
  • Autotrace, DBMS_XPLAN, EXPLAIN PLAN FOR, Developers Graphical tools
  • The Oracle Optimizer and basic concepts of execution plans
  • What is “Cost”, “Cardinality”, “Selectivity” and so on.

Affecting execution plans

  • Hints, Optimizer statistics, Parameters, re-write SQL

Basic access paths

  • Full scans, Indexes and Row-id access paths
  • Common index access paths Unique scan, Range scan, Full scan, Fast full scan
  • Common join algorithms Nested loops, Sort-Merge, Hash-Join

Execution plan real time statistics

  • SQL Monitoring (11g), Gather plan statistics (10g/11g) (View Actual values of the execution compared to the optimizer estimated ones)
  • Gets (logical IO) vs. Reads (physical IO)

Re-writing the SQL to perform less work

  • Tips, tricks and real-life examples.

Application tracing

  • Using SQL trace and TKPROF
  • Using DBMS_APPLICATION_INFO, DBMS_MONITOR and TRCSESS

Generating the Oracle performance reports via developer tools:

  • AWR reports (General / SQL level)
  • ASH reports (General / SQL level)
  • ADDM reports

Advanced Indexes issues – tips, tricks and real-life examples

  • Using indexes efficiently – tips, tricks and real-life examples
  • Various Index types and index considerations
  • Indexes and expressions, Composite indexes, Invisible
  • Indexes Unusable indexes, Function based indexes, Indexes and
  • NULL values, indexes and DMLs.

Partitioned tables

  • Partitioned tables – Overview
  • Global and Local indexes – Pros, cons and considerations
  • Partition pruning – Utilizing and monitoring

Cleaning old data

  • The high-water mark and full scans.
  • Delete vs. Truncate vs. Drop
  • Using partitions to purge old data efficiently

Optimizer Statistics

  • Gathering statistics at different levels (Table, Schema, Database)
  • Histograms and column statistics
  • Oracle automatic mechanism for statistics gathering
  • Extended statistics
  • Statistics and Plan stability – the trade off
  • When to lock statistics
  • How to restore statistics

SQL Tuning – Real world examples, tips and tricks

  • These will be scattered throughout the course
ערן קורןערן הינו יועץ מוביל ובכיר בנאיה טכנולוגיות בתחום מסדי נתונים ו-Big Data. במסגרת תפקידו ערן מתמחה בניהול מסדי נתונים, בניית ארכיטקטורה, וכן בפלטפורמות NoSQL מובילות בעולם ה- Big Data.
  • על פי דרישה מועד פתיחה
  • 9:00-16:30daysימים ושעות
  • 24academic hours שעות אקדמיות
  • מתקדםcourse levelרמת הקורס
  • עברית/Englishlanguageשפת הדרכה
  • לבדיקת התאמה לקורס
  • [current_url]

    השאירו פרטים ונחזור אליכם בהקדם!