IT Technical & End User
Course Listings

For further information, or to make a telephone booking, or to receive a competitive quotation for an in-house course, you can call Pentland Training now on 0800 328 2766.

All of our training courses can be delivered on your own site, at Pentland Training facilities, or at an off-site venue.

Hot Topics

3-day ITIL Foundation

5-day PRINCE2™ Practitioner

4-day Business Analysis

3-day Project Management Fundamentals

Course Title:
Performance Tuning

Duration: 2 days

Course Overview
This course is aimed at novice DBAs and senior developers. This course gives a comprehensive guide to tuning an Oracle database. Initially the course focuses on tuning SQL queries. It identifies the clauses that need to be avoided, if possible, and continues by looking at the different optimisers and how you can use hints in your queries. EXPLAIN PLAN, Trace files and TKPROF are also covered. In the second part of the course database tuning is covered. Delegates will be educated in techniques that will enable them to identify how their own system is performing. They will learn what to look out for and the steps that should be taken to improve the efficiency of an under-performing database.

Target Audience


DBAs with little or no experience of tuning and systems developers who need to know about performance tuning techniques.

Course Objectives


By the end of the course attendees will be able to:

• Understand the concepts behind the tuning of ORACLE applications
• Understand how to write more efficient SQL
• Determine when and how to use indexes
• List the optimisation steps performed by ORACLE
• Find problems with SQL, using the tools SQL Trace, TKPROF and Explain Plan
• Tune SQL code to achieve greater performance
• Tune the INIT.ORA
• Identify, avoid and fix fragmentation
• Use PL/SQL to enhance performance
• Carry out a full system review of their system
• List applications that are available that can help with performance tuning

Prerequisites


Attendees should have experience of Oracle databases and preferably experience of using SQL. Ideally delegates will have attended the Fundamentals of SQL and SQL*Plus course

Course Content


Introduction to Tuning
• The Incentive for Tuning
• Tools Available
• Rules Based Optimiser
• Cost Based Optimiser

Tuning Queries
• Joins
• Joins in General
• Un-indexed, Indexed and Outer Joins
• Writing Good SQL Statements
• Data Conversion
• LIKE, NOT, OR IN Operators
• ORDER BY and GROUP BY
• Query Path Ranking
• Sub Queries
• Indexes and MAX/MIN

Explain Plan
• Creating the Plan Table
• Explain Plan Syntax

SQL Trace
• The Trace Facility
• Enabling SQL Trace
• Analysing Trace output

TKPROF
• Sort Options
• SQL Trace Statistics

The INIT.ORA
• The Crucial Parameters
• Increasing Performance by Tuning the DB_BLOCK_BUFFERS
• Tuning the Memory Structures in Your Database

Disk I/O and Fragmentation
• Avoiding and Fixing Fragmentation
• Avoiding Disk Contention by using Partitions
• Increasing Chances of Recovery

Using PL/SQL to Enhance Performance
• Reusing Memory with PL/SQL
• Shared Pool and Pinning
• Identifying Objects that need Pinning
• Finding Invalid Objects
• Finding Disabled Triggers
• Tuning and Testing PL/SQL

Performing a System Review
• Cache Ratios
• Sorting in Memory
• Identifying Memory Abusers
• Identifying Disk-Read Abusers
• Tablespace Locations
• Fragmentation Issues
• Chaining
• Pinning and Caching Objects

Available Applications
• UTLESTAT
• UTLBSTAT
• STATSPACK

Enter your email address to register for our newsletter: