STA_O12CSPT | English Delivery Only: Oracle 12c SQL Performance Tuning | Training Course | Oracle. Skip to main Content

English Delivery Only: Oracle 12c SQL Performance Tuning

  • Course Code O12CSPT
  • Duration 2 days

Course Delivery

Company Event Price

Please call

Request Group Training Add to Cart

Jump to:

Course Delivery

This course is available in the following formats:

  • Company Event

    Event at company

  • Public Classroom

    Traditional Classroom Learning

  • Virtual Learning

    Learning that is virtual

Request this course in a different delivery format.

Download Course Details

Course Overview

Top
Oracle 12c SQL Performance Tuning Course Overview

This Oracle 12c SQL Performance Tuning course introduces the delegate to the main concepts of Oracle SQL performance tuning. It is designed to give delegates practical experience in analysing and tuning the performance of SQL.

Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.

Versions supported 12cR2,18c,19c.

Company Events

These events can be delivered exclusively for your company at our locations or yours, specifically for your delegates and your needs. The Company Events can be tailored or standard course deliveries.

Course Schedule

Top

Contact us for dates

Request Group Training Dates in Other Countries

Target Audience

Top
Who will the Course Benefit?

The Oracle 12c SQL Performance Tuning course is designed for SQL programmers,application developers,designers and technical support professionals who are required to tune the performance of an Oracle application running under an Oracle Database.

Show me more

Course Objectives

Top
Course Objectives

To provide the skills needed to monitor and tune an Oracle database application.

Show me more

Course Content

Top
Oracle 12c SQL Performance Tuning Training Course Course Contents - DAY 1

Course Introduction

  • Administration and Course Materials
  • Course Structure and Agenda
  • Delegate and Trainer Introductions

Session 1: INTRODUCTION TO ORACLE PERFORMANCE TUNING

  • Overview of Oracle Database Tuning
  • Application Developer Tuning Responsibilities
  • Oracle DBA Tuning Responsibilities
  • Oracle Tuning Process
  • Plan a Routine Monitoring Regime
  • Setting Suitable Goals
  • Syntax Considerations

Session 2: TOOLS FOR EVALUATING SQL STATEMENTS

  • Overview of SQL Statement Tuning
  • Tools to Assist in SQL Tuning
  • Use Explain Plan,Autotrace and SQL Trace to Examine the Execution of a SQL Statement
  • Interpreting a SQL Trace

Session 3: THE SQL OPTIMIZER

  • The SQL Optimizer
  • Statement Transformation
  • The Optimizer_Mode Initialization Parameter
  • Cost Based Optimizer
  • Managing Statistics with DBMS_STATS
  • Automatic Statistics Gathering
  • Dynamic Statistics
  • Adaptive Optimization
  • Transferring Statistics between Databases

Session 4: SORTS

  • How Oracle Processes Sorts
  • Temporary Disk Space Assignment
  • SQL Operations that Use Sorts

Session 5: INDEXES

  • Index Overview
  • Selecting Suitable Columns for an Index
  • B*Tree Indexes
  • Rebuild an Index
  • Create Multiple Indexes on the Same Column
  • Composite Indexes
  • Descending Indexes
  • Access Paths with Indexes
  • Index Scans
  • Conditions That Stop Indexes Being Used
  • Parameters that Affect Optimizer Index Choice
Oracle 12c SQL Performance Tuning Training Course Course Contents - DAY 2

Session 6: ADVANCED INDEXES

  • Bitmap Indexes
  • Key Compressed Indexes
  • Index Organized Tables
  • Function Based Indexes
  • Invisible Indexes
  • Table Partitioning
  • Serial Direct Path Reads

Session 7: JOIN OPERATIONS

  • Understand Access Paths
  • Joining Tables
  • Nested Loops Join
  • Merge Join
  • Cluster Join
  • Hash Join
  • Anti Join and Semi Join
  • Outer Joins
  • Star Join
  • Improve Optimization with Different Access Paths

Session 8: SQL TUNING ADVISOR USING SQL DEVELOPER

  • Overview of the DBMS_SQLTUNE Package
  • Using the SQL Tuning Advisor with SQL Developer

Session 9: SEQUENCES AND VIEWS

  • Sequence Caching
  • Views
  • View Merging
  • Inline Views
  • The WITH Clause

Session 10: USING HINTS

  • Using Hints to Influence Execution Plan
  • Optimization Mode and Goals
  • Access Methods
  • Query Transformations
  • Join Orders
  • Join Operations
  • Hint Examples

Session 11: MISCELLANEOUS

  • Tips for Avoiding Problematic Queries
  • SQL Performance Settings Options
  • Array Size
  • The Shared Pool
  • Intelligent Cursor Sharing
  • Bind Variable Usage
  • Virtual Columns
  • Result Caching
  • Approximate Query Processing
  • Some PL/SQL Performance Issues
Show me more

Course Prerequisites

Top
Requirements

A working knowledge of SQL is required. This can be obtained by attendance on the pre-requisite Oracle SQL course.

Show me more

Follow on Courses

Top
Further Learning
  • Oracle PL/SQL
  • Oracle Program with PL/SQL
  • Oracle Advanced PL/SQL
Show me more

We Care About Your Privacy

We are using cookies to give you the best experience on our site. Cookies are files stored in your browser and are used by most websites to help personalise your web experience. By continuing to use our website without changing the settings, you are agreeing to our use of cookies.

About your Privacy

We process your data for purposes such as delivering content or advertisements and measuring the delivery of such content or advertisements to extract insights about our website. We may share this information with our partners. Cookies set by Global Knowledge are labelled “first party”; you may exercise your preferences in relation to first party cookies by toggling the switch for each first party cookie category below. The remaining cookies are third party cookies; you may exercise your preferences in relation to each purpose by toggling the relevant switch below or by vendor by clicking “List of IAB Vendors.” These choices will be signaled globally to other websites participating in the Transparency and Consent Framework.
Privacy Statement

Necessary Cookies

These are cookies that are required for the Global Knowledge website to function and cannot be switched off in our systems. They include, for example, cookies that enable you to use a shopping cart or log into the booking area of our website.

Analytics Cookies

Analytics cookies are an optional but important part of our website's functionality. They help us to understand how you interact with our site by collecting and reporting information anonymously. The data collected by analytics cookies is used to improve the website's performance and enhance the user experience. It is important to note that these cookies do not collect any personal information that can be used to identify you.

Preferences Cookies

These are optional and you can turn them off and on. Please only use 2 to 3 lines of text here and if needed link to a page with more cookie info in Intro.

Performance Cookies

These cookies allow us to recognise and count the number of visitors to our website, traffic sources and to see how website visitors move around the website when they are using it. This helps us to improve the way the website works and improve your website experience. All information these cookies collect is aggregated and therefore anonymous.

Cookie Control toggle icon