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

This article mainly explains the "database partition maintenance DDL caused by abnormal DML interrupt example analysis", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "database partition maintenance DDL caused by DML abnormal interrupt example analysis"!

Today, a colleague reported that an abnormal interrupt occurred during the execution of an insert statement. The error message is: ERROR at line 1:

ORA-12805: parallel query server died unexpectedly

There are many possible reasons for this error, but it is not of much value. Check the background alert and trace files at the corresponding point in time and find that there are 7445 errors.

ORA-07445: exception encountered: core dump [kdzt_acmp_project () + 124] [SIGSEGV] [ADDR:0x0] [PC:0x308DF84] [SI_KERNEL (general_protection)] []

The first reaction was to encounter BUG. I checked on MOS and found that the error message of Bug 24301654 was a bit similar to mine. I posted part of it, except that the memory address was different.

Problem Key: ORA-7445 [kdzt_acmp_project () + 124]

Error: ORA-7445 [kdzt_acmp_project () + 124] [SIGSEGV] [ADDR:0x0] [PC:0x2DB1BB0]

[SI_KERNEL (general_protection)] []

But read SR's reply-Bug 21097914 is similar, but CT's case doesn't have "> = rowid" predicates. I did not use this sentence to scan the rowid range. I looked at the trace file and saw something similar to the following.

True _ is_lock_table_for_ddl_wait_lock= 1

True _ is_lock_table_for_ddl_wait_lock= 1

True _ is_lock_table_for_ddl_wait_lock= 1

True _ is_lock_table_for_ddl_wait_lock= 1

True _ is_lock_table_for_ddl_wait_lock= 1

True _ is_lock_table_for_ddl_wait_lock= 1

True _ is_lock_table_for_ddl_wait_lock= 1

True _ is_lock_table_for_ddl_wait_lock= 1

True _ is_lock_table_for_ddl_wait_lock= 1

True _ is_lock_table_for_ddl_wait_lock= 1

True _ is_lock_table_for_ddl_wait_lock= 1

True _ is_lock_table_for_ddl_wait_lock= 1

It was suspected that there was a DDL operation on the table or partition at that time. The target table of INSERT was the partition table, and there would be partition maintenance operations every hour. Check the partition maintenance log before and after the corresponding point in time, see that the time really coincided, and took a look at the waiting event at that time.

Library cache lock 65

Library cache: mutex X 23

Usually this waiting event is very low, and it is really higher here than usual.

So I did a test:

1. Create a new test partition table and create a small number of partitions with the same name

2. A window executes the insert statement at that time

3. Quickly execute partition maintenance statements in another window (window B), including 1 add partition,1 truncate partition and 1 drop partition operation

4. As expected, window An appears.

Insert / * + APPEND * / into xxxxx PARTITION (xxxxxxx) NOLOGGING

*

ERROR at line 1:

ORA-12805: parallel query server died unexpectedly

Then execute the partition maintenance statement after the A window is executed for a period of time, and it is found that the A window can be executed normally, and then try to operate the three partition maintenance commands separately, which will not affect the statements of A window. Combined with the above situation, the personal judgment is:

When the DML sql statement is still in the parsing phase (the statement is long and contains a large number of aggregate functions):

If you initiate a DDL statement that takes a little longer, it will cause parsing to fail and an error will be reported

If DDL is executed after parsing, the DML statement will not be interrupted.

If the DDL time is very short, you can also complete the parsing and execute DML normally.

Originally wanted to do a trace, time is limited, do not continue to study, first remind the R & D to perform partition maintenance before performing DML (all in the same stored procedure), should be able to avoid this problem, before encountered a lot of ORA-07445 are caused by BUG, it is rare to encounter a should not be BUG 07445, feel it is worth recording

180507update:

Finally, the original factory of ORACLE concluded that it hit BUG 21097914 and could be repaired by matching patch or upgrading to 12.1.0.2.170418.

Thank you for your reading, the above is the content of "example analysis of abnormal DML interruptions caused by partition maintenance DDL in the database". After the study of this article, I believe you have a deeper understanding of the problem of analyzing abnormal DML interruptions caused by partition maintenance DDL in the database, 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.