ORACLE 11G PERFORMANCE TUNING
Course scheduled to Duration : 2 Days
Course benefits
This course will help participants learn effective application tuning
techniques for optimizing performance in the Oracle server environment.
Topics in this course are troubleshooting application performance, by
analyzing the various options available in Oracle 11g like hints and
creating/ modifying existing physical, logical structures, understanding
various new structures available in Oracle to improve application
performance.
Oracle Database 11g offers improved administration and maintenance
features, including an expanded automation toolset. This assists in
maintaining vital data and allows organizations to ensure high availability,
performance and integrity of their database systems.
In this hands-on course, you gain the knowledge and skills to understand a Oracle 11g
Tuning concepts, write and tune sqls, code to enhance the performance of SQL, PLSQL,
and deploy them to maintain your organization's database and ensure its availability.
Who should attend?
This course is valuable for candidates who have good knowledge in
Oracle 11g SQL, PLSQL and some prior knowledge in DB concepts.
Sessions reinforce the learning objectives and provide participants the opportunity to
gain immediate, hands-on reinforcement of the ideas you are learning.
Day 1
Performance Tuning Duties Of The DBA
What is Performance Tuning?
Trade-offs Between Response Time and Throughput
Steps for Tuning
Measurable Tuning Goals
Oracle Database Memory Structures: Overview
Background Process Roles
Automatic Shared Memory Management
Automatic Memory Management
Tune the Logical Structure of the Database
Types of Tablespace
Segment space management
Extent Management (local, dictionary)
Using Oracle blocks efficiently
About multiple block sizes in oracle
Improving I/O by using multiple block sizes
Creating Indexes using multiple block sizes
How to Choose Data Block Size
Database structures and SQL
Effect of PCTFREE & PCTUSED on DML
Row Chaining & Row Migration
Table high-water mark, Free and Unused space
Row-resequencing and Oracle SQL I/O performance
Tuning I/O using Partitions
Table partitioning
Why partition a table
Partition pruning
Advantages of partitioning
Types of partitioning
Handling Indexes in partition
Partitioning and Storage-Related Enhancements
Partitioning
o Interval Partitioning
o Extended Composite Partitioning
o Reference Partitioning
o System Partitioning
o System-Managed Domain Indexes
o Virtual columns
Creating tables with virtual columns
Partitioning tables with virtual columns
Understanding ROWID
ORACLE 11G PERFORMANCE TUNING
How is rowid used within Oracle
Structure and usage of Rowid
Using Rowid to make select faster
Materialized View
Purpose of Materialized views
Refreshing MV
Query Rewrite
Rewrite or Error
About sorts
Automatic PGA memory management
Tuning sorts in Oracle
PGA advisor
Histogram
About Histograms
Extended statistics
Express statistics
Day 2
Coding, Structures that enhances SQL/plsql performance
PLSQL Native Compilation
Bulk Binds
Use bulk binding for multi-row operations
Selecting huge volume of data
Inserting
Appending data above HWM Advantage
using hints
direct path load
REF CURSOR
Types of Ref Cursors
Advantages
Usage
Passing as parameters
PL/SQL Function Result Cache
Using result cache hint
Cached and not cached clause in Function
Result_cache and relies_on
SQL Result Cache
RESULT_CACHE_MODE
Using and Managing Result cache
Memory Structures that enhances SQL/plsql performance
(continued)
Sizing Shared Pool
Interpreting the V$LIBRARYCACHE Table
Reducing Library Cache Misses
Loading PL/SQL Objects into the Shared Pool
SQL Plan Management
SQL Plan Management: Overview
SQL Plan Baseline: Architecture
Evolving SQL Plan Baselines
SQL Plan Selection
Sizing the Buffer Cache
Buffer Cache
Examining Buffer Cache Activity via the Cache Hit Ratio
Calculating the Cache Hit Ratio
When to Use Multiple Buffer Pools - KEEP,RECYCLE and DEFAULT
Choosing Data Block Size Examples
Tuning redo generation
Unnecessary index
Select for update
Update only the data of significance
Unnecessary commit
Global temp tables
External tables
Indexes: Overview
Using Indexes: Considering Nullable Columns
Bitmap Indexes, Function based, Reverse Key Indexes &Invisible
Index
Guidelines for Managing Indexes
Monitoring Index Usage
SQL Tuning Advisor
Overview
Automatic SQL Analysis
Creating SQL Tuning Tasks
Day 3
ORACLE 11G PERFORMANCE TUNING
Appending data above HWM Advantage
using hints
direct path load
Profiling and Tracing PL/SQL Code
Trace PL/SQL program execution
Profile PL/SQL applications
SQL Tuning
The Oracle Optimizer
Cost-Based Optimization
About Optimizers in Oracle 10g
Components of query optimizer
Building and deleting statistics
System statistics
Locking Statistics
Multicolumn Statistics: Overview
Understanding a method of execution of a SQL
Hash Joins, Sort merge Join, Nested Loop
Using Indexes appropriately
Using Autotrace
Optimizer Mode
Optimizer hints (used in statement level)
Optimizer Modes meant for OLTP and DSS
Invoking the SQL Trace Facility
Tkprof
Formatting Trace Files with TKPROF
Interpreting the Output of the TKPROF Command
Optimizer Plan Stability (overview)
Bind variable Usage ( Soft Parse / Hard Parse (How to reduce),
Evaluating Large-table, full-table scans
Fasten Full Table Scan
Init parameters
Adaptive Cursor Sharing
Effects of CURSOR_SHARING Parameter(Benefits/BAD Effects)
About Bind-Aware Peeking
Bind-Aware Peeking views
Starting a system with bind-aware peeking
Parallel query
About parallel query sets
Overview About of Parallel query explain plan
*****************
Include the below topics as well and include in the above
sections.
Partition Exchange Loading (PEL) types and usage
DBMS Profiler API
Analyzing and tracing the data for performance
Types and Collections
PLSQL Mappings
Set vs Row
Data extraction from remote sources
Native compilation/execution
