sss

Home
/
Training
/
Courses
/
Oracle Courses
/
ORACLE 19c SQL Performance Tuning

Oracle Courses

Enliten IT partner with StayAhead Training to deliver an extensive range of 19c Oracle courses targeted at ensuring delegates, from programmers to administrators, can perform their job roles successfully. We’ll teach you how to administer, configure, secure, backup, manage storage and maintain databases. Oracle SQL reporting courses are also available.

Other, not so popular, Oracle training courses (e.g. Oracle e-Business Suite R12) are also available so if you don’t find what you are looking for in the list below then please contact us.

Note: All Oracle 19c workshops can be modified or tailored for delivery, at your own offices, anywhere within the UK.

ORACLE 19c SQL Performance Tuning

2 days

This Oracle 19c 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.

Delegates will practice:

Selecting an Appropriate SQL Tuning Approach
Tuning Database Applications for Optimal Performance
Managing Statistics
Creating and Using Indexes
Structuring SQL Statements for Performance
Examining and Interpreting the Execution Plan of a SQL Statement using EXPLAIN PLAN
Examining the Efficiency of SQL Statements using SQL Trace and Autotrace
Using the SQL Tuning Advisor
Identifying SQL Statements that Perform Badly
Using Hints to Influence Execution Plan
Identifying Unused Indexes

Download PDF

What will you learn?

On completion of this ORACLE 19c SQL Performance Tuning course delegates will have will have the knowledge to monitor and tune an Oracle database application.

Audience?

SQL programmers, application developers, designers and technical support professionals.

Course Contents

INTRODUCTION TO PERFORMANCE TUNING

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

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

THE SQL OPTIMIZER

The SQL Optimizer
Statement Transformation
The Optimizer_Mode Initialization Parameter
Cost Based Optimizer
Managing Statistics with DBMS_STATS
Automatic Statistics Gathering
Optimizer Adaptive Parameters
Dynamic Statistics
Online Statistics Gathering for Bulk Loads

SORTS

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

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

ADVANCED INDEXES

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

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

SQL TUNING ADVISOR USING SQL DEVELOPER

Overview of the DBMS_SQL_TUNE Package
Using the SQL Tuning Advisor with SQL Developer

SEQUENCES AND VIEWS

Sequence Caching
Views
View Merging
Inline Views

USING HINTS

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

MISCELLANEOUS

Tips for Avoiding Problematic Queries
Oracle 12.2 SQL*Plus Performance Setting Options
Array Size
The Shared Pool
Intelligent Cursor Sharing
Virtual Columns
The WITH Clause
Bind Variable Usage
Result Caching
Approximate Query Processing
Reduce Cursor Invalidations for DDLs
Some PL/SQL Performance Issues

Course Materials

Enliten IT will provide each delegate with a workbook and other useful reference materials where applicable.

“I found Gordon very engaging and knowledgeable. He was helpful and happy to ask questions. Rachael, New Store Europe”I found Gordon very engaging and knowledgeable.

Additional Information

Enliten IT reserves the right to modify course content and materials as required in the interests of continuous course improvement, at any time, without notification. Note: This ORACLE 19c SQL Performance Tuning course is run on a public schedule or can be taken as a private in-house company course UK wide. For pricing and availability just pick up the phone and call us on 0845 1085481.

Contact us to discuss your training needs...