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

A key system of a large state-owned bank was upgraded from 10g to 11g. Old K was responsible for the operation guarantee on the first morning after the upgrade; Party A's customers had done various tests before the upgrade to ensure that there would be no performance problems after the upgrade. However, contrary to expectations, when Old K arrived at the site, the customer application team had already fed back to the customer that the batch was slow for a period of time. According to the application log and the verification of the DBA, a friend responsible for assisting the upgrade, the initial positioning problem was that the export was slow due to the slow storage used after the upgrade, which slowed down the batch execution time. For this conclusion, the customer still has doubts while mobilizing storage-related engineers to check, so Old K began to participate in the investigation of this problem; now the problem can be described as: After oracle is upgraded from 10g to 11g, the import operation becomes slower.

01 Start thinking

Because it is the efficiency change imported before and after upgrading, Old K first compares the logs imported by the production system in 10g and 11g environments, mainly checks the imported time point and then further checks the status of the database at the relevant time point. However, after reading two logs, Old K had his own doubts, so I started my own virtual machine and began my own verification exploration journey.

The above two pictures are the import log files that I directly test imported on my own virtual machine. After carefully reading the pictures below, do you also find some doubts? Old K prompted:

Old K prompted:

1. Parallel enabled on import (parallel is 6, greater than number of tables)

2. When importing, content=data_only is used (indicating that the database already has a table definition)

3. There are partition tables (A1, A2, A3, 24 partitions in total) and non-partition tables (A6) in the imported table.

02 Analyzing...

As shown in the figure, table A6 is relatively large, with more than 5 million records (and here the old K also built an index on the A6 table, and the index of the A6 table needs to be maintained when importing, and other tables have no index), while all other tables take up space and add up to 300M. The log entry for table A6 appears in the last line of the log for 10g and in the fourth line of the log for 11g.

Normally, when importing/exporting multiple tables using the data pump, the information of the corresponding table will be printed in the log when the import/export is completed. If the A6 table here is a large table, but the import operation is completed earlier than other tables, we can suspect that when the partitioned table and the non-partitioned table are imported at the same time, the mechanism of the 11g data pump is problematic, especially after comparing the import situation of 10g.

03 Guess & Verify

The above combs out doubts. How should we verify them? If we are performing an import/export operation, we can attach to the import/export task for real-time observation; if the import/export has been completed, then we need to look for the answer from the historical performance view of the database (here we mainly validate against the 11g database).

First, let's look at the import as a whole through the v$active_session_history view:

Use v$active_session_history view to check, sampling once per second found that during the import period, the database did start 6 parallel processes (workers), of which DW00 process sampling times are the most, but the earliest sampling time and the last sampling time of each process are almost the same, from 12:36 to 12:51; obviously, some worker processes are idle during this process;

So, is there a problem with partition table import?

Let's first analyze the import process of the partition table (take A1 as an example):

From the above results, we can see a feature, as far as table A1 is concerned, the import of each partition is scattered on different DW processes, but from the maximum and minimum sampling time, there is no overlap time area, that is to say, when DATAPUMP starts 6 parallel imports to 4 tables, it seems that table A1 does not import multiple partitions in parallel. Similarly, we check the import of tables A2 and A3:

The conclusion is as follows: when importing, although the parallelism is 6, only 4 tables (including partitioned tables) are imported, multiple partitions of all tables are not imported in parallel, and at least two DW processes are idle (it is more obvious when real-time attach to import process monitoring).

Does this contradict our previous experience?

Knowledge Points:

1. When data pump is imported, parallel is mainly to import different tables through multiple processes to improve the import speed, and different partitions are generally treated as different tables as different segments;

2. When importing data pumps, insert append is used by default, that is, the table lock of the object will be held during import;

This import seems to be inconsistent with the first article. When importing, in the case of idle processes, different partitions of the same table are not parallel. Why is that? We need to note several import features we mentioned earlier, one of which is that we use content=data_only; Using this parameter means that the partition table definition already exists in the current database, and only the data is imported into the existing table. When the partition table definition already exists in the database and data needs to be imported into it, one of the situations that may be encountered is that the partition table definition in the database is inconsistent with the partition table definition in the DUMP file.(For example, the partition keys are inconsistent), resulting in partitions in the DUMP file cannot correspond to existing partitions in the database one by one, and the failure to achieve one-to-one correspondence means that if multiple WORKERS are started to import different partitions of a table at the same time, different WORKERS may perform insert append operations to a target partition at the same time. As mentioned above, insert append operations will hold row locks, so that there will eventually be mutual blocking between imported workers, and deadlock may occur. So in newer versions of oracle, when importing an already partitioned table (using the parameter content=data_only, etc.), multiple partitions of the same table are imported sequentially (although it is possible to do so through different workers).

04 Questions & Answers

Is this the case with 10g database import? We use the same method to test and observe, we can find that the import of 10g database is also the same, then the customer imported data in 10g, 11g environment, table definition, import command, there is no change, it seems parallel +metadata_only is not the problem, then where is the real problem? I return to the original question:

In the log of 10g, the log record of table A6 is written in the last line, while in the log of 11g, it appears in the fourth line. That is to say, in 10g, table A6 is the last completed, while in 11g table A6 has been completed before most partitions of tables A1, A2, and A3 are completed. Is the import process of tables A1, A2, and A3 waiting for something?

Or go back to v$active_session_history view:

As you can see, the A6 import lasted from approximately 12:36 to 12:49, including index maintenance.

In other active sessions during this period, only one partition was imported into each partition table (i.e., the first partition imported into each table), and each WORKER did not wait abnormally after importing the first partition (12:37:32~12:42:17), but was in an idle state.

Let's review the import of each table again:

As you can see, after the first partition of each table is imported, there is a long pause, and it is not until the non-partitioned table is imported that the subsequent partition import is continued;

Combined with the status of each worker and the time status of each table import, as well as the previous conclusions, we can confirm one point:

When partition tables and non-partition tables are imported at the same time, and the table definition already exists in the database (using parameters such as content=metadata_only), partition tables need to be imported one by one. Parallel cannot be used. After all partition tables have been imported for the first partition, you need to wait for all non-partition tables to be imported before continuing partition import of subsequent partition tables, which will affect the import progress.

The above problem does not exist in the 10g data pump, and the problem encountered by the universe client is precisely this problem. The time spent after upgrading is basically the time for importing a single non-partitioned table. The above problems have been submitted to the ORACLE official and confirmed as scheduling mechanism problems imported from 11g, and are currently being developed and solved.

05

What did we run into?

Look at what we have encountered:

1. In 10g, we used parallel import of partitioned and non-partitioned tables, and the table definition already exists in the target library, so we added the content=data_only parameter;

2. In the process of import, partition tables are imported one by one, and parallel import is realized among tables when parallel processes are enough;

3. After upgrading to 11g, each partition table only imports the first partition, and then starts waiting;

4. After the non-partitioned tables are imported, the partitioned tables continue to be imported.

Now we know that the extra time spent is the time of the third step. In the 10g environment, the non-partitioned table can be imported in parallel with the partitioned table, while in the 11g environment, the non-partitioned table can only be imported in parallel with the first partition imported by the partitioned table. Suppose that we need to import a very large non-partitioned table and 3 partitioned tables, each partitioned table has 3 partitions, and each partition has the same size. If it takes 1 hour to import all partitioned tables separately, it takes 1 hour to import all non-partitioned tables separately. Enable 4 parallelism. In a 10g environment, it ideally takes only 1 hour to import.(The slowest non-partitioned table import is completed, i.e. the whole import is completed), while in an 11g environment, it takes 1 hour +3/4 hours (i.e. 105 minutes).

What we can do:

When we need to pursue imported efficiency:

1. When importing partitioned tables, try to avoid using content=data_only or table_exists_action=appen/truncate. If the data volume of the existing table is not large, and the structure of the table in the dump is consistent with that of the existing table, we may as well save the data of the existing table first, and then use table_exists_action=replace;

2. If importing partitioned tables cannot avoid using content=data_only or table_exists_action=appen/truncate, we recommend importing partitioned tables separately from non-partitioned tables.

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.