Shulou(Shulou.com)06/01 Report--

One afternoon in June, a user reported that the order of the additional items in the medical records on the front page of the doctor's station was out of order, affecting the doctor's normal work.

From the tracked SQL, we can see that the data from the execution is not sorted in the expected way, but the same SQL running in the test library can get the normal sorted results.

The hospital has not made any adjustments recently, which is suspected to be related to a series of performance optimizations before we transferred historical data a month ago.

After some analysis, the problem is finally resolved by re-collecting statistics for the tables involved in the SQL.

It's a little weird, isn't it?

Incorrect collection of statistical information will also affect the normality of product functions.

The truth of some things is not what we see, just like the pain in a child's stomach after a few clicks of chicken blood on the grinding heart. If you believe in this kind of witchcraft, it may affect your judgment on many things. Sometimes, what we see is not necessarily the truth, not because of the lack of awe of unknown knowledge, but the basic logical reasoning and the spirit of exploring the truth.

It is true that statistics collection is a panacea for many performance problems, but for this problem, statistics collection is only one of the temporary solutions, the root cause of which is a problem in SQL writing.

Let's unravel the truth of the matter step by step.

The SQL statement is as follows:

Select rownum as serial number, code, name, content from medical record item order by code

How simple SQL, unlike those who need to turn a few pages to see the complete abnormal SQL, this simple SQL not only saves time to understand, but also reduces a lot of interference that may lead to biases in the verification results.

According to the developer's expectation, sort the result set first and then number the result set. Rownum, as a unique "pseudo column" under Oracle, is used to generate a row number incremented from 1 based on the number of rows of data results.

Students with a certain foundation may see what is wrong at once:

When Order by and Rownum are at the same level, sorting is performed last, so taking the line number first will not get the desired line number incremented by the sort result, so, as shown below, the sequence number column is "out of order".

So, the question is, if there's something wrong with this SQL, why is it normal to run in the test library?

Recently, this SQL in the product has not been modified, why it used to be normal, but now there is a sudden problem?

I didn't lie to you, the user sent a screenshot of the correct result after execution on the test library.

At first, when I saw this phenomenon, I wondered if some parameter in the database affected the sorting, such as the influence of the parameter "_ gby_hash_aggregation_enabled" on Group By sorting (a later case may be written). The problem is that there is no Group by clause in this SQL.

Think about it from the basic theory of the database, what other factors affect sorting?

If there is an index, then the index itself is sorted, there is no need to sort when reading data, and then use Rownum to take the value, can you get the expected results?

That is to say, the function of sorting first and then taking the serial number is realized in disguise.

To confirm this, a verification was done on the company's test library:

1. With the same SQL, the execution result is the same as that of the user test library, and the sequence number is sorted normally.

two。 When the primary key "medical record item _ competition" is disabled (indexed by the "code" field), the execution result is the same as that of the user formal library, and the sequence number is disordered, reproducing the problem.

Alter table Medical record Project disable constraint Medical record Project _ Competition

After the primary key is restored, the sequence numbers are sorted normally.

Alter table Medical record Project enable constraint Medical record Project _ Competition

Is it true that the primary key of the user production library is disabled and the index is lost?

It is true that some constraints are disabled during the transfer of historical data, but this table is not transferred out of the relevant table! And after transferring the data, we also checked it after we restored the constraint.

Could it be that the index is invalid for some reason during the user's later run?

Immediately query the user's production library, the primary key is valid, the index is also valid.

Looking at finding a way, I didn't expect to find that it was a dead end. Don't hold your breath. Since the problem has reappeared and the principle is clear, follow this road and look for it carefully. There must be a way out.

In the test environment, compare and analyze the execution plan before and after disabling the primary key (removing the index of the Encoding field).

The difference was found by comparison:

When there is an index, the execution plan contains "INDEX FULL SCAN" and there is no sort operation.

When there is no index, the execution plan contains "TABLE ACCESS FULL" and the sort operation "SORT ORDER BY".

What is the execution plan of the user production repository?

A query, the result is the same as my execution plan in the test environment here, went to the full table scan.

Why didn't you scan all the indexes?

Could it be that there is a problem with the collection of statistics, resulting in the cost assessment that the cost of full table scanning is lower, so I chose it?

After collecting statistics in the user production repository, the result is normal and the execution plan becomes "INDEX FULL SCAN".

The reason for the loss of statistics in the table is unknown.

In fact, there are other ways to solve the problem, for example: through Sql Profile plus prompt word to specify the index.

Since it is a problem that statistics are not collected, is it possible to reproduce the phenomenon of the problem?

Absolutely.

Let's delete the statistics and see if we can recreate the scene of the user's production repository.

Exec dbms_stats.delete_table_stats (ownname = > 'ZLHIS',tabname = >' Medical record item')

If you look at the execution plan of that SQL in PLSQL, it is true that it has changed from a full index scan to a full table scan.

Execute SQL and query the data. Hey, how come the data is not out of order?

Hasn't the implementation plan changed?

What happened?

It is time to emphasize the importance of mastering the basic theory of database. if you only learn the knowledge that can be used, you will hate less when you use the book. Only by opening a solid foundation can you deal with it on the spot in actual combat.

Empty the shared pool and take a look.

Alter system flush SHARED_POOL

Execute the data query again, , this time, the "code" field is finally out of order, a burst of delight.

What's the reason for being happy when the data are messed up? Because it's what you want, and you see the results you want.

Why did you see from PLSQL that the execution plan is a full table scan, but the query data is the same as the result of a full index scan?

Don't forget, PLSQL is just a tool, its F5 function to check the execution plan, I don't know how many people have been fooled and how many people have been cheated.

In fact, if you understand how it looks at the implementation plan, you will know that it is not really trying to deceive you. If you are interested, you can check it out through 10046. Because the space is limited, I will not elaborate on it here.

Summary:

Now that the problem is clear, let's take a look at SQL and rearrange it:

Select rownum as serial number, code, name, content from medical record item order by code

When Order by and Rownum are at the same level, sorting is the last, taking the line number first and then sorting, so that you can't get the correct line number according to the sorting result. If you can avoid sorting, you can get the desired result.

When the field in Order by is a non-empty index (the primary key index is a non-empty index), if the statistics are collected correctly, the full index scan is selected after performing the planned evaluation cost, and sorting can be avoided because the index itself is sorted.

When the table statistics are lost and the optimizer evaluates the cost, due to the lack of statistics as the basis for cost calculation, it chooses "full table scan" as the execution plan, and then sorts it.

To avoid this problem caused by statistics collection and get stable results in a variety of user environments, the SQL can be changed to:

Select Rownum As serial number, code, name, content From (Select code, name, content From medical record item Order By code)

Sort first in the subquery, then Rownum in the outer query, similarly, when we are writing Rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.