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

These waits are coming from a specific INSERT statement. I am assuming that since this GTT has been created with "ON COMMIT DELETE ROWS", there are multiple commits done by the insert statement and with each commit, the table is getting truncated? But, I am not able to find out how many commits are associated with this insert statement.

Thanks,
Amir
-----Original Message-----
From: Stefan Koehler [mailto:contact_at_soocs.de] Sent: Monday, June 05, 2017 3:24 PM
To: Hameed, Amir <Amir.Hameed_at_xerox.com>; 'ORACLE-L' <oracle-l_at_freelists.org> Subject: RE: INSERT into temp table causing "log file switch (checkpoint incomplete)"

Hi,
are these waits the summary or from the specific INSERT?

I would expect to see this kind of picture in case of TRUNCATE (MOS ID #334822.1) and not for the INSERT. You speed up the process and maybe your clean-up/reset procedure screws it up now but it is hard to say without knowing what your business process is exactly doing.  

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK
Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals  

> "Hameed, Amir" <Amir.Hameed_at_xerox.com> hat am 5. Juni 2017 um 20:57 geschrieben:
>
> Thanks Stefan.
> I set the TEMP_UNDO_ENABLED parameter to TRUE and reran the test and
> while I do not see “log file switch (checkpoint incomplete)”, the job
> ran even longer and the 10046 trace of one of the sessions showed a lot of time spent on “local write waits”, which were not there when TEMP_UNDO_ENABLED was set to the default value of FALSE:
>
> Event waited on Times Max. Wait Total Waited
> ---------------------------------------- Waited ---------- ------------
> buffer busy waits 1806 3.60 70.65
> Disk file operations I/O 23 0.00 0.00
> free buffer waits 1097 4.26 270.43
> latch: cache buffers lru chain 3 0.00 0.00
> db file sequential read 92 0.02 0.09
> latch: redo allocation 3 0.00 0.00
> latch: cache buffers chains 21 0.00 0.00
> latch: undo global data 3 0.00 0.00
> local write wait 59478 1.15 696.29
> latch free 2 0.00 0.00
> latch: messages 1 0.00 0.00
>
> **********************************************************************
> **********
>
> From the AWR captured for the duration of the run, I see a total of
> 5,275 commits or 1.42 commits/sec. This particular load runs two
> concurrent sessions of the INSERT statement and therefore, the average commit/session would be ~2,638. I have checked the TEMP tables duration and they are TRANSACTION based. All database files are located on the EMC’s VMAX frame.
��i��0���zX���+��n��{�+i�^ Received on Mon Jun 05 2017 - 21:36:06 CEST