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
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
Requirements
Free use of language SQL and PL/SQL. Practical experience working with Oracle or another relational database engine.
28 Hours
