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 identify inefficient 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. These
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
- Data
Warehouse Administrator
- 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 Table spaces
- 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