Oracle Database 11g R2 - SQL Tuning, Architecture, and Internals

Customized Onsite Training

5
Days
  • Customized Content
  • For Groups of 5+
  • Online or On-location
  • Expert Instructors

Live Online Training

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

Upcoming Classes

  • See More Classes

Please select a class.
Overview

This Oracle Database 11g R2 - SQL Tuning, Architecture, and Internals training class teaches the underlying architecture of the Oracle database environment. This course is also intended for information technology (IT) managers and executives who require an understanding of the technology.

This course will equip database administrators and application developers to build efficient SQL statements and to tune database applications.

The primary target audiences for this course includes 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 students for exam 1Z0-054: Oracle Database 11G R2: Performance Tuning.

Goals
  1. Consider the unique and differing tuning issues found in online database applications, enterprise resource and data warehouse environments and the important metrics of SQL statement performance.
  2. Learn about the internal mechanisms use for SQL statement execution within a database instance and how these can affect performance for good or bad, including the Optimizer facilities known as the Transformation Engine, Estimator and Plan Generator.
  3. Use a variety of techniques to examine the details of SQL statement execution, spotting trouble areas and bottlenecks which require tuning.
  4. Learn about the Auto-Task framework and how to manage the automatic collection of Optimizer statistics and automatic SQL tuning using both the programmatic and Enterprise Manager interfaces.
  5. Learn how statistic deficiencies can dramatically degrade performance, and how these problems are resolved through customized Optimizer statistics collection procedures using the DBMS_STATS() package, system statistics, histograms, expression statistics and MultiColumn statistics.
  6. Influence the behavior of the Optimizer by setting database parameters and other SQL tuning techniques.
  7. Utilize the database advisory framework and the SQL Tuning and SQL Access advisors.
  8. Use plan management to achieve plan stability which is adaptive and even dynamic.
  9. Understand the self-tuning infrastructure and the automatic SQL tuning capabilities found within the database.
  10. Employ SQL hints embedded into the statement text to resolve unique tuning challenges.
  11. Learn to identify poorly performing SQL statements using real-time SQL monitoring and application tracing techniques such as DBMS_MONITOR(), trcsess and tkprof.
Outline
  1. The Broader Oracle Enterprise Architecture
    1. About Enterprise Architectures
    2. About Parallelization
    3. SMP
    4. The Database Server Stack
    5. The Database Server Software
    6. V$VERSION
  2. Exploring the Oracle Database Instance
    1. Database Instance Configurations
    2. About The SGA
    3. Using V$SGA_DYNAMIC_COMPONENTS
    4. About The PGA
    5. Managing Instance Memory
  3. Exploring the Instance Processes
    1. Instance Background Processes
    2. SQL Execution Processes
    3. Using V$SESSION
    4. About The PGA
    5. SQL Statement Execution
  4. Exploring Logical Storage: Database Objects
    1. Application Database Objects
    2. Schema and Ownership Context
    3. Editions Context and Redefinition
  5. Exploring Physical Storage: Database Files
    1. About the Database Files
    2. Database Server Parameter File
    3. Control Files
    4. Alert LOG and TRACE Files
  6. Exploring Physical Storage: TABLESPACES
    1. TABLESPACES and DATA FILES
    2. AUTOEXTEND Options
    3. About Temporary Segments
    4. About Index Segments
  7. 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
  8. 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
  9. Understanding the Optimizer
    1. Optimization Methods
    2. Optimization Goals
    3. OPTIMIZER_MODE
    4. OPTIMIZER_FEATURES_ENABLE
    5. Optimizer Components
    6. Execution Plan Operations
  10. Execution Plan Methods and Operations
    1. Table Access Methods
    2. Join Methods
    3. Index Operations
    4. Data Operations
  11. Managing Optimizer Statistics
    1. More about Optimizer Statistics
    2. Automatic Maintenance Tasks
    3. Manually Gathering Statistics
    4. GATHER_TABLE_STATS()
    5. GATHER_INDEX_STATS()
    6. GATHER_SCHEMA_STATS()
    7. GATHER_DATABASE_STATS()
    8. GATHER_SYS Parameter
    9. GATHER_DICTIONARY_STATS()
    10. GATHER_FIXED_OBJECTS_STATS()
    11. Using Historical Statistics
    12. Dynamic Sampling
    13. Locking Statistics
  12. 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. EXPORT_SYSTEM_STATS()
    13. DELETE_SYSTEM_STATS()
    14. Pending and Published Statistics
  13. Histograms and Extended Statistics
    1. Why Are Histograms Needed?
    2. Histograms Internal Structure
    3. Manually Managing Histograms
    4. Expression Statistics
    5. Multicolumn Statistics
  14. 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
  15. 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
  16. 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
  17. Plan Management
    1. SQL Management Base Architecture
    2. OAD_PLANS_FROM_SQLSET()
    3. LOAD_PLANS_FROM_CURSOR_CACHE()
    4. Using Plan Baselines
    5. Plan Management Using EM
  18. Managing Cursor Sharing
    1. About Cursor Sharing
    2. BIND Variables and Cursor Sharing
    3. The CURSOR_SHARING Parameter
    4. Finding Candidates for Rewrite
  19. Optimizer Hints
    1. What Are Hints?
    2. Hint Explanations
  20. The Broader Oracle Enterprise Architecture
    1. About Enterprise Architectures
    2. About Parallelization
    3. SMP
    4. The Database Server Stack
    5. The Database Server Software
    6. V$VERSION
  21. Exploring the Oracle Database Instance
    1. Database Instance Configurations
    2. About The SGA
    3. Using V$SGA_DYNAMIC_COMPONENTS
    4. About The PGA
    5. Managing Instance Memory
  22. Exploring the Instance Processes
    1. Instance Background Processes
    2. SQL Execution Processes
    3. Using V$SESSION
    4. About The PGA
    5. SQL Statement Execution
  23. Exploring Logical Storage: Database Objects
    1. Application Database Objects
    2. Schema and Ownership Context
    3. Editions Context and Redefinition
  24. Exploring Physical Storage: Database Files
    1. About the Database Files
    2. Database Server Parameter File
    3. Control Files
    4. Alert LOG and TRACE Files
  25. Exploring Physical Storage: TABLESPACES
    1. TABLESPACES and DATA FILES
    2. AUTOEXTEND Options
    3. About Temporary Segments
    4. About Index Segments
  26. 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
  27. 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
  28. Understanding the Optimizer
    1. Optimization Methods
    2. Optimization Goals
    3. OPTIMIZER_MODE
    4. OPTIMIZER_FEATURES_ENABLE
    5. Optimizer Components
    6. Execution Plan Operations
  29. Execution Plan Methods and Operations
    1. Table Access Methods
    2. Join Methods
    3. Index Operations
    4. Data Operations
  30. Managing Optimizer Statistics
    1. More about Optimizer Statistics
    2. Automatic Maintenance Tasks
    3. Manually Gathering Statistics
    4. GATHER_TABLE_STATS()
    5. GATHER_INDEX_STATS()
    6. GATHER_SCHEMA_STATS()
    7. GATHER_DATABASE_STATS()
    8. GATHER_SYS Parameter
    9. GATHER_DICTIONARY_STATS()
    10. GATHER_FIXED_OBJECTS_STATS()
    11. Using Historical Statistics
    12. Dynamic Sampling
    13. Locking Statistics
  31. 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. EXPORT_SYSTEM_STATS()
    13. DELETE_SYSTEM_STATS()
    14. Pending and Published Statistics
  32. Histograms and Extended Statistics
    1. Why Are Histograms Needed?
    2. Histograms Internal Structure
    3. Manually Managing Histograms
    4. Expression Statistics
    5. Multicolumn Statistics
  33. 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
  34. 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
  35. 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
  36. Plan Management
    1. SQL Management Base Architecture
    2. OAD_PLANS_FROM_SQLSET()
    3. LOAD_PLANS_FROM_CURSOR_CACHE()
    4. Using Plan Baselines
    5. Plan Management Using EM
  37. Managing Cursor Sharing
    1. About Cursor Sharing
    2. BIND Variables and Cursor Sharing
    3. The CURSOR_SHARING Parameter
    4. Finding Candidates for Rewrite
  38. 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.

Class Prerequisites

Experience in the following is required for this Oracle class:

  • Introductory experience with SQL.
  • Experience with relational design and data modeling.
  • Experience with Linux for Windows.
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

74,839

Students who have taken Live Online Training

15,233

Organizations who trust Webucator for their training needs

100%

Satisfaction guarantee and retake option

9.41

Students rated our trainers 9.41 out of 10 based on 5,189 reviews

Contact Us or call 1-877-932-8228