Recursive SQL statement: update seq$

Last Friday, one of our production database experienced slowness in about 1 hours time period. During that period of time, there were 60+ blocking sessions. One of the blocking session was issuing the following statment:

This looks like having somthing to do with the sequences. Our team leader suggested that we should increase the cache size for two relevant sequences to proactively avoid the problem. I acutally opened a SR with Oracle, asking what this statment is doing. But they did not give direct answer to this specific question so far. On the other hand, the guy suggested we should increase the log file size to reduce "log file sync" wait event, which appears at the top of the wait event in our statspack report. His suggestions is obviously nonsense. He must have no idea about what "log file sync" wait event is. I am disappointed with the support.

To understand this update statment, I did a test today. I created a table T and a sequence T_SEQ:

Then I have 20 sessions repeatedly executing the following insert statment simutaneouly:

isnert into t values(t_seq.nextval, 'xxxx');

I can observe there is one blocking sessions from time to time, for example:

I traced one of the 20 sessions, found that:

So it can be seen that the "update seq$ ..." statement is a recursive sql issued by Oracle sys user to update dictionary table about sequence.

I did a further test:

I set the cache of t_seq to be 20, and I trace the session when executing following statement:

insert into t
select t_seq.nextval, 'qqq' from all_objects where rownum <=100;

I found the 'update seq$' statment executed 5 times.

Then I set the cache of t_seq to be 50, I found the 'update seq$' executed 2 time.

Thus, it looks like that every time that the cached sequence number is used up, Oracle should do the caching again and issue this statment to update dictionary table.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16566331/viewspace-677243/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16566331/viewspace-677243/