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
Testimonials (5)
The topic was interesting, and the trainer was very friendly and has adept knowledge of the subject.
Jacqueline - Tribal Grou[
Course - Developing Applications with Oracle Application Express (APEX)
During the training, all modules were discussed in great detail - for two days of training - and that was what I cared about the most.
Bernadetta - Urząd Komisji Nadzoru Finansowego
Machine Translated
Szkolenie było przygotowane perfekcyjnie, najbardziej podobał mi się sposób prowadzenia oparty przykładach pisanych w czasie rzeczywistym z dokładnym omówieniem jaki wpływ na bazę danych mają poszczególne instrukcje.
Joanna Dymarczyk
Course - Oracle 11g - Programowanie w PL/SQL I - warsztaty
I like fact, that after each section we had exercises. It helps to remember discussed topic.
Adam Bińczycki
Course - Oracle 11g - SQL language for developers - Workshop
wiedza i przekłady