COURSE OBJECTIVE:
Course Objectives The objective of the Oracle Database 19c DBA Performance Tuning & Management course is to provide the skills needed to monitor and tune an Oracle Database.
TARGET AUDIENCE:
Who will the Course Benefit? The Oracle Database 19c DBA Performance Tuning & Management course is suitable for database administrators and technical support staff who are required to monitor and tune an Oracle Database.
COURSE PREREQUISITES:
Requirements Delegates who wish to attend the Oracle Database 19c DBA Performance Tuning & Management course should have practical knowledge of using SQL and administering an Oracle database. They should have attended the Oracle SQL and the Oracle Database 19c Administration courses or have a good working knowledge of Oracle SQL and Oracle database administration. The ability to describe and use Oracle built-in packages would be highly advantageous but is not essential. This course is run on a Linux operating system,a basic knowledge of Linux/UNIX is recommended but is not essential. Where Oracle 19c courses are listed in pre-requisites or follow-on courses the equivalent Oracle 18c or Oracle 12cR2 courses would also suffice.
COURSE CONTENT:
Oracle Database 19c DBA Performance Tuning & Management Training Course Course Contents – DAY 1 Course Introduction • Administration and Course Materials • Course Structure and Agenda • Delegate and Trainer Introductions Session 1: INTRODUCTION TO ORACLE PERFORMANCE TUNING • Tuning Overview of Oracle Database Tuning • Application Developer Tuning Responsibilities • Oracle DBA Tuning Responsibilities • Oracle Tuning Process • Plan a Routine Monitoring Regime • Setting Suitable Goals • Syntax Considerations Session 2: TOOLS FOR EVALUATING SQL STATEMENTS • Overview of SQL Statement Tuning • Tools to Assist in SQL Tuning • Use Explain Plan,Autotrace and SQL Trace to Examine the Execution of a SQL Statement • Interpreting a SQL Trace Session 3: THE SQL OPTIMIZER • The SQL Optimizer • Statement Transformation • The Optimizer_Mode Initialization Parameter • Cost Based Optimizer • Managing Statistics with DBMS_STATS • Automatic Statistics Gathering • Dynamic Statistics • Adaptive Optimization • Transferring Statistics between Databases Session 4: SORTS • How Oracle Processes Sorts • Temporary Disk Space Assignment • SQL Operations that Use Sorts Session 5: INDEXES • Index Overview • Selecting Suitable Columns for an Index • B*Tree Indexes • Rebuild an Index • Create Multiple Indexes on the Same Column • Composite Indexes • Descending Indexes • Access Paths with Indexes • Index Scans • Conditions That Stop Indexes Being Used • Parameters that Affect Optimizer Index Choice Oracle Database 19c DBA Performance Tuning & Management Training Course Course Contents – DAY 2 Session 6: ADVANCED INDEXES • Bitmap Indexes • Key Compressed Indexes • Index Organized Tables • Function Based Indexes • Invisible Indexes • Table Partitioning • Serial Direct Path Reads Session 7: JOIN OPERATIONS • Understand Access Paths • Joining Tables • Nested Loops Join • Merge Join • Cluster Join • Hash Join • Anti Join and Semi Join • Outer Joins • Star Join • Improve Optimization with Different Access Paths Session 8: SQL TUNING ADVISOR USING SQL DEVELOPER • Overview of the DBMS_SQLTUNE Package • Using the SQL Tuning Advisor with SQL Developer Session 9: SEQUENCES AND VIEWS • Sequence Caching • Views • View Merging • Inline Views • The WITH Clause Session 10: USING HINTS • Using Hints to Influence Execution Plan • Optimization Mode and Goals • Access Methods • Query Transformations • Join Orders • Join Operations • Hint Examples Session 11: MISCELLANEOUS • Tips for Avoiding Problematic Queries • Oracle 12.2 SQL*Plus Performance Setting Options • Array Size • The Shared Pool • Intelligent Cursor Sharing • Virtual Columns • Bind Variable Usage • Result Caching • Approximate Query Processing • Reduce Cursor Invalidations for DDLs • Some PL/SQL Performance Issues Oracle Database 19c DBA Performance Tuning & Management Training Course Course Contents – DAY 3 Session 12: BASIC TUNING DIAGNOSTICS • Performance Tuning Diagnostics,Features,and Tools • DB Time • CPU and Wait Time Tuning Dimensions • Time Model • Dynamic Performance Views • Database Statistics • Wait Events • Diagnostic Sources • Log Files and Trace Files Session 13: REDUCE THE COST OF SQL OPERATIONS • Identify Unusable Objects • Maintain Indexes • Maintain Tables and Reorganize Tables • Manage Extents • Row Chaining and Row Migration • Segment Shrink Session 14: THE SQL PERFORMANCE ANALYZER • An Overview of the SQL Performance Analyzer • Usage of the SQL Performance Analyzer • Capture a SQL Workload into a SQL Tuning Set • Create a SQL Performance Analyzer Task • Generate Comparison Reports • Configuring Analysis Tasks • Transfer SQL Tuning Sets Session 15: SQL PERFORMANCE MANAGEMENT • Maintenance of the Optimizer Statistics • Optimizer Statistics Collection • Gather Statistics Options • Defer Publishing Statistics • The Optimizer Statistics Advisor • The Expression Statistics Store • Adaptive Query Optimization • Automatic SQL Tuning • SQL Monitoring • The SQL Access Advisor • SQL Plan Management Session 16: AUTOMATIC MEMORY MANAGEMENT • Overview of Automatic Shared Memory Management • Dynamic SGA • Parameters for Sizing the SGA • Enable and Disable Automatic Shared Memory Management • Overview of Automatic Memory Management • Enable and Disable Automatic Memory Management • Monitor Dynamic Memory Allocation • Use the Memory Advisors Session 17: TUNE THE SHARED POOL • Overview of the Shared Pool Architecture • Tune the Shared Pool • Tune the Data Dictionary Cache • Tune the Library Cache • Pin objects in the Shared Pool • The Data Dictionary Cache • Latches and Mutexes • The SQL Query Result Cache Oracle Database 19c DBA Performance Tuning & Management Training Course Course Contents – DAY 4 Session 18: TUNE THE BUFFER CACHE • Overview of the Database Buffer Cache Architecture • Buffer Cache Tuning Goals and Techniques • Buffer Cache Performance Symptoms and Solutions • Buffer Cache Advisor • Database Smart Flash Cache • Full Database Caching • When to Flush the Buffer Cache Session 19: TUNE PGA AND TEMPORARY SPACE • Overview of the PGA • SQL Memory Usage • Automatic PGA Memory Mode Configuration • Configure the PGA for a New Instance • Data Dictionary Views and PGA Management • PGA Target Advice Statistics and Histograms • Temporary Tablespace Management • Temporary Tablespace Group • Multiple Temporary Tablespaces • Monitoring Temporary Tablespaces • Temporary Tablespace Shrink • Data Dictionary Views and Sort Segments Session 20: CREATE AND USE SNAPSHOTS AND BASELINES WITH THE AUTOMATIC WORKLOAD REPOSITORY • An Overview of In-Built Automatic Tuning Capabilities • An Overview of the Automatic Workload Repository • AWR Data • Create and Compare Snapshots • Examine AWR Reports • Static and Moving Window Baselines • Baseline Templates • AWR Reports • Monitor AWR using SQL Developer • Performance Hub Active Report Session 21: USE AWR-BASED TOOLS • Automatic Maintenance Tasks • Maintenance Window Configuration • ADDM Performance Monitoring • ADDM Reports • Active Session History • Generate an ASH Report Session 22: USE METRICS AND ALERTS • An Overview of Metrics and Alerts • The Benefits and Limitations of Metrics and Alerts • System Generated,Threshold Generated and Event Based Alerts • Set Thresholds • View Metric History Information • View Histograms • Metric and Alert Views Oracle Database 19c DBA Performance Tuning & Management Training Course Course Contents – DAY 5 Session 23: REAL TIME DATABASE OPERATION MONITORING • Overview of Real Time Database Operation Monitoring • Database Operation Concepts • Define a Database Operation • Enable the Monitoring of Database Operations • Identify,Start and Complete a Database Operation • Monitor the Progress of a Database Operation • Database Operation Views Session 24: MONITOR APPLICATIONS • Aggregation of Services with Tracing • The DBMS_Monitor Package • Enable Tracing for a Client • Enable Session Tracing • Enable Tracing for a Module • Analyze Trace Results Session 25: BIG DATA AND DATA WAREHOUSE FEATURES • Reduce Cursor Invalidations For DDLS • Automatic Indexing with DBMS_AUTO_INDEX • Real Time Statistics Gathering During DML Operations • High Frequency Statistics Gathering • Advanced Index Compression • Basic and Advanced Table Compression • Quarantine of Runaway SQL Statements • Bitmap-Based COUNT(DISTINCT) SQL Functions • Scaleable Sequences Session 26: TUNE DATABASE I/O • An Overview of Database I/O Management • I/O Architecture and Modes •
Important I/O Metrics for Oracle Databases • Layout Files using Operating System or Hardware Striping • Manually Distribute Files to Reduce I/O Contention • Sample Configurations • Asynchronous and Synchronous I/O • Multi-Threaded Oracle • Automatic Storage Management(ASM)Overview Session 27: A SUMMARY OF ORACLE PERFORMANCE TUNING • The Potential Impact of Initialization Parameters on Performance • Initially Size Memory for a Database • Recommended Best Practices for Different Types of Tablespaces • Determine and Use Block Sizes • Size the Redo Log Buffer and the Redo Log Files • Configure Automatic Statistics Gathering
FOLLOW ON COURSES:
Further Learning Oracle Database 19c Backup and Recovery with RMAN Oracle Database 19c Data Guard Oracle Database 19c RAC and Grid Infrastructure Administration Oracle Multitenant Administration