Oracle 选择更新的瓶颈

嗨，AskTOM，

我们最近偶然发现了一个关于我们的设备预订申请的性能问题。
以下是我们的简化模式:

table T_RESOURCE_HEADER : a master table for all resource type, contains below columns
- RESOURCE_ID (PK)
- RESOURCE_STATUS_CD
- RESOURCE_TYPE
- RESOURCE_SUB_TYPE
- SERVICE_ID
- RESERVATION_START_DT
- RESERVATION_END_DT
- CUSTOMER_ACCOUNT_ID

table T_EQUIPMENT : a detail table of equipments
- RESOURCE_ID (PK)
- SERIAL_NO
- MODEL
- MANUFACTURER_ID
- ACTIVATION_DATE
复制

还有许多其他细节表，都以与网络设备布局相同的方式链接到T_RESOURCE_HEADER。
主要功能之一是设备的预订/预购功能。发生这种情况时，我们将选择设备进行更新，并为某个客户 “保留” 设备。

--get lock on oldest equipment for provision
SELECT RESOURCE_ID, RESOURCE_STATUS_CD, CUSTOMER_ACCOUNT_ID, RESERVATION_START_DT 
INTO a,b,c,d
FROM T_RESOURCE_HEADER WHERE RESOURCE_ID = (
SELECT RESOURCE_ID FROM (
SELECT RESOURCE_ID
FROM T_RESOURCE_HEADER hdr, T_EQUIPMENT, dtl
WHERE hdr.RESOURCE_ID = dtl.RESOURCE_ID
AND hdr.RESOURCE_STATUS_CD = 'Ready'
AND hdr.RESOURCE_TYPE = 'Equipment'
AND hdr.RESOURCE_SUB_TYPE = 'Modem'
ORDER BY ACTIVATION_DATE ASC)
WHERE ROWNUM = 1)
FOR UPDATE OF RESOURCE_STATUS_CD, CUSTOMER_ACCOUNT_ID, RESERVATION_START_DT;

--do other reservation logic here

--reserve the resource
UPDATE T_RESOURCE_HEADER 
SET RESOURCE_STATUS_CD = 'Pre-Ordered', CUSTOMER_ACCOUNT_ID = '123', RESERVATION_START_DT = SYSDATE
WHERE RESOURCE_ID = a;

COMMIT;
复制

如您所见，问题在于业务需求指出设备必须是FIFO基于激活日期 (当eq加载到数据库并准备好服务的日期)，最旧的设备必须首先退出。这在晴天的情况下很好 (单个事务以毫秒为单位完成)，但是当请求激增 (每秒最多可达到400个线程) 时，保留设备可能需要5-10分钟。

此外，T_RESOURCE_HEADER约为120万条记录，其中T_EQUIPMENT约为600万条记录。

我的问题是:
1.从数据库的角度来看，有没有一种方法可以设计表，以缓解瓶颈，即分区/子分区，群集，IOT等。
2.是否有办法将设备存储在默认情况下按ACTIVATION_DATE排序的T_EQUIPMENT中？(可能不是一个好主意，但只是将其扔到那里，因为每次状态更新都意味着我们需要 “重组” “列表”)
3.从应用程序的角度来看，我们正在进一步简化流程，但是通过FIFO序列化保留的方式，我们不确定从应用程序方面可以做什么。
4.如果我们缓存2个表，内存中的DB会工作吗？

致以最诚挚的问候，

克里斯·塔乔诺。

好吧，问题的关键归结为:

和hdr.RESOURCE_STATUS_CD = 'Ready'
和hdr.RESOURCE_TYPE = '设备'
和hdr.RESOURCE_SUB_TYPE = 'Modem'
按激活日期ASC订购

这基本上给了我们两条进入数据的路径:

1) 从标头开始 (即装备/调制解调器/就绪)。然后获取该集合的设备，并按激活日期排序

2) 从激活日期开始。对于每个标题，查找标题并查看其设备/调制解调器/准备就绪。

那么这里重要的是你的数据。如果标头值 (状态/类型/子类型) 的扩展范围很广，那么每个排列 * 将不会有很多命中，因此通过标头就可以了。

如果 * 没有 * 良好的扩展，例如，您已经准备好了数千个调制解调器，那么标头优先不是一个好的访问路径。在这种情况下，问题就变成了-如果我按激活日期扫描，我可能会多快找到一个现成的调制解调器。再一次，归结为您的数据外观。

你不必 * 存储 * 设备激活日期-如果你有一个索引 (ACTIVATION_DATE，RESOURCE_ID)，那么我们可以沿着索引走，然后探测标题，并在我们得到匹配时停止。

这里有一个例子显示这是如何在行动 (我已经与2500万和100万行分别)

SQL>
SQL> create table T_RESOURCE_HEADER (
  2   RESOURCE_ID int not null,
  3   RESOURCE_STATUS_CD int not null,
  4   RESOURCE_TYPE int not null,
  5   RESOURCE_SUB_TYPE int not null,
  6   SERVICE_ID int not null,
  7   RESERVATION_START_DT date not null,
  8   RESERVATION_END_DT date not null,
  9   CUSTOMER_ACCOUNT_ID int  not null)
 10   ;

Table created.

SQL>
SQL> create table T_EQUIPMENT (
  2   RESOURCE_ID int not null,
  3   SERIAL_NO int not null,
  4   MODEL int not null,
  5   MANUFACTURER_ID int not null,
  6   ACTIVATION_DATE date  not null)
  7   ;

Table created.

SQL>
SQL>
SQL> insert /*+ APPEND */ into  T_RESOURCE_HEADER
  2  select
  3    rownum,
  4    mod(rownum,3),
  5    mod(rownum,20),
  6    mod(rownum,50),
  7    rownum,
  8    date '2014-01-01'+mod(rownum,1000),
  9    date '2014-01-01'+mod(rownum,1000)+10,
 10    rownum
 11  from
 12   ( select 1 from dual connect by level <= 5000 ),
 13   ( select 1 from dual connect by level <= 5000 );

25000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> insert /*+ APPEND */ into  T_EQUIPMENT
  2  select
  3    rownum*100 + trunc(dbms_random.value(1,90)),
  4    rownum,
  5    mod(rownum,50),
  6    mod(rownum,50),
  7    date '2014-01-01'+ trunc(dbms_random.value(1,1000))
  8  from
  9   ( select 1 from dual connect by level <= 1000 ),
 10   ( select 1 from dual connect by level <= 1000 );

1000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index T_RESOURCE_HEADER_IX on T_RESOURCE_HEADER ( RESOURCE_ID,RESOURCE_STATUS_CD,RESOURCE_TYPE ,RESOURCE_SUB_TYPE);

Index created.

SQL>
SQL> create index T_EQUIPMENT_IX on T_EQUIPMENT ( ACTIVATION_DATE, RESOURCE_ID);

Index created.

SQL>
SQL> set timing on
SQL> SELECT RESOURCE_ID, RESOURCE_STATUS_CD, CUSTOMER_ACCOUNT_ID, RESERVATION_START_DT , RESOURCE_TYPE, RESOURCE_SUB_TYPE
  2  FROM T_RESOURCE_HEADER
  3  WHERE RESOURCE_ID in (
  4    SELECT RESOURCE_ID FROM (
  5      SELECT dtl.RESOURCE_ID
  6      FROM T_RESOURCE_HEADER hdr, T_EQUIPMENT dtl
  7      WHERE hdr.RESOURCE_ID = dtl.RESOURCE_ID
  8      AND hdr.RESOURCE_STATUS_CD = 2
  9      AND hdr.RESOURCE_TYPE = 17
 10      AND hdr.RESOURCE_SUB_TYPE = 27
 11      按激活日期ASC订购)
 12    WHERE ROWNUM = 1);

RESOURCE_ID RESOURCE_STATUS_CD CUSTOMER_ACCOUNT_ID RESERVATI RESOURCE_TYPE RESOURCE_SUB_TYPE
----------- ------------------ ------------------- --------- ------------- -----------------
   14094077                  2            14094077 19-MAR-14            17                27

1 row selected.

Elapsed: 00:00:00.57
SQL>
SQL> SELECT RESOURCE_ID, RESOURCE_STATUS_CD, CUSTOMER_ACCOUNT_ID, RESERVATION_START_DT , RESOURCE_TYPE, RESOURCE_SUB_TYPE
  2  FROM T_RESOURCE_HEADER
  3  WHERE RESOURCE_ID in (
  4    SELECT RESOURCE_ID FROM (
  5      SELECT dtl.RESOURCE_ID
  6      FROM T_RESOURCE_HEADER hdr, T_EQUIPMENT dtl
  7      WHERE hdr.RESOURCE_ID = dtl.RESOURCE_ID
  8      AND hdr.RESOURCE_STATUS_CD = 2
  9      AND hdr.RESOURCE_TYPE = 17
 10      AND hdr.RESOURCE_SUB_TYPE = 12
 11      按激活日期ASC订购)
 12    WHERE ROWNUM = 1);

no rows selected

Elapsed: 00:00:05.82
复制

所以在第一个例子中，我很快找到了一行 (我特别选择了数据)，在第二个例子中，工作力度更大，因为我沿着所有的激活日期寻找匹配。

如果您的数据使 * 两个 * 路径都不会很好，那么您需要考虑

a) 调整您的数据模型 (例如，将标题字段非归一化到设备表中)，或
b) 加快访问速度 (这是内存中可能有用的地方)。