Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Course Outline
Application Tuning Methodology
- Identifying the problem
- Diagnosing the root cause
- Implementing the solution
Database and Instance Architecture
- Overview of server files and processes
- Memory structures (SGA, PGA)
- Cursor parsing and sharing mechanisms
Analyzing the Execution Plan
- Methods for obtaining hypothetical and actual query plans (EXPLAIN PLAN, DBMS_XPLAN, SQL*Plus Autotrace)
- Annotating the sequence of algorithm steps
- Interpreting plan tree content
- Adaptive plans
Cost Optimization Process and Controlling the Optimizer
- Cost-based and rule-based optimization properties
- Session and instance parameters
- Hints
- Query plan outlines
- Query plan management (baselines, Profiles, SQL Patch)
Statistics and Histograms
- Impact of statistics and histograms on performance
- Methods for collecting statistics and histograms
- Strategies for statistics counting and estimation, ad hoc sampling
- Statistics management: blocking, copying, editing, automation, and monitoring changes
- Multi-column and expression-based statistics
- System and dictionary statistics
- Adaptive statistics
Logical and Physical Database Structure
- Tablespaces
- Segments
- Extents
- Blocks
Full Table Scan Optimization Through Proper Space Management
- When to utilize full table scans
- Block and segment space allocation, high-water mark, PCTFREE
- Impact of DML operations and space allocation on read performance
- Data loading via conventional and direct path methods
- Physical data reorganization: truncation, defragmentation, reconstruction
Optimizing Full Table Scans by Physically Isolating 'Hot Data'
- Temporary tables
- Partitioning
- Materialized views
Optimizing Full Table Scans via Data Compression
- OLTP compression
- OLAP compression
Index-Based Read Optimization
- Concept of ROWID
- Structure of BTREE indexes
- Comparing the efficiency of data access via BTREE versus FULL SCAN
- Impact of indexes on DML operations
- Strategies for creating and dropping indexes
- Characteristics of 'good' and 'bad' indexes; the impact of physical data distribution entropy on index usage costs
- Index properties and statistics
- Access types: UNIQUE, RANGE, SKIP, FULL, FAST FULL, MIN/MAX
- Index types: unique, function-based, multi-column, inverted key, local/global, virtual, invisible
- Handling NULL values in indexes
- Index-Organized Tables (IOT)
- Bitmap and join indexes
Optimizing the Sorting Process
- In-memory sorting
- Index-based sorting
- Linguistic sorting
Optimizing Joins and Subqueries
- Join methods: MERGE, HASH, NESTED LOOP
- Joins in OLTP and OLAP systems
- Star joins
- Join order determination
- Outer joins
Performance Monitoring and Bottleneck Identification
- Views: v$sql..., dba_hist...
- Database session/process tracking
- Application/user session tracking within the database connection lifecycle
- TkProf, TrcSess tools
PL/SQL Performance
- Use of literal values in SQL
- Rules regarding cursor sharing
- Use of literal values in SQL
- Statements on adaptive cursors
- Correct SQL/PL/SQL communication methods <=>
- Cursors and bulk operations
- Prefetching
- FOR UPDATE clauses
- User-defined functions in SQL
- Local functions
- Caching function results
- Determinism and efficiency
- Passing parameters by value versus reference
- Short routines evaluated at compile time
- Compiler management
- Compiler optimization levels
- Native compilation
- Other aspects of PL/SQL optimization
Requirements
Proficiency in SQL and PL/SQL. Practical experience working with Oracle or other relational database management systems.
28 Hours
Testimonials (3)
he explained very well, and gave guidance on the exercises.
Stoyan - OPEN COURSE
Course - Oracle Database 19c PL and Advanced SQL
I liked the hands-on experience and the opportunity to work on actual coding activities
Kristine - Isuzu Philippines Corporation
Course - ORACLE PL/SQL Fundamentals
The PL/SQL session basics. I used PL/SQL but never got the basics of it and this course helped me a lot understand PL/SQL queries.
