direct path write temp wait event on temp lob
We have an operation that involves the following: read lobs from a table, decompress it, write the decompressed data into temp lobs, then process the data in the temp lobs and write the processed data into other temp lobs, at the end, compress the data in the temp lob of last step and write to permanent lob. So there are a lot of lob reading and writing. The problem is the execution time of the operation's first invocation is 2-3 times of the following invocations. Here "the following invocations" could be in the same session or after the database is shutdown or restarted. The difference doesn't seem to be caused by the caching or buffering.
I generated AWR report for both cases. I found that the first invocation has long "direct path write temp" wait time as follows:
First invocation:
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
direct path write temp 7,719 79 10 64.53 User I/O
DB CPU 25 20.52
direct path read temp 18,235 0 0 0.39 User I/O
local write wait 744 0 1 0.34 User I/O
db file sequential read 23,260 0 0 0.28 User I/O
later invocation:
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU 36 61.25
direct path write temp 7,914 11 1 18.37 User I/O
sort segment request 1 1 982 1.67 Configuration
db file sequential read 25,616 1 0 1.42 User I/O
direct path read temp 18,289 0 0 0.79 User I/O
Could anyone shed some lights on this? What could have caused this difference?
Thanks,