Training

 

 

Popular Courses

Browse Our Free Resources

  • whitepapers
  • whitepapers
  • webinars
  • blogs

Our Locations

Training Centres

Vancouver, BC
Calgary, AB
Edmonton, AB
Toronto, ON
Ottawa, ON
Montreal, QC
Hunt Valley
Columbia

locations map

Calgary

550 6th Av SW
Suite 475
Calgary, AB
T2P 0S2

Toronto

821A Bloor Street West
Toronto, ON
M6G 1M1

Vancouver

409 Granville St
Suite 902
Vancouver, BC
V6C 1T2

U.S. Office

436 York Road
Suite 1
Jenkintown, PA
19046

Other Locations

Dallas, TX
Miami, FL

Home > Training > Oracle 10g > Oracle Database 10g: SQL Tuning Training

Oracle Database 10g: SQL Tuning Training

Course#: WA1386

This course will equip developers to build efficient SQL statements and to tune applications from the standpoint of an application developer. When this effort is complemented by database server and possibly PL/SQL application tuning, then a highly efficient environment has been built.

Topics 

Major subject areas to be explored are:

  • Consider the unique and differing tuning issues in OLTP database and data warehouse environments.
  • Learn the phases of SQL statement execution within the database instance.
  • Use various techniques to examine SQL execution plans which are produced.
  • Learn about the operation of the Oracle Cost-Based Optimizer, including optimizer statistics and histograms.
  • Influence the behavior of the Optimizer and tune SQL statement execution.
  • Utilize the database advisory framework and those Advisors specifically intended for SQL tuning.
  • Use private and public stored outlines to achieve absolute execution plan stability.
  • Understand the self-tuning infrastructure and the automatic SQL tuning capabilities found within the database.

Audience

Target audience for this course is both senior application developers and database administrators. Developers who will be building, debugging and tuning complex SQL statements will benefit from this course. Database administrators responsible for tuning the database will need to collaborate with application developers in order to implement the techniques discussed herein.

Prerequisites

ORACLE DATABASE 10G: INTRODUCTION TO SQL – COMPLETE LIBRARY.

Duration

Five days.

Outline of WA1386 Oracle Database 10g: SQL Tuning Training

1. CONTEMPLATING THE TUNING ISSUES

  • DIFFERENCES BETWEEN APPLICATIONS
  • THE CHALLENGES OF TUNING
  • THE IMPACT OF THE SYSTEMS INFRASTRUCTURE
  • USING THE MANAGEMENT & ADVISORY FRAMEWORK

2. SQL STATEMENT EXECUTION

  • THE EXECUTION PHASES
  • SQL EXECUTION MODES

3. VIEWING THE EXECUTION PLAN

  • MORE ABOUT THE EXECUTION PLAN
  • VIEWING THE EXECUTION PLAN
  • MORE INTERPRETATION OF THE PLAN

4. UNDERSTANDING THE COST BASED OPTIMIZER (CBO)

  • OPTIMIZATION GOALS
  • CBO COMPONENTS

5. OPTIMIZER OPERATIONS

  • TABLE ACCESS OPERATIONS
  • JOIN OPERATIONS
  • INDEX OPERATIONS

6. MANAGING OPTIMIZER STATISTICS

  • MORE ABOUT OPTIMIZER STATISTICS
  • AUTOMATIC DATABASE STATISTICS COLLECTION
  • MANUALLY GATHERING STATISTICS
  • USING THE DBMS_STATS() PACKAGE
  • USING THE EM INTERFACE
  • COLLECTING & MANAGING SYSTEM STATISTICS
  • MANAGING SYSTEM STATISTICS

7. PERFORMANCE MONITORING & PROACTIVE MAINTENANCE

  • THE MANAGEMENT & ADVISORY FRAMEWORK
  • REAL-TIME MONITORS
  • MANAGING THE AUTOMATIC WORKLOAD REPOSITORY
  • AUTOMATIC DATABASE DIAGNOSTIC MONITOR
  • METRICS & ALERT THRESHOLDS

8. USING ADDM & THE SQL TUNING ADVISOR

  • APPLICATION TUNING SETUP
  • APPLICATION MONITORING & TUNING WITH EM
  • SQL TUNING ADVISOR

9. PROGRAMMATIC ACCESS TO THE SQL TUNING ADVISOR

  • ABOUT SQL TUNING ADVISOR TASKS
  • SQL PROFILE MANAGEMENT
  • SQL TUNING SET MANAGEMENT

10. USING THE SQL ACCESS ADVISOR

  • USING THE SQL ACCESS ADVISOR
  • PERFORMING A QUICK TUNE TASK
  • INDEX OPERATION DATABASE PARAMETERS

11. OPTIMIZER HINTS

  • ABOUT HINTS
  • HINT EXPLANATIONS

12. HISTOGRAMS

  • WHY ARE HISTOGRAMS NEEDED?
  • HISTOGRAMS INTERNAL STRUCTURE
  • MANUALLY MANAGING HISTOGRAMS

13. USING PLAN STABILITY

  • ABOUT PLAN STABILITY
  • PREPARING FOR STORED OUTLINES
  • CREATING & USING STORED OUTLINES
  • DATA DICTIONARY STORAGE
  • MANAGING STORED OUTLINES

14. EDITING PRIVATE OUTLINES

  • ABOUT PRIVATE OUTLINES
  • EDITING PRIVATE OUTLINES
  • UTILIZING PRIVATE OUTLINES

15. EXPLOITING & MANAGING CURSOR SHARING

  • ABOUT CURSOR SHARING
  • BIND VARIABLES & CURSOR PEEKING
  • USING THE CURSOR_SHARING PARAMETER

16. USING TKPROF & AUTOTRACE

  • USING TKPROF
  • USING AUTOTRACE

17. MANAGING SCHEMA OBJECTS

  • ABOUT CHAINED & MIGRATED ROWS
  • VALIDATING DATABASE OBJECTS
We regularly offer classes in these and other cities. Atlanta, Austin, Baltimore, Calgary, Chicago, Cleveland, Dallas, Denver, Detroit, Houston, Jacksonville, Miami, Montreal, New York City, Orlando, Ottawa, Philadelphia, Phoenix, Pittsburgh, Seattle, Toronto, Vancouver, Washington DC.
*Your name:

*Your e-mail:

*Phone:

*Company name:

Additional notes:

We have received your message. A sales representative will contact you soon.

Thank you!.

more details
buy this course

Register for a courseware sample

It's simple, and free.

 

Thank You!

You will receive an email shortly containing a link to download the requested sample of the labs for this course.