Live Chat
Monday - Friday 8am - 6pm EST Chat Now
Contact Us
Monday - Friday 8am - 8pm EST 1-866-716-6688 Other Contact Options
Checkout

Cart () Loading...

    • Quantity:
    • Delivery:
    • Dates:
    • Location:

    $

Oracle Database 12c Performance Tuning

In this course, you will learn the methodology for identifying and resolving poorly performing SQL statements.

GK# 7623

Course Overview

TOP

Ensuring optimal Oracle database performance is a key facet of a DBA's day-to-day tasks. In this learning path, you will learn the Oracle methodology for identifying and resolving poorly performing SQL statements, using a variety of tools that are built into the Oracle 12c Database. You will also learn how to use built-in performance analysis features and tools, such as the Automatic Workload Repository and the SQL Performance Analyzer.

Schedule

TOP
  • Delivery Format:
  • Date:
  • Location:
  • Access Period:

$

What You'll Learn

TOP
  • Oracle 12c Performance Tuning: Introduction
  • Oracle 12c Performance Tuning: Automatic Workload Repository           
  • Oracle 12c Performance Tuning: Metrics and Monitoring
  • Oracle 12c Performance Tuning: Application Monitoring and SQL Diagnostics
  • Oracle 12c Performance Tuning: Tuning Problem SQL Statements
  • Oracle 12c Performance Tuning: Measuring and Maintaining SQL Performance
  • Oracle 12c Performance Tuning: Tuning Instance Memory
  • Oracle 12c Performance Tuning: Performance Tuning Summary

Outline

TOP
Viewing outline for:

On-Demand Outline

Oracle 12c Performance Tuning: Introduction

  • Define the components of performance management
  • Identify who is responsible for performance management of the Oracle database
  • Describe the Oracle tuning methodology
  • Describe effective tuning goals
  • List the steps of a general tuning session
  • Identify tuning objectives
  • Examine top timed events in an AWR report
  • Define the concept of DB time
  • Describe CPU and wait time tuning dimensions
  • Define the time model
  • Work with the dynamic performance views
  • Describe database wait events
  • Describe database wait classes
  • Use SQL to display wait event statistics
  • Describe commonly observed wait events
  • Describe Oracle Enterprise Manager
  • Describe the Oracle EM architecture
  • Use OEM to view the performance hub page
  • Describe the instance alert log
  • Describe user trace files
  • Describe how to define and limit the scope of a problem
  • List common tuning problems
  • Describe the tuning life cycle phases
  • Evaluate the balance between performance and business requirements
  • Work with Oracle support to file a performance service request
  • Use SQL commands to query the dynamic performance views to view tuning information

Oracle 12c Performance Tuning: Automatic Workload Repository

  • Describe the Automatic Workload Repository
  • Describe the types of statistics stored in AWR and the AWR architecture
  • Describe how AWR is administered
  • Use a PL/SQL package to manage and modify AWR snapshot settings
  • Use EM cloud control and SQL*Plus to generate an AWR report
  • Describe the sections of an AWR report
  • Describe how to compare two periods in AWR
  • Describe the structure of an AWR comparison report
  • Describe the ADDM infrastructure
  • Describe the ADDM methodology
  • Use EM cloud control to identify and review ADDM results
  • Describe how to create an ADDM task manually and to modify ADDM attributes
  • Describe the ADDM comparison report and compare to its AWR counterpart
  • Describe the components of an ADDM comparison report
  • Describe the ASH infrastructure
  • Specify where to locate the ASH data and how to analyze it
  • Describe how to generate an ASH report
  • Describe the components of an ASH report
  • Describe the challenges and goals of Emergency Monitoring
  • Describe the challenges and goals of Real-Time ADDM
  • Describe the benefits of Real-Time ADDM
  • Use EM cloud control to invoke Real-Time ADDM and view the results
  • Use EM cloud control to generate and review an AWR report

Oracle 12c Performance Tuning: Metrics and Monitoring

  • Describe the use of metrics and the importance of statistics within an Oracle database
  • Define the Oracle database metrics and describe their benefits
  • Use dictionary views and EM cloud control to report on database metrics
  • Describe the benefits of having statistics histograms
  • Define database alerts and the dictionary views associated with them
  • Describe how AWR baselines can be used to compare two periods
  • Define AWR baselines and its benefits
  • List and describe the types of AWR baselines
  • Describe how to create AWR baselines
  • Configure AWR baselines and templates
  • Describe how baselines are used when defining alert thresholds
  • Describe database operations and provide use cases
  • List the scope and concepts around database operations
  • Describe how to identify and enable monitoring of a database operation
  • List the steps in identifying, starting, and completing a database operation
  • Describe the concept of load database operations and list the dictionary views associated with it
  • Describe the PL/SQL package needed to report on database operations
  • Use EM cloud control to create baselines and configure alert thresholds upon them

Oracle 12c Performance Tuning: Application Monitoring and SQL Diagnostics

  • List the characteristics of a service
  • List the attributes and types of services
  • Describe how services are created and managed
  • Identify how to work with services
  • Specify how services integrate with database resource management
  • Use EM Cloud Control to show how services work in conjunction with the Oracle Scheduler
  • Describe how services can be integrated with metric thresholds
  • Describe service aggregation and tracing
  • Describe how to configure service aggregation
  • List the dictionary views relevant to service performance
  • Define the phases of SQL statement processing, and the concepts of cursors and parsing
  • Explain SQL binding, executing, and fetching
  • Describe the process structures involved with DML and commit processing
  • Define the role of the Oracle optimizer
  • Define what an execution plan is and uses thereof
  • Describe how to generate and view an execution plan
  • Describe how to read and understand an execution plan
  • List the dynamic performance views which provide detail about SQL execution plans
  • Use the SQL*Plus Autotrace facility
  • Use extended SQL tracing
  • Describe how to format trace files with TKPROF
  • Interpret TKPROF output
  • Examine a bad SQL statement and gather information about it using AutoTrace and DBMS_XPLAN

Oracle 12c Performance Tuning: Tuning Problem SQL Statements

  • Describe the tasks of the query optimizer during SQL parsing
  • Describe the concepts of selectivity, cardinality and cost
  • Describe statistics used by the optimizer
  • List the database parameters which influence the behavior of the optimizer
  • Describe adaptive and dynamic execution plans
  • Describe the cardinality feedback feature
  • Describe how manipulating the optimizer_mode parameter affects SQL operations
  • Describe the ways in which data is retrieved from the database
  • Describe the most commonly used index access paths
  • Describe the concept of join operations
  • List the types of join operations used by the optimizer
  • Describe the conditions under which each join operation works best
  • Describe the basics of sorting and briefly touch on tuning sort performance
  • Describe how adding indexes can improve SQL performance
  • Describe how index maintenance operations can improve SQL performance
  • Describe how space usage of tables can impact performance
  • Define the concept of extents and how they relate to the performance of SQL operations
  • Describe the structure of a database block
  • List the ways of reducing block visits and describe the methods of block allocation
  • Describe block space management with freelists
  • Describe the concept of ASSM
  • Describe block space management with ASSM
  • Define the concepts of block migration and chaining
  • Describe how shrinking segments can improve the performance of SQL statements
  • Describe how data compression can assist in improving the performance of SQL statements
  • Describe the concept of HCC and using the compression advisor
  • Examine how inaccurate statistics can mislead the optimizer and rectifying the problem

Oracle 12c Performance Tuning: Measuring and Maintaining SQL Performance

  • Use Real Application Testing
  • Use SQL Performance Analyzer
  • Create and compare SQL Performance Analyzer tasks
  • Use the SQL Tuning Advisor to assist in tuning regressing SQL statements
  • Describe the PL/SQL functions used to manipulate the SQL Performance Analyzer
  • Describe the fundamental methods for maintaining SQL performance
  • Describe the options available when configuring Optimizer statistics
  • Describe the concept of deferred statistics publishing
  • Recognize the concept of automatic SQL tuning
  • Describe the use of the SQL Tuning Advisor
  • Identify the use of the SQL Access Advisor
  • Recognize the concept and architecture of SQL plan baselines
  • Describe how SQL plan baselines are created and evolve over time
  • Describe adaptive SQL plan management
  • Recognize how SQL plan selection works and list scenarios under which it comes into play
  • List the methods available to configure the SPM infrastructure and show the interaction with Enterprise Manager
  • Define the concept of Database Replay
  • Describe the architecture of the components that make up Database Replay
  • Describe the workflow implementation of Database Replay within Enterprise Manager
  • Describe considerations for capturing a workload
  • Describe how to properly cater for Database Replay tasks
  • Describe the process of replaying a workload
  • Compare the results of a workload replay report
  • List the packages and views associated with using and configuring Database Replay
  • Use the DBMS_SPM package to fix a particular execution plan for a given SQL statement

Oracle 12c Performance Tuning: Tuning Instance Memory

  • Describe the components that make up the shared pool
  • Define the terms latch and mutex
  • Use dictionary views and AWR to provide tuning information for the shared pool
  • Use sections of an AWR report to provide tuning information for the shared pool
  • Describe how to optimize the use of cursors
  • Describe the concept of adaptive cursor sharing
  • Describe how to properly size the shared pool
  • Describe ways to reduce fragmentation within the shared pool
  • Describe the SQL query result cache feature
  • Describe the DBMS_RESULT_CACHE package and considerations when using the SQL query result cache feature
  • Describe the Oracle database architecture in terms of the buffer cache
  • Describe the use of the buffer hash table
  • Describe the goals and techniques relevant to tuning the buffer cache
  • Identify examples of buffer cache contention
  • Describe parameters related to the buffer cache size
  • Use the buffer cache advisor
  • List the conditions under which multiple buffer pools may be needed
  • List the ways in which the dbwr background process can be optimized to allow higher throughput
  • Describe further mechanisms related to disk throughput and the buffer cache
  • Describe how Database Smart Flash Cache can extend the buffer cache
  • Configure and specify devices for the Database Smart Flash Cache
  • List memory intensive SQL operators
  • Describe how automatic PGA is configured
  • Describe how to monitor SQL memory usage and discuss the PGA advisor
  • Define the use of temporary tablespaces
  • Define the concept of temporary tablespace groups and their use
  • Describe administrative commands related to maintaining temporary tablespace
  • Identify large objects and configure them to be retained in the shared pool

Oracle 12c Performance Tuning: Performance Tuning Summary

  • Describe the concepts of dynamic SGA and memory granules
  • Describe parameters relevant to dynamic SGA and how to increase the size of a SGA component
  • Distinguish between auto-tuned and manually tuned SGA parameters
  • Enable, disable, and monitor Automatic Shared Memory Management (ASMM)
  • Describe Automatic Memory Management (AMM) and how it differs from ASMM
  • Enable and monitor Automatic Memory Management (AMM)
  • Describe a general performance tuning methodology and common symptoms of performance issues
  • Recognize common errors, their diagnosis, and solutions
  • Diagnose and tune CPU-related waits
  • Diagnose and tune redo-related waits
  • Diagnose and tune waits related to the Redo Log
  • Diagnose and tune waits related to the buffer cache
  • Diagnose and tune waits related to the shared and large pools
  • Identify best practices for creating and configuring tablespaces
  • Diagnose and tune waits related to SQL execution
  • Describe the concept of direct I/O and related waits
  • Distinguish between automated and manual statistics gathering, and lock statistics on a table
  • Configure automatic shared memory management

Who Should Attend

TOP

This course is designed for Database Administrators, Application Developers, Technical Consultants and learners looking to obtain the Oracle Database 12c: Performance Management and Tuning certification.

Course Delivery

This course is available in the following formats:

On-Demand

Train at your own pace with 24/7 access to courses that help you acquire must-have technology skills.



Request this course in a different delivery format.
Enroll