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.
