1Z0-062 · Question #294
Your database supports an online transaction processing (OLTP) workload in which one of the applications creates a temporary table for a session and performs transactions on it. This consumes a lot of
The correct answer is A. Increase the size of the temporary tablespace. B. Enable Automatic Memory Management (AMM).. The question describes an OLTP workload where temporary table operations consume significant undo and generate redo, asking for two actions to mitigate this issue.
Question
Your database supports an online transaction processing (OLTP) workload in which one of the applications creates a temporary table for a session and performs transactions on it. This consumes a lot of undo tablespace and generates lots of redo. Which two actions would you take to solve this problem? (Choose two.)
Options
- AIncrease the size of the temporary tablespace.
- BEnable Automatic Memory Management (AMM).
- CEnable undo retention guarantee.
- DEnable temporary undo for the database.
- EIncrease the size of the redo log buffer.
How the community answered
(30 responses)- A77% (23)
- C7% (2)
- D13% (4)
- E3% (1)
Why each option
The question describes an OLTP workload where temporary table operations consume significant undo and generate redo, asking for two actions to mitigate this issue.
Increasing the size of the temporary tablespace can ensure that temporary segments for the session's temporary table operations have sufficient space, preventing contention or frequent resizing that could indirectly contribute to system overhead and impact perceived undo/redo activity.
Enabling Automatic Memory Management (AMM) allows Oracle to automatically tune the overall memory allocation between the System Global Area (SGA) and Program Global Area (PGA). This optimization can lead to more efficient processing, reduced disk I/O, and better resource utilization, which might indirectly alleviate the strain caused by high undo/redo generation from temporary table transactions.
Enabling undo retention guarantee ensures that undo information is kept for a specified duration, primarily for read consistency or Flashback queries. This setting would likely increase undo tablespace usage, not decrease it.
Enabling temporary undo for the database (via `TEMP_UNDO_ENABLED`) would directly solve the problem by having undo for temporary tables stored in the temporary tablespace and not generating redo, but it is not among the selected correct answers in this context.
Increasing the size of the redo log buffer (part of SGA) helps with writing redo entries to the redo logs more efficiently, but it does not reduce the amount of redo generated by the transactions themselves.
Concept tested: Oracle temporary table performance tuning
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-memory.html#GUID-E5E35805-E792-4809-B264-585A1D9C319E
Topics
Community Discussion
No community discussion yet for this question.