Oracle 18 Advanced PL/SQL & SQL Tuning

Catalog Home Databases, Business Intelligence & Data Science Oracle Programming

There are no scheduled dates for this course.

  Available by Request

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.

  • Skill with GUI interfaces
  • Data processing background
  • Solid SQL and PL/SQL skill (3 to 6 months of development effort)

5 Days/Lecture & Lab

This course is designed for Intermediate Oracle PL/SQL developers and DBAs. Solid understanding of PL/SQL, including the use of program units (procedures, functions, packages and triggers) is required to succeed in this class. Persons planning to attend ORA-230-10 without this should attend the Oracle 10g/11g PL/SQL Quick Tour (ORA-240-10) class that can be conducted on the day preceding this class.

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

< >

Recently Viewed Courses:

Copyright © 2019 ProTech. All Rights Reserved.

Sign In Create Account


Social Media