??? The session
will have to wait until the DBA increases the size of the undo pool or until other transactions
from users in the same consumer group complete.
In the following example, we change the default value of UNDO_POOL from NULL
(unlimited) to 50000KB (50MB) for users in the resource consumer group LOW_GROUP:
begin
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'system_plan',
group_or_subplan => 'low_group',
new_comment => 'Limit undo space for low priority groups',
new_undo_pool => 50000);
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
end;
Oracle Resource Manager and other resource directives are covered in more detail in Chapter 5.
Read Consistency vs. Successful DML
For OLTP databases, generally we want DML commands to succeed at the expense of read-consistent
queries. For a DSS environment, however, we may want long-running queries to complete without
getting a ???Snapshot too old??? error. Although increasing the UNDO_RETENTION parameter or
increasing the size of the undo tablespace helps to ensure that undo blocks are available for readconsistent
queries, undo tablespaces have another characteristic to help ensure that queries will run
to completion: the RETENTION GUARANTEE setting.
Undo retention guarantee is set at the tablespace level, and it can be altered at any time.
Setting a retention guarantee for an undo tablespace ensures that an unexpired undo within the
tablespace should be retained even if it means that DML transactions might not have enough
undo space to complete successfully.
Pages:
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382