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.
Testimonials (7)
wiedza i przekłady
Jan Maksymowski
Course - Oracle 11g - Język SQL dla administratorów - 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
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
Method of translating the material and examples shown.
Marek Jakimowicz
Course - Tuning SQL i PL/SQL dla programistów
Machine Translated
I like this training because it was interactive
Amalia - Banca Transilvania
Course - SQL Fundamentals
Trainer expertise on SQL tuning
Bogdan - DB Global Technology
Course - Oracle 11g - Application Tuning - Workshop
it was very well organized