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
Oracle8i Application Tuning
Oracle Training Overview

This course will provide you with the skills necessary to tune Oracle applications. Particular attention is paid to tuning SQL statements, PL/SQL programs and 3GL programs that access Oracle data.

Oracle Training Prerequisites

Programming experience with Oracle databases. Oracle SQL, SQL*Plus and PL/SQL programming experience. This knowledge can be obtained in Introduction to Oracle for Developers class.

Oracle Training Course duration

3 Days

Oracle Training Objectives

After successfully completing this course, you will be able to:

  • Improve the performance of Oracle applications
  • Use the EXPLAIN PLAN command to externalize access path choices made by the optimizer
  • Tune SQL statements to reduce statement cost and elapsed time
  • Create indexes on appropriate columns
  • Use the trace and TKPROF utilities
  • Implement partitioning (V8)
  • Take advantage of parallel processing features
  • Tune 3GL programs (Pro*C)
  • Use HINTs to influence optimizer choices
  • Describe the various access paths used by Oracle
  • Understand how PL/SQL procedures can be used to improve performance
  • Improve the performance of database triggers
Oracle Training Course outline

  • Introduction to SQL Tuning
    • What Can Be Tuned?
    • What This Course Covers
    • Tuning SQL: What We Need to Know
    • Tuning Programs: What We Need to Know
  • Oracle Architecture Overview
    • Introduction to the Oracle Architecture
    • The SGA
    • Data Block Buffer Cache
    • Redo Log Buffer
    • Background Processes
  • SQL Statement Processing
    • SQL Statement Processing Overview
    • Parse
    • Parse Steps
    • Execute
    • Fetch
    • Shared SQL
    • Stored Procedures
    • Shared Pool Memory Size Requirements
    • Viewing Pool Size
    • Excessive ReParsing
    • Query Re-Parse
    • Is the Shared Pool Too Big?
    • Monitoring Shared SQL
    • Monitoring Shared SQL Script -1
    • Monitoring Shared SQL Script -2
    • Monitoring Shared SQL Script - 3
    • Bind Variables - 1
    • Bind Variables - 2
  • Indexes
    • Index Usage
    • B-Tree Indexes
    • B-Tree Illustration
    • B-Tree Effect on Query Performance
    • Creating B-Tree Indexes
    • Run ANALYZE or DBMS_STATS
    • Columns Updated by Analyze
    • Choosing Columns to Index
    • Determine Selectivity
    • Add an Index?
    • Utility Output Continued
    • Add Another Index?
    • Utilities Output for Dept_no
    • Multi-Column Indexes - 1
    • Multi-Column Indexes - 2
    • Which Column First?
    • Avoiding Table Access with Multi-Column Indexes
    • Another Example of Eliminating Table Access
    • Eliminate Redundant Indexes
    • Indexes vs. Full Table Scans
    • Indexes vs. Full Table Scans Factors
    • Forcing Full Table Scans
    • Eliminating Index Access
    • Indexes and Parallel Operations
    • Parallel Hint
    • Parellelize
    • What to Avoid when using an Index
    • Column Functions
    • Column Type Conversion
    • Additional Indexing Guidelines
    • When to Create
    • Where to Create
    • Use REBUILD
    • Use NOLOGGING
    • Introduction to Bitmap Indexes
    • Bitmap Indexes Example
    • Bitmap Benefits
    • Multiple Indexes - Merging
    • Nulls
    • When to Use Bitmap Indexes
    • When Not to Use Bitmap Indexes
    • Restrictions
    • Bitmap and the Data Dictionary
    • Bitmap INIT.ORA PARAMETERS
    • Index-Organized Tables
    • Reverse Key Indexes
    • Function-Based Indexes
  • Optimization
    • Optimization Overview
    • Rule-Based Optimizer
    • Cost-Based Optimizer
    • Enabling Cost-Based Optimization
    • Check the Optimizer Setting
    • Check Statistics Creation
    • ALTER SESSION
    • Specifying the Goal: FIRST_ROWS or ALL_ROWS
    • Generating Statistics
    • Analyzing with ESTIMATE
    • Syntax of ANALYZE
    • Statistics Updated by ANALYZE
    • Using the ANALYZE_SCHEMA Proc
    • Creating Histograms
    • Access Paths
    • Full Table Scans
    • Index Scans
    • Table Access by ROWID
    • Oracle7 ROWID Format
    • Oracle8 ROWID Format
    • More Table Access by ROWID
    • Optimizer Calculations for Equal Comparisons
    • Optimizer Calculations for Range Comparisons
    • Range Optimization Involving Bind Variables
  • Explain & TKPROF
    • Displaying Execution Plan Steps
    • Create the PLAN_TABLE
    • PLAN_TABLE Columns
    • The EXPLAIN PLAN Command
    • Explaining a Simple Query
    • Reading Execution Plans
    • Alternative Displays
    • SQL*Plus Autotrace
    • SQL*Plus Autotrace Example
    • Tracing SQL Statements
    • Enabling the Trace Facility
    • Execute TKPROF
    • TKPROF Syntax
    • TKPROF Sample Trace
    • TKPROF Report Notes
    • Library Cache
    • TKPROF Explain Output
  • Using Hints
    • Objectives
    • Using Hints
    • Hint Examples: Full scan
    • Hint Examples: Alias
    • Hint Examples: RBO
    • Hints: CBO
    • Hint Examples: Join Order
    • Hints: Spelling Counts
    • Syntax Notes
    • Optimization Approach and Goal Hints
    • Access Method Hints
    • Syntax of the INDEX Hint
    • Join Hints
    • When Hints will be ignored
  • Tuning SQL
    • Finding the Bad Queries
    • Adding Indexes
    • Consider Bitmap Indexes
    • Indexes vs. Full Table Scans
    • Forcing Full Table Scans
    • Avoiding Table Access with Multi-Column Indexes
    • Multiple Single Column Indexes
    • AND-EQUAL Operation
    • ORDER BY on Indexed Columns
    • EXISTS over DISTINCT
    • UNION ALL over UNION
    • Conditions To Avoid
  • Join and Subquery Access Paths
    • Join Operations
    • Nested Loops Join
    • Sort Merge Join
    • Hash Join
    • Join Hints
    • Additional Filters
    • Subquery Conversion
  • PL/SQL Tuning
    • Using PL/SQL to Improve Performance
    • PL/SQL Performance Improvements Illustration
    • PL/SQL Common Routines
    • PL/SQL in Memory
    • Explicit vs. Implicit Cursors
    • Defining Explicit Cursors
    • Using Implicit Cursors
    • Triggers
    • Efficient Trigger Coding
    • When Not to Use a Trigger
    • Procedures and Functions
    • Packages
    • SQL *PLUS Example
    • PL/SQL Example
    • PL/SQL Performance Problem
    • PL/SQL Performance Example
    • The Solution - Bulk Binding
    • Bulk Binding Keywords
  • Tuning 3GL Programs
    • Using Host Arrays
    • Host Array Declare Statement
    • Array Rules and Restrictions
    • SELECT INTO Statement
    • DECLARE CURSOR Statement
    • FETCH Results Notes
    • INSERTing Data
    • Updating Data
    • Deleting Data
    • Processing NULLs
    • Set Array Element Processing
    • Specifying Predicate Information
    • Processing Fetched Data
    • Communication Information
    • Embedding PL/SQL Programs
    • Embedding PL/SQL Programs Code
    • Final Tuning Tips
  • Oracle8i Optimizer Enhancements
    • Plan Stability Concepts
    • Implementing Plan Stability
    • DBMS_STATS Concepts
    • Using DBMS_STATS
    • Automated Statistics Gathering
    • Index Statistics
    • Types
  • Tuning Views
    • Mergeable Views
    • Hints & Mergeable Views
    • Non-Mergeable Views
    • Hints and Non-Mergeable Views
  • Summary of Tuning Tips
    • Conditions to Avoid
    • Use Hints
    • Using Array Processing
    • Using Array Processing Illustration
    • Oracle Tool Support for Array Processing
    • SQL*Plus Array Support
    • Use Sequences
    • Use Sequences Illustration
    • Assigning Specific Rollback Segments
    • Using SET TRANSACTION
    • SQL Performance Tips
    • Avoid Deadlocks
    • Deadlocks: Sequences
    • Pinning Objects in Memory
    • Pinning Objects Types
Hardware/Software Requirements
  • Hardware Requirements
    • Minimal Processor: Pentium 166 or Pentium 200
    • Recommended Processor: Pentium 233 or Pentium 266
    • RAM: 128 MB (256 MB recommended)
      • Oracle Corporation also recommends increasing virtual memory to 200 MB. (Modify it in the Performance tab of System Properties in the Control Panel.)
    • Available Disk Space (FAT or HTFS): 3 GB
    • Video: 256 color
    • A connection to the Internet is desirable (but not absolutely necessary) to show students various Oracle-related sites.
  • Software Requirements
    • Windows NT 4.0 or Windows 2000
    • Oracle8i 8.1.6 or above
    • Microsoft Internet Explorer 5 or Netscape Navigator 4.5
    • Adobe Acrobat Reader or Acrobat 4.0 or later (for the course presentation)*
    • Student Files
    • Text Editor*

 
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
Copyright SB