During the course Oracle Database 12c R2 – SQL Tuning for Developers Ed 2, focused on application development, participants learn about tuning SQL statements Oracle and how to write efficient, database-appropriate SQL statements Oracle.
Participants learn to interpret execution plans and the different ways data can be accessed. They are shown how to decipher, decide and then apply tuning to SQL code. Various tuning techniques are demonstrated.
This course will be supported by practical laboratory exercises.
- Administrators
- Developers
- Data Warehouse Developers
- PL/SQL Developers
- Application Developers
- Developers
- Support Engineers
Participants in this course will learn to:
• Use the tools Oracle to identify SQL inefficiencies.
• Use automatic SQL tuning.
• Use real-time SQL monitoring.
• Write more efficient SQL statements.
• Monitor and track high load SQL statements.
• Manage optimizer statistics for database objects.
• Understand optimization process steps and operators.
• Interpret execution plans.
• Perform application tracking.
• Modify an SQL statement to give the best performance
• Identifies poorly performing SQL
• Trace an application through the various application architecture levels
• Understand how the query optimizer makes decisions about how to access data
• Define how optimizer statistics affect SQL performance
• Identifies the possible methods of accessing the data, including different association methods
- Familiarization with the database architecture;
- Knowledge of SQL and PL/SQL.
Introduction
- Course Objectives, Course Agenda and Appendices Used in this Course
- Audience and Requirements
- Sample Schemas Used in the Course
- Class Account Information
- SQL Environments Available in the Course
- Workshops, Demo Scripts, and Code Example Scripts
- Appendices in the Course
Introduction to SQL Tuning
- SQL Tuning Session
- SQL Tuning Strategies
- Development Environments: Overview
- SQLTXPLAIN (SQLT) Diagnostic Tool
Using Application Tracing Tools
- Using the SQL Trace Facility: Overview
- Steps Needed Before Tracing
- Available Tracing Tools: Overview
- The trcsess Utility
- Formatting SQL Trace Files: Overview
Understanding Basic Tuning Techniques
- Developing Efficient SQL statements
- Scripts Used in This Lesson
- board designer
- Usage Index
- Transformed Index
- Date Type Mismatch
- NULL use
- Tune the ORDER BY Clause
Optimizer Fundamentals
- SQL Statement Representation
- SQL Statement Processing
- Why Do You Need an Optimizer?
- Components of the Optimizer
- Query Transformer
- Cost-Based Optimizer
- Adaptive Query Optimization
- Optimizer Features and Oracle Database Releases
Generating and Displaying Execution Plans
- Execution Plan?
- The EXPLAIN PLAN Command
- Table Plan
- AutoTrac
- V$SQL_PLAN View
- Automatic Workload Repository
- SQL Monitoring
- DBML_SQL_MONITOR
Interpreting Execution Plans and Enhancements
- Interpreting a Serial Execution Plan
- Adaptive Optimizations
- Optimizer: Table and Index Access Paths
- Row Source Operations
- Main Structures and Access Paths
- Full Table Scan
- Indexes
- Common Observations
Optimizer Join Operations
- Join Methods
- Join Types
Other Optimizer Operators
- SQL operators
- Other N-Array Operations
- Result Cache operators
Introduction to Optimizer Statistics Concepts
- Statistics Optimizer
- Types of Optimizer Statistics
- Gather and Manage Optimizer Statistics: Overview
Using Bind Variables
- Cursor Sharing and Different Literal Values
- Cursor Sharing and Bind Variables
SQL Plan Management
- Maintaining SQL Performance
- SQL Plan Management
Workshops
There are no recommended courses after completing this course.
Oracle Database 12c R2 – SQL Tuning for Developers Ed 2
