Oracle Advanced PL/SQL & SQL Tuning

Summary

This class covers advanced topics related to Oracle PL/SQL. This class provides the technical expertise necessary to utilize these powerful components of Oracle.

Attention in this class is given to some aspects of PL/SQL that are often not clearly understood (PL/SQL composite datatypes, regular expressions) or fully utilized (Oracle supplied packages, native compilation) along with advanced topics such as wrapping PL/SQL and pipelined functions and PL/SQL profiling. Techniques for measuring PL/SQL performance through profiling are also presented.

The PL/SQL content includes a class project consisting of the creation of a PL/SQL package to work with contents of the Oracle data dictionary.

Additional coverage to build knowledge about Oracle SQL tuning issues is also included. These areas begin with understanding how a SQL statement is processed by Oracle RDBMS, how the chosen execution plan can be obtained, gaining understanding of the various approaches the Oracle cost-based optimizer (CBO) can take to satisfy a SQL statement and basic to moderate coverage of actions to improve performance.

Prerequisites

Before taking this course, students should have skill with GUI interfaces, data processing background, and solid SQL and PL/SQL skill (3 to 6 months of development effort).

Duration

5 Days/Lecture & Lab

Audience

This course is designed for Intermediate Oracle PL/SQL developers and DBAs

Topics

• The PetSaver sample database
• PL/SQL review
• PL/SQL composite variables
• Working with large objects (LOBs)
• PL/SQL wrapping
• Advanced Oracle supplied packages
• Pipelined and table functions
• The PL/SQL profiler & hierarchical profiler
• Set up requirements for the PL/SQL profiler
• Understanding and interpreting hierarchical profiler output
• Using PL/SQL Scope
• Regular Expressions
• Regular expression concepts
• Oracle regex operators
• Advanced Compilation Techniques
• NATIVE PL/SQL compilation
• Introduction to tuning
• Kinds of performance problems
• SQL statement processing
• The Oracle optimizer
• Optimizing SHARED_POOL utilization
• Effective Indexing
• Using the EXPLAIN PLAN command
• Creating and understanding an EXPLAIN PLAN
• Tuning tools that measure resource consumption
• TKPROF
• Histograms

Related Scheduled Courses