Oracle 12c Performance Tuning: Tuning Instance Memory

Database Administrators, Application Developers, Technical Consultants.


Expected Duration
131 minutes

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).


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





Multi-license discounts available for Annual and Monthly subscriptions.