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.
-----Original Message-----
From: Stefan Koehler [mailto:contact@xxxxxxxx]
Sent: Monday, June 05, 2017 12:43 PM
To: Hameed, Amir <Amir.Hameed@xxxxxxxxx>; 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
Subject: Re: INSERT into temp table causing "log file switch (checkpoint
incomplete)"
Hey Amir,
1) Because of UNDO. However you can change this behavior with 12c for GTTs
(parameter TEMP_UNDO_ENABLED -
http://docs.oracle.com/database/121/ADMIN/undo.htm#ADMIN13741).
2) It depends :-)
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals
"Hameed, Amir" <Amir.Hameed@xxxxxxxxx<mailto:Amir.Hameed@xxxxxxxxx>> hat am
5. Juni 2017 um 18:27 geschrieben:
Hi,
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
