This course gives intense understanding of SQL and PL/SQL and their tuning capabilities which can enhance the performance. It discusses the goals for tuning and provides tuning suggestions. This presents the operation of SQL Tuning Advisor utility and portrays the SQL Tuning sets that include execution statistics and execution context. It depicts the idea of automatic SQL tuning with SQL Profiles and includes the types of tuning analysis.

This course provides an overview of data access methods using indexes and clusters that can improve the performance along with the usage of SQL Tuning Information Views. It describes the use of Dynamic SQL with PL/SQL to make programs more flexible by building and processing SQL statements at run time. It also delivers the idea of data abstraction which lets us to work with the properties of data, how to manage the object type and how to deal with the language elements. It supplies information about the standalone wrap utility to obfuscate the PL/SQL source code.

Prerequisites

• Knowledge of SQL and PL/SQL

Objectives

• Get to know about SQL Tuning
• Find out the tuning suggestions
• Automating SQL Tuning
• Learn about Tuning Sets and Information views
• Understand how the Query Optimizer makes decisions about how to access data
• Interpret execution plans
• Building Indexes and Clusters
• Analyzing the Dynamic SQL
• Supervising Object type with PL/SQL
• Obfuscating PL/SQL Source Code with wrap utility

Course Content

SQL Tuning

• Goals for Tuning
• Identifying High-Load SQL
• Automatic SQL Tuning Features
• Developing Efficient SQL Statements

Query Optimizer and Execution Plans

• Modes of Query Optimizer
• Optimizer Operations and Features
• Understanding the Query Optimizer
• Maintaining the Execution Plans

SQL Tuning Advisor

• Input Sources
• Tuning Options
• Advisor Output
• Using SQL Tuning Advisor APIs

SQL Tuning Sets

• SQL Tuning Sets APIs
• Creating and Loading a SQL Tuning Set
• Displaying the Contents of a SQL Tuning Set
• Modifying and Transporting a SQL Tuning Set
• Dropping a SQL Tuning Set

SQL Tuning Information Views

• Summary of Views

SQL Profiles

• Accepting a SQL Profile
• Altering a SQL Profile
• Dropping a SQL Profile

Using Indexes and Clusters

• Understanding Index Performance
• Using Different Index for Performance
• Choosing clusters for Performance
• Using Hash Clusters for Performance

Tuning Analysis

• Statistics Analysis
• SQL Profiling
• Access Path Analysis
• SQL Structure Analysis

Pseudo columns in SQL

• Hierarchical Query Pseudocolumns
• Sequence Pseudocolumns
• Version Query Pseudocolumns
• Using Different Pseudocolumns

Dynamic SQL in PL/SQL

• Why Use Dynamic SQL
• Developing with Native Dynamic SQL
• Advantages of  Native Dynamic SQL
• Using Bulk Dynamic SQL in PL/SQL
• Guidelines for Using Dynamic SQL with PL/SQL

Data Abstraction

• Cursors
• Collections
• Records
• Object Types

Advantages of PL/SQL

• Tight Interaction with SQL
• Better Performance
• Higher Productivity
• Full Portability
• Tight Security

Tuning PL/SQL Applications for Performance

• Initialization Parameters for PL/SQL Compilation
• Guidelines for Avoiding PL/SQL Performance Problems
• Profiling and Tracing PL/SQL Programs
• Reducing Loop Overhead

Managing object type with PL/SQL

• Declaring and Initializing Objects in PL/SQL
• Manipulating Objects
• Defining SQL Types Equivalent to PL/SQL Collection Types
• Using PL/SQL Collections with SQL Object Types

Language Elements in PL/SQL

• AUTONOMOUS_TRANSACTION Pragma
• Block Declaration
• Collection Methods
• Exception_Init Pragma
• Fetch Statement

Obfuscating PL/SQL Source Code

• Introduction to Obfuscation
• Obfuscating PL/SQL Code with the wrap Utility
• Using DBMS_DDL Subprograms to Obfuscate the Code

Course Duration

2 days