Oracle Database 11g SQL Tuning Workshop Training & Placements in Chennai "ISQL GLOBAL"
Oracle Database 11g: SQL Tuning Workshop
(The Course Materials and Course Completion Certificates are directly delivered from Oracle University to those seeking for Foreign
Opportunity)
(Government of India Approved Education Center)
This Database 11g SQL Tuning Workshop training teaches database developers, DBAs and SQL developers to identify and tune inefficient SQL statements. Learn how to investigative methods to reveal how the Oracle database executes the SQL statement.
This Oracle Database 11g: SQL Tuning Workshop Release 2 training assists database developers, DBAs and SQL developers in identifying and
tuning inefficient SQL statements. You'll explore investigative methods to reveal varying levels of detail about how the Oracle database executes the SQL statement; this helps you determine the root causes of the inefficient SQL statements.
Learn To:
- Use Oracle tools to identifyinefficient SQL statements.
- Use Automatic SQL Tuning.
- Use Real Time SQL monitoring.
- Write more efficient SQL statements.
- Monitor and trace high load SQL statements.
- Manage optimizer statistics on database objects.
- Interpret execution plans, and the different ways in which data can be accessed.
Benefits to You Gain expertise in relational database data management as you learn how to effectively use SQL commands against your business data. The se features will help you query and manipulate data within the database, use the dictionary views to retrieve metadata and create reports about their schema objects.
Explore the Optimizer
Expert instructors will also help you explore how the optimizer chooses the path. You'll also learn how to influence the optimizer to ensure the best method is used.
Automatic SQL Tuning Tools
This course covers Automatic SQL Tuning tools and resources available in the Automatic Workload Repository. Furthermore, take advantage of
bind variables, trace files and different types of indexes.
Note:
this course is based on Oracle Database 11g Release 2.
Prerequisites
Audience
- Application Developers
- Database Administrators
- Database Administrators
- Developer
- Support Engineer
- Support Engineer
- Data Warehouse Developer
- Data
Warehouse Developer
- Data Warehouse Administrator
- PL/SQL Developer
Course Objectives
- Trace an application through its different levels of the application
architecture
- Understand how the Query Optimizer makes decisions about how to access data
- Define how optimizer statistics affect the performance of SQL
- List the possible methods of accessing data, including different join
methods
- Identify poorly performing SQL
- Modify a SQL statement to perform at its best
Course Topics
Exploring the Oracle Database Architecture
- Oracle Database Server Architecture: Overview
- Automated
SQL Execution Memory Management
- Database Storage Architecture, Logical and Physical Database Structures
- Physical
Structure
- Segments, Extents, and Blocks & SYSTEM and SYSAUX Tablespaces
- Automatic
Shared Memory Management
- Connecting to the Database Instance
- Oracle Database Memory Structures: Overview
Introduction
to SQL Tuning- Monitoring and Tuning Tools: Overview
- Scalability with Application Design, Implementation, and Configuration
- Simplicity in Application Design
- Reason
for Inefficient SQL Performance
- Performance Monitoring Solutions
- Common
Mistakes on Customer systems & Proactive Tuning Methodology
- CPU
and Wait Time Tuning Dimensions
- Data
Modeling, Table Design, Index Design, Using Views, SQL Execution
Efficiency, Overview of SQL*Plus & SQL Developer
Introduction
to the Optimizer- Transformer
& Estimator
- Optimization
During Hard Parse Operation
- SQL
Statement Parsing: Overview
- Structured
Query Language
- Plan Generator
- Cost-Based
Optimizer
- Controlling the Behavior of the Optimizer, Optimizer Features and Oracle Database Releases
- Why Do You Need an Optimizer?
Interpreting Execution Plans- Looking Beyond Execution Plans
- Automatic
Workload Repository (AWR)
- Interpreting an Execution Plan
- Using the V$SQL_PLAN View
- What Is an Execution Plan? Where To Find Execution Plans and Viewing Execution Plans
- Reading More Complex Execution Plans and Reviewing the Execution Plan
- SQL
Monitoring: Overview
- Plan
Table & AUTOTRACE
Application
Tracing- End-to-End
Application Tracing Challenge
- Use Enterprise Manager to Trace Services
- tkprof
Output with and without Index: Example
- Location
for Diagnostic Traces
- What is a Service? Use Services with Client Applications & Tracing Services
- Session
Level Tracing: Example
- The trcsess Utility and SQL Trace File Contents
- Invoking the tkprof Utility and Output of the tkprof Command
Optimizer:
Table and Index Operations- Row
Source Operations, Main Structures and Access Paths
- Index-Organized
Tables
- Bitmap
Indexes, Bitmap Operations and Bitmap Join Index
- Guidelines for Managing Indexes and Investigating Index Usage
- Using Indexes: Considering Nullable Columns
- Full
Table Scan
- Indexes:
Overview and B*-tree Indexes and Nulls
- Composite Indexes and Invisible Index
Optimizer
Join Methods- Hash
Join and Cartesian Join
- Equijoins and Nonequijoins
- Outer
Joins
- Antijoins
- Nested
Loops Join
- Nested
Loops Join: 11g Implementation
- Semijoins
- Sort Merge join
Optimizer:
Other Operators- Result Cache Operator
- When
Are Clusters Useful?
- Filter
operations and Concatenation Operations
- Count Stop Key Operator
- Min/Max
and First Row Operators and Other N-Array Operations
- Sorting Operators and Buffer Sort Operator
- Inlist Iterator and View Operator
- UNION
[ALL], INTERSECT, MINUS
Case Study: Star Transformation- Star
Transformation Hints
- Using Bitmap Join Indexes
- The Star Schema Model and The Snowflake Schema Model
- Bitmap
Join Indexes: Join Model 1 to 4
- Star
Transformation Plan Examples
- Star
Transformation
- Retrieving Fact Rows from One Dimension and from All Dimensions
- Joining the Intermediate Result Set with Dimensions
Optimizer
Statistics- Locking Statistics, Export/Import Statistics and Set Statistics
- Gathering System Statistics and Statistic Preferences
- Manual
Statistics Gathering
- Table,
Index and Column Statistics
- Histograms,
Frequency Histograms and Histogram Considerations
- Types of Optimizer Statistics
- Multicolumn
Statistics and Expression Statistics Overview
- Index
Clustering Factor
Using Bind Variables- Bind
Variable Peeking
- Cursor
Sharing Enhancements
- The CURSOR_SHARING Parameter
- Interacting with Adaptive Cursor Sharing
- Cursor
Sharing and Different Literal Values
- Forcing Cursor Sharing
- Adaptive Cursor Sharing
- Cursor
Sharing and Bind Variables
Using SQL Tuning Advisor- Tuning SQL Statements Automatically
- Database Control and SQL Tuning Advisor
- Stale or Missing Object Statistics and SQL Statement Profiling
- SQL
Tuning Loop, Access Path Analysis and SQL Structure Analysis
- Application
Tuning Challenges
- SQL
Tuning Advisor: Overview
- Implementing Recommendations
- Plan Tuning Flow and SQL Profile Creation
Using SQL Access Advisor- SQL Access Advisor: Overview
- SQL Access Advisor: Schedule and Review
- SQL Access Advisor: Workload Source
- SQL Access Advisor: Results
- Possible Recommendations
- SQL Access Advisor Session: Initial Options
- SQL Access Advisor: Recommendation Options
- SQL Access Advisor: Results and Implementation
Using Automatic SQL Tuning- Automatic SQL Tuning
- Automatic SQL Tuning: Result Summary
- Configuring Automatic SQL Tuning
- SQL Tuning Loop
- Automatic SQL Tuning: Result Details
- Automatic SQL Tuning Result Details: Drilldown
- Automatic SQL Tuning Considerations
- Automatic Tuning Process
SQL Performance Management- SQL Plan Baseline: Architecture
- Maintaining SQL Performance and SQL Plan Management: Overview
- SQL Plan Selection
- Enterprise Manager and SQL Plan Baselines
- Possible SQL Plan Manageability Scenarios
- SQL Performance Analyzer and SQL Plan Baseline Scenario
- Loading a SQL Plan Baseline Automatically and Purging SQL Management Base Policy
- Important Baseline SQL Plan Attributes