Skip to Main Content
  • Questions

  • Re: What is this system sequence IDGEN1$

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Alex.

Asked: April 13, 2011 - 3:12 pm UTC

Last updated: November 19, 2012 - 10:01 am UTC

Version: 10.2.0.3

Viewed 1000+ times

You Asked

On February 26, 2004 - 3am Central time zone, Saibabu Devabhaktuni asked you about the sys.IDGEN1$ sequence, and your response was to ask "Why did he need to know." First off, that is a completely legitimate response and I didn't see a follow-up so I'm assuming the discussion went no further.

However, I believe I have a good reply: Because I ran oracle's RDA perl script to check for compliance of my RAC installation and it told me that the cache size value was set to less than 10,000 and I needed to set the cache value to >= 10,000 for that sequence.

OTN didn't produce any hits on this object, and Google's most useful link was back to Saibabu's question here. In my experience, Oracle's best-practices are never to modify system objects unless directed to do so by an Oracle Support representative. So that leads me back to the original question. What is this sequence, and, additionally, why would RDA be asking me to change it?

and Tom said...


ops$tkyte%ORA11GR2> select cache_size from dba_sequences where sequence_name = 'IDGEN1$';

CACHE_SIZE
----------
      1000


In 11gr2 - it'll be set to 1000 (10,000 is wrong, it should be 1,000 - that is the recommendation).

Prior to 11gr2, it is allowed to modify it to be cached with a higher value - it is an internal sequence created at database creation that we use internally for various things.

In RAC - having frequently used sequences set with a high cache size is a good idea as it avoids us having to hit the SEQ$ table (and ping the block around) frequently.

It will also ensure that sequences cannot be used to "order" things however, not that using a sequence to order something was ever a good idea.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What to do if this is being used heavily

Jed, November 15, 2012 - 1:53 pm UTC

I was pointed to this because I have been looking at dc_sequences on a system and the misses are very high (>96%). I've modified application sequences, but it isn't improving. I found this SYS.IDGEN1$ sequence and it is incrementing by 10,000 every minute.

is there something I need to look at (since you've said the cache_size should not be modified)?
Tom Kyte
November 19, 2012 - 10:01 am UTC

what are the underlying numbers behind this 96%.

96 misses out of 100 would be 96%, but so what? What are the raw numbers here (meaning: is this an issue that needs anyones attention)

are you sure it is increasing by 10,000 every minute??

are you using advanced replication or heavy lob modification? do you see big waits for an SQ Enqueue?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.