Today, OPS team raised an issue related to a program using IOT. The program generated too many archive logs thus it took more time to FTP archive logs to our DR server and apply them. 1~4 hours lag were caused by this issue. So our focus is why this program is generating so many archived logs.
Following is my testing:
CREATE TABLE IOT_OE_ORDER_LINES
(
LINE_ID NUMBER NOT NULL PRIMARY KEY,
ORG_ID NUMBER,
HEADER_ID NUMBER NOT NULL,
LINE_TYPE_ID NUMBER NOT NULL,
LINE_NUMBER NUMBER NOT NULL,
ORDERED_ITEM VARCHAR2(2000 BYTE),
REQUEST_DATE DATE,
PROMISE_DATE DATE,
SCHEDULE_SHIP_DATE DATE,
ORDER_QUANTITY_UOM VARCHAR2(3 BYTE),
PRICING_QUANTITY NUMBER,
PRICING_QUANTITY_UOM VARCHAR2(3 BYTE)
)
ORGANIZATION INDEX;
In sqlplus, set autot trace exp stat
SQL>
INSERT INTO IOT_OE_ORDER_LINES ( LINE_ID
,ORG_ID
,HEADER_ID
,LINE_TYPE_ID
,LINE_NUMBER
,ORDERED_ITEM
,REQUEST_DATE
,PROMISE_DATE
,SCHEDULE_SHIP_DATE
,ORDER_QUANTITY_UOM
,PRICING_QUANTITY
,PRICING_QUANTITY_UOM)
SELECT * FROM (SELECT LINE_ID
,ORG_ID
,HEADER_ID
,LINE_TYPE_ID
,LINE_NUMBER
,ORDERED_ITEM
,REQUEST_DATE
,PROMISE_DATE
,SCHEDULE_SHIP_DATE
,ORDER_QUANTITY_UOM
,PRICING_QUANTITY
,PRICING_QUANTITY_UOM
FROM OE_ORDER_LINES_ALL
ORDER BY LINE_ID
)
WHERE ROWNUM < 1000;
The order by line_id clause will reduce the redo size. When I tried to insert into a heap table, it generated even less redos.