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
- Essential information about server files and processes
- Memory structures (SGA, PGA)
- Cursor parsing and sharing processes
Analysis of the command execution plan
- Methods for obtaining hypothetical and actual query plans (EXPLAIN PLAN, DBMS_XPLAN, SQLPlus Autotrace)
- Marking the sequence of algorithmic steps
- Interpreting the content of the plan tree
- Adaptive plans
The process of cost optimization and controlling the cost optimizer
- Cost-based and rule-based optimization properties
- Session and instance parameters
- Hints
- Query plan patterns (outlines)
- Management of query plans (baselines, Profiles, SQL Patch)
Statistics and histograms
- The impact of statistics and histograms on performance
- Methods for collecting statistics and histograms
- Strategies for counting and estimating statistics, ad hoc sampling
- Statistics management: blocking, copying, editing, automation of collection, monitoring changes
- Multi-column and expression-based statistics
- System and dictionary statistics
- Adaptive statistics
The logical and physical structure of the database
- Tablespaces
- Segments
- Extents
- Blocks
Full read optimization through proper space management
- When to use full reading
- Block and segment space allocation, high water mark, PCTFREE
- The impact of DML operations and space allocation on read performance
- Loading data via conventional and direct path
- Physical reorganization of data: truncation, defragmentation, reconstruction
Full read optimization by physically separating "hot data"
- Temporary tables
- Partitioning
- Materialized views
Full read optimization by data compression
- OLTP compression
- OLAP compression
Optimization of reading via index
- The concept of ROWID
- Construction of BTREE indexes
- Comparing the effectiveness of data access through BTREE indexes versus FULL SCAN
- The impact of indexes on DML operations
- Strategies for creating and dropping indexes
- "Good" and "bad" indexes: how the entropy of physical data distribution affects index usage costs
- Index properties and statistics
- Types of reading: UNIQUE, RANGE, SKIP, FULL, FAST FULL, MIN/MAX
- Types of indexes: unique, function-based, multicolumn, inverted key, local/global, virtual, invisible
- NULL values in indexes
- Index-Organized Tables (IOT)
- Bitmap and join indexes
Optimization of the sorting process
- Memory sorting
- Index-based sorting
- Linguistic sorting
Optimization of joins and subqueries
- Join methods: MERGE, HASH, NESTED LOOP
- Joins in OLTP and OLAP systems
- Star joins
- Join sequence
- Outer joins
Performance monitoring and identifying process bottlenecks
- v$sql..., dba_hist...
- Database session/process tracking
- Application/user session tracking in the database connection lease model
- TkProf, TrcSess tools
PL/SQL performance
- Using literal values in SQL
-Statements regarding cursor sharing rules
-Using literal values in SQL
-Statements about adaptive cursors
- The correct way to communicate between SQL <=> and PL/SQL
-Cursors and bulk operations
-Prefetching
-FOR UPDATE
- Inline functions in SQL
-Local functions
-Caching function results
-Determinism and efficiency
- Passing parameters by copy vs. by reference
- Inlining short routines 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 engines.
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.
