• Online, Self-Paced
Course Description

The SGA a key component to the effective working of the Oracle database engine. This course covers the SGA as well as topics such as the buffer cache, which enhances database I/O, the shared pool, which is key to application scalability, and the PGA and its role in sort operations. This course is one of a series in the Skillsoft learning path that cover the objectives for the Oracle Database 12c: Performance Management and Tuning exam (1ZO-064).

Learning Objectives

Tuning the Shared Pool

  • start the course
  • 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


Advanced Tuning of the Shared Pool

  • 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


Tuning the Buffer Cache

  • 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


Advanced Tuning of the Buffer Cache

  • 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


Tuning PGA and Temporary Space

  • 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


Practice: Tuning the Shared Pool

  • identify large objects and configure them to be retained in the shared pool


Framework Connections

The materials within this course focus on the NICE Framework Task, Knowledge, and Skill statements identified within the indicated NICE Framework component(s):

Specialty Areas

  • Data Administration
  • Systems Administration