In ORACLE transaction, rollback segment is used to store the before data block image. Inserting record will require min rollback segment , but delete and update will ask for more rollback space since old impages need be stored for rollback transaction. Transaction size for update/delete need be count in when config the undo tablespace in ORACLE database. If undo tablespace is manual management, then No. of rollback segments and Size of rollback segments need be config according to No. of concurrent transaction and size of transaction. Application need pick up the suitable rollback segments for their transactions. however, when parallel DML be turn on, rollback segments managment will be more complex.
From ORACLE 9i, auto undo management (AUM ) had been launched, ORACLE can allocate the rollback segments to the transaction and handle parallel DML transaction as well. But, when DB config as AUM, the application lost the control on the rollback segment specially for big transaction. And application will hit the “ORA-01628: max # extents (32765) reached for rollback segment _SYSxxxx” more frequencely.
Parallel DML will performs data block change in multiplex sessions, and transaction will distrobute to child transaction, each child transaction will be one rollback segment and auto allocated by ORACLE under AUM mode. When the transaction size is huge and has high chance to hit “ORA-01628”, the possiable soultion is enable the parallel DML in the session and undo blocks will be distributed to available rollback segments depends on the degree of parallel the system can run for the transaction
1. Rollback segmentss usage in the serial DML and parallel DML
For the serial DML, the rollback segment will be only one for one transaction, and size should be same as changed blocks in the transaction.
SQL> merge /*+ parallel(a) parallel */ into par_object_list a using type_index b on (a.TYPE_ID=b.TYPE_ID)
2 when matched then update set a.LOOP_ID = b.type_id , a.OTHER_DESC=b.TYPE_NAME;
881199 rows merged.
Elapsed: 00:00:51.47
SQL> r
1 select b.name "UNDO Segment Name", b.inst# "Instance ID", b.status$ STATUS, a.ktuxesiz "UNDO Blocks", a.ktuxeusn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxecfl
2 from x$ktuxe a, undo$ b
3* where a.ktuxesta = 'ACTIVE' and a.ktuxeusn = b.us#
UNDO Segment Name Instance ID STATUS UNDO Blocks KTUXEUSN XID_SLOT XID_SEQ KTUXECFL
------------------------------ ----------- ---------- ----------- ---------- ---------- ---------- ------------------------
_SYSSMU13_896945249$ 1 3 24431 13 15 369740 NONE
Elapsed: 00:00:00.01
the merge DML change 881199 records and used 24431 blocks (8124 byte one block) in one rollback segment, (the Max size of segment is 8*32765 blocks).
For the same transaction, if enable parallel DML and add the parallel hint in the SQL, it ran on parallel DML mode.
SQL> alter session enable parallel dml;
Session altered.
Elapsed: 00:00:00.00
SQL> merge /*+ parallel(a) parallel */ into par_object_list a using type_index b on (a.TYPE_ID=b.TYPE_ID)
2 when matched then update set a.LOOP_ID = b.type_id , a.OTHER_DESC=b.TYPE_NAME;
881199 rows merged.
Elapsed: 00:00:17.11
SQL> r
1 select b.name "UNDO Segment Name", b.inst# "Instance ID", b.status$ STATUS, a.ktuxesiz "UNDO Blocks", a.ktuxeusn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxecfl
2 from x$ktuxe a, undo$ b
3* where a.ktuxesta = 'ACTIVE' and a.ktuxeusn = b.us#
UNDO Segment Name Instance ID STATUS UNDO Blocks KTUXEUSN XID_SLOT XID_SEQ KTUXECFL
------------------------------ ----------- ---------- ----------- ---------- ---------- ---------- ------------------------
_SYSSMU1_3780397527$ 2 3 1568 1 15 339161 NONE
_SYSSMU2_2232571081$ 2 3 1594 2 26 334765 NONE
_SYSSMU3_2097677531$ 2 3 1598 3 14 296724 NONE
_SYSSMU4_1152005954$ 2 3 1486 4 5 335262 NONE
_SYSSMU6_2443381498$ 2 3 1386 6 5 338601 NONE
_SYSSMU7_3286610060$ 2 3 1462 7 27 335753 NONE
_SYSSMU8_2012382730$ 2 3 1531 8 7 296203 NONE
_SYSSMU9_1424341975$ 2 3 1657 9 2 333536 NONE
_SYSSMU11_3261146618$ 1 3 1389 11 6 327540 NONE
_SYSSMU13_896945249$ 1 3 1495 13 11 369631 NONE
_SYSSMU14_275913025$ 1 3 1539 14 10 372703 NONE
_SYSSMU15_4154100692$ 1 3 0 15 32 376512 NONE
_SYSSMU16_1795321349$ 1 3 1501 16 26 334989 NONE
_SYSSMU17_3464266284$ 1 3 1594 17 5 531840 NONE
_SYSSMU18_3575979404$ 1 3 1612 18 2 363924 NONE
_SYSSMU19_2056677713$ 1 3 1481 19 16 366822 NONE
_SYSSMU20_3160523878$ 1 3 1549 20 22 364987 NONE
17 rows selected.
the parallel DML changed same records, and used 24442 blocks in total, however, it distributed to 16 rollback segments between 2 instances. And it used 1/3 serial time to completed. for this transaction, the max size of rollback (or transaction size) can be 16*8*32765 blocks if distributing evenly.
2. Is you SQL runs on parallel mode ?
As descriped above, one way to find out the transaction is on the parallel mode or not, is to check the No. of rollback segments be used. But, it need on line enquiring on the fix tables or system view. and information is instance. when transaction commit/rollback, the information would gone. In the application deployment, check execution plan is the way to confirm whether such transaction can be on parallel mode or not.
In below is the execution plan for the “MERGE” statement ran when session enable the parallel DML.
SQL> explain plan for
merge /*+ parallel(a) parallel */ into par_object_list a using type_index b on (a.TYPE_ID=b.TYPE_ID)
when matched then update set a.LOOP_ID = b.type_id , a.OTHER_DESC=b.TYPE_NAME; 2 3
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1043000430
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1210K| 208M| 188 (3)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1210K| 213M| 188 (3)| 00:00:01 | | | Q1,02 | P->S | QC (RAND) |
| 3 | MERGE | PAR_OBJECT_LIST | | | | | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 1210K| 213M| 188 (3)| 00:00:01 | | | Q1,02 | PCWP | |
| 5 | PX SEND HYBRID (ROWID PKEY)| :TQ10001 | 1210K| 213M| 188 (3)| 00:00:01 | | | Q1,01 | P->P | HYBRID (ROW|
| 6 | VIEW | | | | | | | | Q1,01 | PCWP | |
|* 7 | HASH JOIN | | 1210K| 213M| 188 (3)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | 8 | 136 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST LOCAL| :TQ10000 | 8 | 136 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | BCST LOCAL |
| 10 | PX BLOCK ITERATOR | | 8 | 136 | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL | TYPE_INDEX | 8 | 136 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 12 | PX BLOCK ITERATOR | | 1210K| 193M| 185 (3)| 00:00:01 |KEY(SQ)|KEY(SQ)| Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL | PAR_OBJECT_LIST | 1210K| 193M| 185 (3)| 00:00:01 |KEY(SQ)|KEY(SQ)| Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------
W/O session enable parallel DML, then the execution plan will showed MERGE run path was out of parallel path. and position of Merge will move up (ID=3 will move to ID=1). It means Merge self on the DB block changes is not running on parallel mode.
3. Conclusion
The DML runs on serial mode only can consume one rollback segment, the transaction size will depend on the MAX size of single rollback segment; Parallel DML can distribute the transaction into diff rollback segments depends on the parallel degree. The limition of transaction will be MIN of (MAX sub-stransactions, MAX size of single rollback segment). For the partition table with full table changes, the MAX of partition size should not exceed the MAX size of parallel segments(either MAX extends or MAX size).
The parallel DML has to be turn on the parallel DML in the session, it can be global wise turn on or ad-hoc enable within the session. In most of case, parallel hint or append hint (for insert) is be advised to add in. For some DML, parallel hint may need add into sub-query level as well.
Fred's ORACLE DBA Works 