ORA-00600 error troubleshooting caused by an SQL statement

Source: Internet
Author: User

ORA-00600 error troubleshooting caused by an SQL statement

Recently, a colleague asked me a question, saying that he ran an SQL statement and throw a ORA-00600 error. I want to help me analyze this problem. I am really interested in it, after learning about the general situation of the problem, it is worth analyzing and analyzing, because the client call throws an exception and does not have any fatal impact on the server, this problem is quite intriguing.

After simple communication, I got the SQL statements and execution environment provided by my colleagues. The statements are similar to the following:

Merge into (SELECT * FROM TEST_SERVER_LOG WHERE BUY_TIME> = TO_DATE (: 1, 'yyyy-MM-DD HH24: MI: ss ')
AND BUY_TIME <to_date (: 2, 'yyyy-mm-dd = "" hh24: mi: ss') AND PUT_DATE = TO_DATE (: 3, 'yyyy-MM-DD ') T
USING (SELECT
TO_CHAR (: 4) as sn,
TO_NUMBER (: 5) AS GROUP_ID,
TO_NUMBER (: 6) AS SERVER_IP,
TO_CHAR (: 7) AS SERVER_NAME,
TO_NUMBER (: 8) as word,
TO_NUMBER (: 9) as server,
TO_NUMBER (: 10) as scene,
TO_CHAR (: 11) AS CN_GUID,
TO_DATE (: 12, 'yyyy-MM-DD HH24: MI: ss') AS BUY_TIME,
TO_NUMBER (: 13) AS JEWEL_TOTAL,
TO_CHAR (: 14) as cn,
TO_CHAR (: 15) AS CHARACTER_PUT,
TO_CHAR (: 16) as ip,
TO_NUMBER (: 17) as weaponid,
TO_DATE (: 18, 'yyyy-MM-DD ') AS PUT_DATE,
TO_NUMBER (: 19) AS WEAPONID_NEW,
TO_NUMBER (: 20) as count,
TO_NUMBER (: 21) AS USER_CLASS,
TO_CHAR (: 22) AS CONSUME_WAY,
TO_NUMBER (: 23) AS CLIENT_STYLE,
TO_CHAR (: 24) AS GAME_TYPE
From dual)
ON (T. SN = A. SN)
WHEN NOT MATCHED THEN
INSERT (T. SN, T. GROUP_ID, T. SERVER_IP, T. SERVER_NAME, T. WORD, T. SERVER, T. SCENE, T. CN_GUID, T. BUY_TIME, T. JEWEL_TOTAL, T. CN, T. CHARACTER_PUT,
T. IP, T. WEAPONID, T. PUT_DATE, T. WEAPONID_NEW, T. COUNT, T. USER_CLASS, T. CONSUME_WAY, T. CLIENT_STYLE, T. GAME_TYPE)
VALUES (. SN,. GROUP_ID,. SERVER_IP,. SERVER_NAME,. WORD,. SERVER,. SCENE,. CN_GUID,. BUY_TIME,. JEWEL_TOTAL,. CN,. CHARACTER_PUT,
A. IP, A. WEAPONID, A. PUT_DATE, A. WEAPONID_NEW, A. COUNT, A. USER_CLASS, A. CONSUME_WAY, A. CLIENT_STYLE, A. GAME_TYPE)
Such a statement looks complicated, but the logic is not complicated. I have recently dealt with several performance problems. In fact, many of them are related to the use of Merge. What are the problems with this statement? Currently, there are no direct problems. The only difference is that there are a lot of variable bindings, in addition, the database version is 11.2.0.3.
Get the statement to analyze, can reproduce the problem is a very rare opportunity, there are a lot of ORA-00600 errors to reproduce the Context Environment to trigger certain conditions can reproduce, this problem has saved me a lot of trouble. I also found the corresponding trace file in the alert Log. Of course, this type of trace files is quite boring to tell the truth. It seems that they are basically just too many objects.
I didn't spend too much time on this trace and instead tried to reproduce this problem,
First, I use v $ SQL to view the SQL statement in the database. The result is very careful and there is no information, as if this statement has not been executed.
Then I switch to the owner user and try to generate an execution plan. Fortunately, the problem can be reproduced at this time.
SQL> @ 600. SQL
Explain plan for merge into (SELECT * FROM TEST_SERVER_LOG WHERE BUY_TIME> = TO_DATE (: 1, 'yyyy-MM-DD HH24: MI: ss ')
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qcsfbdnp: 1], [4], [], [25], [], [],
[], [], [], [], [], []
It can be seen that this statement will throw a problem during SQL parsing. For this error, perform a search on MetaLink and find that there are a lot of related bugs, locking in a more suitable bug.
Bug 13496884 ORA-600 [qcsfbdnp: 1] from Merge Statement with Bind Variables
It is related to executing Merge, but check the explanation in it, that is, patching, there is no other solution.
In fact, for ORA-00600 errors, similar to the Development of NULLPointerException, the boundaries of such problems are vague, troubleshooting requires some effort.
My initial feeling is that the problem may occur in two ways.
1. One is TEST_SERVER_LOG. The table has a large amount of data. Is there any impact on Merge?
2. The statement contains a large number of bind variables. Whether to bind too many variables causes Merge support problems.
So I analyzed and checked in this direction. I replaced the bound variable one by one and temporarily replaced it with a constant. I found that the error still occurs, but the error parameter may change to the lower mark.
Until I replaced all the binding variables of the entire using part, I finally threw a seemingly unrelated error.
T. IP, T. WEAPONID, T. PUT_DATE, T. WEAPONID_NEW, T. COUNT, T. USER_CLASS, T. CONSUME_WAY, T. CLIENT_STYLE, T. GAME_TYPE)
*
ERROR at line 30:
ORA-00904: "T". "GAME_TYPE": invalid identifier
Check that the field does not exist. Check the field information in the source table carefully and find that the field does not exist. It seems that I have found the cause of the problem.
Select game_type from TEST_SERVER_LOG where rownum <2
*
ERROR at line 1:
ORA-00904: "GAME_TYPE": invalid identifier
After temporarily fixing this problem, the statement can be parsed by removing this field first, but what is the cause of the problem? It is still confusing at this time. I tried to add another binding variable in the using clause. The problem occurs again.
ERROR at line 1:
ORA-00600: internal error code, arguments: [qcsfbdnp: 1], [7], [], [2], [], [],
[], [], [], [], [], []
Therefore, my preliminary conclusion is that this error is irrelevant to the number of Bound variables. The direct cause of this problem is that a field in the statement does not match.
So is this problem related to the data volume? After consulting a developer, I found that the Field Mismatch problem still exists because the developer provides another user, the owner is completely different from the one I tested. The data volume of TEST_SERVER_LOG in the test user is as follows:
SQL> select count (*) from dystat_bg.TEST_SERVER_LOG;
COUNT (*)
----------
1
It can be proved that this bug has nothing to do with the data volume of TEST_SERVER_LOG and the number of Bound variables.
Now let's take a look at the content in the trace file:
Seeing such a paragraph is still helpful for us to analyze the error of this ORA-00600.
----- Incident Context Dump -----
Address: 0x7fff07bebbc0
Incident ID: 324423
Problem Key: ORA 600 [qcsfbdnp: 1]
Error: ORA-600 [qcsfbdnp: 1] [4] [] [7] [] [] [] [] [] [] [] [] []
[00]: dbgexProcessError [diag_dde]
[01]: dbgeExecuteForError [diag_dde]
[02]: dbgePostErrorKGE [diag_dde]
[03]: dbkePostKGE_kgsf [rdbms_dde]
[04]: kgeadse []
[05]: kgerinv_internal []
[06]: kgerinv []
[07]: kgeasnmierr []
[08]: qcsfbdnp [SQL _Parser] <-- Signaling
[09]: qcpibva []
[10]: qcpiapr []
[11]: qcpiafa []
[12]: qcpiaex []
[13]: qcpifn1 []
[14]: qcpifun []
[15]: qcpiapr []
[16]: qcpiafa []
[17]: qcpiaex []
[18]: qcpiexl []

The error code is [qcsfbdnp: 1], [7], [], [2], [], [], in this log, we can see that some calls that are familiar with the SQL parser have encountered problems.

When analyzing the problem, we can re-examine this statement to see if there are any hidden risks. We will continue to add more information later.

For more details, please continue to read the highlights on the next page:

  • 1
  • 2
  • Next Page

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.