MES case Study 1-Large table concurrency query

A factory is the production of digital metal shell, nearly 1 million pieces of production per day, with the advent of Christmas, customer orders a large increase, but production is not keep up, after a preliminary analysis, found that the problem occurred in the laser engraving two-dimensional code of the station, because the laser carving machine from the MES to take the time too long, resulting in production bottlenecks.

The main function of MES is to do production traceability, including: production process record, key station inspection, quality problem collection, etc.

In the laser engraving station, the client program from the MES to find the corresponding model of the minimum serial number, and then passed to the laser engraving machine.

The core logic for querying SQL is:

SELECT MIN (Serial_number) From T_product_history T WHERE t.type = ' PN1 '//Product type and t.status = 1; Product status, 1 indicates not yet radium carving

With Oracle AWR, you can get the total execution time for this query SQL over a period of time.

In addition, we can query the number of executions of sql_text.

Divide the two and get the average time for this sql: 2 seconds!

Worse, after looking at the client code, found not to do concurrent processing, when the scene more than 20 laser engraving machine at the same time, workers once found that the response is slow, will continue to click the Manual Request button, resulting in multiple concurrency, resulting in longer query execution time.

The next step is to carefully analyze the business of the T_product_history table.

This table is a product of the production history table, the entire production process to go through nearly 100 collection stations, each product through each station will add a record in this table, so that 1 million pieces a day to add nearly 100 million records, the table capacity is quite large.

This table is also partitioned, is partitioned by month, but the Where condition (product type and status) of the query SQL does not take advantage of the partitioning field, so the system scans the records from the index, and the index is not partitioned, and the index capacity is already very large, the consequence is that each query will be scanned tens of billions of records , the natural efficiency is low.

In fact, there is another table in the database t_product_status, used to represent the state of the product, the data capacity is much smaller, but since the work order issued, the status value is 1, until the completion of the status of 2, so you can not get from this table has not yet the minimum number of laser engraving.

Without changing the functionality of the two tables, only the alternative.

First of all, we analyze the characteristics of production: a large number of small varieties, each work order corresponding to a product, the amount of each work order is very large, generally in the amount of tens of thousands of to hundreds of thousands of, after each work order issued, in the Radium carving station to do continuous production, the middle will not jump serial number.

Therefore, we can abstract the minimum sequence number to be taken by the radium carving into a characteristic of the work order that matches the condition, and a pointer value of intermediate quantity.

Start by building an extended attribute table T_wo_pointer for a ticket, defining the following properties: Ticket number, product type, status, quantity, minimum serial, current pointer value, current serial number.

When the ticket is issued, add a small logic in the original stored procedure, that is, add a record in this extension table, the state is set to 1 is not yet a laser carving, and calculate the minimum sequence number, the value of the collocated pointer is 0.

At the laser engraving station, the SQL changes for the query are:

SELECT MIN (CURRENT_SN),//Current serial number Pointer//Current pointer value From T_wo_pointer WHERE t.type = ' PN1 '//Product type and t.status = 1; Product status, 1 indicates not yet radium carving

The minimum sequence number and pointer values that match the criteria are then queried.

The serial number is output, then the pointer value is added 1 and the current serial number is updated, if the pointer value = number of work orders, the status of the ticket is changed to 2.

Then add concurrent transactions.

After this processing, and did not change the business logic, but the query time from the original 2 seconds into only 1.8 microseconds (the table to query only more than 100 records), immediately resolved the bottleneck here, increased the capacity of 8%, to help the factory successfully completed the Christmas order.

The lesson I learned from this case is:

1. Big table query to be cautious.
2. Do not perform production site control logic in the history table as much as possible.
3. When a large table is partitioned, the partition field is not necessarily used by the query statement, and the index of the large table does not have to be partitioned.
4. Sequential numbers and serial numbers can be considered as a pointer table, provided that the concurrency transaction is handled well.

MES case Study 1-Large table concurrency query