Oracle Advanced PL/SQL & SQL Tuning

PT25001
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
  • Skill with GUI interfaces
  • Data processing background
  • 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 composite variables
  • Working with large objects (LOBs)
  • PL/SQL wrapping
  • Group coding project – The Oracle data dictionary
  • Advanced Oracle supplied packages
  • Pipelined and table functions
  • The PL/SQL profiler & hierarchical profiler
  • Using PL/SQL Scope
  • Regular Expressions
  • Oracle regex operators
  • Advanced Compilation Techniques
  • Introduction to tuning
  • SQL statement processing
  • The Oracle optimizer
  • Optimizing SHARED_POOL utilization
  • Effective IndexingC
  • Creating and understanding an EXPLAIN PLAN
  • Tuning tools that measure resource consumption
  • TKPROF
  • Histograms

Related Scheduled Courses