Oracle SQL Tuning and Troubleshooting Techniques

Catalog Home Databases, Business Intelligence & Data Science Oracle Programming
Your Training Location:  

Oracle SQL Tuning and Troubleshooting Techniques

Let us know if you would like a date for this course added to our schedule

Give your technical staff that competitive edge with this local Oracle Workshop.The focus of this tuning course is to illustrate coding techniques that insure a consistent response time between instances and releases of the Oracle database. This course works closely with performance tuning of actual SQL statements as well as covering a variety of Oracle Server tuning topics. This course covers Oracle10, Oracle11, and Oracle12 SQL tuning topics.The course starts out with a complete overview of the Oracle architecture so students can get an understanding how their SQL and applications can take advantage of the computing environment. This course includes a discussion on the differences of the various Explain Plan steps such as Merge-Join and Nested-Loop, and when is it best to use each. The instructor goes into considerable detail, with SQL examples, on how the optimizers (both rule and cost) make their decisions. Students will work with a variety of SQL statements, reviewing explain plans and making changes to make these SQL statements perform better. Students will also experience how to monitor their application environment, their indexes, and see how to find problem SQL in their applications. This course takes a close look at indexes: how Oracle selects them, why they are sometimes not used, and how to tell if indexes are being used/not being used.The focus this course is tuning SQL via coding style. The instructor finds that tuning in this fashion maintains the performance of the SQL when migrating to other Oracle environments (upgrades to newer releases). Students will also experience how to monitor their application environment, their indexes, and see how to find problem SQL in their applications using Stats Pack and the V$ dictionary tables.This course covers hints, index utilization, as well as a variety of useful tools such as Oracle Trace, PL/SQL Profiler, StatsPack and Advanced Workload Repository. The course utilizes a variety of current tools. Students will have the opportunity to learn more about TOAD, SQL Developer, TextPAD, as well as the Oracle tools like SQL*Plus, TKProf, and Stats Pack.

A working knowledge of the SQL language is required. Knowledge of SQL Explain Plans is helpful but not necessary.

3 Days/Lecture & Lab

This course is perfect for developers who need to know more on how Oracle works and how to get their SQL to perform better. This course is also good for those who are light on SQL tuning or perhaps are new to the Oracle RDBMS.

  • Oracle RDBMS Architecture Overview
  • Understanding/Reading/Interpreting Explain Plans
  • Understanding the Cost-based Optimizer
  • Understanding Parallel Explain Plans
  • Working with Hints
  • Index Review/Tips & Techniques
  • SQL Tuning
  • A Close Look at Sub-Query Coding Techniques
  • Where Clause and Controlling Cardinality
  • A Close Look at CBO Statistics
  • Tuning Tool Review Statspack, Events, Tracing/Tkprof
  • Profiling and Tuning PL/SQL
  • Hint Review and Tips
  • Advanced SQL Troubleshooting Tips and Techniques
  • Review SGA Memory Structures
  • Library Cache Trace File Analysis
  • A Close Look at Other Useful Oracle Traces
  • Oracle Internals How Oracle Writes
  • Finding Problem SQL Using v$ Information
  • Using Automated Workload Repository
  • Review Database Replay and External Tables
  • Review the SQL Tuning Advisor

< >

Copyright © 2020 ProTech. All Rights Reserved.

Sign In Create Account


Social Media