EncartaLabs

PLSQL Performance Tuning

( Duration: 1 Day )

The PLSQL Performance Tuning training course focuses on the advanced features of Oracle’s PL/SQL and SQL features. Additionally, the performance and tuning issues are elaborated for

  • Collections
  • SPARSE collections
  • Table functions
  • BULK COLLECT
  • Cursor variables
  • Dynamic SQL
  • Packages
  • PL/SQL optimization
  • Locking & concurrency
  • Performance and good practices

By attending PL/SQL Performance Tuning workshop, attendees will learn to develop and tune PL/SQL applications.

This PL/SQL Performance tuning class is designed for practicing Oracle PL/SQL professionals who have basic experience with PL/SQL and the use of a procedural language. Prior experience with PL/SQL is not required, but experience with a relational database is highly desirable.

  • PL/SQL developers, DBAs and production DBAs.

COURSE AGENDA

1

Session - 1

  • How PLSQL Optimizes your programs
  • When to tune PLSQL code ?
  • Guidelines for avoiding PLSQL Performance Problems
    • Avoiding CPU overhead in PLSQL code
    • Avoiding memory overhead in PLSQL code
  • Profiling and tracing PLSQL programs
    • Using the Profiler API : DBMS_PROFILER package
  • Overview
  • Security Model
  • Operational Notes
  • Exceptions
2

Session - 2

  • Using DBMS_TRACE package
    • Requirements, Restrictions, and constants for DBMS_TRACE
    • Using DBMS_TRACE Package
    • Summary of DBMS_TRACE subprograms
  • Reducing the loop overhead for DML statements and queries
    • Using the FOR-ALL statement
    • Retrieving the query results into collections with BULK COLLECT clause
  • Writing computation intensive programs in PLSQL
  • Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor variables
3

Session - 3

  • Tuning PLSQL procedure calls with the NOCOPY compiler hint
    • Restrictions on NOCOPY
  • Compiling the PLSQL code for Native Execution
  • Setting up transformation pipelines with table functions
    • Overview of table functions
    • Using pipelined table functions for transformation
    • Writing a pipelined table function
    • Returning results from table function
    • Pipelining data between PLSQL table functions
    • Querying table functions
4

Session - 4

  • Optimizing multiple calls to table functions
  • Fetching from the results of table functions
  • Passing data with cursor variables
  • Performing DML operations inside table functions
  • Performing DML operations on table functions
  • Handling exceptions in table functions

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 4,000 Modules on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting http://encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top