Type
Virtual
Classroom ILT
Skill Level

Available dates
Learning Path
Virtual
Duration
1 Day

TYPE
Virtual
Classroom ILT
LEARNING PATH
SKILL LEVEL

DURATION
AVAILABLE DATES
Introduction:
This 3 day instructor led course assists database developers, DBAs, and SQL developers to identify and tune inefficient SQL statement. It covers investigative methods to reveal varying levels of detail about how the Oracle database executes the SQL statement. This allows the student to determine the root causes of the inefficient SQL statements. Students learn to interpret execution plans, and the different ways in which data can be accessed. They will learn how the optimizer chooses the path and how to influence the optimizer to ensure that the best method is used. This course covers Automatic SQL Tuning tools, and resources available in the Automatic Workload Repository, in addition to taking advantage of bind variables, trace files, and different types of indexes.
Audience profile:
Developers, Support Staff, Database Administrators and Data Warehouse Administrators
Pre-requisites:
Before attending this course, students must have:
- A thorough knowledge of the SQL language
- Working experience with SQL *Plus
- Working experience with SQL Developer or similar development environments Oracle requires and recommends that delegates have attended the following course prior to attending this course.
- Required Course: D64252GC10 Oracle Database Introduction to SQL
Course objectives:
After completing this course, students will be able to:
- Identify poorly performing SQL
- Trace an application through its different levels of the application architecture
- Understand how the Query Optimizer makes decisions about how to access data
- Define how optimizer statistics affect the performance of SQL
- List the possible methods of accessing data, including different join methods
- Modify a SQL statement to perform at its best
Lesson 1: Exploring the Oracle Database Architecture
This lesson provides an overview of the Oracle Database server architecture. You learn about physical and logical structures and about the various components. Lessons: |
|
|
|
|
|
|
Listing the different components of an Oracle Database server |
|
|
Lesson 2: Introduction to SQL Tuning
This lesson describes the nature of the tuning process and the different components of an Oracle database that may require tuning. Lessons: |
|
|
|
|
|
|
|
Lesson 3: Introduction to the Optimizer
This lesson describes in detail how SQL statements are processed within Oracle Database 11g. It also details the Oracle SQL Optimizer and ways of controlling its behaviour. Lessons: |
|
|
|
|
|
|
|
Lesson 4: Optimizer Operators
This lesson describes how Optimizer execution plans by examining the characteristics of specific SQL statements’ syntax. Lessons: |
|
|
|
|
|
|
|
|
|
Lesson 5: Interpreting Execution Plans
This lesson introduces the star and snowflake schema models used in a data warehouse environment. Participants will learn how to implement transformation and show query plan data. Lessons: |
|
|
|
|
|
|
|
Lesson 6: Case Study: Star Transformation
This lesson introduces the star and snowflake schema models used in a data warehouse environment. Participants will learn how to implement transformation and show query plan data. Lessons: |
|
|
|
|
|
|
|
Lesson 7: Optimizer Statistics
This lesson demonstrates how to define, gather, use and manipulate Oracle database optimizer statistics. Lessons: |
|
|
|
|
|
|
|
|
|
Lesson 8: Using Bind Variables
This lesson describes the advantages of using bind variables and advanced cursor sharing techniques. Lessons: |
|
|
|
|
|
|
|
|
|
Lesson 9: Using Optimizer Hints
This lesson explores the use of optimizer hints and how they could influence query performance. Lessons: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Lesson 10: Application Tracing
This lesson demonstrates the configuration and usage of tracing facilities and utilities to generate meaningful tracing reports. Lessons: |
|
|
|
|
|
|
|
|
|
Lesson 11: Automating SQL Tuning
This lesson explains the use of builtin advisors as well as automatic SQL tuning for application tuning. Lessons: |
|
|
|
|
|
|
|
|
|
Associated certifications and exam:
This course prepares students to write Exam (None). On successful completion of this course students will receive an Oracle University attendance certificate.

Oracle Overview
Torque IT considers authorised Oracle training to be an integral part of any Oracle Solaris, Oracle Enterprise Linux, Oracle Applications, Java development, or Oracle Database implementation. Oracle authorized training, and associated certification, ensures that you get the most from your technology investment and that you are able to operate above the technology curve.
Our instructor led training provides customers with a hands-on experience to match job role requirements and prepares delegates for Oracle Certification exams. From in-class demonstrations to hands-on-labs, Torque IT’s authorised Oracle instructor led training provides our customers with a comprehensive and dynamic learning experience.
Oracle certifications are universally recognized as demonstrating a high level of expertise and credibility for individuals and the organizations that employ them. Authorised Oracle training and certification is the industry standard for any solution that includes designing, selling, implementing, upgrading, developing and managing Solaris, Java, and Oracle Database solutions.
Torque IT is recognised by Oracle as an Oracle University Training Centre which means that we offer our clients internationally recognised and globally accredited Oracle training and certification. These achievements reflect our commitment to providing our customers with quality skills development, enablement, training, and certification solutions that demonstrate exceptional depth, breadth, and expertise.
Torque is recognised by Oracle, and the industry, as having met rigorous standards for educational competency, service, customer satisfaction and investment in technology that will prepare the next generation of IT industry professionals to exploit Oracle Solutions.