Oracle 10g Application Tuning Workshop

Training Summary
This class will teach Oracle developers and DBAs how to performance tune applications for an Oracle 10g database server. Good design techniques, programming techniques and performance tuning practices will be emphasized throughout the course. Students will learn how Oracle processes SQL statements and makes decisions on how to execute SQL statements. The first day of class helps the students develop a tuning methodology and develop an understanding of the Oracle architecture. The second day will focus on tuning SQL statements and the Oracle tools used to tune SQL statements. The last day will focus on customizing an Oracle session for different types of environments and how to optimize applications OLTP and DSS. This class is taught in a workshop environment to maximize the time students spend tuning SQL code in the class. Understanding how to leverage the cost-based optimizer is the focus of the class. Developers will also be introduced to SQL tuning tools available to DBAs.
Before attending this course, students should have a solid understanding of SQL programming. PL/SQL knowledge is recommended. This course includes PL/SQL tuning concepts.
3 Days/Lecture & Lab
Course Topics
  • Developing a tuning methodology
  • The Oracle architecture for developers
  • Fundamentals of SQL tuning
  • Understanding the cost-based optimizer
  • Top SQL and ADDM
  • SQL tuning advisor
  • SQL tuning sets
  • SQL profiles
  • Generating statistics using DBMS_STATS - ANALYZE versus DBMS_STATS
  • Understanding explain plans
  • Access methods
  • Table access methods
  • Index unique scan
  • Index range scan
  • Index skip scan
  • Index fast full scan
  • Index full scan
  • Index joins
  • Bitmap join indexing
  • Evaluating execution plans
  • Autotrace
  • SQL Trace
  • Oracle 10046
  • 10053 trace data
  • Statspack
  • Tkprof
  • Evaluating runtime statistics
  • Evaluating complex execution plans
  • Join optimization methods
  • Nested loops join
  • Hash join
  • Sort merge join
  • Merge join cartesian
  • Tuning joins and subqueries
  • Tuning using hints
  • Tuning for different types of tables
  • Tuning for indexes
  • Composite key order
  • Histograms
  • IN vs. EXISTS
  • CASE
  • Session tuning
  • Tuning data warehouse environments

Related Scheduled Courses