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
- 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
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
