Oracle Database: SQL Tuning for Developers

Datum
21/09/2020 - 23/09/2020
9:00 - 17:00
Kalender-Import: iCal

Veranstaltungsort
ETC Trainingcenter
Modecenterstrasse 22, 1030 - Wien




In this course, students learn about Oracle SQL tuning and how to apply tuning techniques to their SQL code. This course assists database developers, DBAs, and SQL developers to identify and tune inefficient SQL. It covers investigative methods that reveal varying levels of detail about how the Oracle database executes a SQL statement. Students learn the different ways in which data can be accessed, and how to determine if those are the most efficient ways is a particular database

Vorkenntnisse:
Required Prerequisite:
Oracle Database: Introduction to SQL Ed 2
Oracle Database: SQL Workshop I Ed 2

Suggested Prerequisite:
Knowledge of SQL and PL/SQL
Familiarity with database architecture
 

Kurs Inhalte:
Introduction

  • Workshops, Demo Scripts, and Code Example Scripts
  • SQL Environments Available in the Course
  • Course Objectives, Course Agenda and Appendixes Used in this Course
  • Sample Schemas Used in the Course
  • Class Account Information
  • Appendices in the Course
  • Audience and Prerequisites

Introduction to SQL Tuning

  • SQLTXPLAIN (SQLT) Diagnostic Tool
  • Development Environments: Overview
  • SQL Tuning Session
  • SQL Tuning Strategies

Using Application Tracing Tools

  • Using the SQL Trace Facility: Overview
  • The trcsess Utility
  • Formatting SQL Trace Files: Overview
  • Available Tracing Tools: Overview
  • Steps Needed Before Tracing

Understanding Basic Tuning Techniques

  • Developing Efficient SQL statement
  • Index Usage
  • Data Type Mismatch
  • Table Design
  • Scripts Used in This Lesson
  • Transformed Index
  • Tune the ORDER BY Clause
  • NULL usage

Optimizer Fundamentals

  • Why Do You Need an Optimizer?
  • Query Transformer
  • SQL Statement Processing
  • Optimizer Features and Oracle Database Releases
  • Cost-Based Optimizer
  • SQL Statement Representation
  • Components of the Optimizer
  • Adaptive Query Optimization

Generating and Displaying Execution Plans

  • DBML_SQL_MONITOR
  • V$SQL_PLAN View
  • AUTOTRACE
  • The EXPLAIN PLAN Command
  • Plan Table
  • Execution Plan?
  • SQL Monitoring
  • Automatic Workload Repository

Interpreting Execution Plans and Enhancements

  • Optimizer: Table and Index Access Paths
  • Full Table Scan
  • Common Observations
  • Row Source Operations
  • Indexes
  • Adaptive Optimizations
  • Main Structures and Access Paths
  • Interpreting a Serial Execution Plan

Optimizer Join Operations

  • Join Methods
  • Join Types

Other Optimizer Operators

  • SQL operators
  • Result Cache operators
  • Other N-Array Operations

Introduction to Optimizer Statistics Concepts

  • Gather and Manage Optimizer Statistics: Overview
  • Optimizer Statistics
  • Types of Optimizer Statistics

Using Bind Variables

  • Cursor Sharing and Bind Variables
  • Cursor Sharing and Different Literal Values

SQL Plan Management

  • Maintaining SQL Performance
  • SQL Plan Management

Anmeldelink: https://www.etc.at/seminare/D99667

Preis: 2133

Kategorien


Mehr Artikel