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

Fluency in SQL and PL/SQL. Practical experience in working with Oracle or other relational database engine.

Level
Expert
Form
Lecture, Presentation, Discussion, Problem solving
Purpose of the training
This training is intended for experienced programmers and database administrators who encounter performance problems on a daily basis, both in specific SQL commands and in entire processes implemented on the Oracle database side.
Benefits of completing the training
The training clearly and extensively presents issues that allow you to understand the internal mechanisms that occur when executing SQL commands and PL/SQL programs, monitor and diagnose performance problems and implement solutions. It enables effective management of the process of optimizing SQL commands and PL/SQL programs.

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

Suggested pre-training
ORA_S2, ORA_P2