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