CN117743015A - SQL fault positioning method, device, system and equipment - Google Patents

Abstract

The application discloses a SQL fault locating method, device, system and equipment, through comparing the current execution plan result with the historical execution plan result, thereby can intuitively see which stage the problem SQL has failed, thereby locate the fault cause rapidly, optimize business SQL, simultaneously, the application can be applied to multiple types of databases, the practicability is extensive, the problem that the analysis of the execution plan is relatively dependent on expert experience at present is solved, uncertainty exists, the complexity of the database structure is improved, the data distribution and the statistical information are also changed along with the uncertainty, the result of the execution plan is more complex, the difficulty of solving the fault by DBA analysis is greatly increased, and the technical problem of work efficiency is reduced.

Description

SQL fault positioning method, device, system and equipment

Technical Field

The present disclosure relates to the field of database processing technologies, and in particular, to a method, an apparatus, a system, and a device for locating an SQL fault.

Background

With the development of society, various industries have made great progress in technology, especially in the coming of big data age, so that data amount has exponentially increased, and databases play an increasingly important role therein. The common SQL problem of databases, such as slow queries, lock waiting, lock timeout, etc., is also increasingly emphasized in the face of increasingly complex database environments.

An execution plan is a plan that describes the manner and order of execution that a database system generates when executing SQL statements, and provides information about how queries are executed, which indexes, connections, run times, etc., which database administrators (Database Administrator, DBA) can use to optimize query performance, debug, and diagnose query problems.

Through analysis of the execution plan, a way of solving SQL problems such as slow query is provided for DBA operation and maintenance specialists, but the process is extremely dependent on experience and has uncertainty. Especially in the situation that the data volume is rapidly increased and the distributed database becomes the mainstream nowadays, the data in the production environment database is not changed at any time, the complexity of the database structure is improved, wherein the data distribution and the statistical information are changed along with the data distribution and the statistical information, so that the result of the execution plan is more complex, the difficulty of solving faults by DBA analysis is greatly increased, and the working efficiency is reduced.

Disclosure of Invention

The application provides a SQL fault positioning method, device, system and equipment, which solve the technical problems that the analysis of an execution plan is dependent on expert experience, uncertainty exists, the complexity of a database structure is improved, and data distribution and statistical information are changed along with the uncertainty, so that the result of the execution plan is more complex, the difficulty of solving faults in DBA analysis is greatly increased, and the working efficiency is reduced.

In view of this, a first aspect of the present application provides a method for locating an SQL fault, the method comprising:

s1, acquiring a first execution result of an execution plan of a current problem SQL sentence;

s2, acquiring a second execution result of a historical execution plan of a historical SQL statement with the same SQL ID as the current problem SQL statement in a historical execution plan table;

s3, comparing the first execution result with the second execution result, and determining the fault reason of the SQL of the current problem.

Optionally, the step S2 specifically includes:

s21, acquiring a plurality of first historical SQL sentences with the same SQL ID as the current problem SQL sentence in a historical execution schedule;

s22, determining that the execution result in the plurality of first historical SQL sentences is a second historical SQL sentence which is successful in execution and longest in execution time;

s23, if the SQL type of the second historical SQL statement is a non-target type, eliminating the second historical SQL statement from the first historical SQL statement and returning to the step S22;

s24, if the SQL type of the second historical SQL statement is the target type, a second execution result of the historical execution plan corresponding to the second historical SQL statement is called.

Optionally, the step S24 specifically includes:

if the SQL type of the second historical SQL statement is the target type and the computing node of the target label exists in the current example, acquiring a second execution result of a historical execution plan corresponding to the second historical SQL statement through the computing node;

if the SQL type of the second historical SQL statement is the target type, but no computing node with the target label exists in the current example, acquiring a second execution result of the historical execution plan through the computing node corresponding to the second historical SQL statement in the full SQL table.

Optionally, the step S3 specifically includes:

comparing the first execution result with the second execution result to obtain the historical SQL statement and the table connected process data processed before and after the database;

and determining the fault stage and the fault reason of the current problem SQL according to the process data.

A second aspect of the present application provides an SQL fault location apparatus, the apparatus comprising:

the first acquisition unit is used for acquiring a first execution result of an execution plan of the SQL statement of the current problem;

the second acquisition unit is used for acquiring a second execution result of a history execution plan of a history SQL sentence with the same SQL ID as the current problem SQL sentence in the history execution plan table;

and the fault locating unit is used for comparing the first execution result with the second execution result and determining the fault reason of the SQL of the current problem.

Optionally, the second obtaining unit specifically includes:

the acquisition subunit is used for acquiring a plurality of first historical SQL sentences with the same SQL ID as the current problem SQL sentence in the historical execution schedule;

the determining subunit is used for determining that the execution result in the plurality of first historical SQL sentences is a second historical SQL sentence which is successful in execution and has the longest execution time;

the first processing subunit is configured to, if the SQL type of the second historical SQL statement is a non-target type, reject the second historical SQL statement from the first historical SQL statement and skip to the determining unit;

and the second processing subunit is used for calling a second execution result of the history execution plan corresponding to the second history SQL statement if the SQL type of the second history SQL statement is the target type.

Optionally, the second processing subunit is specifically configured to:

if the SQL type of the second historical SQL statement is the target type and the computing node of the target label exists in the current example, acquiring a second execution result of a historical execution plan corresponding to the second historical SQL statement through the computing node;

if the SQL type of the second historical SQL statement is the target type, but no computing node with the target label exists in the current example, acquiring a second execution result of the historical execution plan through the computing node corresponding to the second historical SQL statement in the full SQL table.

Optionally, the fault locating unit is specifically configured to:

comparing the first execution result with the second execution result to obtain the historical SQL statement and the table connected process data processed before and after the database;

and determining the fault stage and the fault reason of the current problem SQL according to the process data.

A third aspect of the present application provides an SQL fault localization system, the system comprising:

the operation and maintenance platform, the computing node and the system instance;

the system instance is used for creating a full SQL table corresponding to the recorded full SQL for each instance;

the computing node is used for issuing an execution plan of the SQL sentence to each data node, receiving an execution result of the SQL sentence, and updating the execution result of the execution plan in a full SQL table corresponding to the SQL sentence in the system instance;

the operation and maintenance platform is used for executing the steps of the SQL fault locating method according to the first aspect.

A fourth aspect of the present application provides an SQL fault localization apparatus, the apparatus comprising a processor and a memory:

the memory is used for storing program codes and transmitting the program codes to the processor;

the processor is configured to execute the steps of the SQL fault localization method according to the first aspect according to the instructions in the program code.

From the above technical solutions, the embodiments of the present application have the following advantages:

in the application, the SQL fault locating method, device, system and equipment are provided, and the current execution plan result is compared with the historical execution plan result, so that the fault of the problem SQL can be intuitively seen at the stage, the fault cause is quickly located, and the service SQL is optimized.

Drawings

FIG. 1 is a flow chart of a method for SQL fault localization in an embodiment of the present application;

FIG. 2 is a schematic structural diagram of an SQL fault locating device according to an embodiment of the present application;

FIG. 3 is a schematic structural diagram of an SQL fault locating system according to an embodiment of the present application;

fig. 4 is a schematic structural diagram of an SQL fault location device in an embodiment of the present application.

Detailed Description

In order to make the present application solution better understood by those skilled in the art, the following description will clearly and completely describe the technical solution in the embodiments of the present application with reference to the accompanying drawings in the embodiments of the present application, and it is apparent that the described embodiments are only some embodiments of the present application, not all embodiments. All other embodiments, which can be made by one of ordinary skill in the art without undue burden from the present disclosure, are within the scope of the present disclosure.

The application designs the SQL fault positioning method, the device, the system and the equipment, which solve the technical problems that the analysis of the execution plan at present is dependent on expert experience, uncertainty exists, the complexity of a database structure is improved, and the data distribution and the statistical information are changed along with the uncertainty, so that the result of the execution plan is more complex, the difficulty of solving the fault by DBA analysis is greatly increased, and the working efficiency is reduced.

For ease of understanding, referring to fig. 1, fig. 1 is a flowchart of a method for locating an SQL fault in an embodiment of the present application, as shown in fig. 1, specifically:

s1, acquiring a first execution result of an execution plan of a current problem SQL sentence;

s2, acquiring a second execution result of a historical execution plan of a historical SQL statement with the same SQL ID as the current problem SQL statement in the historical execution plan table;

it should be noted that, the second execution result of the history execution plan of the history SQL statement having the same SQL template as the current problem SQL statement is queried in the history execution plan table, where the same SQL template refers to having the same SQL ID.

S3, comparing the first execution result with the second execution result, and determining the fault reason of the SQL of the current problem.

Further, step S2 specifically includes:

s21, acquiring a plurality of first historical SQL sentences with the same SQL ID as the current problem SQL sentences in a historical execution schedule;

s22, determining that the execution result in the plurality of first historical SQL sentences is a second historical SQL sentence which is successful in execution and longest in execution time;

s23, if the SQL type of the second historical SQL statement is a non-target type, eliminating the second historical SQL statement from the first historical SQL statement and returning to the step S22;

s24, if the SQL type of the second historical SQL statement is the target type, a second execution result of the historical execution plan corresponding to the second historical SQL statement is called.

It should be noted that, according to the SQL ID information in one collection period, one second historical SQL statement that is executed successfully and has the longest execution time in the plurality of first historical SQL statements is selected, and the SQL statement with the SQL type of INSERT, DELETE, UPDATE, SELECT is selected, which is more representative, so that the pressure of acquiring the corresponding execution plan of the second historical SQL statement through the computing node is reduced, the influence on normal service is reduced, and the resource occupation of the disk storage space is reduced.

Further, step S24 specifically includes:

if the SQL type of the second historical SQL statement is the target type and the computing node with the target label exists in the current example, acquiring a second execution result of a historical execution plan corresponding to the second historical SQL statement through the computing node;

if the SQL type of the second historical SQL statement is the target type, but no computing node with the target label exists in the current example, acquiring a second execution result of the historical execution plan through the computing node corresponding to the second historical SQL statement in the full SQL table.

It should be noted that, after the second historical SQL statement is determined, a corresponding execution plan is collected from the corresponding computing node through the interface of the operation and maintenance collection agent (INSIGHTAGENT, IA), if the computing node marked with the "operation and maintenance CN" label exists in the example, the computing node is used for collecting the execution plan, otherwise, the computing node corresponding to the SQL statement in the full SQL table is used for collecting the execution plan, so that the influence on the normal service is reduced.

Further, the step S3 specifically includes:

comparing the first execution result with the second execution result to obtain historical SQL sentences and table connected process data of the database front and back processing;

and determining the fault stage and the fault reason of the SQL of the current problem according to the process data.

It should be noted that, the problem SQL such as the slow query SQL has the information of its own SQL ID, which database is executed and the instance to which the SQL belongs, and through these information, the historical execution plan corresponding to the SQL ID is queried from the SQL execution plan table of the corresponding tenant, and the DBA can simultaneously check the current execution plan result and the historical execution plan result, so as to compare and obtain the desired information.

Referring to fig. 2, fig. 2 is a schematic structural diagram of an SQL fault location device according to an embodiment of the present application, as shown in fig. 2, specifically:

a first obtaining unit 201, configured to obtain a first execution result of an execution plan of a current problem SQL statement;

a second obtaining unit 202, configured to obtain a second execution result of a history execution plan of a history SQL statement in the history execution plan table that has the same SQL ID as the current problem SQL statement;

the fault locating unit 203 is configured to compare the first execution result with the second execution result, and determine a fault cause of the current problem SQL.

Further, the second acquisition unit 202 specifically includes:

the acquisition subunit is used for acquiring a plurality of first historical SQL sentences with the same SQL ID as the current problem SQL sentences in the historical execution schedule;

the determining subunit is used for determining that the execution result in the plurality of first historical SQL sentences is a second historical SQL sentence which is successful in execution and has the longest execution time;

the first processing subunit is configured to reject the second historical SQL statement from the first historical SQL statement and skip to the determining unit if the SQL type of the second historical SQL statement is a non-target type;

and the second processing subunit is used for calling a second execution result of the history execution plan corresponding to the second history SQL statement if the SQL type of the second history SQL statement is the target type.

Further, the second processing subunit is specifically configured to:

if the SQL type of the second historical SQL statement is the target type and the computing node with the target label exists in the current example, acquiring a second execution result of a historical execution plan corresponding to the second historical SQL statement through the computing node;

if the SQL type of the second historical SQL statement is the target type, but no computing node with the target label exists in the current example, acquiring a second execution result of the historical execution plan through the computing node corresponding to the second historical SQL statement in the full SQL table.

Further, the fault locating unit 203 is specifically configured to:

comparing the first execution result with the second execution result to obtain historical SQL sentences and table connected process data of the database front and back processing;

and determining the fault stage and the fault reason of the SQL of the current problem according to the process data.

Referring to fig. 3, fig. 3 is a schematic structural diagram of an SQL fault location system according to an embodiment of the present application, and as shown in fig. 3, specifically:

an operation and maintenance platform 301, a computing node 302, and a system instance 303;

the system instance 303 is used for creating a full SQL table corresponding to the recorded full SQL for each instance;

the computing node 302 is configured to issue an execution plan of an SQL statement to each data node, receive an execution result of the SQL statement, and update the execution result of the execution plan in a full-scale SQL table corresponding to the SQL statement in the system instance 303;

the operation and maintenance platform 301 is configured to perform the steps of the SQL fault localization method of the first aspect described above.

It should be noted that, a system instance 303 is newly added as a storage unit to create a monitoring library, and a Metadata management node (MDS) creates a separate full SQL table record full SQL for each instance in the system instance 303, and periodically cleans up.

The clients access the database through connection instances, each of which binds a compute Node 302 (CN), the compute Node 302 being responsible for SQL optimization, SQL routing, load balancing of data nodes, scheduling of distributed transactions, etc., so the compute Node 302 can be used to record the full amount of SQL. A switch is set for the record full SQL statistics function and is turned on, the compute node 302 performs statistics.

The computing Node 302 issues SQL to the Data Node (Data Node, DN), and when the DN returns a result, the DN brings statistics information executed in the DN layer corresponding to the SQL, including the number of scanning lines, the number of hit lines, physical read IO, logical read IO, lock waiting time, and the like, back to the CN by extending the SQL protocol.

The internal memory table is introduced into the computing node 302, and the CN gathers the time-consuming and DN-executing data of each stage of the original SQL execution and stores the data into the memory table.

The compute node 302 periodically inserts the data of the memory table into the full SQL table of the corresponding instance of the system instance 303 through a batch protocol.

The task of collecting an execution plan is added to an operation and maintenance platform 301 (INSIGHT), a collection switch and a collection period are set for the collection function of the execution plan, the collection switch is turned on, then the INSIGHT executes collection, the collection period is used for selecting one SQL statement which is successful in corresponding execution and longest in execution time according to SQL ID (the same SQL template has the same SQL ID) information in the collection period from a full amount SQL table of each instance corresponding to a system instance library, and the SQL statement with the SQL type INSERT, DELETE, UPDATE, SELECT is screened, the type statement is more representative, the pressure of the subsequent acquisition of the execution plan through a calculation node 302 is reduced, the influence on normal business is reduced, and the resource occupation of a disk storage space is reduced.

Acquiring corresponding SQL sentences, acquiring corresponding execution plans from corresponding computing nodes 302 through an operation and maintenance acquisition agent (INSIGHTAGENT, IA) interface, if the computing nodes 302 marked with an operation and maintenance CN label exist in the example, acquiring the execution plans by using the computing nodes 302, otherwise, acquiring the execution plans by using the computing nodes 302 corresponding to the SQL sentences in the full SQL table, and also reducing the influence on normal business.

Finally, the IA returns the execution plan result to the operation and maintenance platform 301 through the interface, and the operation and maintenance platform 301 stores the execution plan result to the SQL execution plan table of each tenant corresponding to the system instance 303.

The embodiment of the present application further provides another SQL fault location device, as shown in fig. 4, for convenience of explanation, only the portion related to the embodiment of the present application is shown, and specific technical details are not disclosed, please refer to the method portion of the embodiment of the present application. The terminal can be any terminal equipment including a mobile phone, a tablet personal computer, a personal digital assistant (English full name: personal Digital Assistant, english abbreviation: PDA), a Sales terminal (English full name: point of Sales, english abbreviation: POS), a vehicle-mounted computer and the like, taking the mobile phone as an example of the terminal:

fig. 4 is a block diagram showing a part of a structure of a mobile phone related to a terminal provided in an embodiment of the present application. Referring to fig. 4, the mobile phone includes: radio Frequency (RF) circuit 1010, memory 1020, input unit 1030, display unit 1040, sensor 1050, audio circuit 1060, wireless fidelity (wireless fidelity, wiFi) module 1070, processor 1080, and power source 1090. Those skilled in the art will appreciate that the handset configuration shown in fig. 4 is not limiting of the handset and may include more or fewer components than shown, or may combine certain components, or may be arranged in a different arrangement of components.

The following describes the components of the mobile phone in detail with reference to fig. 4:

the RF circuit 1010 may be used for receiving and transmitting signals during a message or a call, and particularly, after receiving downlink information of a base station, the signal is processed by the processor 1080; in addition, the data of the design uplink is sent to the base station. Generally, RF circuitry 1010 includes, but is not limited to, an antenna, at least one amplifier, a transceiver, a coupler, a low noise amplifier (English full name: low Noise Amplifier, english abbreviation: LNA), a duplexer, and the like. In addition, the RF circuitry 1010 may also communicate with networks and other devices via wireless communications. The wireless communication may use any communication standard or protocol, including but not limited to global system for mobile communications (english: global System of Mobile communication, english: GSM), general packet radio service (english: general Packet Radio Service, GPRS), code division multiple access (english: code Division Multiple Access, english: CDMA), wideband code division multiple access (english: wideband Code Division Multiple Access, english: WCDMA), long term evolution (english: long Term Evolution, english: LTE), email, short message service (english: short Messaging Service, SMS), and the like.

The memory 1020 may be used to store software programs and modules that the processor 1080 performs various functional applications and data processing of the handset by executing the software programs and modules stored in the memory 1020. The memory 1020 may mainly include a storage program area and a storage data area, wherein the storage program area may store an operating system, an application program (such as a sound playing function, an image playing function, etc.) required for at least one function, and the like; the storage data area may store data (such as audio data, phonebook, etc.) created according to the use of the handset, etc. In addition, memory 1020 may include high-speed random access memory and may also include non-volatile memory, such as at least one magnetic disk storage device, flash memory device, or other volatile solid state memory device.

The input unit 1030 may be used to receive input numeric or character information and generate key signal inputs related to user settings and function control of the handset. In particular, the input unit 1030 may include a touch panel 1031 and other input devices 1032. The touch panel 1031, also referred to as a touch screen, may collect touch operations thereon or thereabout by a user (e.g., operations of the user on the touch panel 1031 or thereabout using any suitable object or accessory such as a finger, stylus, etc.), and drive the corresponding connection device according to a predetermined program. Alternatively, the touch panel 1031 may include two parts, a touch detection device and a touch controller. The touch detection device detects the touch azimuth of a user, detects a signal brought by touch operation and transmits the signal to the touch controller; the touch controller receives touch information from the touch detection device and converts it into touch point coordinates, which are then sent to the processor 1080 and can receive commands from the processor 1080 and execute them. Further, the touch panel 1031 may be implemented in various types such as resistive, capacitive, infrared, and surface acoustic wave. The input unit 1030 may include other input devices 1032 in addition to the touch panel 1031. In particular, other input devices 1032 may include, but are not limited to, one or more of a physical keyboard, function keys (e.g., volume control keys, switch keys, etc.), a track ball, a mouse, a joystick, etc.

The display unit 1040 may be used to display information input by a user or information provided to the user and various menus of the mobile phone. The display unit 1040 may include a display panel 1041, and alternatively, the display panel 1041 may be configured in the form of a liquid crystal display (english full name: liquid Crystal Display, acronym: LCD), an Organic Light-Emitting Diode (OLED), or the like. Further, the touch panel 1031 may overlay the display panel 1041, and when the touch panel 1031 detects a touch operation thereon or thereabout, the touch panel is transferred to the processor 1080 to determine a type of touch event, and then the processor 1080 provides a corresponding visual output on the display panel 1041 according to the type of touch event. Although in fig. 4, the touch panel 1031 and the display panel 1041 are two independent components for implementing the input and output functions of the mobile phone, in some embodiments, the touch panel 1031 and the display panel 1041 may be integrated to implement the input and output functions of the mobile phone.

The handset may also include at least one sensor 1050, such as a light sensor, a motion sensor, and other sensors. Specifically, the light sensor may include an ambient light sensor and a proximity sensor, wherein the ambient light sensor may adjust the brightness of the display panel 1041 according to the brightness of ambient light, and the proximity sensor may turn off the display panel 1041 and/or the backlight when the mobile phone moves to the ear. As one of the motion sensors, the accelerometer sensor can detect the acceleration in all directions (generally three axes), and can detect the gravity and direction when stationary, and can be used for applications of recognizing the gesture of a mobile phone (such as horizontal and vertical screen switching, related games, magnetometer gesture calibration), vibration recognition related functions (such as pedometer and knocking), and the like; other sensors such as gyroscopes, barometers, hygrometers, thermometers, infrared sensors, etc. that may also be configured with the handset are not described in detail herein.

Audio circuitry 1060, a speaker 1061, and a microphone 1062 may provide an audio interface between a user and a cell phone. Audio circuit 1060 may transmit the received electrical signal after audio data conversion to speaker 1061 for conversion by speaker 1061 into an audio signal output; on the other hand, microphone 1062 converts the collected sound signals into electrical signals, which are received by audio circuit 1060 and converted into audio data, which are processed by audio data output processor 1080 for transmission to, for example, another cell phone via RF circuit 1010 or for output to memory 1020 for further processing.

WiFi belongs to a short-distance wireless transmission technology, and a mobile phone can help a user to send and receive emails, browse webpages, access streaming media and the like through a WiFi module 1070, so that wireless broadband Internet access is provided for the user. Although fig. 4 shows a WiFi module 1070, it is understood that it does not belong to the necessary constitution of the handset, and can be omitted entirely as required within the scope of not changing the essence of the invention.

Processor 1080 is the control center of the handset, connects the various parts of the entire handset using various interfaces and lines, and performs various functions and processes of the handset by running or executing software programs and/or modules stored in memory 1020, and invoking data stored in memory 1020, thereby performing overall monitoring of the handset. Optionally, processor 1080 may include one or more processing units; preferably, processor 1080 may integrate an application processor primarily handling operating systems, user interfaces, applications, etc., with a modem processor primarily handling wireless communications. It will be appreciated that the modem processor described above may not be integrated into processor 1080.

The handset further includes a power source 1090 (e.g., a battery) for powering the various components, which may preferably be logically connected to the processor 1080 by a power management system, such as to provide for managing charging, discharging, and power consumption by the power management system.

Although not shown, the mobile phone may further include a camera, a bluetooth module, etc., which will not be described herein.

In the embodiment of the present application, the processor 1080 included in the terminal further has the following functions:

s1, acquiring a first execution result of an execution plan of a current problem SQL sentence;

s2, acquiring a second execution result of a historical execution plan of a historical SQL statement with the same SQL ID as the current problem SQL statement in the historical execution plan table;

s3, comparing the first execution result with the second execution result, and determining the fault reason of the SQL of the current problem.

In the embodiment of the application, the SQL fault locating method, device, system and equipment are provided, and the current execution plan result is compared with the historical execution plan result, so that the fault of the problem SQL can be intuitively seen at the stage, the fault reason is quickly located, and the service SQL is optimized.

It will be clear to those skilled in the art that, for convenience and brevity of description, specific working procedures of the above-described systems, apparatuses and units may refer to corresponding procedures in the foregoing method embodiments, which are not repeated herein.

The terms "first," "second," "third," "fourth," and the like in the description of the present application and in the above-described figures, if any, are used for distinguishing between similar objects and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used may be interchanged where appropriate such that embodiments of the present application described herein may be capable of operation in sequences other than those illustrated or described herein, for example. Furthermore, the terms "comprises," "comprising," and "having," and any variations thereof, are intended to cover a non-exclusive inclusion, such that a process, method, system, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.

It should be understood that in this application, "at least one" means one or more, and "a plurality" means two or more. "and/or" for describing the association relationship of the association object, the representation may have three relationships, for example, "a and/or B" may represent: only a, only B and both a and B are present, wherein a, B may be singular or plural. The character "/" generally indicates that the context-dependent object is an "or" relationship. "at least one of" or the like means any combination of these items, including any combination of single item(s) or plural items(s). For example, at least one (one) of a, b or c may represent: a, b, c, "a and b", "a and c", "b and c", or "a and b and c", wherein a, b, c may be single or plural.

In the several embodiments provided in this application, it should be understood that the disclosed systems, apparatuses, and methods may be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative, e.g., the division of the units is merely a logical function division, and there may be additional divisions when actually implemented, e.g., multiple units or components may be combined or integrated into another system, or some features may be omitted or not performed. Alternatively, the coupling or direct coupling or communication connection shown or discussed with each other may be an indirect coupling or communication connection via some interfaces, devices or units, which may be in electrical, mechanical or other form.

The units described as separate units may or may not be physically separate, and units shown as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

In addition, each functional unit in each embodiment of the present application may be integrated in one processing unit, or each unit may exist alone physically, or two or more units may be integrated in one unit. The integrated units may be implemented in hardware or in software functional units.

The integrated units, if implemented in the form of software functional units and sold or used as stand-alone products, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present application may be embodied in essence or a part contributing to the prior art or all or part of the technical solution in the form of a software product stored in a storage medium, including several instructions to cause a computer device (which may be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the methods described in the embodiments of the present application. And the aforementioned storage medium includes: u disk, mobile hard disk, read-Only Memory (ROM), random access Memory (Random Access Memory, RAM), magnetic disk or optical disk, etc.

The above embodiments are merely for illustrating the technical solution of the present application, and not for limiting the same; although the present application has been described in detail with reference to the foregoing embodiments, it should be understood by those of ordinary skill in the art that: the technical scheme described in the foregoing embodiments can be modified or some technical features thereof can be replaced by equivalents; such modifications and substitutions do not depart from the spirit and scope of the corresponding technical solutions.

Claims (10)

1. A method for locating an SQL fault, comprising:

s1, acquiring a first execution result of an execution plan of a current problem SQL sentence;

s2, acquiring a second execution result of a historical execution plan of a historical SQL statement with the same SQL ID as the current problem SQL statement in a historical execution plan table;

s3, comparing the first execution result with the second execution result, and determining the fault reason of the SQL of the current problem.

2. The SQL fault localization method according to claim 1, wherein the step S2 specifically comprises:

s21, acquiring a plurality of first historical SQL sentences which are the same as the SQLID of the current problem SQL sentence in a historical execution schedule;

s22, determining that the execution result in the plurality of first historical SQL sentences is a second historical SQL sentence which is successful in execution and longest in execution time;

s23, if the SQL type of the second historical SQL statement is a non-target type, eliminating the second historical SQL statement from the first historical SQL statement and returning to the step S22;

s24, if the SQL type of the second historical SQL statement is the target type, a second execution result of the historical execution plan corresponding to the second historical SQL statement is called.

3. The method of claim 2, wherein the step S24 specifically includes:

if the SQL type of the second historical SQL statement is the target type and the computing node of the target label exists in the current example, acquiring a second execution result of a historical execution plan corresponding to the second historical SQL statement through the computing node;

if the SQL type of the second historical SQL statement is the target type, but no computing node with the target label exists in the current example, acquiring a second execution result of the historical execution plan through the computing node corresponding to the second historical SQL statement in the full SQL table.

4. The SQL fault localization method according to claim 1, wherein the step S3 specifically comprises:

comparing the first execution result with the second execution result to obtain the historical SQL statement and the table connected process data processed before and after the database;

and determining the fault stage and the fault reason of the current problem SQL according to the process data.

5. An SQL fault location device, comprising:

the first acquisition unit is used for acquiring a first execution result of an execution plan of the SQL statement of the current problem;

the second acquisition unit is used for acquiring a second execution result of a history execution plan of the history SQL sentence which is the same as the SQLID of the current problem SQL sentence in the history execution plan table;

and the fault locating unit is used for comparing the first execution result with the second execution result and determining the fault reason of the SQL of the current problem.

6. The SQL fault location device of claim 5, wherein the second acquisition unit specifically comprises:

the acquisition subunit is used for acquiring a plurality of first historical SQL sentences which are the same as the SQLID of the current problem SQL sentence in the historical execution schedule;

the determining subunit is used for determining that the execution result in the plurality of first historical SQL sentences is a second historical SQL sentence which is successful in execution and has the longest execution time;

the first processing subunit is configured to, if the SQL type of the second historical SQL statement is a non-target type, reject the second historical SQL statement from the first historical SQL statement and skip to the determining unit;

and the second processing subunit is used for calling a second execution result of the history execution plan corresponding to the second history SQL statement if the SQL type of the second history SQL statement is the target type.

7. The SQL fault location device of claim 6, wherein the second processing subunit is specifically configured to:

if the SQL type of the second historical SQL statement is the target type and the computing node of the target label exists in the current example, acquiring a second execution result of a historical execution plan corresponding to the second historical SQL statement through the computing node;

if the SQL type of the second historical SQL statement is the target type, but no computing node with the target label exists in the current example, acquiring a second execution result of the historical execution plan through the computing node corresponding to the second historical SQL statement in the full SQL table.

8. The SQL fault location device of claim 5, wherein the fault location unit is specifically configured to:

comparing the first execution result with the second execution result to obtain the historical SQL statement and the table connected process data processed before and after the database;

and determining the fault stage and the fault reason of the current problem SQL according to the process data.

9. An SQL fault location system, comprising:

the operation and maintenance platform, the computing node and the system instance;

the system instance is used for creating a full SQL table corresponding to the recorded full SQL for each instance;

the computing node is used for issuing an execution plan of the SQL sentence to each data node, receiving an execution result of the SQL sentence, and updating the execution result of the execution plan in a full SQL table corresponding to the SQL sentence in the system instance;

the operation and maintenance platform is used for executing the SQL fault locating method according to any one of claims 1 to 4.

10. An SQL fault location device, the device comprising a processor and a memory:

the memory is used for storing program codes and transmitting the program codes to the processor;

the processor is configured to execute the SQL fault localization method of any one of claims 1-4 according to instructions in the program code.