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@xxxxxxxx] ;
Sent: Monday, June 05, 2017 3:24 PM
To: Hameed, Amir <Amir.Hameed@xxxxxxxxx>; 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
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: @OracleSK
Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals
"Hameed, Amir" <Amir.Hameed@xxxxxxxxx> 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.
