13.7. Exam Essentials

Be able to monitor space usage in a tablespace. Respond to space warning and critical alerts by adding disk space or removing objects. Adjust the space thresholds using either DBMS_SERVER_ALERT or the EM Database Control interface.

Describe two types of segments that can provide benefits beyond the traditional heap-based table segments. Use index-organized tables to maximize performance and space usage for tables exclusively accessed by the primary key. Leverage clustered tables to maximize performance for tables that are frequently joined in a query.

List the tools and methods available to monitor and maintain indexes. Be able to list the pros and cons of rebuilding an index versus coalescing an index. Identify the dynamic performance view used to determine if an index is accessed. Write a SQL query to determine the percentage of space used in each index block.

Understand how the Segment Advisor and segment shrink work together to optimize space usage and performance. Use the Segment Advisor to analyze one segment or an entire tablespace, and then use segment shrink functionality to compress one or more segments and optionally move the HWM.

Describe how sorted hash clusters—a special type of clustered table—are created and used. Identify the types of applications that can benefit from hash clusters whose elements are maintained in a sorted list for each value of the cluster key.

Identify the purpose of the redo log files. Describe the redo log file architecture. Provide details about how to create new redo log file groups and add new members to redo log file groups. Be able to drop redo log group members. Know how to clear online redo log file groups when a log file member becomes corrupted.

Identify the purpose of the Undo Advisor and Redo Logfile Size Advisor within the Oracle advisory framework. Be able to optimize the UNDO_RETENTION parameter as well as the size of the undo tablespace by using the Undo Advisor. Use the Redo Logfile Size Advisor to maximize performance by optimizing the time between log file switches.

Be able to leverage Resumable Space Allocation to correct repairable space-related errors while the statement that triggered the error is suspended. List the key columns of the data dictionary view containing information about Resumable Space Allocation sessions. Enable and disable Resumable Space Allocation for a session. Set up a system-level trigger to trap Resumable Space Allocation errors and take corrective action.