Hi,
This is an Oracle E-Business Suite (EBS 12.1.3) database, version 12.1.0.2
running on RHL 6.8.
The 10046 trace file generated on a standard Oracle statement shows the
following statistics:
INSERT INTO QP_NPREQ_LINE_ATTRS_TMP (LINE_INDEX, LINE_DETAIL_INDEX,
ATTRIBUTE_LEVEL, ATTRIBUTE_TYPE, LIST_HEADER_ID, LIST_LINE_ID, CONTEXT,
ATTRIBUTE, VALUE_FROM, SETUP_VALUE_FROM, SETUP_VALUE_TO, GROUPING_NUMBER,
COMPARISON_OPERATOR_TYPE_CODE, VALIDATED_FLAG, APPLIED_FLAG,
PRICING_STATUS_CODE, PRICING_STATUS_TEXT, QUALIFIER_PRECEDENCE, DATATYPE,
PRICING_ATTR_FLAG, QUALIFIER_TYPE, PRODUCT_UOM_CODE, EXCLUDER_FLAG,
PRICING_PHASE_ID, INCOMPATABILITY_GRP_CODE, LINE_DETAIL_TYPE_CODE,
MODIFIER_LEVEL_CODE, PRIMARY_UOM_FLAG )
VALUES
(:B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 ,
:B13 , :B14 , :B15 , :B16 , :B17 , :B18 , :B19 , :B20 , :B21 , :B22 , :B23 ,
:B24 , :B25 , :B26 , :B27 , :B28 )
;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 540.97 953.67 33 5758514 180288179 6583352
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 540.97 953.67 33 5758514 180288179 6583352
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log buffer space 3246 0.21 71.98
buffer busy waits 1468 0.10 3.00
latch free 3 0.00 0.00
log file switch completion 7 0.21 1.00
log file switch (private strand flush incomplete)
17 2.55 11.59
latch: cache buffers chains 5 0.00 0.00
latch: redo allocation 11 0.00 0.00
Disk file operations I/O 5 0.00 0.00
latch: undo global data 6 0.00 0.00
db file sequential read 33 0.00 0.00
log file switch (checkpoint incomplete) 39 16.54 241.64
free buffer waits 122 3.20 83.04
enq: HW - contention 4 0.00 0.00
********************************************************************************
A few pieces of information:
1. There were two concurrent processes running the above statement. So,
there was concurrency involved. The TKPROF of the 10046 trace files of both
processes have almost identical waits and wait times.
2. QP_NPREQ_LINE_ATTRS_TMP is an editioning view defined on a temporary
table, QP_PREQ_LINE_ATTRS_TMP_T.
3. There are ten indexes defined on this temporary table.
4. Each redo log file is 1GB in size and there are eight redo log groups.
5. There are four DBWR processes for this database
6. The DB_CACHE_SIZE is 16GB in size
7. While the average redo switches are 2 per minute, there were intervals
when three logs were being switched per minute.
What I am trying to understand is:
1. Why would insert into a global temporary table generate so much redo?
2. Initially there were four redo log groups but I added another four to
see if that would alleviate the "log file switch (checkpoint incomplete)" wait
but it did not help. Is that because even four DBWR processes are not enough to
perform checkpoints in a timely manner?
Thanks
Amir