This BC Oracle PL/SQL programming & Tuning Training class is an intense course that is designed to give the student maximum exposure to Oracle PL/SQL tuning and performance optimization.  The student learns by example, studying actual performance case studies.

SQL

• Controlling User Access
• Managing Objects with Data Dictionary View
• Manipulating Large Data Sets
• Regular Expression Support (RegExp)
• Create indexes and constraints; alter existing schema objects.
• Hierarchical Queries
• Recover data from database using flashback querries
• WITH clause
• Pivot / Unpivot
• Use SQL with aggregation operators.
• SQL for Analysis and Reporting functions
• Group and aggregate data using the ROLLUP and CUBE operators
• The GROUPING function
• Composite Columns and the concatenated Groupings
• RANK & DENSE_RANK
• PERCENT_RANK
• CUME_DIST (cumulative distribution)
• The LAG/LEAD Functions
• LISTAGG function
• Aggregate functions VS Analytic Part
• Aggregate functions & windowing clause In-Depth
• Moving Average and running totals
• ROWNUM & ROW_NUMBER

SQL Tuning

•  Find the poorly performing queries
•  Learn Advanced Query Tuning Techniques
•  Understand the Database Architecture to perform Better Tuning Techniques
•  Learn Advanced Indexing Techniques to Maximize Database Performance
•  Learn how to use various Performance Tuning Tools
•  Compare BAD vs GOOD Queries with Hands-on Examples.
•  Gather Statistics for the bad SQLs
•  Interpret the Execution Plans with different methods
•  Choose an appropriate SQL tuning approach
•  Guide the Oracle Optimizer for a better execution
•  Use various tuning techniques like using bind variables, different types of indexes

PL/SQL

•  Working with Composite Data Types
•  Using Explicit Cursors
•  Handling Exceptions
•  Using Oracle-Supplied Packages in Application Development
•  Using Dynamic SQL
•  Design Considerations for PL/SQL Code
•  Creating Compound, DDL, and Event Database Triggers
•  Conditional 
•  Perform Data processing using Records and Cursors
•  Manage data using Collections
•  Peform Bulk Processing to improve performance
•  Execute SQL using Dynamic SQL
•  Pipelining the data as it is processed
•  Working with Large Objects (LOB's)

This PL/SQL tuning training course 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.