Oracle Database 19c: SQL Tuning Workshop Live Class

• 1 Course Introduction
• Lesson Agenda
• Course Objectives
• Audience and Prerequisites
• Course Outline Map
• Activities
• Lesson Agenda
• Sample Schemas Used in the Course
• Human Resources (HR) Schema
• Sales History (SH) Schema
• Order Entry(OE) Schema
• Lesson Agenda
• Class Account Information
• Lesson Agenda
• SQL Environments Available in the Course
• Lesson Agenda
• Workshops, Demo Scripts, Code Examples, and Solution Scripts
• Appendixes in the Course
• 2 Introduction to SQL Tuning
• Objectives
• Lesson Agenda
• What Is SQL Tuning?
• SQL Tuning Session
• Recognize: What Is Bad SQL?
• Clarify: Understand the Current Issue
• Verify: Collect Data
• Verify: Is Bad SQL a Real Problem? (Top-Down Analysis)
• Lesson Agenda
• SQL Tuning Strategies: Overview
• Checking the Basics
• Advanced SQL Tuning Analysis
• Plan Comparison Strategy
• Quick Solution Strategy
• Finding a Good Plan
• iii
• Implementing the New Good Plan
• Query Analysis Strategy: Overview
• Query Analysis Strategy: Collecting Data
• Query Analysis Strategy: Examining SQL Statements
• Query Analysis Strategy: Analyzing Execution Plans
• Query Analysis Strategy: Finding Execution Plans
• Query Analysis Strategy: Reviewing Common Observations and Causes
• Query Analysis Strategy: Determining Solutions
• Lesson Agenda
• Development Environments: Overview
• What Is Oracle SQL Developer?
• Coding PL/SQL in Oracle SQL*Plus
• Quiz
• Summary
• Practice 2: Overview
• 3 Using Application Tracing Tools
• Objectives
• Lesson Agenda
• Using Application Tracing: Overview
• Steps Needed Before Tracing
• Lesson Agenda
• Application Tracing Tools: Overview
• Lesson Agenda
• Using the SQL Tracing Facility
• Tracing Your Own Session: Example
• Tracing a Specific User: Example
• Consideration: Tracing Challenge
• Lesson Agenda
• End-to-End Application Tracing
• Service Tracing: Example
• Module Tracing: Example
• Action Tracing: Example
• Client Tracing: Example
• Session Tracing: Example
• Tracing Your Own Session: Example
• Lesson Agenda
• trcsess Utility
• Invoking the trcsess Utility
• Using the trcsess Utility: Example
• Lesson Agenda
• iv
• TKPROF Utility: Overview
• Invoking the TKPROF Utility
• TKPROF Sorting Options
• TKPROF Report Structure
• Interpreting a TKPROF Report: Example 1
• Interpreting a TKPROF Report: Example 2
• Interpreting a TKPROF Report: Example 3
• What to Verify: Example
• Quiz
• Summary
• Practice 3: Overview
• 4 Optimizer Fundamentals
• Objectives
• Lesson Agenda
• SQL Statement Representation
• Lesson Agenda
• SQL Statement Processing: Overview
• SQL Statement Parsing
• SQL Optimization
• SQL Row Source Generation
• SQL Row Source Generation: Example
• SQL Execution
• Quiz
• Lesson Agenda
• Why Do You Need an Optimizer?
• Components of the Optimizer
• Lesson Agenda
• Query Transformer
• Transformer: Cost-Based OR Expansion Example
• Transformer: Subquery Unnesting Example
• Transformer: View Merging Example
• Transformer: Predicate Pushing Example
• Transformer: Transitivity Example
• Cursor-Duration Temporary Tables
• Hints for Query Transformation
• Quiz
• Lesson Agenda
• Query Estimator: Selectivity and Cardinality
• Importance of Selectivity and Cardinality
• Selectivity and Cardinality: Example
• v
• Query Estimator: Cost
• Query Estimator: Cost Components
• Lesson Agenda
• Plan Generator
• Lesson Agenda
• Adaptive Query Optimization
• Lesson Agenda
• Quarantined SQL Plans
• Lesson Agenda
• Controlling the Behavior of the Optimizer
• Optimizer Features and Oracle Database Releases
• Summary
• Practice 4: Overview
• 5 Generating and Displaying Execution Plans
• Objectives
• Lesson Agenda
• What Is an Execution Plan?
• Reading an Execution Plan
• Reviewing the Execution Plan
• Where to Find Execution Plans
• Viewing Execution Plans
• Lesson Agenda
• The EXPLAIN PLAN Command: Overview
• The EXPLAIN PLAN Command: Syntax
• The EXPLAIN PLAN Command: Example
• Lesson Agenda
• PLAN_TABLE
• Displaying from PLAN_TABLE
• Displaying from PLAN_TABLE: ALL
• Displaying from PLAN_TABLE: ADVANCED
• Explain Plan Using Oracle SQL Developer
• Quiz
• Lesson Agenda
• AUTOTRACE
• The AUTOTRACE Syntax
• AUTOTRACE: Examples
• AUTOTRACE: Statistics
• AUTOTRACE by Using SQL Developer
• Quiz
• Lesson Agenda
• vi
• Links Between Important Dynamic Performance Views
• V$SQL_PLAN View: Overview
• V$SQL_PLAN Columns
• The V$SQL_PLAN_STATISTICS View
• Querying V$SQL_PLAN
• Hint Usage Reporting
• Lesson Agenda
• Automatic Workload Repository
• Important AWR Views
• Comparing Execution Plans by Using AWR
• Comparing SQL Execution Plans
• Generating SQL Reports from AWR Data
• Lesson Agenda
• SQL Monitoring: Overview
• SQL Monitoring Report: Example
• Quiz
• Summary
• Practice 5: Overview
• 6 Interpreting Execution Plans and Enhancements
• Objectives
• Lesson Agenda
• Interpreting a Serial Execution Plan
• Execution Plan Interpretation: Example 1
• Execution Plan Interpretation: Example 2
• Execution Plan Interpretation: Example 3
• Execution Plan Interpretation: Example 4
• Lesson Agenda
• Looking Beyond Execution Plans
• Quiz
• Lesson Agenda
• Adaptive Query Optimization: Overview
• Adaptive Plans: Join Method
• Adaptive Join Method: Example
• Adaptive Join Method: Working
• Displaying the Default Plan
• Displaying the Final Plan
• Displaying the Full Adaptive Plan
• Adaptive Plans: Indicator in V$SQL
• Summary
• Practice 6: Overview
• vii
• 7 Optimizer: Table and Index Access Paths
• Objectives
• Lesson Agenda
• Row Source Operations
• Lesson Agenda
• Main Structures and Access Paths
• Lesson Agenda
• Full Table Scan
• Using Full Table Scan
• ROWID Scan
• Sample Table Scans
• Quiz
• Lesson Agenda
• Indexes: Overview
• Normal B*-tree Indexes
• Index Scans
• Index Unique Scan
• Index Range Scan
• Index Range Scan: Descending
• Descending Index Range Scan
• Index Range Scan: Function-Based
• Index Full Scan
• Index Fast Full Scan
• Index Skip Scan
• Index Join Scan
• Index Skip Scan: Example
• B*-tree Indexes and Nulls
• Using Indexes: Considering Nullable Columns
• Index-Organized Tables
• Index-Organized Table Scans
• Bitmap Indexes
• Bitmap Index Access: Examples
• Combining Bitmap Indexes: Examples
• Combining Bitmap Index Access Paths
• Bitmap Operations
• Bitmap Join Index
• Composite Indexes
• Invisible Index: Overview
• Invisible Indexes: Examples
• Guidelines for Managing Indexes
• Quiz
• viii
• Lesson Agenda
• Common Observations
• Why Is a Full Table Scan Used?
• Why Is a Full Table Scan Not Used?
• Why Is an Index Scan Not Used?
• Lesson Agenda
• Automatic Indexing Task
• Workflow
• Automatic Indexing Task Reporting
• Automatic Indexing Views
• Summary
• Practice 7: Overview
• 8 Optimizer: Join Operators
• Objectives
• Lesson Agenda
• How the Query Optimizer Executes Join Statements
• Join Methods
• Nested Loops Join
• Nested Loops Join: Prefetching
• Sort-Merge Join
• Hash Join
• Cartesian Join
• Lesson Agenda
• Join Types
• Equijoins and Nonequijoins
• Outer Joins
• Semijoins
• Antijoins
• Quiz
• Summary
• Practice 8: Overview
• 9 Other Optimizer Operators
• Objectives
• Lesson Agenda
• Result Cache Operator
• Using RESULT_CACHE
• Using Result Cache Table Annotations
• Lesson Agenda
• Clusters
• ix
• When Are Clusters Useful?
• Cluster Access Path: Examples
• Lesson Agenda
• Sorting Operators
• Lesson Agenda
• Buffer Sort Operator
• Lesson Agenda
• Inlist Iterator
• Lesson Agenda
• View Operator
• Lesson Agenda
• Count Stop Key Operator
• Lesson Agenda
• Min/Max and First Row Operators
• Lesson Agenda
• Other N-Array Operations
• FILTER Operations
• OR Expansion Operation
• UNION [ALL], INTERSECT, MINUS
• Quiz
• Summary
• Practice 09: Overview
• 10 Introduction to Optimizer Statistics Concepts
• Objectives
• Lesson Agenda
• Optimizer Statistics
• Table Statistics(USER_TAB_STATISTICS)
• Index Statistics(USER_IND_STATISTICS)
• Index Clustering Factor
• Column Statistics (USER_TAB_COL_STATISTICS)
• Lesson Agenda
• Column Statistics: Histograms
• Frequency Histograms
• Viewing Frequency Histograms
• Top Frequency Histogram
• Viewing Top Frequency Histograms
• Height-Balanced Histograms
• Viewing Height-Balanced Histograms
• Hybrid Histograms
• Viewing Hybrid Histograms
• x
• Best Practices: Histograms
• Lesson Agenda
• Column Statistics: Extended Statistics
• Column Group Statistics
• Expression Statistics
• Lesson Agenda
• Session-Specific Statistics for Global Temporary Tables
• Session-Specific Statistics for Global Temporary Tables: Example
• Lesson Agenda
• System Statistics
• System Statistics: Example
• Lesson Agenda
• Gathering Statistics: Overview
• Manual Statistics Gathering
• When to Gather Statistics Manually
• Managing Statistics: Overview (Export/Import/Lock/Restore/Publish)
• Lesson Agenda
• Real-Time Statistics
• Quiz
• Summary
• Practice 10: Overview
• 11 Using Bind Variables
• Objectives
• Lesson Agenda
• Cursor Sharing and Different Literal Values
• Lesson Agenda
• Cursor Sharing and Bind Variables
• Bind Variables in SQL*Plus
• Bind Variables in Oracle SQL Developer
• Lesson Agenda
• Bind Variable Peeking
• Lesson Agenda
• Cursor Sharing Enhancements
• CURSOR_SHARING Parameter
• Forcing Cursor Sharing: Example
• Lesson Agenda
• Adaptive Cursor Sharing: Overview
• Adaptive Cursor Sharing: Architecture
• Adaptive Cursor Sharing: Views
• Adaptive Cursor Sharing: Example
• xi
• Interacting with Adaptive Cursor Sharing
• Quiz
• Summary
• Practice 11: Overview
• 12 SQL Plan Management
• Objectives
• Lesson Agenda
• Maintaining SQL Performance
• Lesson Agenda
• SQL Plan Management: Overview
• Components of SQL Plan Management
• SQL Plan Baseline: Architecture
• Lesson Agenda
• Basic Tasks in SQL Plan Management
• Configuring SQL Plan Management
• Loading SQL Plan Baselines
• SQL Plan Selection
• Evolving SQL Plan Baselines
• Lesson Agenda
• Adaptive SQL Plan Management
• Managing the SPM Evolve Advisor Task
• Important SQL Plan Baseline Attributes
• Lesson Agenda
• Possible SQL Plan Manageability Scenarios
• SQL Performance Analyzer and SQL Plan Baseline Scenario
• Loading a SQL Plan Baseline Automatically
• Purging SQL Management Base Policy
• Lesson Agenda
• Loading Hinted Plans into SPM: Example
• Quiz
• Summary
• Practice 12: Overview
• 13 Workshops
• Objectives
• Overview
• Workshop 1
• Workshop 2
• Workshop 3
• Workshop 4
• xii
• Workshop 5
• Workshop 6 and 7 (Optional)
• Workshop 8
• Workshop 9
• Summary
• A Using SQL Developer
• Objectives A-2
• What Is Oracle SQL Developer? A-3
• SQL Developer: Specifications A-4
• SQL Developer 19.1 Interface A-5
• Creating a Database Connection A-7
• Browsing Database Objects A-10
• Displaying the Table Structure A-11
• Browsing Files A-12
• Creating a Schema Object A-13
• Creating a New Table: Example A-14
• Using the SQL Worksheet A-15
• Executing SQL Statements A-18
• Saving SQL Scripts A-19
• Executing Saved Script Files: Method 1 A-20
• Executing Saved Script Files: Method 2 A-21
• Formatting the SQL Code A-22
• Using Snippets A-23
• Using Snippets: Example A-24
• Using the Recycle Bin A-25
• Debugging Procedures and Functions A-26
• Database Reporting A-27
• Creating a User-Defined Report A-28
• Search Engines and External Tools A-29
• Setting Preferences A-30
• Resetting the SQL Developer Layout A-32
• Data Modeler in SQL Developer A-33
• Summary A-34
• B SQL Tuning Advisor
• Objectives B-2
• Tuning SQL Statements Automatically B-3
• Application Tuning Challenges B-4
• SQL Tuning Advisor: Overview B-5
• Stale or Missing Object Statistics B-6
• xiii
• SQL Statement Profiling B-7
• Plan Tuning Flow and SQL Profile Creation B-8
• SQL Tuning Loop B-9
• Access Path Analysis B-10
• SQL Structure Analysis B-11
• SQL Tuning Advisor: Usage Model B-12
• Cloud Control and SQL Tuning Advisor B-13
• Running SQL Tuning Advisor: Example B-14
• Schedule SQL Tuning Advisor Page B-15
• Implementing Recommendations B-16
• Compare Explain Plan Page B-17
• Quiz B-18
• Summary B-20
• C Using SQL Access Advisor
• Objectives C-2
• SQL Access Advisor: Overview C-3
• SQL Access Advisor: Usage Model C-4
• Recommendations C-5
• SQL Access Advisor Session: Initial Options C-6
• SQL Access Advisor: Workload Source C-8
• SQL Access Advisor: Recommendation Options C-9
• SQL Access Advisor: Schedule and Review C-10
• SQL Access Advisor: Results C-11
• SQL Access Advisor: Results and Implementation C-12
• Quiz C-13
• Summary C-15
• D Exploring the Oracle Database Architecture
• Objectives D-2
• Oracle Database Server Architecture: Overview D-3
• Connecting to the Database Instance D-4
• Oracle Database Memory Structures: Overview D-6
• Database Buffer Cache D-7
• Redo Log Buffer D-8
• Shared Pool D-9
• Processing a DML Statement: Example D-10
• COMMIT Processing: Example D-11
• Large Pool D-12
• Java Pool and Streams Pool D-13
• Program Global Area D-14
• xiv
• Background Process D-15
• Automatic Shared Memory Management D-16
• Automated SQL Execution Memory Management D-17
• Automatic Memory Management D-18
• Database Storage Architecture D-19
• Logical and Physical Database Structures D-21
• Segments, Extents, and Blocks D-23
• SYSTEM and SYSAUX Tablespaces D-24
• Quiz D-25
• Summary D-28
• E Real-Time Database Operation Monitoring
• Objectives E-2
• Real-Time Database Operation Monitoring: Overview E-3
• Use Cases E-4
• Current Tools E-5
• Defining a DB Operation E-6
• Scope of a Composite DB Operation E-7
• Database Operation Concepts E-8
• Identifying a Database Operation E-9
• Enabling Monitoring of Database Operations E-10
• Identifying, Starting, and Completing a Database Operation E-11
• Monitoring the Progress of a Database Operation E-12
• Monitoring Load Database Operations E-13
• Monitoring Load Database Operation Details E-14
• Reporting Database Operations by Using Views E-15
• Reporting Database Operations by Using Functions E-17
• Database Operation Tuning E-18
• Quiz E-19
• Summary E-21
• F Gathering and Managing Optimizer Statistics
• Objectives F-2
• Lesson Agenda F-3
• Gathering Statistics: Overview F-4
• Automatic Optimizer Statistics Gathering F-5
• Optimizer Statistic Preferences: Overview F-6
• Manual Statistics Gathering
• F-8
• When to Gather Statistics Manually F-9
• Manual Statistics Collection: Factors F-10
• Gathering Object Statistics: Example F-11
• xv
• Object Statistics: Best Practices F-12
• Lesson Agenda F-13
• Dynamic Statistics: Overview F-14
• Dynamic Statistics at Work F-15
• OPTIMIZER_DYNAMIC_SAMPLING F-17
• Lesson Agenda F-18
• Automatic Re-optimization F-19
• Re-optimization: Statistics Feedback F-20
• Statistics Feedback: Monitoring Query Executions F-21
• Statistics Feedback: Reparsing Statements F-22
• Lesson Agenda F-23
• SQL Plan Directives F-24
• SQL Plan Directives: Example F-28
• Lesson Agenda F-29
• Online Statistics Gathering for Bulk Loads F-30
• Lesson Agenda F-32
• Concurrent Statistics Gathering F-33
• Concurrent Statistics Gathering: Creating Jobs at Different Levels F-34
• Lesson Agenda F-35
• Gathering System Statistics: Automatic Collection – Example F-36
• Gathering System Statistics: Manual Collection – Example F-37
• Lesson Agenda F-38

Managing Statistics: Overview (Export / Import / Lock / Restore / Publish) F-39

• Exporting and Importing Statistics F-40
• Locking and Unlocking Statistics F-41
• Restoring Statistics F-42
• Deferred Statistics Publishing: Overview F-44
• Deferred Statistics Publishing: Example F-46
• Running Statistics Gathering Functions in Reporting Mode F-47
• Reporting on Past Statistics Gathering Operations F-48
• Managing SQL Plan Directives F-49
• Quiz F-50
• Summary F-53