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

This Oracle Database 11g R2 - SQL Tuning, Architecture, and Internals training class teaches the underlying architecture of the Oracle database environment. Students will learn to build efficient SQL statements and to tune database applications.

This course is intended for senior application designers and database developers, PL/SQL developers, database administrators, web server administrators, system administrators, implementation specialists, and data center support engineers. IT managers and executives who require an understanding of the technology will also benefit from this course.

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

Training for your Team

Length: 5 Days
  • Private Class for your Team
  • Online or On-location
  • Customizable
  • Expert Instructors

Training for Yourself

$2,625.00 or 5 vouchers

Upcoming Classes

  • See More Classes

Please select a class.
  • Live Online Training
  • For Individuals
  • Expert Instructors
  • Guaranteed to Run
  • 100% Free Re-take Option
  • 1-minute Video

What people say about our training

This was a very thorough class. The instructor has a very exceptional knowledge base and ability to make everyone who is willing to learn an expert!
Derek Imig
Nevada Army National Guard
I liked everything about this class! Great tool, great teacher, the convenience of the online instructor-led format, and the fantastic student/teacher ratio to name a few...
Susan Cohen
Hewlett Packard
Really enjoyed the class and the instructor. I was worried at first about it being a live online class, but the format was great. I could sit in my office and if something was going wrong the instructor could access our computer and walk us through. GREAT CLASS, GREAT INSTRUCTOR!!! Would definitely recommend.
Debbie Evans
West Coast Florida Enterprises
This was a great course! I really enjoyed working hands on with the instructor and the discussion kept the flow of the course seamless and engaging.
Laura Gamez
Texas DFPS

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

61,023

Students who have taken Instructor-led Training

11,715

Organizations who trust Webucator for their Instructor-led training needs

100%

Satisfaction guarantee and retake option

9.29

Students rated our trainers 9.29 out of 10 based on 28,956 reviews

Contact Us or call 1-877-932-8228