INSERT into temp table causing "log file switch (checkpoint incomplete)"

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Mon, 5 Jun 2017 16:27:26 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED202395A7_at_USA7109MB012.na.xerox.net>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 05 2017 - 18:27:26 CEST