	Training/Mentoring

	E-Business Suite
	Business Intelligence
	Custom Development
	Database Services
	Managed Services
	Training/Mentoring
		The Service
		The Value
		References
		Contact Us / Registration
		Oracle Certification Prep
		Pricing
		Course Schedule
		Directions, Travel & Accommodations
		Register Online
	Oracle Licensing

Application Tuning for Developers

Click here to contact us

Course Overview

Application Tuning for Developers is a three-day course focusing on the issues of tuning the individual queries that are causing the most problems in the system. The course will instruct students on the intricacies of several tools included within Oracle and provide several pieces of information that no other third-party tool can. These tools are the basics for tuning any system. The course will discuss how to use the cost-based optimizer and statistics generated from the DBMS_STATS utility to help tune an application. It will demonstrate how-to hints that can drive queries to a more optimized path. Students will learn how to use different joining methods, such as merges and hash joins, taking advantage of many of the new features being offered in the latest version of Oracle.

Course Goals

To understand how Oracle processes queries from an application tuning perspective
To develop a "hit list" of possible avenues for tuning both queries and the system as a whole
To develop skills that will make an immediate impact on performance
To understand how to utilize Explain Plan
To learn how to use SQL Trace and TKPROF
To understand the new features in the latest version of the Oracle Database and how to put them to work for you

Prerequisites

SQL & SQL*Plus
PL/SQL

Who Should Attend?

Database administrators at any level
Application developers

Course Materials

Application Tuning Course Notes
Performance Tuning Tips & Techniques Reference Publication (authored by TUSC)
Application Tuning Sample Script Disk

Duration

Three days

Tuition

$1,395

Course Outline

Course overview
Where is the problem?
Know your system!
- What you need to know when you start
- What types of applications are running?
- Investigate internal and external processes
- Communicate with your users
- Oracle architecture -- instance and database
- Oracle RDBMS operation
- What type of applications are running on your system?
- Are your init{SID}.ora parameters set high enough?
- Are your init{SID}.ora parameters set properly?
- Storage resources -- logical database storage
- Oracle8i/Oracle9i upper limits -- FYI
Methods of tuning
- Overview
- Available methods
- Using TKPROF
- What is TKPROF?
- Using TKPROF -- TKPROF FLOW
- Trace files
- Advance Tracing with event 10046
- TKPROF syntax
- Interpreting the TKPROF output
- Explain/Trace symptoms
- Using V$SQLAREA to find problem queries
- Finding queries in memory (v$slqarea)
- Finding the largest number of logical reads
- Examining execution plan
Using the Explain Plan command
- Explain Utility -- execution path
- How do I use Explain by itself?
- Explain Plan
- FYI -- columns in the plan table
- Preparing to use Autotrace
- Setting Autotrace on
- Autotrace output
- Autotrace advantages and disadvantages
- Setting timing on
- dbms_utility.get_time
The optimizers
- Topics
- Rule-based optimizer
  - Determining an execution path (rule)
  - Index usage (rule)
- Cost-based optimizer
  - About the cost-based optimizer
  - Choosing an execution path
  - Features that require the CBO
- OPTIMIZER_MODE
- OPTIMIZER_GOAL
- DBMS_STATS
  - Gather statistics
  - Export/save statistics
  - Setting statistics
- Analyze command
- Data collected by analyzing objects
- Default values used by the optimizer
- Choosing an execution path
Driving tables
- Topics
- What is a driving table?
- Which table should be the driving table?
- Rule-based optimizer
- Cost-based optimizer
- Using hints to choose a driving table
Table/index access
- Topics
- Introduction
- Table/index access by rowid
- Full table scan
- Cluster scan
- Hash scan
- Index scans
- Unique and range scans
- Fast full scan
- Skip scan
Table joins
- Topics
- Things you should know
- Nested loops
- Sort-merge
- Cluster joins
- Hash joins
- Cartesian
- Outer joins
- Index joins (Oracle8i)
- Antijoins
- Semijoins
WHERE clause operators
- Topics
- "=","!=","<>","<" and ">"
- IN and NOT IN
- ANY and ALL operators
- Is NULL and is NOT NULL
- EXISTS and NOT EXISTS
- BETWEEN and LIKE
- OR
- Transitivity
- Blind variables
- Short circuiting
- Subqueries
  - Types of subqueries
  - Rewriting a table join as a subquery
  - UPDATE statements
Oracle SQL features
- The GROUP BY clause
- Set operators
- Cube and rollup
- Top N queries
- Random sampling
- Select list inline views
- The MERGE statement
- Subquery factoring
Distributed queries
- Topics
- Overview
- Tuning Net*8
Indexes
- Topics
- Are indexes always faster?
- Some problems with indexes
- Types of indexes
  - Btree -- unique/primary key
  - Btree-composite
  - BTree-reverse key
  - Bitmap indexes
  - Domain
- When to use indexes-foreign keys
- When to use indexes -- best times
- What columns to index
- Merging indexes
- Histograms
- Function-based indexes
Index suppression factors
- Topics
- The use of functions
- Using the "NOT" or "IS NULL"
- Comparing wrong data types
- Inadvertent index suppression
- Intentional index suppression
Using clusters
- Overview
- Hash clusters
- Hash clusters -- when not to use them
Global temporary tables
- What are they
- Functionality/benefits
- Example
Index-organized tables
- Topics
- Background
- Creating an index-organized table
Using transactions
- Topics
- Background
- Discrete transactions
- Discrete transactions -- when they can/should be used
- Discrete transactions how do they work
- Discrete transactions -- errors
- Serializable transactions
Using the parallel query option
- Topics
- Overview
- System benefits
- Degree of parallelism
- INIT.ORA parameters
- Managing query servers
- Parallel server contention
- Parallel query tuning 8i
- Automated parallel query tuning
- Controlling parallel query execution
- New dynamic performance views
Partitioned tables and indexes
- What is partitioning?
- Partitioning for performance
- Kinds of partitions
- Indexing options
- Partitioned indexes
  - Local indexes
    - Local prefixed indexes
    - Local non-prefixed indexes
  - Global indexes
    - Global prefixed indexes
    - Global non-prefixed indexes (not supported)
- Improved maintenance
- Restrictions
Range partitioning
- Explanation
- What you need for it to work
Hash partitioning
- Introduced in Oracle8i
- What you need for it to work
- How it works
- Power of 2 rule
- Where's the benefit?
Composite partitioning
- Introduced in Oracle8i
- What you need for it to work
- How it works
- Where's the benefit?
List partitioning
- How it works?
- What you need for it to work
Range-list partitioning
- What you need for it to work?
- How it works?
Partitionwise joins
- Prior to Oracle8i
- New in Oracle8i
- What you need for it to work
- How it works
- What is the benefit?
Views
- Overview
Materialized views
- Introduction
- Join indexes
- Summary management
- Creating a summary
- Registering existing tables as a summary
- Refreshing materialized views
- Query rewrite examples
  - Exact match
  - Aggregate rollup
- Enabling rewrites
- Benefits of materialized views and summary management in 8i
Using hints
- Topics
- What are they
- What are they used for
- Syntax
- For optimization goals
- For access methods
- For query transformations
- For join orders
- For join operations
- For parallel execution
- Other hints
- Hints and views
- Global hints
- Stored outlines
Using PL/SQL
- Okno problem
- _PROFILER package -- example
Course summary