8226 Reviews star_rate star_rate star_rate star_rate star_half

Oracle Database 12c R2: SQL Tuning for Developers Ed 2

In this application development-centric course, students learn about Oracle SQL statement tuning and how to write well-tuned SQL statements appropriate for the Oracle database. Students learn to...

By Request

$3,600 USD

Course Code D99669GC20

Duration 3 days

Available Formats Classroom

Enter your Email to Download Full Course Details

In this application development-centric course, students learn about Oracle SQL statement tuning and how to write well-tuned SQL statements appropriate for the Oracle database. Students learn to interpret execution plans and the different ways in which data can be accessed. Students are shown how to decipher, decide and then apply tuning to their SQL code. Various tuning techniques are demonstrated. For example, taking advantage of bind variables, trace files, and using the different types of indexes.

Learn To:

Use Oracle tools to identify inefficient SQL statements.
Use Automatic SQL Tuning.
Use Real Time SQL monitoring.
Write more efficient SQL statements.
Monitor and trace high load SQL statements.
Manage optimizer statistics on database objects.
Understand the optimizer process steps and operators.
Interpret execution plans.
Perform application tracing.

Benefits To You:

Benefit from gaining a deeper understanding of Oracle SQL statement tuning and how to write well-tuned SQL statements appropriate for the Oracle database in this application development-centric course. You will learn how to decipher, decide and then apply tuning to your SQL code. Various tuning techniques are demonstrated.

Skills Gained

Modifying a SQL statement to perform at its best
Identifying poorly performing SQL
Tracing an application through its different levels of the application architecture
Understanding how the Query Optimizer makes decisions about how to access data
Defining how optimizer statistics affect the performance of SQL
Listing the possible methods of accessing data, including different join methods

Who Can Benefit

Administrator
Developer

Prerequisites

Required Prerequisite Courses: â¢ Oracle Database: Introduction to SQL Required Prerequisite Skills: â¢ Familiarity with database architecture â¢ Knowledge of SQL and PL/SQL

Course Details

Topics

Introduction
- Course Objectives, Course Agenda and Appendixes Used in this Course
- Audience and Prerequisites
- Sample Schemas Used in the Course
- Class Account Information
- SQL Environments Available in the Course
- Workshops, Demo Scripts, and Code Example Scripts
- Appendices in the Course
Introduction to SQL Tuning
- SQL Tuning Session
- SQL Tuning Strategies
- Development Environments: Overview
- SQLTXPLAIN (SQLT) Diagnostic Tool
Using Application Tracing Tools
- Using the SQL Trace Facility: Overview
- Steps Needed Before Tracing
- Available Tracing Tools: Overview
- The trcsess Utility
- Formatting SQL Trace Files: Overview
Understanding Basic Tuning Techniques
- Developing Efficient SQL statement
- Scripts Used in This Lesson
- Table Design
- Index Usage
- Transformed Index
- Data Type Mismatch
- NULL usage
- Tune the ORDER BY Clause
Optimizer Fundamentals
- SQL Statement Representation
- SQL Statement Processing
- Why Do You Need an Optimizer?
- Components of the Optimizer
- Query Transformer
- Cost-Based Optimizer
- Adaptive Query Optimization
- Optimizer Features and Oracle Database Releases
Generating and Displaying Execution Plans
- Execution Plan?
- The EXPLAIN PLAN Command
- Plan Table
- AUTOTRACE
- V$SQL_PLAN View
- Automatic Workload Repository
- SQL Monitoring
- DBML_SQL_MONITOR
Interpreting Execution Plans and Enhancements
- Interpreting a Serial Execution Plan
- Adaptive Optimizations
- Optimizer: Table and Index Access Paths
- Row Source Operations
- Main Structures and Access Paths
- Full Table Scan
- Indexes
- Common Observations
Optimizer Join Operations
- Join Methods
- Join Types
Other Optimizer Operators
- SQL operators
- Other N-Array Operations
- Result Cache operators
Introduction to Optimizer Statistics Concepts
- Optimizer Statistics
- Types of Optimizer Statistics
- Gather and Manage Optimizer Statistics: Overview
Using Bind Variables
- Cursor Sharing and Different Literal Values
- Cursor Sharing and Bind Variables
SQL Plan Management
- Maintaining SQL Performance
- SQL Plan Management
Workshops
- Workshop 1
- Workshop 2
- Workshop 3
- Workshop 4
- Workshop 5
- Workshop 6 & 7
- Workshop 8
- Workshop 9

Course Objectives, Course Agenda and Appendixes Used in this Course
Audience and Prerequisites
Sample Schemas Used in the Course
Class Account Information
SQL Environments Available in the Course
Workshops, Demo Scripts, and Code Example Scripts
Appendices in the Course

Introduction to SQL Tuning

SQL Tuning Session
SQL Tuning Strategies
Development Environments: Overview
SQLTXPLAIN (SQLT) Diagnostic Tool

Using Application Tracing Tools

Using the SQL Trace Facility: Overview
Steps Needed Before Tracing
Available Tracing Tools: Overview
The trcsess Utility
Formatting SQL Trace Files: Overview

Understanding Basic Tuning Techniques

Developing Efficient SQL statement
Scripts Used in This Lesson
Table Design
Index Usage
Transformed Index
Data Type Mismatch
NULL usage
Tune the ORDER BY Clause

Optimizer Fundamentals

SQL Statement Representation
SQL Statement Processing
Why Do You Need an Optimizer
Components of the Optimizer
Query Transformer
Cost-Based Optimizer
Adaptive Query Optimization
Optimizer Features and Oracle Database Releases

Generating and Displaying Execution Plans

Execution Plan
The EXPLAIN PLAN Command
Plan Table
AUTOTRACE
V$SQL_PLAN View
Automatic Workload Repository
SQL Monitoring
DBML_SQL_MONITOR

Interpreting Execution Plans and Enhancements

Interpreting a Serial Execution Plan
Adaptive Optimizations
Optimizer: Table and Index Access Paths
Row Source Operations
Main Structures and Access Paths
Full Table Scan
Indexes
Common Observations

Optimizer Join Operations

Join Methods
Join Types

Other Optimizer Operators

SQL operators
Other N-Array Operations
Result Cache operators

Introduction to Optimizer Statistics Concepts

Optimizer Statistics
Types of Optimizer Statistics
Gather and Manage Optimizer Statistics: Overview

Using Bind Variables

Cursor Sharing and Different Literal Values
Cursor Sharing and Bind Variables

SQL Plan Management

Maintaining SQL Performance
SQL Plan Management

Workshops

Workshop 1
Workshop 2
Workshop 3
Workshop 4
Workshop 5
Workshop 6 7
Workshop 8
Workshop 9

Read Less

0 options available

There are currently no scheduled dates for this course. If you are interested in this course, request a course date with the links above. We can also contact you when the course is scheduled in your area.

Request Other Date Request On-site Course

When does class start/end?

Classes begin promptly at 9:00 am, and typically end at 5:00 pm.

Does the course schedule include a Lunchbreak?

Lunch is normally an hour long and begins at noon. Coffee, tea, hot chocolate and juice are available all day in the kitchen. Fruit, muffins and bagels are served each morning. There are numerous restaurants near each of our centers, and some popular ones are indicated on the Area Map in the Student Welcome Handbooks - these can be picked up in the lobby or requested from one of our ExitCertified staff.

How can someone reach me during class?

If someone should need to contact you while you are in class, please have them call the center telephone number and leave a message with the receptionist.

What languages are used to deliver training?

Most courses are conducted in English, unless otherwise specified. Some courses will have the word "FRENCH" marked in red beside the scheduled date(s) indicating the language of instruction.

What does GTR stand for?

GTR stands for Guaranteed to Run; if you see a course with this status, it means this event is confirmed to run. View our GTR page to see our full list of Guaranteed to Run courses.

How do I find an ExitCertified training location?

We have training locations across the United States and Canada. View a full list of classroom training locations.

Which delivery formats are available?

At ExitCertified we offer training that is Instructor-Led, Online, Virtual and Self-Paced.

Does ExitCertified deliver group training?

Yes, we provide training for groups, individuals and private on sites. View our group training page for more information.

What does vendor-authorized training mean?

As a vendor-authorized training partner, we offer a curriculum that our partners have vetted. We use the same course materials and facilitate the same labs as our vendor-delivered training. These courses are considered the gold standard and, as such, are priced accordingly.

Is the training too basic, or will you go deep into technology?

It depends on your requirements, your role in your company, and your depth of knowledge. The good news about many of our learning paths, you can start from the fundamentals to highly specialized training.

How up-to-date are your courses and support materials?

We continuously work with our vendors to evaluate and refresh course material to reflect the latest training courses and best practices.

Are your instructors seasoned trainers who have deep knowledge of the training topic?

ExitCertified instructors have an average of 27 years of practical IT experience. They have also served as consultants for an average of 15 years. To stay up to date, instructors will at least spend 25 percent of their time learning new emerging technologies and courses.

Do you provide hands-on training and exercises in an actual lab environment?

Lab access is dependent on the vendor and the type of training you sign up for. However, many of our top vendors will provide lab access to students to test and practice. The course description will specify lab access.

Will you customize the training for our company’s specific needs and goals?

We will work with you to identify training needs and areas of growth. We offer a variety of training methods, such as private group training, on-site of your choice, and virtually. We provide courses and certifications that are aligned with your business goals.

How do I get started with certification?

Getting started on a certification pathway depends on your goals and the vendor you choose to get certified in. Many vendors offer entry-level IT certification to advanced IT certification that can boost your career. To get access to certification vouchers and discounts, please contact customerexp@exitcertified.com.

Will I get access to content after I complete a course?

You will get access to the PDF of course books and guides, but access to the recording and slides will depend on the vendor and type of training you receive.

How to request a W9 for ExitCertified LLC?

View our filing status and how to request a W9.

I was very satisfied about how the course was organized. Sean Did a very good work

Maximilien Belinga Belinga

ExitCertified

It was very informative and covered all the required materials along with handson labs for practice.

ExitCertified Student

ExitCertified

The platform is very intuitive and easy to navigate. Great tool for learning

ExitCertified Student

ExitCertified

had a good time with the course, however some topics were left out due to the compact amount of time for training.

ExitCertified Student

ExitCertified

Course was great and informative. The instructor had a good flow and was very personable.

ExitCertified Student

ExitCertified