CN111984501A - Sql tracking method and tool for oracle database - Google Patents

Sql tracking method and tool for oracle database Download PDF

Info

Publication number
CN111984501A
CN111984501A CN202010806440.1A CN202010806440A CN111984501A CN 111984501 A CN111984501 A CN 111984501A CN 202010806440 A CN202010806440 A CN 202010806440A CN 111984501 A CN111984501 A CN 111984501A
Authority
CN
China
Prior art keywords
sql
trace
oracle database
database
view
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN202010806440.1A
Other languages
Chinese (zh)
Inventor
陈静
孙鹏
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Inspur Software Group Co Ltd
Original Assignee
Inspur Software Group Co Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Inspur Software Group Co Ltd filed Critical Inspur Software Group Co Ltd
Priority to CN202010806440.1A priority Critical patent/CN111984501A/en
Publication of CN111984501A publication Critical patent/CN111984501A/en
Pending legal-status Critical Current

Links

Images

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/30—Monitoring
    • G06F11/3003—Monitoring arrangements specially adapted to the computing system or computing system component being monitored
    • G06F11/302—Monitoring arrangements specially adapted to the computing system or computing system component being monitored where the computing system component is a software system
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/30—Monitoring
    • G06F11/3089—Monitoring arrangements determined by the means or processing involved in sensing the monitored data, e.g. interfaces, connectors, sensors, probes, agents
    • G06F11/3093—Configuration details thereof, e.g. installation, enabling, spatial arrangement of the probes
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/30—Monitoring
    • G06F11/34—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment
    • G06F11/3409—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment for performance assessment
    • G06F11/3419—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment for performance assessment by assessing time
    • G06F11/3423—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment for performance assessment by assessing time where the assessed time is active or idle time
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/30—Monitoring
    • G06F11/34—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment
    • G06F11/3466—Performance evaluation by tracing or monitoring
    • G06F11/3476—Data logging
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F2201/00—Indexing scheme relating to error detection, to error correction, and to monitoring
    • G06F2201/80—Database-specific techniques
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F2201/00—Indexing scheme relating to error detection, to error correction, and to monitoring
    • G06F2201/865—Monitoring of software

Abstract

The invention discloses an sql tracking method of an oracle database, which relates to the technical field of data tracking and comprises the following steps: 1) after logging in and connecting an oracle database needing to track sql, backing up a current v $ sql view through sql, and recording the database time at the moment; 2) starting a business operation system, and executing a business operation needing to track sql, for example, executing a certificate query or certificate storage business of a financial management system; 3) after the business operation needing to be tracked is executed, backing up the view of the current database v $ sqlarea again; 4) comparing the corresponding fields of the v $ sqlarea view in the step 3) and the v $ sqlarea view in the step 1), and obtaining all sql and related indexes through the difference value of the corresponding fields in the two views. The invention also discloses an sql tracking tool of the oracle database, which realizes the four steps through a login module, a backup module I, a backup module II and a comparison processing module. The invention can realize the quick tracking of the oracle database sql in a certain service scene and solve the problem of difficult tracking of the oracle database sql.

Description

Sql tracking method and tool for oracle database
Technical Field
The invention relates to the technical field of oracle databases, in particular to an sql tracking method and tool of an oracle database.
Background
In a business software production environment, a plurality of product back ends use various databases, wherein an oracle database is high in proportion, and the database is particularly suitable for large projects. In the using process of a product, various performance problems may be encountered, analysis and research from various aspects are needed to position the encountered problems, wherein one important item is to optimize the database sql, that is, analyze the execution time, cpu occupation, disk reading and writing and the like of the sql to see whether an optimizable space exists.
Compared with other databases, such as microsoft sql trace analysis tool sql profiler and the like, the oracle database authority does not provide a convenient, easy-to-use and quick method for tracing sql in a certain time, so that much time is spent on sql acquisition in the tuning process, multiple operations and multiple analyses are possibly required, and the cost for acquiring the sql is higher. Currently, there are two general ways to obtain oracle sql:
the first method is that the product developer outputs the used sql in the application program and calculates the execution time of the sql. However, the method is not universal, developers need to write output codes additionally, only time consumption of sql can be calculated, and occupation of cpu, disk reading and writing and the like of sql cannot be calculated;
the second method is to use an oracle arw report and generate awr reports for a certain period to obtain sql-related indexes in the report. The conventional method has the disadvantages that the steps for generating awr reports are complicated, time is long especially when multiple capturing and analysis are needed, other indexes in the reports are more, and sql is difficult to find.
Disclosure of Invention
Aiming at the requirements and the defects of the prior art development, the invention provides an sql tracking method and a tool of an oracle database, aiming at realizing the quick and convenient tracking of the sql of the oracle database in a certain period of time.
Firstly, the invention provides an sql tracking method for an oracle database, which adopts the following technical scheme for solving the technical problems:
an sql tracking method of an oracle database, the implementation process of the method comprises:
1) after logging in the oracle database that needs to track sql, the current v $ sql view is backed up by sql, and the database time at this moment is recorded,
2) starting a business operation system, executing the business operation needing to track the sql, such as executing the certificate inquiry or certificate storage business of a financial management system,
3) after the business operation needing to be tracked is finished, the current database v $ sqlarea view is backed up again,
4) comparing the corresponding fields of the v $ sqlarea view in the step 3) and the v $ sqlarea view in the step 1), and obtaining all sql and related indexes through the difference value of the corresponding fields in the two views.
Further, in step 1), create table trace _ start as select from v $ sql is executed, and the entire v $ sql table data at the current time is backed up to the table trace _ start by the sql.
Further, in step 1), select sysdate from dual is executed to assign the time of query to program variable T.
Further, in step 3), create table trace _ end as select from v $ sql area is executed, and the entire v $ sql area table data at this moment is backed up to the table trace _ end.
Furthermore, in the Oracle database, each field data of the v $ sql area view is an accumulated value, and sql executed in the latest period of time and each accumulated index are recorded, and the indexes are not limited to the total execution time and the total times of the sql, the total disk read-write and cpu occupation;
during the step 4):
the sql data after the generation of trace start, i.e. during the execution of the business operation,
and then, comparing the sql _ id fields in the trace _ end and the trace _ start of the table, if the sql fields are the same, subtracting the corresponding index field value in the trace _ start table from the other index field values in the trace _ end of the same sql table, and thus obtaining the total execution time, the total times, the total disk read-write and the indexes occupied by the cpu of all the sqls in the service execution period.
Further preferably, during the step 4), there may be new sql present in trace _ end, i.e. sql _ id present in trace _ end but not present in trace _ start, and then it is necessary to convert the empty value with left join trace _ end and using nvl in data processing sql.
Secondly, the invention provides an sql tracking tool of an oracle database, and the technical scheme adopted for solving the technical problems is as follows:
an sql tracking tool of an oracle database comprises a login module, a first backup module, a second backup module and a comparison processing module,
the login module is used for assisting a user to log in an oracle database connected with sql to be tracked,
the first backup module is used for backing up the current v $ sqlarea view to the table trace _ start through sql, recording the database time at the moment, assigning the inquired time to a program variable T,
starting a service operating system needing to track sql, after a certain service operation is carried out, the backup module is used for backing up a v $ sql view of the current database to a table trace _ end,
the comparison processing module is used for comparing corresponding fields of the v $ sql views backed up by the first backup module and the second backup module and obtaining all sql and related indexes through the difference value of the corresponding fields in the two views.
Furthermore, in an Oracle database, each field data of a v $ sqlarea view is an accumulated value, and sql executed in a recent period of time and each accumulated index are recorded, wherein the indexes are not limited to the total execution time of the sql, the total times, the total disk reading and writing and cpu occupation;
the comparison processing module firstly can obtain sql data after generating trace _ start, namely sql data during executing service operation, by inquiring last _ active _ time > T in the trace _ end table, and then obtain all indexes of the sql, such as total execution time, total times, total disk read-write and cpu occupation, during executing service by comparing trace _ end and sql _ id fields in the trace _ start, if the same, the same sql is obtained, and subtracting corresponding index field values in the trace _ start table from the rest index field values in the trace _ end table of the same sql.
Furthermore, in the process of comparing the sql _ id fields in the table trace _ end and trace _ start by the comparison processing module, there may be a new occurrence of sql in trace _ end, i.e. an occurrence of sql _ id in trace _ end but not in trace _ start, and in this case, it is necessary to convert the lower null value by using left join trace _ end and using nvl in data processing sql.
Compared with the prior art, the sql tracking method and the tool of the oracle database have the following beneficial effects that:
the invention can realize fast tracking of oracle database sql in a certain service scene, including sql execution time, disk read-write and the like, solves the problem of difficulty in tracking oracle database sql, realizes fast acquisition of various indexes of sql and analysis of performance without depending on awr reports, and greatly saves time.
Drawings
FIG. 1 is a flow chart of the tracking method of the present invention;
FIG. 2 is a diagram of a tracking tool framework of the present invention.
The reference information in the drawings indicates:
1. the device comprises a login module 2, backup modules I and 3, backup modules II and 4 and a comparison processing module.
Detailed Description
In order to make the technical scheme, the technical problems to be solved and the technical effects of the present invention more clearly apparent, the following technical scheme of the present invention is clearly and completely described with reference to the specific embodiments.
The first embodiment is as follows:
with reference to fig. 1, this embodiment provides an sql tracking method for an oracle database, where the implementation process of the method includes:
1) after logging in the oracle database connected with sql to be tracked, backing up the current database v $ sql view through sql: creating table trace _ start as select from v $ sqlarea, backing up data contained in the current database v $ sqlarea view to table A, wherein the table A is the table trace _ start; after the backup is complete, the database time at this point is recorded: select sysdate from dual, assigns the time of the query to the program variable T. The specific implementation codes are as follows:
Figure BDA0002629303190000051
2) and starting a business operation system, and executing business operation needing to track the sql, such as executing a certificate inquiry or certificate storage business of a financial management system.
3) After the business operation needing to be tracked is finished, the current database v $ sqlarea view is backed up again: create table trace _ end as select from v $ sqlarea, and the data contained in the entire v $ sqlarea view at this point is backed up to table B, which is table trace _ end. The specific implementation codes are as follows:
Figure BDA0002629303190000061
in an Oracle database, each field data of a v $ sqlarea view is an accumulated value, and sql executed in a recent period of time and each accumulated index are recorded, wherein the indexes are not limited to the total execution time and the total times of the sql, the total disk reading and writing and cpu occupation.
4) Comparing the corresponding fields of the v $ sqlarea view in the step 3) and the v $ sqlarea view in the step 1), and obtaining all sql and related indexes according to the difference value of the corresponding fields in the two views, wherein the specific process is as follows:
4.1) the sql data after the generation of trace start, i.e. the sql data during the execution of the business operation,
4.2) comparing the trace _ end with the sql _ id field in the trace _ start in the table, if the same is the same, the same sql is obtained, and subtracting the corresponding index field value in the trace _ start table from the other index field values in the trace _ end table of the same sql, so as to obtain the total execution time, the total times, the total disk read-write and the indexes occupied by cpu of all sql during the service execution period.
During the execution of step 4), there may be new sql present in trace _ end, i.e. sql _ id present in trace _ end but not present in trace _ start, at which time the left join trace _ end needs to be used in data processing sql and nvl is used to convert the empty value.
The specific implementation code of the step 4) is as follows:
Figure BDA0002629303190000062
Figure BDA0002629303190000071
example two:
referring to fig. 1 and 2, the present embodiment provides an sql tracking tool for an oracle database, which includes a login module 1, a backup module i 2, a backup module ii 3, and a comparison processing module 4.
The login module 1 is used for assisting a user to log in an oracle database connected with sql to be tracked.
The first backup module 2 is used for backing up the current v $ sqlarea view to the table trace _ start through sql, recording the database time at the moment, and assigning the inquired time to the program variable T.
And starting a service operating system needing to track sql, performing a certain service operation, for example, executing a certificate query or certificate storage service of the financial management system, and after the execution is completed, a backup module II 3 is used for backing up a view of the current database v $ sql to a table trace _ end.
The comparison processing module 4 is used for comparing corresponding fields of the v $ sql views backed up by the backup module one 2 and the backup module two 3, and obtaining all sql and related indexes through corresponding field differences in the two views.
In an Oracle database, each field data of a v $ sqlarea view is an accumulated value, and sql executed in a recent period of time and each accumulated index are recorded, wherein the indexes are not limited to the total execution time and the total times of the sql, the total disk reading and writing and cpu occupation.
In this embodiment, the comparison processing module 4 firstly queries last _ active _ time > T in the trace _ end table to obtain sql data after generating the trace _ start, that is, sql data during executing the service operation, and then obtains the total execution time, the total number of times, the total disk read-write, and each index occupied by cpu of all sql during the service execution by subtracting the corresponding index field value in the trace _ start table from the other index field values in the trace _ end table of the same sql, where the same is the same as the sql field in the trace _ end and the sql _ id field in the trace _ start table.
In the process of comparing the sql _ id fields in the table trace _ end and trace _ start by the comparison processing module 4, there may be a new sql appearing in the trace _ end, that is, a new sql _ id appearing in the trace _ end but not appearing in the trace _ start, at this time, it is necessary to convert the lower null value by using left join trace _ end and using nvl in the data processing sql.
In summary, the method and the tool for tracking the oracle database sql can realize the fast tracking of the oracle database sql in a certain service scene, including sql execution time, disk reading and writing and the like, and solve the problem of difficulty in tracking the oracle database sql.
The principles and embodiments of the present invention have been described in detail using specific examples, which are provided only to aid in understanding the core technical content of the present invention. Based on the above embodiments of the present invention, those skilled in the art should make any improvements and modifications to the present invention without departing from the principle of the present invention, and therefore, the present invention should fall into the protection scope of the present invention.

Claims (9)

1. An sql tracking method for an oracle database is characterized in that the implementation process of the method comprises the following steps:
1) after logging in the oracle database that needs to track sql, the current v $ sql view is backed up by sql, and the database time at this moment is recorded,
2) starting a business operation system, executing the business operation needing to track the sql,
3) after the business operation needing to be tracked is finished, the current database v $ sqlarea view is backed up again,
4) comparing the corresponding fields of the v $ sqlarea view in the step 3) and the v $ sqlarea view in the step 1), and obtaining all sql and related indexes through the difference value of the corresponding fields in the two views.
2. The method as claimed in claim 1, wherein in step 1), create table trace _ start as select from v $ sqlarea is executed, and the entire v $ sqlarea table data at the current time is backed up to table trace _ start through the sql.
3. The method as claimed in claim 2, wherein in step 1), a select sysdate from dual is executed to assign the time of query to the program variable T.
4. The method for tracking sql of an oracle database as claimed in claim 3, wherein in step 3), create table trace _ end as select from v $ sql is executed, and the entire v $ sql area table data at this moment is backed up to table trace _ end.
5. The sql tracking method of the Oracle database according to claim 4, wherein in the Oracle database, each field data of the v $ sql area view is an accumulated value, and the executed sql in the latest period and each accumulated index are recorded, and the indexes are not limited to the total execution time, the total times, the total disk read-write and cpu occupation of the sql;
during the step 4):
the sql data after the generation of trace start, i.e. during the execution of the business operation,
and then, comparing the sql _ id fields in the trace _ end and the trace _ start of the table, if the sql fields are the same, subtracting the corresponding index field value in the trace _ start table from the other index field values in the trace _ end of the same sql table, and thus obtaining the total execution time, the total times, the total disk read-write and the indexes occupied by the cpu of all the sqls in the service execution period.
6. The method for tracking sql of an oracle database as claimed in claim 5, wherein during the step 4), there may be new sql occurring in trace _ end, that is, sql _ id occurring in trace _ end but not occurring in trace _ start, and when it is necessary to convert the lower empty value with left join trace _ end and using nvl in data processing sql.
7. An sql tracking tool of an oracle database is characterized by comprising a login module, a first backup module, a second backup module and a comparison processing module,
the login module is used for assisting a user to log in an oracle database connected with sql to be tracked,
the first backup module is used for backing up the current v $ sqlarea view to the table trace _ start through sql, recording the database time at the moment, assigning the inquired time to a program variable T,
starting a service operating system needing to track sql, after a certain service operation is carried out, the backup module is used for backing up a v $ sql view of the current database to a table trace _ end,
the comparison processing module is used for comparing corresponding fields of the v $ sql views backed up by the first backup module and the second backup module and obtaining all sql and related indexes through the difference value of the corresponding fields in the two views.
8. The sql tracking tool of the Oracle database as claimed in claim 7, wherein in the Oracle database, each field data of the v $ sql area view is an accumulated value, and the executed sql and each accumulated index in the latest period are recorded, and the indexes are not limited to the total execution time, the total number of times, the total disk read-write and cpu occupation of the sql;
the comparison processing module firstly can obtain sql data after generating trace _ start, namely sql data during executing service operation, by inquiring last _ active _ time > T in the trace _ end table, and then obtains all indexes of the sql, such as total execution time, total times, total disk read-write and cpu occupation during service execution, by comparing trace _ end and sql _ id fields in the trace _ start, if the same, the same sql is obtained, and subtracting corresponding index field values in the trace _ start table from the rest index field values in the trace _ end table of the same sql.
9. The tool of claim 8, wherein in the process of comparing the sql _ id fields in the tables trace _ end and trace _ start, there may be a new occurrence of sql in trace _ end, that is, a new occurrence of sql _ id in trace _ end but not in trace _ start, when the empty value needs to be converted by left join trace _ end and nvl in data processing sql.
CN202010806440.1A 2020-08-12 2020-08-12 Sql tracking method and tool for oracle database Pending CN111984501A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202010806440.1A CN111984501A (en) 2020-08-12 2020-08-12 Sql tracking method and tool for oracle database

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202010806440.1A CN111984501A (en) 2020-08-12 2020-08-12 Sql tracking method and tool for oracle database

Publications (1)

Publication Number Publication Date
CN111984501A true CN111984501A (en) 2020-11-24

Family

ID=73433896

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202010806440.1A Pending CN111984501A (en) 2020-08-12 2020-08-12 Sql tracking method and tool for oracle database

Country Status (1)

Country Link
CN (1) CN111984501A (en)

Similar Documents

Publication Publication Date Title
CN107247808B (en) Distributed NewSQL database system and picture data query method
CN110019218B (en) Data storage and query method and equipment
CN108694195B (en) Management method and system of distributed data warehouse
CN101719149B (en) Data synchronization method and device
CN110489699B (en) Asynchronous data acquisition method and system
CN105446991A (en) Data storage method, query method and device
CN111382226A (en) Database query retrieval method and device and electronic equipment
GB2507094A (en) Selecting and loading a subset of data from a database backup
CN103064933A (en) Data query method and system
CN111259004B (en) Method for indexing data in storage engine and related device
Yang et al. F1 Lightning: HTAP as a Service
CN110837520A (en) Data processing method, platform and system
CN110134663B (en) Organization structure data processing method and device and electronic equipment
US20030033291A1 (en) SQL execution analysis
CN104657387A (en) Data query method and device
CN114201505A (en) Data query method and device and database system
CN111858730A (en) Data importing and exporting device, method, equipment and medium of graph database
US20130041887A1 (en) Adding entries to an index based on use of the index
CN111694853B (en) Data increment collection method and device based on lineage, storage medium and electronic equipment
CN110825744B (en) Cluster environment-based air quality monitoring big data partition storage method
CN104317820B (en) Statistical method and device for report forms
KR20220011184A (en) Incremental data comparison implementation system and method
CN111984501A (en) Sql tracking method and tool for oracle database
CN111984625B (en) Database load characteristic processing method and device, medium and electronic equipment
CN115114297A (en) Data lightweight storage and search method and device, electronic equipment and storage medium

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination