Today I encountered a problem with an insert statement which was executing slowly (the client started to complain). It was about sql statement which had to insert something about 592 000 rows into table that already had ~2 million rows. The process was taking about 90 seconds for simple insert into … select … from … where ….
I’ve used tkprof to trace the session in order to see which statement was causing the problem. From tkprof output (sort by elapsed time) I got this:
As you can see 77.40 seconds were used for execution of that insert statement. When I reproduced this in new session I run query for V$SESS_TIME_MODEL to see the time model statistics:
From the output we can see that ~51 seconds are spent on sequence load elapsed time which is amount of elapsed time spent getting the next sequence number from the data dictionary. This shows that the problem is with the sequence which is used for this particular statement.
I checked the sequence in USER_SEQUENCES in order to see if cache is used. But as I assumed cache was not used.
Next what I did was to set cache for this sequence. With CACHE we are pre-allocating sequence numbers in memory so those cached numbers can be accessed faster. Be careful because in some circumstances those numbers in memory can be lost and you can end up with gaps.
And when I re-run the .sql script (insert statement) it finished for ~2 seconds.
You can comment via Twitter.
Link for this post: comment