Oracle SQL and PL/SQL Performance Tuning Tips & Techniques

Request more details:

submit request
The focus of this tuning course is to illustrate coding techniques that insure a consistent response time between instances and releases of the Oracle database. This course works closely with performance tuning of actual SQL statements and various coding techniques and options in and around the PL/SQL language.
This course is current with Oracle12 SQL and PL/SQL tuning topics.
All attendees must have some prior Oracle SQL and PL/SQL experience.
The focus this course is tuning SQL via coding style. The instructor finds that tuning in this fashion maintains the performance of the SQL when migrating to other Oracle environments (upgrades to newer releases).
Lectures and topics are enhanced with live illustrations and hands-on exercises.
Overview of Topics Covered:
• Oracle10g, Oracle11g, and Oracle12c
• Oracle Architecture from a SQL Performance point-of-view
• Understanding SQL Tuning Statement Topics
• Reading Explain Plans/Understanding Explain Plans
• Controlling the Cost-based Optimizer
• A close look at Indexes – how they work and how they are selected
• SQL Tuning via coding style
• A review of how Oracle computes row cardinality
• Important CBO Statistics review
• Various useful Oracle traces
• Useful Metalink Tuning Tools
• Oracle Trace Facility – collecting SQL and interpreting using TKProf
o Will show TOAD’s Trace File Analyzer
• Library Cache Management
• Result Cache Management
• Oracle PL/SQL Architecture
• PL/SQL Topics
o Package Usage (both User Defined and Oracle Defined Packages)
o Compiler Options, Code Encryption, Conditional Compilation
o Cursor Sharing/Cursor Variables
o Collections (the PL/SQL gas pedal!)
o Package options that work well with collections
o Table Functions (very efficient when mixed with the SQL in the first part of the course)
o Triggers (new features and auditing performance)
o Dynamic SQL (useful to solve SQL tuning issues as well)
- Includes a lecture on preventing SQL Injection
o Autonomous Transactions
o PL/SQL Coding Tips
o Debugging PL/SQL
o PL/SQL Profiling
Lectures and topics are enhanced with live illustrations and hands-on exercises.
Attendees Receive::
• Study guide with presentations and relevant white papers
• ALL of the presented tuning and problem discovery scripts
• Opportunity to ask the tough Oracle questions
• A hands-on opportunity to learn more about Oracle, SQL Developer, and TOAD
Course Outline::
• Day 1:
- Oracle RDBMS Architecture overview
- Understanding/Reading/Interpreting Explain Plans
- Understanding the Cost-based Optimizer
- Understanding Parallel Explain Plans
- Working with Hints
- Index Review/Tips & Techniques
o How Oracle utilizes Indexes
o What is a good index?
o How do I know if Oracle is even using my indexes?
o How does Oracle select Indexes for use
o Major statistics with a cool report for monitoring Indexes
• Day 2:
- A close look at sub-query coding techniques
- SQL Coding Tips
- Where Clause and controlling Cardinality
- A close look at CBO statistics
- Hint Review/Tips & Techniques
- Tuning Tool Review: SQL Tracing/Tkprof
- Library Cache Analysis – purging SQL from the Library Cache
- A look at useful Oracle Traces (with scripts to run them!)
- Useful Metalink SQL Tuning Tools
o Using SQL TXPlan (new free Oracle SQL analysis tool)
• Day 3:
- PL/SQL Overview/Review [could be expanded to as much as half a day based on group needs]
- PL/SQL Compiler Options
- Code Encryption
- Conditional Compilation
- Definer/Invoker Rights
- Executing and Exception handling
- Autonomous Transactions
- Package Options
- Overloading
- Database Triggers
o DML, Instead of, ServerError, Startup, Shutdown, Logon
• Day 4: Advanced Oracle PL/SQL:
- Working with Cursors
o User Defined Datatypes
o Cursor Sharing
o Cursor Variables
o Strong vs Weak Cursors
- Working with Collections
o Associate Arrays
o Nested Tables
o Varrays
o Bulk Collect/Forall Bulk Binding
o Using Collections for Reference Table
- Working with Table Functions
o What are they?
o How they are used with SQL
• Day 5: Advanced Oracle PL/SQL:
- Dynamic Partitioning and Parallelization
o Dynamic Chunk Processing
o Coding tips and techniques
o Monitoring Chunk Processing
- Dynamic SQL
o Cursor Reuse with DBMS_SQL
o Efficiencies with Native Dynamic SQL
o Lecture on SQL Injection
- Debugging PL/SQL Routines
- Finding problems with the Hierarchical PL/SQL Profiler
o Demo the older PL/SQL Profiler as well
- PL/SQL Tuning Tips
Summary Course Information:
The course starts out with a complete overview of the Oracle architecture so students can get an understanding how their SQL and applications can take advantage of the computing environment. This course goes in-depth on understanding and controlling the explain plan (how Oracle retrieves data and in what order). The discussion includes considerable detail, with SQL examples, on how the optimizers (both rule-based and cost-based but mostly cost-based) make their decisions. Students will work with a variety of SQL statements, reviewing explain plans and revising code to make these SQL statements perform better. Lectures include index design, using hints and coding style to control the explain plans, and how to use useful tools such as index monitoring, SQL Trace, and the PL/SQL profiler. This course takes a close look at indexes: how Oracle selects them, why they are sometimes not used, and how to tell if indexes are being used/not being used.
The latter part of this class focuses on PL/SQL newer compiler features, memory structures that enhance performance, and over all good practices to follow. The focus of this part of the course is to advance attendee knowledge of the PL/SQL programming language. This is an excellent course for those people that need a better knowledge of the PL/SQL language. This course covers all the latest features of PL/SQL using databases Oracle11g and Oracle12c. All of the Oracle PL/SQL performance features will be covered including collections, triggers, table functions (and how this mixes in with SQL tuning), handling partitioned objects via PL/SQL, and profiling/debugging/coding tips.
The attendees have the opportunity to learn how to find the poorly-performing components of PL/SQL routines, use Oracle’s newer DEBUG routines to find problems in PL/SQL logic and how to use the latest features of the PL/SQL language such as the new optimizing compiler and collections. The course contains additional white papers on Java and PL/SQL and executing external procedures.
About Dan Hotka:
Dan Hotka is a Training Specialist and an Oracle ACE Director who has over 32 years in the computer industry, over 27 years of experience with Oracle products. His experience with the Oracle RDBMS dates back to the Oracle V4.0 days. Dan’s latest book is the Oracle SQL Tuning: A Close Look at Explain Plans by Amazon. He is also the author of TOAD Handbook by Pearson Education, SQL Developer Handbook by Oracle Press, Oracle9i Development By Example, and Oracle8i from Scratch by Que and has co-authored 7 other popular books including the Database Oracle10g Linux Administration by Oracle Press. He is frequently published in Oracle trade journals, and regularly speaks at Oracle conferences and user groups around the world.