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
- Finding the problem
- Diagnosing the cause
- Applying the solution
Database and instance architecture
- Basic information about server files and processes
- Memory structures (SGA, PGA)
- Cursor parsing and sharing process
Analysis of the command execution plan
- Ways of obtaining a hypothetical and real query plan (EXPLAIN PLAN, DBMS_XPLAN, SQLPlus Autotrace)
- Marking the sequence of algorithm steps
- Interpretation of plan tree content
- Adaptive plans
The process of cost optimization and controlling the work of the cost optimizer
- Cost and rule optimization properties
- Session and instance parameters
- Hints
- Patterns of query plans (outlines)
- Management of query plans (baselines, Profiles, SQL Patch)
Statistics and histograms
- Impact of statistics and histograms on performance
- Ways of collecting statistics and histograms
- Statistics counting and estimation strategies, ad hoc sampling
- Statistics management: blocking, copying, editing, collection automation, changes monitoring
- Multi-column, expression-based statistics
- System and dictionary statistics
- Adaptive statistics
The logical and physical structure of the database
- Tablespaces
- Segments
- Extensions
- Blocks
Full read optimization through proper space management
- When to use full reading
- Block and segment space allocation, high water indicator, PCTFREE
- 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
- ROWID concept
- Construction of BTREE indices
- Comparison of the effectiveness of data access through the BTREE and FULL SCAN indexes
- Impact of indexes on DML operations
- Strategies for creating and deleting indexes
- "good" and "bad" index, the impact of the entropy of the physical distribution of data on the costs of using the index
- Index properties and statistics
- Reading types: UNIQUE, RANGE, SKIP, FULL, FAST FULL, MIN/MAX
- Types of indexes: unique, function, 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 sort
- Index sorts
- Linguistic sorts
Optimization of joins and subqueries
- Merge methods: MERGE, HASH, NESTED LOOP
- Joins in OLTP and OLAP systems
- Star joins
- Connection sequence
- External joins
Performance monitoring and process bottleneck finding
- v$sql…., dba_hist…
- Database session/process tracking
- Application/user session tracking in the database connection lease model
- TkProf, TrcSess tool
PL/SQL performance
- Using literal values in SQL
-statements about the rules of sharing cursors
-using literal values in SQL
-statements about adaptive cursors
- The correct way to communicate SQL <=> PL/SQL
-cursors and mass operations
-prefetch
-for update
- Eigenfunctions in SQL
-local
-caching function results
-determinism and efficiency
- Passing parameters by copy/pointer
- Feather short routines at compile time
- Compiler management
-compiler optimization levels
-Native build
- Other aspects of PL/SQL optimization
Requirements
Fluency in SQL and PL/SQL. Practical experience in working with Oracle or other relational database engine.
28 Hours
Testimonials (3)
I liked very much the format of the training, hands on, directly in sql developer.
Andreea - OPEN COURSE
Course - Oracle Database 19c PL and Advanced SQL
The exercises were explained very good.
Monica - DB Global Technology
Course - PL/SQL in Oracle 19c
It was quite hands-on, not too much theory.
