Hi AskTOM,
We recently stumbled on a performance issue on our application for reservation of equipment.
Below is our simplified schema:
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
There are many other detail tables, all linked to T_RESOURCE_HEADER in the same way the network equipment is laid out.
One of the main functionality is a reservation/pre-order feature for an equipment. When that happens we will do a SELECT FOR UPDATE on an equipment and "reserve" the equipment for a certain customer.
--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;
As you can see, the problem is the business requirement states that the equipment must be FIFO based on activation date (date when eq is loaded to the DB and ready for service), oldest equipment must go out first. This is fine in a sunny day scenario (a single transaction is done in milliseconds), but when there is a surge of request coming in (can go up to 400 threads per second), reserving an equipment can take up to 5-10 minutes.
Additionally, T_RESOURCE_HEADER is around 120M records, where T_EQUIPMENT is around 600K records.
My question is :
1. from DB standpoint, is there a way we can design the table in such a way that it will relieve the bottlenecks i.e. partitions/sub-partitions, clusters, IOT, etc..
2. Is there a way to store the equipments in T_EQUIPMENT sorted by ACTIVATION_DATE by default? (might not be a good idea, but just throwing this out there, since on every status update means we need to "reorganize" the "list")
3. from application standpoint, we are streamlining the process further, but with the way the reservation is serialized by the FIFO, we are not sure what can be done from the application side.
4. will in-memory DB works if we cache the 2 tables?
Best Regards,
Chris Tarjono.
OK, the crux of the matter comes down to this:
AND hdr.RESOURCE_STATUS_CD = 'Ready'
AND hdr.RESOURCE_TYPE = 'Equipment'
AND hdr.RESOURCE_SUB_TYPE = 'Modem'
ORDER BY ACTIVATION_DATE ASC
That basically gives us two paths into the data:
1) Start with the header (ie Equip/Modem/Ready). Then get the equipment for that set, and sort by activation date
2) Start with activation date. For each one, lookup the header and see if its Equip/Modem/Ready.
What matters here then is your data. If the spread of header values (status/type/subtype) is broad, then there will not be many hit *per permutation* and thus going via header will be fine.
If there is *not* a good spread, eg, you've thousands of modems that are ready, then header first is not a good access path. In that case, then the question becomes - if I scan by activation date, how quickly am I likely to find a ready modem. Once again, comes down to how your data looks.
You dont have to *store* equipment by activation date - if you had an index on (ACTIVATION_DATE ,RESOURCE_ID) then we can walk along the index and then probe the header and stop when we get a match.
Here's an example showing how this in action (I've gone with 25 million and 1 million rows respectively)
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 ORDER BY ACTIVATION_DATE 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 ORDER BY ACTIVATION_DATE ASC)
12 WHERE ROWNUM = 1);
no rows selected
Elapsed: 00:00:05.82
So in the first instance, I quickly found a row (I chose the data specifically) and in the second instance, the effort was larger, because I walked along all of the activation dates looking for a match.
If your data is such that *neither* path is going to be good, then you'll need to consider either
a) adjusting your data model (eg denormalising the header fields into the equipment table), or
b) speeding up the access (which is where in-memory might be useful).