sequence主键索引单向增长问题

Yong Huang

reserve != reverse

Reverse key indexes appear more on paper, or in technical articles, than in practice, RAC or not. I personally have never used it.

> 表中很可能有其他索引，实际上可能无法保证所有索引的clustering factor都小吧？

Correct. Clustering factor can be made small for one index only, unless, of course, multiple indexes start with the same column, or their starting columns are correlated.

bisal

Yong Huang 发表于 2015-2-27 23:07
reserve != reverse

Reverse key indexes appear more on paper, or in technical articles, than in pr ...

Sorry，不清醒了，将您的reserve看成reverse了。
“The idea I talked about very early is still a good one: Common practice is that you reserve a large range of sequence values for one instance, but only use sequence, not a combination of it with anything else, as PK.”

这个问题好像又回到最初了，我理解如果没有并发压力的情况下，sequence作为自增主键最适合了，但如果有高并发，那么他的主键索引单向增长问题才会比较突出，RAC下可能会放大(因为还会涉及不同节点间争用同一个索引块的消耗)，因此这帖子一直说的是做一种复合主键，打散单向增长的索引，并保证不同RAC节点间不会产生同一个索引块的争用，纯粹的独立使用。
不知道是否这样理解？

Yong Huang

> 如果有高并发，那么他的主键索引单向增长问题才会比较突出，RAC下可能会放大(因为还会涉及不同节点间争用同一个索引块的消耗)

The part in your parenthesis may not be true if you allocate different ranges of sequence values in different RAC nodes, or concatenate instance ID with sequence (just these two components).

Another trick people use on RAC is to partition the applications in functionality. For example, if your RAC database is the backend for multiple applications, use service to limit one application to connect to one RAC instance, another application to another instance. By limit, I mean set the preferred instance. You still allow the other instance to serve as a failover (called "available") instance. Alternatively, use one RAC instance for DML and the other for mostly queries. Partitioning applications is a general strategy, not specific to sequences.

> 做一种复合主键，打散单向增长的索引

In the real world, if a table has a high insert rate, the table probably uses a single column surrogate (meaningless, usually sequence-based) key, not a natural key. I don't recall seeing anybody using a composite key for a major table with high concurrent insert rate. I have a feeling that the overhead of dealing with the multiple columns of the key, compared with the simple single column, outweighs the benefit, if any, of reducing the contention. If any shop does that, I'd like to hear. In practice, check enqueue contention on SQ. If it's indeed high, do these in order: (1) make sure its cache is high and order is NOT specified, (2) allocate separate ranges to nodes, (3) partition the applications (see above), (4) prefix sequence with instance ID to be used as PK, (5) something more complicated.

bisal

Yong Huang 发表于 2015-3-2 23:12
> 如果有高并发，那么他的主键索引单向增长问题才会比较突出，RAC下可能会放大(因为还会涉及不同节点间争用 ...

>“The part in your parenthesis may not be true if you allocate different ranges of sequence values in different RAC nodes, or concatenate instance ID with sequence (just these two components).”

这里您说的不同RAC节点使用不同范围的sequence值，其实就是这篇帖子一直说的：
“instacne_number||mod(sessionid)||sequence.nextval”这种方式的复合主键，前缀是实例号，区别于不同的实例。

>You still allow the other instance to serve as a failover (called "available") instance.

如果不同应用连接不同节点做不同的业务，您说可以failover，就是TNS客户端配置中用failover的方式配置，例如：
TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.3)(PORT = 1521))
    (LOAD_BALANCE = no)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

> “做一种复合主键，打散单向增长的索引”
可能我没说清楚，我这说的复合主键还是上面的“instacne_number||mod(sessionid)||sequence.nextval”。
如果是这样，以下两步是不是已经满足了？
(2) allocate separate ranges to nodes,
(4) prefix sequence with instance ID to be used as PK,

Yong Huang

> 不同应用连接不同节点做不同的业务，您说可以failover，就是TNS客户端配置中用failover的方式配置

Beginning with 11g, the recommended TNS entry is to use SCAN (single client access name). There's no need to have multiple address lines in the entry combined with failover_* options.

> 我这说的复合主键还是上面的“instacne_number||mod(sessionid)||sequence.nextval”。
> 如果是这样，以下两步是不是已经满足了？

Yes.

bisal

Yong Huang 发表于 2015-3-4 04:29
> 不同应用连接不同节点做不同的业务，您说可以failover，就是TNS客户端配置中用failover的方式配置

Beg ...

Thanks

bisal

Yong Huang 发表于 2015-3-4 04:29
> 不同应用连接不同节点做不同的业务，您说可以failover，就是TNS客户端配置中用failover的方式配置

Beg ...

黄老师，使用“select instacne_number||mod(sessionid)||sequence.nextval from v$instance”可以创建主键ID，现在有这么个问题，就是有些场景会在一次交易中插入不同表或相同表很多记录，例如200条，每次插之前就需要执行一次“select instacne_number||mod(sessionid)||sequence.nextval from v$instance"，就相当要执行200次这个查询操作，时间累积起来，就比较长了。
这块还有什么可优化的余地么？有什么方法可以批量一次获得任意多的“instacne_number||mod(sessionid)||sequence.nextval”？

Yong Huang

Hi Bisal, did you monitor your database for top wait events? If enqueue SQ wait is not near the top, I suggest you not worry about this at all. The tricks to scatter sequences on RAC are only relevant to the databases with very high concurrency in insert. All you need in other i.e. the majarity of cases is to set a large cache size for the sequence.

Let us see your top wait events, and Oracle version.