Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to solve the DPR caused by Oracle without index". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to solve the DPR caused by Oracle without index".

Look directly at TOP 5 EVENTS, which is the quickest way to diagnose database problems.

Let's first take a look at the direct path read events that account for 63.33% of DB TIME. The waiting times are 78586 and the total waiting time is 3833s (about 64 minutes), while that of elapsed time is only 20 minutes. So we need to figure out what action causes such a high direct path read.

So what is direct path read? Generally speaking, the data block BLOCK (that is, the minimum storage unit of ORACLE) is always buffered to buffer cache by the background server process before being obtained by the server process. But for some large tables, buffering them to buffer cache is bound to extrude many other objects in buffer cache, namely ageing out. To avoid this, direct path read is generated, which does not need to be buffered to the cache, but is fetched directly from disk by the server process. ORACLE uses a number of parameters to control when to take direct path read.

Since the direct path read is high, go directly to see which objects have a high direct path read. By looking at segment by direct physical reads, you can get this information:

Obviously, direct physical reads is caused by visiting tbcm_catalogfile. Because physical reads= physical reads cache + physical reads direct, in addition to looking at segment by direct physical reads, it is also necessary to take a look at segment by physical reads:

The most Physical reads is still table tbcm_catalogfile. Now we know which object physical reads occurs mainly on, but we still don't know on which business (that is, on which SQL logic). Even though Physical reads is waiting the most, naturally, we need to look at the SQL statements that Physical reads has the most:

View the first SQL statement according to SQL_ID, with the text:

SELECT F_ID, F_OBJECTID, F_FILELOCATION, f_filesrclocation, F_ISONSERVER, F_DATASIZE, F_PACKAGEPATH, F_SERVERID, F_ISMAINFILE, F_FILEPROPERTY, F_DIRTYPE FROM TBCM_CATALOGFILE where Foundry OBJECTID: "SYS_B_0" and FACKAGEPATHER: "SYS_B_1" order by F_OBJECTID

Sure enough, it has something to do with the table tbcm_catalogfile. Next, let's take a look at the information about that table. I learned that the table has more than 4000000 records, and the F_OBJECTID field is almost unique, but there are no indexes on the table. Since there is no index, when the above SQL is executed, ORACLE can only choose the full table scan method, and for such a large table, it happens to meet the conditions of DIRECT PATH READ, so the implementation plan chooses to use DIRECT PATH READ to obtain data. If it is a single process, it is actually very bad. Multiple processes, like direct path read, do not buffer block to the cache, so each process has to get the data it wants through direct path read. As a result, the situation gets worse.

Solution: create a composite index on the FinherOBJECTID field of the tbcm_catalogfile table.

Thank you for reading, the above is the content of "how to solve the DPR caused by Oracle without index". After the study of this article, I believe you have a deeper understanding of how to solve the DPR problem caused by Oracle without index, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.