COURSE DESCRIPTION: This is a 5-day class (6-hours per day) that includes lecture, lab and testing for up to 12 students.
Attendees will learn tips and techniques on how to advance their expertise when programming in SQL and PL/SQL. They will learn tuning tips and debugging techniques.
The attendees will work with a variety of SQL statements and tune them by making coding changes based on their review of the explain plans, modifying indexes and using hints and coding style to control the explain plans. They will also learn how to use tools such as index monitoring, SQL Trace and the PL/SQL profiler. They will be instructed on how Oracle selects indexes, why they are sometimes not used and how to tell which indexes are being used or not.
The attendees will find poorly-performing components of PL/SQL routines, use Oracle's DEBUG routines to find problems in PL/SQL logic and will learn how to use PL/SQL's new optimizing compiler and collections. Students will learn how to use Toad when performing the hands-on exercises.
Lectures and topics are enhanced with live illustrations and hands-on exercises.
Introduction to Explain Plans and Hints.
Introduction to Cost-Based Optimizer.
Introduction to Tuning Tools: SQL Tracing & TKProf
Learning SQL Coding Tips.
Learning PL/SQL Coding Tips.
Review of PL/SQL syntax and language structure.
Review of cursor management.
Review of PL/SQL Profiling.
Review of Package Usage (User Defined and Oracle Defined)
Exposure to Quest Software's application named Toad.
Introduction to advanced PL/SQL topics including compiler options, code encryption, conditional compilation, collections, triggers, dynamic SQL and much more.
AUDIENCE: Students who wish to learn advanced tips and techniques of SQL and PL/SQL programming who already have a working knowledge of SQL and PL/SQL.
PREREQUISITES: Working knowledge of SQL and PL/SQL.
ATTENDEES RECEIVE: o Study Guide with presentations o PL/SQL Reference pocket guide o Example files with SQL and PL/SQL scripts o Java and PL/SQL white paper o Executing external procedures through PL/SQL white paper
COURSE OUTLINE: Day 1: SQL Statement Tuning o Oracle RDBMS Architecture overview o Explain Plans explained - Understanding - Reading - Interpreting o Understanding the Cost-Based Optimizer (CBO) o Understanding Parallel Explain Plans o Working with Hints Day 2: SQL Troubleshooting Tips and Techniques o Indexes: - Review - Tips and Techniques o Sub-Query Coding Techniques o Tuning Tools - Statspack - Events - SQL Tracing - TKProf o Profiling and tuning PL/SQL
Day 3: Advanced SQL Troubleshooting Tips and Techniques o Review SGA Memory Structures - Buffer Cache - Result Cache o Library Cache Trace File Analysis - Using SQL TXPlan (new free Oracle SQL analysis tool) - Using Toad o Other Useful Oracle Traces - 10053 CBO Trace - 10030 & 10031 Sort Traces - 10104 Hash Join Traces - Bulk Collect/Forall Bulk Binding - Using Collections for Reference Table o Oracle Internals - Various Space Management Issues discussed - Monitoring Sorting o Finding Problem SQL using v$ information o Using Automated Workload Repository o Review Database Replay and External Tables o Review the SQL Tuning Advisor
Day 4: Advanced Oracle PL/SQL o PL/SQL Compiler Options o Code Encryption o Conditional Compilation o Definer/Invoker Rights o Executing and Exception handling o Autonomous Transactions o Package Options o Overloading o Database Triggers - DML (Data Manipulation Language) - Instead of - ServerError - Startup - Shutdown - Logon o Cursors - User Defined Datatypes - Cursor Sharing - Cursor Variables - Strong vs. Weak Cursors
Day 5: Advanced Oracle PL/SQL o Collections - Associate Arrays - Nested Tables - Varrays - Bulk Collect/Forall Bulk Binding - Using Collections for Reference Table o Oracle Provided Packages - DBMS_ALERT - DBMS_JOB - DBMS_OUTPUT - DBMS_SCHEDULER - UTL_FILE - UTL_MAIL - Other Provided Packages o DBMS_SQL and Dynamic SQL o Working with Large Objects o Debugging PL/SQL Routines o PL/SQL Profile o PL/SQL Tuning Tips