Oracle Database 12c: SQL Tuning Ed.2
(Government of India Approved Education Center – SQL StarOracle Authorized Training in Chennai Institute – ISQL Global)
(The Course Materials and Course Completion Certificates are issued from Oracle University to those required for Foreign Opportunity)
Oracle Database 12c R2: SQL Tuning for Developers Ed 2
In this course, students learn about Oracle SQL tuning and how to apply tuning techniques to their SQL code. This course assists database developers, DBAs, and SQL
developers to identify and tune inefficient SQL. It covers investigative methods that reveal varying levels of detail about how
the Oracle database executes a SQL statement. Students learn the different ways in which data can be accessed, and how to determine if
those are the most efficient ways is a particular database. Student will learn how to ensure that the best method is used.
In this
application development-centric course, students learn about Oracle SQL statement tuning and how to write well-tuned SQL statements
appropriate for the Oracle database. Students learn to interpret execution plans and the different ways in which data can be
accessed. Students are shown how to decipher, decide and then apply tuning to their SQL code. Various tuning techniques are
demonstrated. For example, taking advantage of bind variables, trace files, and using the different types of indexes.
Learn To:- Use Oracletools to identifyinefficient SQL statements.
- UseAutomatic SQL Tuning.
- Use RealTime SQL monitoring.
- Write moreefficient SQL statements.
- Monitorand trace high load SQL statements.
- Manageoptimizer statistics on database objects.
- Understand the optimizer process steps and operators.
- Interpretexecution plans.
- Performapplication tracing.
Benefits To You:
Benefit from gaining a deeper understanding of Oracle SQL statement tuning and how to write well-tuned SQL statements appropriate for the Oracle
database in this application development-centric course. You will learn how to decipher, decide and then apply tuning to your SQL
code. Various tuning techniques are demonstrated.
Audience
Course
Objectives - Modifying a SQL statement to perform at its best
- Identifyingpoorly performing SQL
- Tracing an application through its different levels of the applicationarchitecture
- Understandinghow the Query Optimizer makes decisions about how to access data
- Defininghow optimizer statistics affect the performance of SQL
- Listing the possible methods of accessing data, including different joinmethods
Course Topics
Introduction
- Course Objectives, Course Agenda and Appendixes Used in this Course
- Audienceand Prerequisites
- SampleSchemas Used in the Course
- ClassAccount Information
- SQLEnvironments Available in the Course
- Workshops,Demo Scripts, and Code Example Scripts
- Appendicesin the Course
Introduction
to SQL Tuning- SQL Tuning Session
- SQWorkshopsSQLPlan ManagementUsing Bind VariablesIntroduction to OptimizerStatistics ConceptsOther Optimizer OperatorsOptimizer JoinOperationsInterpreting Execution Plans and EnhancementsGenerating and Displaying Execution PlansOptimizer FundamentalsUnderstandingBasic Tuning TechniquesUsing Application Tracing ToolsL Tuning Strategies
- DevelopmentEnvironments: Overview
- SQLTXPLAIN(SQLT) Diagnostic Tool
Using
Application Tracing Tools- Using theSQL Trace Facility: Overview
- StepsNeeded Before Tracing
- AvailableTracing Tools: Overview
- The trcsess Utility
- Formatting SQL Trace Files: Overview
Understanding
Basic Tuning Techniques- Developing Efficient SQL statement
- ScriptsUsed in This Lesson
- TableDesign
- Index Usage
- Transformed Index
- Data TypeMismatch
- NULL usage
- Tune the ORDER BY Clause
Optimizer
Fundamentals- SQLStatement Representation
- SQLStatement Processing
- Why Do YouNeed an Optimizer?
- Componentsof the Optimizer
- QueryTransformer
- Cost-BasedOptimizer
- AdaptiveQuery Optimization
- OptimizerFeatures and Oracle Database Releases
Generating
and Displaying Execution Plans- ExecutionPlan?
- The EXPLAIN PLAN Command
- Plan Table
- AUTOTRACE
- V$SQL_PLANView
- AutomaticWorkload Repository
- SQLMonitoring
- DBML_SQL_MONITOR
Interpreting
Execution Plans and Enhancements- Interpreting a Serial Execution Plan
- AdaptiveOptimizations
- Optimizer:Table and Index Access Paths
- Row SourceOperations
- MainStructures and Access Paths
- Full TableScan
- Indexes
- CommonObservations
Optimizer
Join OperationsOther
Optimizer Operators- SQLoperators
- OtherN-Array Operations
- ResultCache operators
Introduction
to Optimizer Statistics Concepts- OptimizerStatistics
- Types ofOptimizer Statistics
- Gather andManage Optimizer Statistics: Overview
Using Bind Variables- CursorSharing and Different Literal Values
- CursorSharing and Bind Variables
SQL Plan
Management- Maintaining SQL Performance
- SQL PlanManagement
Workshops- Workshop 1
- Workshop 2
- Workshop 3
- Workshop 4
- Workshop 5
- Workshop 6 & 7
- Workshop 8
- Workshop 9