Customized Onsite Training

3
Days
  • Customized Content
  • For Groups of 5+
  • Online or On-location
  • Expert Instructors
Request Class or call 1-877-932-8228

Live Online Training

$1,575.00
or 0 vouchers
  • Live Online Training
  • Expert Instructors
  • Guaranteed to Run
  • 100% Free Re-take Option

Upcoming Classes

  • See More Classes

Please select a class.
Overview

This Oracle Database 11g R2: SQL Tuning training class equips database administrators and application developers to build efficient SQL statements and to tune database applications. Students will learn about the internals of SQL statement execution, how to monitor the performance of such execution, and how to influence the behavior of the database to achieve performance gains.

The primary target audiences for this course are senior application designers and database developers, PL/SQL developers, database administrators, web server administrators, system administrators, implementation specialists, and data center support engineers.

This course helps prepare student to take exam 1Z0-054: Oracle Database 11G R2: Performance Tuning.

Goals
  1. Learn the unique and differing tuning issues found in online database applications.
  2. Learn about resource and data warehouse environments and the important metrics of SQL statement.
  3. Learn about the internal mechanisms use for SQL statement execution within a database instance.
  4. Learn how these can affect performance for good or bad, including the Optimizer facilities.
  5. Learn about Engine, Estimator and Plan Generator.
  6. Learn to use a variety of techniques to examine the details of SQL statement execution, spotting trouble.
  7. Learn about areas and bottlenecks that require tuning.
  8. Learn about the Auto-Task framework and how to manage the automatic collection of Optimizer.
  9. Learn about statistics and automatic SQL tuning using both the programmatic and Enterprise Manager interfaces.
  10. Learn how statistic deficiencies can dramatically degrade performance.
  11. Learn how statistic deficiencies can dramatically degrade performance, and how these problems are resolved through customized Optimizer statistics collection procedures using the DBMS_STATS().
  12. Learn to package, system statistics, histograms, expression statistics and MultiColumn statistics.
  13. Learn to influence the behavior of the Optimizer by setting database parameters and other SQL tuning.
  14. Learn to utilize the database advisory framework and the SQL Tuning and SQL Access advisors.
  15. Learn to use plan management to achieve plan stability that is adaptive and even dynamic.
  16. Learn to understand the self-tuning infrastructure and the automatic SQL tuning capabilities.
  17. Learn to employ SQL hints embedded into the statement text to resolve unique tuning challenges.
  18. Learn to identify poorly performing SQL statements using real-time SQL monitoring and application.
  19. Learn techniques such as DBMS_MONITOR(), trcsess and tkprof.
Outline
  1. Tuning and the Oracle Database Advisory Framework
    1. The Challenges of Tuning
    2. Performance Metrics
    3. Management and Advisory Framework
    4. ADDM and AWR
    5. SQL Tuning Privileges
  2. Viewing and Monitoring the Execution Plan
    1. About the Execution Plan
    2. Collecting Performance Statistics
    3. Viewing the Execution Plan
    4. Real-time SQL Monitoring
  3. Understanding the Optimizer
    1. Optimization Methods
    2. Optimization Goals
    3. OPTIMIZER_MODE
    4. OPTIMIZER_FEATURES_ENABLE
    5. Optimizer Components
    6. Execution Plan Operations
  4. Execution Plan Methods and Operations
    1. Table Access Methods
    2. Join Methods
    3. Index Operations
    4. Data Operations
  5. Managing Optimizer Statistics
    1. More about Optimizer Statistics
    2. Automatic Maintenance Tasks
    3. Manually Gathering Statistics
  6. GATHER_TABLE_STATS()
    1. GATHER_INDEX_STATS()
    2. GATHER_SCHEMA_STATS()
    3. GATHER_DATABASE_STATS()
    4. GATHER_SYS Parameter
    5. GATHER_DICTIONARY_STATS()
    6. GATHER_FIXED_OBJECTS_STATS()
    7. USING HISTORICAL STATISTICS
    8. Dynamic Sampling
    9. Locking Statistics
  7. Enhanced Optimizer Statistics
    1. About Optimizer System Statistics
    2. Manage System Statistics
    3. CREATE_STAT_TABLE(), DROP_STAT_TABLE()
    4. CREATE_STAT_TABLE()
    5. DROP_STAT_TABLE()
    6. GATHER_SYSTEM_STATS()
    7. GET_SYSTEM_STATS()
    8. SET_SYSTEM_STATS()
    9. IMPORT_SYSTEM_STATS(),
    10. IMPORT_SYSTEM_STATS()
    11. EXPORT_SYSTEM_STATS()
    12. DELETE_SYSTEM_STATS()
    13. Pending and Published Statistics
  8. Histograms and Extended Statistics
    1. Why Are Histograms Needed?
    2. Histograms Internal Structure
    3. Manually Managing Histograms
    4. Expression Statistics
    5. EXPORT_SYSTEM_STATS()
    6. Multicolumn Statistics
  9. Application Tracing
    1. Application Tracing Packages
    2. SET_SQL_TRACE()
    3. TRACE_ENABLE()
    4. SET_IDENTIFIER()
    5. EM Application Monitoring and Tuning
    6. REAL-time Monitoring
    7. Using TRCSESS and TKPROF
  10. ADDM and the SQL Tuning Advisor
    1. ADDM Performance Analysis
    2. Using the SQL Tuning Advisor
    3. Automatic SQL Tuning
    4. Configuring Automatic SQL Tuning
  11. The SQL Access Advisor
    1. Using the SQL Access Advisor
    2. SQL Access Advisor Templates
    3. Performing a Quick Tune Task
    4. Index Database Parameters
    5. OPTIMIZER_INDEX_COST_ADJ
    6. OPTIMIZER_INDEX_CACHING
    7. SKIP_UNUSABLE_INDEXES
    8. OPTIMIZER_USE_INVISIBLE_INDEXES
  12. Plan Management
    1. SQL Management Base Architecture
    2. LOAD_PLANS_FROM_SQLSET()
    3. LOAD_PLANS_FROM_CURSOR_CACHE()
    4. Using Plan Baselines
    5. Plan Management Using EM
  13. Managing Cursor Sharing
    1. About Cursor Sharing
    2. Bind Variables and Cursor Sharing
    3. THE CURSOR_SHARING Parameter
    4. Finding Candidates for Rewrite
  14. Optimizer Hints
    1. What Are Hints?
    2. Hint Explanations
Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Prerequisite Courses

Courses that can help you meet these prerequisites:

Preparing for Class

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

72,346

Students who have taken Live Online Training

15,155

Organization who trust Webucator for their training needs

100%

Satisfaction guarantee and retake option

9.42

Students rated our trainers 9.42 out of 10 based on 5,232 reviews

Contact Us or call 1-877-932-8228