Oracle - Tuning SQL commands and performance of PL/SQL programs Training Course

Application Tuning Methodology for Government

• Finding the Problem
• Diagnosing the Cause
• Applying the Solution

Database and Instance Architecture for Government

• Basic Information About Server Files and Processes
• Memory Structures (SGA, PGA)
• Cursor Parsing and Sharing Process

Analysis of the Command Execution Plan for Government

• 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 for Government

• 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 for Government

• 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 for Government

• Tablespaces
• Segments
• Extensions
• Blocks

Full Read Optimization Through Proper Space Management for Government

• 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" for Government

• Temporary Tables
• Partitioning
• Materialized Views

Full Read Optimization by Data Compression for Government

• OLTP Compression
• OLAP Compression

Optimization of Reading via Index for Government

• 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 for Government

• Memory Sort
• Index Sorts
• Linguistic Sorts

Optimization of Joins and Subqueries for Government

• Merge Methods: MERGE, HASH, NESTED LOOP
• Joins in OLTP and OLAP Systems
• Star Joins
• Connection Sequence
• External Joins

Performance Monitoring and Process Bottleneck Finding for Government

• v$sql…, dba_hist…
• Database Session/Process Tracking
• Application/User Session Tracking in the Database Connection Lease Model
• TkProf, TrcSess Tool

PL/SQL Performance for Government

• Using Literal Values in SQL
  • Rules of Sharing Cursors
  • Using Literal Values in SQL
  • Adaptive Cursors
• The Correct Way to Communicate SQL <=> PL/SQL
  • 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

Suggested Pre-Training for Government
ORA_S2, ORA_P2