Help, SQL execution suddenly slowed down, please everyone

The full update to this order of magnitude will certainly be slow.

First. Your records don't have to be in the same partition, second. I don't understand why so many people suggest that you build an index, the more indexes you build, the slower your updates will be, because you update the records at the same time as you update the index.

Third. You must know that the bottleneck of slow updates is **. Whether it's too much reading and writing, or not enough memory, or not fast enough, then prescribe the right medicine.

Here are two simple ways to do what might work:

First, split the table in 100w rows vertically into two and connect them with foreign key relationships, one containing small and frequently changing data such as id, foreign key, state value, time, etc., and the other containing large and infrequently changing data, such as very long strings, xml, text, etc.

In this way, the operation of this small table during update can greatly save memory and CPU overhead, and reduce disk operations.

The downside is that the query is slower.

Second: Cut the 100w row horizontally into many tables, for example, the records of each month are packed in a table, so that the number of records in each table may only be tens of thousands, and the query and update will be much faster.

The disadvantage is that the query and update are not as easy to write as the original.

If it is a SQL server, there is a mechanism such as read-ahead to improve the query efficiency of SQL Server, which means that the physical table is read into the system's cache synchronously when the execution plan is generated, which can reduce the IO. However, if the system runs for a long time, the larger the cache will be occupied, which will also affect the performance of the system. Of course, this is only one of the possibilities, and the specific situation needs to be analyzed on a case-by-case basis.

It will also take time to turn on the data connection.

Problem description The data of a table in the oracle database has been more than 100 million, and this table has created an independent index, due to business needs, it is necessary to insert 10,000 records into this table twice a day, due to the large amount of data, each insertion takes more than an hour, which seriously affects the efficiency, so the algorithm of the system is modified to store only the new records of the day in this table, and after the table is truncate, when the update operation on this table is executed the next day, it is very time-consuming, and when there are more than 100 million pieces of data in the table, this sql statement takes time in the second table When there are 10,000 pieces of data, this SQL statement takes several hours, and after consulting DBA, it is concluded that the index needs to be rebuilt, and this operation is completed in seconds, but the problem still appears on the third day, and DBA is looking for the reason. Is it necessary to rebuild the index every time the truncate table is rebuilt?

For this problem, DBA does not give an explanation for the slag segment theory, and speculates that the main reason is Oracle's complex query optimization algorithm.

The solution given by the final backup DBA.

truncate table

drop index

insert data

create index

yze table table_name pute statistics;Newly generated stats for re-imitating Liang Tu.

lishixinzhi/article/program/oracle/201311/16938

This is a big problem, and a thick book could be written. It is advisable to read books on DBA and database optimization.

Is it because the memory is not released in time during your use?

That's because the data is too large to read and write, and the performance of your machine itself is insufficient, so if necessary, optimize the database, you can build fields and indexes.

Restarting the machine is the best solution.