I am going to add a performance monitoring report function to JDBMonitor. In "report condition", enter the start time, end time, and unit time for statistics, and click query. The program collects statistics from the table T_LOG_SQLLog recorded by DataBaseDBListener and displays the report. For example, you can enter "18:00:00" in the start time and "12:00:00" in the end time, select "5 minutes" in the time unit, and click "query ". The program filters out records with FbeginTime greater than or equal to "18:00:00" and less than or equal to "12:00:00" in T_LOG_SQLLog. Then, the number of records (that is, the number of database executions) is recorded every five minutes and displayed as a report in a time sequence diagram.
For example:
Fid ftime
1 2006-05-05 10:00:00. 000
2 2006-05-05 10:00:01. 000
3 2006-05-05 10:00:10. 000
4 2006-05-05 10:01:10. 000
5 2006-05-05 10:01:20. 000
Such data is discrete data, because the execution time of the database is uncertain. Data Mining is often required to count the number of occurrences of the database. Data Mining is usually implemented by different databases, and JDBMonitor is cross-database. Therefore, we certainly cannot use the unique features of these databases. There are also some open-source cross-Database Data Mining engines, but the size is huge and must be more than 50 MB. JDBMonitor is a small tool. To facilitate deployment and use, a basic principle of JDBMontior is to avoid using classes other than JDK as much as possible, so they must not be used. After careful consideration, I found that the following skills can be used to solve the problem:
Select count (sub. f) as exeCount, min (sub. FBeginTime) as FTime from (
Select cast (DateDiff (ss ,?, FBeginTime )/? As int) as f, FBeginTime from T_LOG_SQLLog
) As sub
Where 1 = 1
And FBeginTime >=?
And FBeginTime <=?
Group by sub. f
Order by sub. f ASC
The first and third parameters are the start time of the selected time period, the second parameter is the selected time interval (in seconds), and the fourth parameter is the end time of the selected time period.
I used the division technique to achieve this effect.
FBeginTime is the start time of SQL statement execution (Here we regard it as the execution time of SQL statements), cast (DateDiff (ss ,?, FBeginTime )/? As int) as f indicates the number of seconds between the execution time of SQL statements and the start time of the selected time period, the time interval selected by the user is then divisible (in seconds ). After division, the data in the same period of time is the same. Then we have a groupby and a count (sub. f), so that the number of SQL statements executed in each period of time comes out.
For example, if the input start time is 10:00:00. 000, the end time is 10:02:00. 000.
So select cast (DateDiff (ss ,?, FBeginTime )/? As int) as f, the result set of FBeginTime from T_LOG_SQLLog is:
F FBeginTime
0 2006-05-05 10:00:00. 000
0 2006-05-05 10:00:01. 000
0 2006-05-05 10:00:10. 000
1 2006-05-05 10:01:00. 000
1 2006-05-05 10:01:00. 000
Then, f is used as the grouping condition to summarize and count the count of f, so that the number of SQL statements executed in each time period comes out:
3 2006-05-05 10:00:00. 000
2 2006-05-05 10:01:00. 000