Email Us   Phone : 503-259-0312   
  Home    |    Instructor-led Training    |    Self-Paced Learning    |    Online Training     


Contact Us   -   Why Choose Wintrac   -   Clients    

Courses
ADA
Adobe
Agile
AJAX
Android
Apache
AutoCAD
Big Data
Business Analysis
Business Intelligence
Business Objects
Business Skills
C++ programming
Cisco
Citrix
Cloud Computing
COBOL
Cognos
ColdFusion
COM/COM+
CompTIA
CORBA
CRM
Crystal Reports
Datawarehousing
DB2
Desktop Application Software
DNS
Embedded Systems
Google Web Toolkit (GWT)
IPhone
ITIL
Java
JBoss
LDAP
Leadership Development
Lotus
Machine learning/AI
Macintosh
Mainframe programming
Microsoft technologies
Mobile
MultiMedia and design
.NET
NetApp
Networking
New Manager Development
Object oriented analysis and design
OpenVMS
Oracle
Oracle VM
Perl
PHP
PowerBuilder
Professional Soft Skills Workshops
Project Management
Rational
Ruby
Sales Performance
SAP
SAS
Security
SharePoint
SOA
Software quality and tools
SQL Server
Sybase
Symantec
Telecommunications
Teradata
Tivoli
Tomcat
Unix/Linux/Solaris/AIX/
HP-UX
Visual Basic
Visual Foxpro
VMware
Web Development
WebLogic
WebSphere
Websphere MQ (MQSeries)
Windows programming
XML
XML Web Services
Other
Oracle Database 11g R2: SQL Tuning
Oracle Training Overview

This learning guide will equip database administrators and application developers to build efficient SQL statements and to tune database applications. When this effort is complemented by database server and PL/SQL application tuning, then a highly efficient application execution environment is created. One will learn about the internals of SQL statement execution, how to monitor the performance of such execution, and how one can influence the behavior of the database to achieve performance gains. This textbook is a mandatory reference for any database administrator or SQL database application developer.

Oracle Training Objectives

The following specific topics are among those considered:
  • Consider the unique and differing tuning issues found in online database applications, enterprise resource and data warehouse environments and the important metrics of SQL statement performance.
  • Learn about the internal mechanisms use for SQL statement execution within a database instance and how these can affect performance for good or bad, including the Optimizer facilities known as the Transformation Engine, Estimator and Plan Generator.
  • Use a variety of techniques to examine the details of SQL statement execution, spotting trouble areas and bottlenecks which require tuning.
  • Learn about the Auto-Task framework and how to manage the automatic collection of Optimizer statistics and automatic SQL tuning using both the programmatic and Enterprise Manager interfaces.
  • Learn how statistic deficiencies can dramatically degrade performance, and how these problems are resolved through customized Optimizer statistics collection procedures using the DBMS_STATS() package, system statistics, histograms, expression statistics and MultiColumn statistics.
  • Influence the behavior of the Optimizer by setting database parameters and other SQL tuning techniques.
  • Utilize the database advisory framework and the SQL Tuning and SQL Access advisors.
  • Use plan management to achieve plan stability which is adaptive and even dynamic.
  • Understand the self-tuning infrastructure and the automatic SQL tuning capabilities found within the database.
  • Employ SQL hints embedded into the statement text to resolve unique tuning challenges.
  • Learn to identify poorly performing SQL statements using real-time SQL monitoring and application tracing techniques such as DBMS_MONITOR(), trcsess and tkprof.
Oracle Training Audience

The primary target audiences for this course are:
  • Senior application designers and database developers
  • PL/SQL developer
  • Database administrators
  • Web server administrators
  • System administrators
  • Implementation specialists
  • Data center support engineers
Oracle Training Certification

This course covers information relevant for the certification test: ORACLE DATABASE 11G R2: PERFORMANCE TUNING.

When delivered as a standalone instructor-led training (ILT) or live virtual training session, the suggested length of this module is between 3 4 days. It is most often joined with the mandatory prerequisite course ORACLE DATABASE 11G R2: ARCHITECTURE & INTERNALS for a combined 5-day presentation.


Oracle Training Prerequisites

Specific prerequisites for this course are the following titles, or equivalent experience:
  • ORACLE DATABASE 11G R2: SQL FUNDAMENTALS COMPLETE LIBRARY
  • ORACLE DATABASE 11G R2: ARCHITECTURE & INTERNALS
Suggested Next Courses

You may wish to consider the following courses which are also related to the subject of Oracle database performance tuning:
  • ORACLE DATABASE 11G R2: RESOURCE MANAGER & SCHEDULER
  • ORACLE DATABASE 11G R2: ADVANCED PL/SQL PROGRAMMING & TUNING
  • ORACLE DATABASE 11G R2: PERFORMANCE TUNING
  • ORACLE DATABASE 11G R2: IMPLEMENT PARALLEL SQL & PARTITIONING
Oracle Training Course duration

3-4 Days

Oracle Training Course outline

TUNING & THE ORACLE DATABASE ADVISORY FRAMEWORK
  • THE CHALLENGES OF TUNING
  • PERFORMANCE METRICS
  • MANAGEMENT & ADVISORY FRAMEWORK
  • ADDM & AWR
  • SQL TUNING PRIVILEGES
VIEWING & MONITORING THE EXECUTION PLAN
  • ABOUT THE EXECUTION PLAN
  • COLLECTING PERFORMANCE STATISTICS
  • VIEWING THE EXECUTION PLAN
  • REAL-TIME SQL MONITORING
UNDERSTANDING THE OPTIMIZER
  • OPTIMIZATION METHODS
  • OPTIMIZATION GOALS
  • OPTIMIZER_MODE
  • OPTIMIZER_FEATURES_ENABLE
  • OPTIMIZER COMPONENTS
  • EXECUTION PLAN OPERATIONS
EXECUTION PLAN METHODS & OPERATIONS
  • TABLE ACCESS METHODS
  • JOIN METHODS
  • INDEX OPERATIONS
  • DATA OPERATIONS
MANAGING OPTIMIZER STATISTICS
  • MORE ABOUT OPTIMIZER STATISTICS
  • AUTOMATIC MAINTENANCE TASKS
  • MANUALLY GATHERING STATISTICS
  • GATHER_TABLE_STATS()
  • GATHER_INDEX_STATS()
  • GATHER_SCHEMA_STATS()
  • GATHER_DATABASE_STATS()
  • GATHER_SYS Parameter
  • GATHER_DICTIONARY_STATS()
  • GATHER_FIXED_OBJECTS_STATS()
  • USING HISTORICAL STATISTICS
  • DYNAMIC SAMPLING
  • LOCKING STATISTICS
ENHANCED OPTIMIZER STATISTICS
  • ABOUT OPTIMIZER SYSTEM STATISTICS
  • MANAGE SYSTEM STATISTICS
  • CREATE_STAT_TABLE(), DROP_STAT_TABLE()
  • CREATE_STAT_TABLE()
  • DROP_STAT_TABLE()
  • GATHER_SYSTEM_STATS()
  • GET_SYSTEM_STATS()
  • SET_SYSTEM_STATS()
  • IMPORT_SYSTEM_STATS(), EXPORT_SYSTEM_STATS()
  • IMPORT_SYSTEM_STATS()
  • EXPORT_SYSTEM_STATS()
  • DELETE_SYSTEM_STATS()
  • PENDING & PUBLISHED STATISTICS
HISTOGRAMS & EXTENDED STATISTICS
  • WHY ARE HISTOGRAMS NEEDED?
  • HISTOGRAMS INTERNAL STRUCTURE
  • MANUALLY MANAGING HISTOGRAMS
  • EXPRESSION STATISTICS
  • MULTICOLUMN STATISTICS
APPLICATION TRACING
  • APPLICATION TRACING PACKAGES
  • SET_SQL_TRACE()
  • TRACE_ENABLE()
  • SET_IDENTIFIER()
  • EM APPLICATION MONITORING & TUNING
  • REAL-TIME MONITORING
  • USING TRCSESS & TKPROF
ADDM & THE SQL TUNING ADVISOR
  • ADDM PERFORMANCE ANALYSIS
  • USING THE SQL TUNING ADVISOR
  • AUTOMATIC SQL TUNING
  • CONFIGURING AUTOMATIC SQL TUNING
THE SQL ACCESS ADVISOR
  • USING THE SQL ACCESS ADVISOR
  • SQL ACCESS ADVISOR TEMPLATES
  • PERFORMING A QUICK TUNE TASK
  • INDEX DATABASE PARAMETERS
  • OPTIMIZER_INDEX_COST_ADJ
  • OPTIMIZER_INDEX_CACHING
  • SKIP_UNUSABLE_INDEXES
  • OPTIMIZER_USE_INVISIBLE_INDEXES
PLAN MANAGEMENT
  • SQL MANAGEMENT BASE ARCHITECTURE
  • LOAD_PLANS_FROM_SQLSET()
  • LOAD_PLANS_FROM_CURSOR_CACHE()
  • USING PLAN BASELINES
  • PLAN MANAGEMENT USING EM
MANAGING CURSOR SHARING
  • ABOUT CURSOR SHARING
  • BIND VARIABLES & CURSOR SHARING
  • THE CURSOR_SHARING PARAMETER
  • FINDING CANDIDATES FOR REWRITE
OPTIMIZER HINTS
  • WHAT ARE HINTS?
  • HINT EXPLANATIONS

 
About us
Contact us
Careers at Wintrac
Our Clients
Why Wintrac


Register for a free training CD-ROM drawing
Refer a client or instructor and earn $$$


Wintrac Inc.
16523 SW McGwire Ct.
Beaverton OR 97007
 
Wintrac, Inc. All rights reserved.                                                                               Site Map   |   Terms of Use   |   Privacy Policy