Free use of language SQL and PL/SQL. Practical experience working with Oracle or another relational database engine.
This training is intended for experienced developers and database administrators who encounter performance problems on a daily basis, both in specific commands SQL and in entire processes implemented on the database side Oracle.
The training presents in a clear and broad way the issues that allow to understand the internal mechanisms that occur during the execution of commands SQL and programs PL/SQL, to monitor and diagnose performance problems and implement solutions. It allows to effectively manage the optimization process of commands SQL and programs PL/SQL.
The training is conducted in the form of lectures, presentations, discussions, and problem solving.
Application tuning methodology
- Finding the problem
- Diagnosing the cause
- Applying the solution
Command execution process SQL
- The process of parsing and sharing cursors
- Adaptive cursors
Analysis of the command execution plan
- Ways to obtain hypothetical and real query plan (EXPLAIN PLAN, DBMS_XPLAN, SQLPlus Autotrace)
- Marking the order of algorithm steps
- Interpretation of the contents of the plan tree
- Adaptive plans
Cost optimization process and cost optimizer operation control
- Properties of cost and rule optimization
- Session and instance parameters
- Hints
- Query plan patterns (outlines)
- Managing query plans (baselines)
- Patching commands
- Profiles and SQL Tuning Advisor
Statistics and histograms
- The impact of statistics and histograms on performance
- Methods of collecting statistics and histograms
- Strategies for counting and estimating statistics, ad hoc sampling
- Statistics management: blocking, copying, editing, collecting automation, monitoring changes
- Multi-column, expression-based statistics
- System and dictionary statistics
- Adaptive statistics
Optimization of full reading through proper space management
- When to use full reading
- Block and segment space allocation, high water indicator, PCTFREE
- The impact of DML operations and space allocation on read performance
- Loading data via conventional and direct paths
- Physical data reorganization, truncation, defragmentation, reconstruction
Full read optimization by physically separating "hot data"
- Temporary boards
- Partitioning
- Materialized views
Full read optimization through data compression
- OLTP compression
- OLAP compression
Read optimization via index
- The concept of ROWID
- Construction of BTREE indices
- Comparison of the effectiveness of data access through the BTREE and FULL SCAN indexes
- The impact of indexes on DML operations
- Strategies for creating and deleting indexes
- "Good" and "bad" index, the impact of the entropy of physical data arrangement on the costs of index use
- Index properties and statistics
- Reading types: UNIQUE, RANGE, SKIP, FULL, FAST FULL, MIN/MAX
- Index types: unique, functional, multi-column, reverse-keyed, local/global, virtual, private
- NULL values in indexes
- Index Organized Boards (IOT)
- Bitmap and join indexes
Optimization of the sorting process
- Memory sorting
- Index sorts
- Linguistic sorting
Optimization of joins and subqueries
- Connection methods: MERGE, HASH, NESTED LOOP
- Joins in OLTP and OLAP systems
- Star joints
- Connection sequence
- Outer joins
Monitor performance and find process bottlenecks with SQLTrace
- Database session/process tracking
- Application/user session tracking in a leased database connection model
- TkProf tool, TrcSess
Code Performance PL/SQL
- Using literal values in commands SQL
- Rules for sharing cursors
- Adaptive cursors - Correct way of communication SQL, PL/SQL
- Cursors and mass operations
- Custom functions in SQL
- Caching function results - Passing parameters via copy/pointer
- Feathering short procedures at compilation stage
- Compiler optimization levels
- Native build
- Other aspects of optimization PL/SQL