Oracle SQL Tuning 19c
Course Duration: 30 Hours
Required Prerequisites
- Basic Knowledge of Oracle SQL
- Basic Knowledge of Oracle DBA
Target Audience
- SQL Developers, PL/SQL Developers and Oracle DBAs who want to gain an in-depth understanding of Oracle SQL Tuning.
Students Enquiry Form
Course Highlights
- Introduction to SQL Tuning
- Setting Up Oracle DB 19c For Practice
- Query Optimizer & Execution Plans
- Indexes
- Oracle Hints
- Optimizer Statistics
- Adaptive Query Optimization
- Cursor Sharing
- Monitoring and Tracing SQL
- SQL Tuning Sets
- SQL Tuning Advisor
- SQL Plan Management
- SQL Access Advisor
- Server Result Cache
- More SQL Tuning Tools
- Partitioning in Oracle Database
Course Content
Section 1: Oracle Database Architecture Overview
- Memory Architecture
- Physical Architecture
- Logical Architecture
Section 2: Introduction to SQL Tuning
- About SQL Tuning
- SQL Tuning Cases Examples
- SQL Tuning Approach
- SQL Tuning Tool
Section 3: Setting Up Oracle DB 19c For Practice
- Install Oracle VirtualBox on Windows Operating System (O.S)
- Install Oracle Enterprise Linux 8 ( OEL 8 ) on Oracle VirtualBox
- Perform Oracle Database Software – Pre-Requisite Task on Linux 8
- Install Oracle Database Software 19c using Oracle Universal Installer (OUI).
- Create Non-Container Oracle Database using DBCA.
- Create Container ( CDB ) Oracle Database using DBCA.
- Create pluggable database ( PDB ) Database using DBA and Manually.
Section 4: Query Optimizer
- SQL Processing
- Query Optimizer – CBO – Cost Base Optimizer
- SELECTIVITY
- CARDINALITY
Section 5: Execution Plans
- How to read Query execution plan
- Displaying Query execution plan – AUTOTRACE , EXPLAIN PLAN FOR , DBMS_XPLAN,SQL Developer, EM Express , V$ Views
- SQL Operators – Access Path
- Full Table Scan
- Table Access by ROWID
- Sample Table Scan
- Index Unique Scan
- Index Range Scan
- Index Full Scan
- Index Fast Full Scan
- Index Skip Scan
- Index Join Scan
- Bitmap Index Single Value
- Bitmap Index Range Scan
- Bitmap Merge
- Cluster scan
- Hash scan
- SQL Joins
- Nested Loop Joins
- Hash Joins
- Sort Merge Joins
- Estimated Rows vs Actual Rows
- A-Rows / E-Rows Mismatch analysis
Section 6: Indexes
- What is an Index and how oracle uses it.
- B-Tree Index Structure – Root, Branch, Lead blocks
- Unique Index
- Non-Unique Index
- Composite Index
- Function-Based Index
- Reverse Key Index
- Invisible index
- BitMap Index
- Automatic Indexing in Oracle 19c
- Index Design and Selection
- Index Maintenance and Management
Section 7: Oracle Hints
- Need of Hints
- How it Influence Optimizer
- Need to Influence the optimizer
- Techniques of Influencing the Optimizer
- Types of Hints –
- Optimizer Goal
- Table and Index Access Paths
- Join Order
- Joins
- SQL Block Name
Section 8 : Optimizer Statistics
- Optimizer Statistics Concept
- Gathering Optimizer Statistics
- Setting Optimizer statistics Preferences
- Data Skew Histogram
- Types of Histogram
- Extended Statistics
- Managing Optimizer statistics
- Managing Historical Optimizer Statistics
- Optimizer Statistics Advisor
Section 9 : Adaptive Query Optimization
- Adaptive Plans
- Adaptive Statistics
- Adaptive Optimizer Features
- Statistics Feedback
- Dynamic Statistics
- SQL Plan Directives ( SPD )
Section 10 : Cursor Sharing
- Improving Performance through Cursor Sharing
- Identical Statements
- Different Statements
- Cursor Sharing Impact
- Resolving Cursor with Literal Issue
- CURSOR_SHARING Parameter
- Bind Peeking , Bind Sensitivity , Bind Aware
- Adaptive Cursor Sharing
Section 11: Monitoring and Tracing SQL
- Monitoring Database Operations in Real-time using DBMS_MONITOR and DBMS_SQL_MONITOR
- Tracing SQL statements using DBMS_MONITOR
- TKPROF Utility
- More SQL Tracing Methods , Tracing by Enabling Events
Section 12: SQL Tuning Sets
- Managing SQL Tuning Sets( STS )
Section 13: SQL Tuning Advisor
- Using SQL Tuning Advisor – Automatic Mode
- Using SQL Tuning Advisor – Manual Mode
- Managing SQL_PROFILER
Section 14: SQL Plan Management
- SQL Plan Baselines
- Using SQL Outlines and Migrating them to SQL Plan Baselines
- SQL Management Base (SMB)
Section 15: SQL Access Advisor
- What is SQL Access Advisor
- How to User it.
- Difference between SQL Access Advisor and SQL Tuning Advisor
Section 16: Server Result Cache
- Using Server Result Cache
- Memory Configuration
Section 17: More SQL Tuning Tools
- SQL Performance Analyzer
- SQL Tuning Health – Check scripts (SQLHC)
Section 18: Partitioning in Oracle Database
- What is Partitioning and why it improves SQL performance
- Partition Pruning (Static vs Dynamic)
- Local vs Global Indexes
- Impact of partitioning on execution plans
- Types of Partitioning
- Range Partitioning
- List Partitioning
- Hash Partitioning
- Composite Partitioning
- Range-Hash
- Range-List
- List-Hash
- Interval Partitioning
- Partitioning Enhancements in 19c
- Automatic interval partition maintenance
- Improved partition pruning