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
Database and instance architecture
- Server processes
- Memory structures (SGA, PGA)
- Cursor parsing and sharing
Analysis of the command execution plan
- Hypothetical execution plan (EXPLAIN PLAN, SQLPlus Autotrace, XPlan)
- Actual execution plan (V$SQL_PLAN, XPlan, AWR)
Monitor performance and find process bottlenecks
- Monitoring the current state of the instance through system dictionary views
- Monitoring historical dictionaries
- Application Trace (SQLTrace, TkProf, TreSess
Optimization process
- Properties of cost and rule optimization
- Setting an optimization goal
- Adaptive cursors
Controlling the work of the cost optimizer by:
- Session and instance parameters
- Hints
- Query plan patterns
Statistics and histograms
- The impact of statistics and histograms on performance
- Methods of collecting statistics and histograms
- Strategy for counting and estimating statistics
- Statistics management: blocking, copying, editing, collecting automation, monitoring changes
- Dynamic data sampling (temporary arrays, complex predicates)
- Multi-column, expression-based statistics
- System statistics
Logical and physical structure of the database
- Tablespaces
- Segments
- EXTENTS
- Blocks
Data storage methods
- Physical aspects of table construction
- Temporary tables
- Index tables
- Outdoor boards
- Table partitioning (range, list, hash, mixed)
- Physical reorganization of tables
Materialized views and the QUERY REWRITE mechanism
Data indexing methods
- Construction of B-TREE indices
- Index properties
- Indexes: unique, multi-column, functional, inverted
- Index compression
- Rebuilding and merging indexes
- Virtual indexes
- Private and public indexes
- Bitmap and join indexes
Case study – full data scan
- The impact of table and block level space management on read performance
- Loading data via conventional and direct paths
- Predicate order
Case study – access to data through indexes
- Index reading methods (UNIQUE SCAN, RANGE SCAN, FULL SCAN, FAST FULL SCAN, MIN/MAX SCAN)
- Using functional indexes
- Index selectivity (CLUSTERING FACTOR)
- Multi-column indexes and SKIP SCAN
- NULL value and indexes
- Index Tables (IOT)
- The impact of indexes on DML operations
Case study - sorting
- Memory sorting
- Index sorts
- Linguistic sorting
- The influence of the degree of entropy on sorting (CLUSTERING FACTOR)
Case analysis - joins and subqueries
- Connection methods: MERGE, HASH, NESTED LOOP
- Joins in OLTP and OLAP systems
- Connection sequence
- Outer joins
- Anti-join
- Incomplete joins (SEMI)
- Simple subqueries
- Correlated subqueries
- Views, WITH clause
Requirements
Fluent use of the language SQL and knowledge of the Oracle database environment Practical experience in working with Oracle
28 Hours
Testimonials (2)
Wide range presented in logical and concise manner, good examples.
Paweł Kielich - Aptitude Software (Poland) sp. z o.o.
Course - Oracle 12c – Strojenie Poleceń SQL - warsztaty
Form, many examples and trainer attitude
